Retail Management | Skillpundit | India's No.1 | Learning

Tables Overview

S.No	Table Name	No. of Fields	Demo Data
S.No	Table Name	No. of Fields	Create	Insert
1	banks	4	Create	Insert
2	customers	8	Create	Insert
3	customer_bank_details	9	Create	Insert
4	items	6	Create	Insert
5	payments	9	Create	Insert
6	payment_type	4	Create	Insert
7	purchase_items	9	Create	Insert
8	suppliers	8	Create	Insert
9	supplier_bank_details	9	Create	Insert
10	category	4	Create	Insert
11	sub_category	5	Create	Insert
12	stock	7	Create	Insert
13	sales	5	Create	Insert

Table Structure: banks

Field Name	Data Type
Field Name	Id	int
bank_name	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "banks"

CREATE TABLE banks (
id int,
bank_name varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 4 records in "banks"

INSERT INTO banks (id, bank_name, created_at, updated_at) VALUES
( 1, 'Statebankofindia', '2020-12-14 10:46:56', '2020-12-14 10:46:56'),
( 2, 'icici', '2020-12-14 10:46:56', '2020-12-14 10:46:56'),
( 3, 'unionbank', '2020-12-14 10:46:56', '2020-12-14 10:46:56');

Table Structure: customers

Field Name	Data Type
Field Name	Id	int
customer_id	varchar
name	varchar
address	varchar
mobile	varchar
alternate_mobile	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "customers"

CREATE TABLE customers (
id int,
customer_id varchar(100),
name varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 8 records in "customers"

INSERT INTO customers (id, customer_id, name, address, mobile, alternate_mobile, created_at, updated_at) VALUES
(1, 'c1', 'dharma teja', 'hyderabad', '2587413698', '5478963210', '2020-12-14 10:50:22', '2020-12-14 10:50:22'),
(2, 'c2', 'sailesh', 'pune', '9517536541', '3698521475', '2020-12-14 10:50:22', '2020-12-14 10:50:22'),
(3, 'c3', 'narendra', 'delhi', '1593574569', '1258743698', '2020-12-14 10:50:22', '2020-12-14 10:50:22');

Table Structure: customer_bank_details

Field Name	Data Type
Field Name	Id	int
customer_id	varchar
bank_name	varchar
account_number	varchar
ifsc_code	varchar
branch_code	varchar
branch	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "customer_bank_details"

CREATE TABLE customer_bank_details (
id int,
customer_id varchar(100),
bank_name varchar(100),
account_number varchar(100),
ifsc_code varchar(100),
branch_code varchar(100),
branch varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 9 records in "customer_bank_details"

INSERT INTO customer_bank_details (id, customer_id, bank_name, account_number, ifsc_code, branch_code, branch, created_at, updated_at) VALUES
(1, 'c1', '1', '65412548745', 'sb0258', '001', 'mushirabad', '2020-12-14 10:52:45', '2020-12-14 10:52:45'),
(2, 'c2', '2', '200058736954', 'icici02698', '0002', 'ameerpet', '2020-12-14 10:52:45', '2020-12-14 10:52:45'),
(3, 'c3', '3', '35715964528', 'un0255588', '003', 'khairatabad', '2020-12-14 10:52:45', '2020-12-14 10:52:45');

Table Structure: items

Field name	Data type
Field name	Id	int
category	varchar
subcategory	varchar
item_name	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "items"

CREATE TABLE items (
id int,
category varchar(100),
sub_category varchar(100),
item_name varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 6 records in "items"

INSERT INTO items (id, category, sub_category, item_name, created_at, updated_at) VALUES
(1, '1', '1', 'freedom refind oil', '2020-12-14 11:00:02', '2020-12-14 11:00:02'),
(2, '2', '2', 'lifeboy', '2020-12-14 11:00:02', '2020-12-14 11:00:02'),
(3, '3', '3', 'dailymilk_nuts', '2020-12-14 11:00:02', '2020-12-14 11:00:02');

Table Structure: payments

Field name	Data type
Field name	Id	int
payment_id	varchar
payment_type	varchar
supplier_id	varchar
bank	varchar
account_number	varchar
ifsc_code	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "payments"

CREATE TABLE payments (
id int,
payment_id varchar(100),
payment_type varchar(100),
supplier_id varchar(100),
bank varchar(100),
account_number varchar(100),
ifsc varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 9 records in "payments"

INSERT INTO payments (id, payment_id, payment_type, supplier_id, bank, account_number, ifsc, created_at, updated_at) VALUES
(1, 'p1', '1', '1', '-', '-', '-', '2020-12-14 11:02:29', '2020-12-14 11:02:29'),
(2, 'p2', '2', '2', 'icici', '35715969854', 'icici2558', '2020-12-14 11:02:30', '2020-12-14 11:02:30'),
(3, 'p3', '2', '3', 'unionbank', '15987452145', 'un85247', '2020-12-14 11:02:30', '2020-12-14 11:02:30');

Table Structure: payment_type

Field name	Data type
Field name	Id	int
payment_type	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "payment_type"

CREATE TABLE payment_type (
id int,
payment_type varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 4 records in "payment_type"

INSERT INTO payment_type (id, payment_type, created_at, updated_at) VALUES
(1, 'cash', '2020-12-14 11:03:05', '2020-12-14 11:03:05'),
2, 'bank', '2020-12-14 11:03:05', '2020-12-14 11:03:05'),

Table Structure: purchase_items

Field name	Data type
Field name	Id	int
customer_id	varchar
item_name	varchar
prize	varchar
quantity	varchar
date	date
payment_type	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "purchase_items"

CREATE TABLE purchase_items (
id int,
customer_id varchar(100),
item_name varchar(100),
prize varchar(100),
quantity varchar(100),
date date,
payment_type varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 9 records in "purchase_items"

INSERT INTO purchase_items (id, customer_id, item_name, prize, quantity, date, payment_type, created_at, updated_at) VALUES
(1, 'c1', 'freedom oil', '130', '5', '2020-05-03', '1', '2020-12-14 11:25:38', '2020-12-14 11:25:38'),
(2, 'c2', 'lifeboy', '55', '3', '2020-05-12', '1', '2020-12-14 11:25:38', '2020-12-14 11:25:38'),
(3, 'c3', 'dailymilk nuts', '75', '5', '2020-11-15', '1', '2020-12-14 11:25:38', '2020-12-14 11:25:38');

Table Structure: suppliers

Field name	Data type
Field name	Id	int
supplier_id	varchar
name	varchar
address	varchar
mobile	varchar
alternate_mobile	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "suppliers"

CREATE TABLE suppliers (
id int,
supplier_id varchar(100),
name varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 8 records in "suppliers"

INSERT INTO suppliers (id, supplier_id, name, address, mobile, alternate_mobile, created_at, updated_at) VALUES
(1, 's1', 'newoilcompany', 'hyderabad', '7536984512', '5896471236', '2020-12-14 11:42:40', '2020-12-14 11:42:40'),
(2, 's2', 'trendysoapcompany', 'mumbai', '2587413698', '3571486352', '2020-12-14 11:42:40', '2020-12-14 11:42:40'),
(3, 's3', 'tastymilk', 'kerala', '8574963512', '3698521474', '2020-12-14 11:42:40', '2020-12-14 11:42:40');

Table Structure: supplier_bank_details

Field name	Data type
Field name	Id	int
supplier_id	varchar
bank_name	varchar
bank_account_number	varchar
ifsc_code	varchar
branch_code	varchar
branch	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "supplier_bank_details"

CREATE TABLE supplier_bank_details (
id int,
supplier_id varchar(100),
bank_name varchar(100),
bank_account_number varchar(100),
ifsc_code varchar(100),
branch varchar(100),
branch_code varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 9 records in "supplier_bank_details"

INSERT INTO supplier_bank_details (id, supplier_id, bank_name, bank_account_number, ifsc_code, branch, branch_code, created_at, updated_at) VALUES
(1, 's1', '1', '36985471254', 'sbi5698', 'khairatabad', 'sb5698', '2020-12-14 11:45:19', '2020-12-14 11:45:19'),
(2, 's2', '2', '554785215486', 'icici358', 'ameerpet', 'icici555698', '2020-12-14 11:45:19', '2020-12-14 11:45:19'),
(3, 's3', '3', '998547125642', 'un55885', 'punjagutta', 'unn58745', '2020-12-14 11:45:20', '2020-12-14 11:45:20');

Table Structure: category

Field name	Data type
Field name	Id	int
category_name	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "category"

CREATE TABLE category (
id int,
category_name varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 4 records in "category"

INSERT INTO category (id, category_name, created_at, updated_at) VALUES
(1, 'oil', '2020-12-14 10:47:40', '2020-12-14 10:47:40'),
(2, 'soaps', '2020-12-14 10:47:40', '2020-12-14 10:47:40'),
(3, 'chocalates', '2020-12-14 10:47:40', '2020-12-14 10:47:40');

Table Structure: sub_category

Field name	Data type
Field name	Id	int
category_id	varchar
sub_category	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "sub_category"

CREATE TABLE sub_category (
id int,
category_id varchar(100),
sub_category varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 5 records in "sub_category"

INSERT INTO sub_category (id, category_id, sub_category, created_at, updated_at) VALUES
(1, '1', 'refind oil', '2020-12-14 10:58:37', '2020-12-14 10:59:07'),
(2, '2', 'lifeboy', '2020-12-14 10:58:37', '2020-12-14 10:58:37'),
(3, '3', 'dairymilk', '2020-12-14 10:58:37', '2020-12-14 10:58:37');

Table Structure: stock

Field name	Data type
Field name	Id	int
item_name	varchar
stock	varchar
category	varchar
sub_category	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "stock"

CREATE TABLE stock (
id int,
item_name varchar(100),
stock varchar(100),
category_id varchar(100),
sub_category varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 7 records in "stock"

INSERT INTO stock (id, item_name, stock, category_id, sub_category, created_at, updated_at) VALUES
(1, 'freedom', '100', '1', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(2, 'lifeboy', '500', '2', '2', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(3, 'dairymilk_nuts', '500', '3', '3', '0000-00-00 00:00:00', '0000-00-00 00:00:00');

Table Structure: sales

Field name	Data type
Field name	Id	int
payment_id	varchar
date	date
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "sales"

CREATE TABLE sales (
id int,
payment_id varchar(100),
date date,
created_at datetime ,
updated_at datetime );

Following SQL inserts 5 records in "sales"

INSERT INTO sales (id, payment_id, date, created_at, updated_at) VALUES
(1, 'p1', '2020-12-14', '2020-12-14 11:38:52', '2020-12-14 11:38:52'),
(2, 'p2', '2020-12-25', '2020-12-14 11:38:52', '2020-12-14 11:38:52'),
(3, 'p3', '2020-12-30', '2020-12-14 11:38:53', '2020-12-14 11:38:53');

Questions

1. Display the payment types?

A. SELECT FROM `payment_type`

2. Dispaly the item names?

A. SELECTi.id,i.item_name from items i

3. Display the stock of the items?

A. SELECT s.id,s.item_name,s.stock from stock s

4. In which category the item is related to?

A. SELECT i.id,c.category_name,i.item_name from items i left join category c on i.category=c.id

5. Dispaly the bank details?

A. SELECT * FROM `banks`

6. How much quantity of items we purchased?

A. SELECTpi.id,pi.item_name,pi.prize,pi.quantity from purchase_items pi

7. Display the payment details based on payment id?

A. SELECT p.id,p.payment_id,p.bank,p.account_number,p.ifsc from payments p

8. Display the subcategory names based on category id?

A. SELECT sc.id,c.category_name,sc.sub_category from sub_category sc left join category c on sc.category_id=c.id

9. In which payment type the supplier paid the amount?

A. SELECT p.id,p.payment_id,pt.payment_type,p.bank,p.account_number,p.ifsc from payments p left join payment_type pt on p.payment_type=pt.id

10. Display the date time of the suppliers record?

A. SELECT * FROM suppliers where DATE(created_at) = DATE('2020-12-14')

11. Dispaly the supplier details?

A. SELECT s.id,s.name,s.address,s.mobile,s.alternate_mobile,sb.bank_name,sb.bank_account_number,sb.
ifsc_code,sb.branch,sb.branch_code from suppliers s left join supplier_bank_details sb on s.supplier_id=sb.supplier_i

12. Display the customer details?

A. SELECT c.id,c.name,c.address,c.mobile,c.alternate_mobile,cb.bank_name,cb.account_number,cb.
ifsc_code,cb.branch_code,cb.branch from customers c left join customer_bank_details cb on c.customer_id=cb.customer_id

13. Display the customer bank details based on customer id?

A. SELECT c.id,c.name,c.address,c.mobile,c.alternate_mobile,cb.bank_name,cb.account_number,cb.
ifsc_code,cb.branch_code,cb.branch from customers c left join customer_bank_details cb on c.customer_id=cb.customer_id where c.customer_id="c1"

14. Display the supplier bank details based on supplier id?

A. SELECT s.id,s.name,s.address,s.mobile,s.alternate_mobile,sb.bank_name,sb.bank_account_number,sb.
ifsc_code,sb.branch,sb.branch_code from suppliers s left join supplier_bank_details sb on s.supplier_id=sb.supplier_id where s.supplier_id="s1"

15. For which supplier the payment has done?

A. SELECT p.payment_id,pt.payment_type,s.name,p.bank,p.account_number,p.ifsc from payments p left join suppliers s on p.supplier_id=s.id LEFT join payment_type pt on p.payment_type=pt.id

16. Display the bank account number of the particular customer?

A. SELECT c.id,c.name,c.address,c.mobile,c.alternate_mobile,cb.bank_name,cb.account_number,cb.
ifsc_code,cb.branch_code,cb.branch from customers c LEFT join customer_bank_details cb on c.customer_id=cb.customer_id where c.customer_id="c1"

Web Tutorials

</> SQL Tutorial

Retail Management Project

Database Name: retail_management

Practical Questions