Inventory Control 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	balance	5	Create	Insert
2	banks	4	Create	Insert
3	category	4	Create	Insert
4	customers	8	Create	Insert
5	payments	4	Create	Insert
6	products	7	Create	Insert
7	purchase	8	Create	Insert
8	sales	6	Create	Insert
9	stock	7	Create	Insert
10	sub_category	5	Create	Insert
11	suppliers	8	Create	Insert
12	suppliers_bank_details	7	Create	Insert
13	transactions	7	Create	Insert

Table Structure: balance

Field Name	Data Type
Field Name	Id	int
transction_id	varchar
balance	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "balance"

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

Following SQL inserts 5 records in "balance"

INSERT INTO balance (id, transaction_id, balance, created_at, updated_at) VALUES
(1, 't1', '1000', '2020-12-14 18:50:10', '2020-12-14 18:50:10'),
(2, 't2', '0', '2020-12-14 18:50:10', '2020-12-14 18:50:10');

Table Structure: banks

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

Following SQL creates a new table called "banks"

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

Following SQL inserts 4 records in "banks"

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

Table Structure: category

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

Following SQL creates a new table called "category"

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

Following SQL inserts 4 records in "category"

INSERT INTO category (id, category, created_at, updated_at) VALUES
(1, 'agriculture products', '2020-12-14 18:20:26', '2020-12-14 18:20:26'),
(2, 'food products', '2020-12-14 18:20:27', '2020-12-14 18:20:27'),
(3, 'choclates', '2020-12-14 18:20:27', '2020-12-14 18:20:27');

Table Structure: customers

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

Following SQL creates a new table called "customers"

CREATE TABLE customers (
id int,
cusotmer_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, cusotmer_id, name, address, mobile, alternate_mobile, created_at, updated_at) VALUES
(1, 'c1', 'jagadeesh', 'mumbai', '7524588745', '3658745896', '2020-12-14 18:22:31', '2020-12-14 18:22:31'),
(2, 'c2', 'naresh', 'vizag', '3598644587', '6589655475', '2020-12-14 18:22:31', '2020-12-14 18:22:31'),
(3, 'c3', 'nakul', 'hyderabad', '3658455874', '3578966542', '2020-12-14 18:22:31', '2020-12-14 18:22:31');

Table Structure: payments

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

Following SQL creates a new table called "payments"

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

Following SQL inserts 4 records in "payments"

INSERT INTO payments (Id, payment_type, created_at, updated_at) VALUES
(1, 'cash', '2020-12-14 18:47:25', '2020-12-14 18:47:25'),
(2, 'bank', '2020-12-14 18:47:25', '2020-12-14 18:47:25');

Table Structure: products

Field name	Data type
Field name	Id	int
product_id	varchar
category_id	varchar
subcategory	varchar
product_name	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "products"

CREATE TABLE products (
id int,
product_id varchar(100),
catgeory_id varchar(100),
subcategory_id varchar(100),
product_name varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 7 records in "products"

INSERT INTO products (id, product_id, catgeory_id, subcategory_id, product_name, created_at, updated_at) VALUES
(1, 'p1', '1', '1', 'naturalgrowth', '2020-12-14 18:26:20', '2020-12-14 18:26:20'),
(2, 'p2', '2', '2', 'kamalrice', '2020-12-14 18:26:20', '2020-12-14 18:26:20'),
(3, 'p3', '3', '3', 'dairymilknuts', '2020-12-14 18:26:20', '2020-12-14 18:26:20');

Table Structure: purchase

Field name	Data type
Field name	Id	int
product_id	varchar
supplier_id	varchar
purchase_date	date
quantity	varchar
order_date	date
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "purchase"

CREATE TABLE payments (
id int,
product_id varchar(100),
supplier_id varchar(100),
purchase_date varchar(100),
quantity varchar(100),
order_date date,
created_at datetime ,
updated_at datetime );

Following SQL inserts 8 records in "purchase"

INSERT INTO purchase (id, product_id, supplier_id, purchase_date, quantity, order_date, created_at, updated_at) VALUES
(1, 'p1', 's1', '2020-05-10', '500', '2020-05-05', '2020-12-14 18:29:07', '2020-12-14 18:29:07'),
(2, 'p2', 's2', '2020-05-15', '1000', '2020-05-01', '2020-12-14 18:29:07', '2020-12-14 18:29:07'),
(3, 'p1', 's3', '2020-05-20', '500', '2020-05-15', '2020-12-14 18:29:07', '2020-12-14 18:29:07');

Table Structure: sales

Field name	Data type
Field name	Id	int
product_id	varchar
quantity	varchar
sales_date	date
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "sales"

CREATE TABLE sales (
id int,
product_id varchar(100),
quantity varchar(100),
sales_date varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 6 records in "sales"

INSERT INTO sales (id, product_id, quantity, sales_date, created_at, updated_at) VALUES
(1, 'p1', '200', '2020-11-05', '2020-12-14 18:30:17', '2020-12-14 18:30:38'),
(2, 'p2', '400', '2020-12-05', '2020-12-14 18:30:17', '2020-12-14 18:30:42'),
(3, 'p3', '300', '2020-12-11', '2020-12-14 18:30:17', '2020-12-14 18:30:47');

Table Structure: stock

Field name	Data type
Field name	Id	int
product_id	varchar
stock	varchar
category_id	varchar
subcategory_id	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "stock"

CREATE TABLE stock (
id int,
product_id varchar(100),
stock varchar(100),
category_id varchar(100) ,
subcategory_id varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 7 records in "stock"

INSERT INTO stock (id, product_id, stock, category_id, subcategory_id, created_at, updated_at) VALUES
(1, 'p1', '1000', '1', '1', '2020-12-14 18:32:00', '2020-12-14 18:32:00'),
(2, 'p2', '1500', '2', '2', '2020-12-14 18:32:00', '2020-12-14 18:32:24'),
(3, 'p3', '2000', '3', '3', '2020-12-14 18:32:01', '2020-12-14 18:32:28');

Table Structure: sub_category

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

Following SQL creates a new table called "sub_category"

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

Following SQL inserts 5 records in "sub_category"

INSERT INTO subcategory (id, category_id, subcategory, created_at, updated_at) VALUES
(1, '1', 'animal', '2020-12-14 18:24:55', '2020-12-14 18:24:55'),
(2, '2', 'rice', '2020-12-14 18:24:55', '2020-12-14 18:24:55'),
(3, '3', 'dailymilk', '2020-12-14 18:24:55', '2020-12-14 18:24:55');

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', 'manikanta suppliers', 'vijayawada', '5632144785', '5745844578', '2020-12-14 18:38:17', '2020-12-14 18:38:17'),
(2, 's1', 'nagarjuna suppliers', 'guntur', '3598455715', '3698544751', '2020-12-14 18:38:18', '2020-12-14 18:38:18'),
(3, 's1', 'mahalacto', 'kadapa', '8563577489', '3658744514', '2020-12-14 18:38:18', '2020-12-14 18:38:18');

Table Structure: supplier_bank_details

Field name	Data type
Field name	Id	int
supplier_id	varchar
bank	varchar
account_number	varchar
ifsc	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 varchar(100),
account_number varchar(100),
ifsc varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 7 records in "supplier_bank_details"

INSERT INTO supplier_bank_details (id, supplier_id, bank, account_number, ifsc, created_at, updated_at) VALUES
(1, 's1', '1', '2365844578', 'sbi5368', '2020-12-14 18:40:18', '2020-12-14 18:40:18'),
(2, 's2', '2', '357489635123', 'icici368', '2020-12-14 18:40:18', '2020-12-14 18:40:18'),
(3, 's3', '3', '2584179635845', 'un68', '2020-12-14 18:40:18', '2020-12-14 18:40:18');

Table Structure: transactions

Field name	Data type
Field name	Id	int
transaction_id	varchar
customer_id	varchar
amount	varchar
transaction_date	date
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "transactions"

CREATE TABLE transactions (
id int,
transaction_id varchar(100),
customer_id varchar(100),
amount varchar(100),
transaction_date date,
created_at datetime ,
updated_at datetime );

Following SQL inserts 7 records in "transactions"

INSERT INTO transactions (id, transaction_id, customer_id, amount, transaction_date, created_at, updated_at) VALUES
(1, 't1', 'c1', '5000', '2020-12-14', '2020-12-14 18:48:58', '2020-12-14 18:48:58'),
(2, 't2', 'c2', '10000', '2020-12-14', '2020-12-14 18:48:58', '2020-12-14 18:48:58'),
(3, 't3', 'c3', '500', '2020-12-14', '2020-12-14 18:48:58', '2020-12-14 18:48:58');

Questions

1. Display how many products are there?

A. SELECT count(*) FROM `products``

2. Display the bank names?

A. SELECT* FROM `banks`

3. Dispay the balance amount based on transaction id?

A. SELECT b.transaction_id,b.balance from balance b

4. How many types of payments are there in this system and what are they?

A. SELECT FROM `payments`

5. Display the date time of the product records?

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

6. Display the customer details completely?

A. SELECTc.id,c.customer_id,c.name,c.address,c.mobile,c.alternate_mobile from customers c

7. On which date the product has been purchased?

A. SELECT pr.id,p.product_name,pr.purchase_date from purchase pr left join products p on pr.product_id=p.product_id

8. How much stock is there based on product?

A. SELECT s.id,s.stock,p.product_name from stock s left join products p on s.product_id=p.product_id

9. How much sales are done based on product?

A. SELECT s.id,p.product_name,s.quantity,s.sales_date from sales s left join products p on s.product_id=p.product_id

10. Display the transaction date based on customer id?

A. SELECT * FROM `transactions` where customer_id="c1"

11. Display the supplier details completely?

A. SELECT s.id,s.supplier_id,s.name,s.address,s.mobile,s.alternate_mobile,sb.bank,sb.account_number,sb.ifsc from suppliers s left join supplier_bank_details sb on s.supplier_id=sb.supplier_id

12. Display the transactions based on customers?

A. SELECT t.id,t.transaction_id,c.name,t.amount from transactions t left join customers c on t.customer_id=c.customer_id

13. How munch quantity remains based on sales?

A. SELECT(select sum(stock) from stock where product_id='p1')-(select sum(quantity) from sales where product_id='p1')as difference

14. Display the category names of the products?

A. SELECT p.id,c.category,p.product_name from products p left join category c on p.catgeory_id=c.id

15. Display the subcategory names of the products?

A. SELECT p.id,c.category,p.product_name,sb.subcategory from products p left join category c on p.catgeory_id=c.id LEFT join subcategory sb on p.subcategory_id=sb.id

16. On which date the sale was done based on product id?

A. SELECT s.id,p.product_name,s.sales_date,s.quantity from sales s LEFT join products p on p.product_id=s.product_id

Web Tutorials

</> SQL Tutorial

Inventory Control Management Project

Database Name: inventory_control

Practical Questions