DBMS Assignment(Retailer)
Name:- Deepak Kumar 2023ugcs004
Schema:
database retailer;
use retailer;
create table brands(
brand_id varchar(60) primary key,
brand_name varchar(500) not null
);
create table product_type (
type_id varchar(20) primary key,
type_name varchar(100) not null,
parent_id varchar(20),
foreign key(parent_id) references
product_type(type_id)
);
create table vendor(
vendor_id varchar(60) primary key,
vendor_name varchar(50) not null,
mobile numeric(10,0) not null
);
create table product(
product_id varchar(50) primary key,
upc varchar(50) not null,
product_name varchar(100) not null,
cost_price numeric(12,0),
sell_price numeric(12,0),
mf_date date,
exp_date date,
brand_id varchar(60) not null,
vendor_id varchar(60) not null,
foreign key(brand_id) references
brands(brand_id),
foreign key(vendor_id) references
vendor(vendor_id)
);
create table customer(
customer_id varchar(60) primary key,
customer_name varchar(100) not null,
customer_type varchar(100),
phone numeric(10,0),
email varchar(60),
address varchar(60),
city varchar(50),
pin numeric(6,0)
);
create table store(
store_id varchar(60) primary key,
store_name varchar(60),
address varchar(100) not null,
city varchar(60) not null,
pin numeric(6,0) not null
);
create table inventory(
store_id varchar(60),
product_id varchar(60),
quantity numeric(4,0),
primary key(store_id, product_id),
foreign key(store_id) references
store(store_id),
foreign key(product_id) references
product(product_id)
);
create table bill(
bill_no int primary key,
store_id varchar(60) not null,
product_id varchar(60) not null,
sell_price numeric(10,0) not null,
discount numeric(7,0) not null,
final_price numeric(10,0) not null,
bill_date date not null,
foreign key(store_id) references
store(store_id),
foreign key(product_id) references
product(product_id)
);
create table customer_bill(
customer_id varchar(60) not null,
bill_no int,
primary key(customer_id, bill_no),
foreign key(customer_id) references
customer(customer_id),
foreign key(bill_no) references bill(bill_no)
);
insert into brands values
('b001', 'Patanjali'),
('b002', 'Britannia'),
('b003', 'Parle'),
('b004', 'Haldiram'),
('b005', 'Amul');
insert into product_type (type_id,
type_name, parent_id) values
('t00', 'All Products', null),
('t01', 'Personal Care', null),
('t02', 'Food & Beverages', null),
('t03', 'Electronics', null),
('t04', 'Home Essentials', null);
insert into vendor values
('v001', 'Sharma Traders', 9876543210),
('v002', 'Gupta Distributors', 9123456789),
('v003', 'Joshi Enterprises', 9988776655),
('v004', 'Rao Wholesale', 9112233445),
('v005', 'Verma Foods', 9001122334);
insert into product values
('p001', '123456789012', 'Patanjali Dant
Kanti', 30, 45, '2024-01-01', '2026-01-01',
'b001', 'v001'),
('p002', '987654321098', 'Britannia Good Day
100g', 10, 15, '2024-02-01', '2025-02-01',
'b002', 'v002'),
('p003', '111122223333', 'Parle-G 500g', 25,
35, '2024-01-15', '2025-12-15', 'b003', 'v003'),
('p004', '444455556666', 'Haldiram Bhujia
1kg', 120, 150, '2023-12-10', '2025-12-10',
'b004', 'v004'),
('p005', '777788889999', 'Amul Butter 500g',
170, 210, '2024-03-01', '2025-03-01', 'b005',
'v005');
insert into customer values
('c001', 'Aarav Mehta', 'Regular', 9998887776,
'aarav.mehta@gmail.com', '12A MG Road',
'Delhi', 110001),
('c002', 'Ishita Kapoor', 'Premium',
8887776665, 'ishita.kapoor@gmail.com', '45B
Park Street', 'Mumbai', 400001),
('c003', 'Rohan Verma', 'Regular',
7776665554, 'rohan.verma@gmail.com', '101
Green Lane', 'Lucknow', 226001),
('c004', 'Pooja Joshi', 'Premium', 9665544332,
'pooja.joshi@gmail.com', '10A Cyber City',
'Gurgaon', 122001),
('c005', 'Kunal Singh', 'Regular', 9785642310,
'kunal.singh@gmail.com', '75 Sector 21',
'Chandigarh', 160022);
insert into store values
('s001', 'Retail Mart - Delhi', 'Connaught
Place', 'Delhi', 110001),
('s002', 'Retail Mart - Mumbai', 'Bandra West',
'Mumbai', 400050),
('s003', 'Retail Mart - Lucknow', 'Hazratganj',
'Lucknow', 226001),
('s004', 'Retail Mart - Gurgaon', 'Sector 29',
'Gurgaon', 122001),
('s005', 'Retail Mart - Chandigarh', 'Sector 17',
'Chandigarh', 160022);
insert into inventory values
('s001', 'p001', 100),
('s001', 'p002', 80),
('s002', 'p003', 60),
('s003', 'p004', 70),
('s004', 'p005', 50);
insert into bill values
(101, 's001', 'p001', 45, 5, 40, '2025-04-07'),
(102, 's001', 'p002', 15, 0, 15, '2025-04-07'),
(103, 's002', 'p003', 35, 3, 32, '2025-04-06'),
(104, 's003', 'p004', 150, 20, 130, '2025-04-
05'),
(105, 's004', 'p005', 210, 10, 200, '2025-04-
05');
insert into customer_bill values
('c001', 101),
('c002', 102),
('c003', 103),
('c004', 104),
('c005', 105);
-- question1
SELECT
st.store_name,
pr.product_name,
COUNT(b.bill_no) AS total_sales
FROM
bill b
JOIN
store st ON b.store_id = st.store_id
JOIN
product pr ON b.product_id = pr.product_id
GROUP BY
st.store_name, pr.product_name
ORDER BY
st.store_name, total_sales DESC
LIMIT 20;
-- question2
ALTER TABLE store ADD COLUMN state
VARCHAR(60);
UPDATE store SET state = 'Delhi' WHERE
store_id = 's001';
UPDATE store SET state = 'Maharashtra'
WHERE store_id = 's002';
UPDATE store SET state = 'Uttar Pradesh'
WHERE store_id = 's003';
UPDATE store SET state = 'Haryana' WHERE
store_id = 's004';
UPDATE store SET state = 'Chandigarh' WHERE
store_id = 's005';
SELECT
s.state,
p.product_name,
COUNT(b.bill_no) AS total_sales
FROM
bill b
JOIN
store s ON b.store_id = s.store_id
JOIN
product p ON b.product_id = p.product_id
GROUP BY
s.state, p.product_name
ORDER BY
s.state, total_sales DESC
LIMIT 20;
-- question 3
SELECT
s.store_name,
COUNT(b.bill_no) AS total_sales
FROM
bill b
JOIN
store s ON b.store_id = s.store_id
WHERE
YEAR(b.bill_date) = YEAR(CURDATE())
GROUP BY
s.store_name
ORDER BY
total_sales DESC
LIMIT 5;
-- 4
SELECT s.store_name, COUNT(*) AS
total_sales
FROM bill b
JOIN store s ON b.store_id = s.store_id
WHERE YEAR(b.bill_date) =
YEAR(CURRENT_DATE())
GROUP BY s.store_id, s.store_name
ORDER BY total_sales DESC
LIMIT 5;
-- 5
SELECT pt.type_name, COUNT(*) AS
type_count
FROM bill b1
JOIN product p1 ON b1.product_id =
p1.product_id
JOIN bill b2 ON b1.bill_no = b2.bill_no
JOIN product p2 ON b2.product_id =
p2.product_id
JOIN product_type pt ON p2.product_id =
pt.type_id
WHERE p1.product_name = 'Amul Milk'
AND p2.product_name != 'Amul Milk'
GROUP BY pt.type_name
ORDER BY type_count DESC
LIMIT 3;
