Inventory Management System Database : Purchase is a method used by businesses to buy products and/or services. A purchasing system manages the entire acquisition process, from requisition, to purchase order, to product receipt, to payment. Purchasing systems are a key component of effective inventory management. It is used to monitor existing stock and help companies determine what to buy, how much to buy and when to buy it.
Inventory Management System Database
CREATE DATABASE
CREATE DATABASE database_name;
Create Employee Table
CREATE TABLE EMP
(
ENO VARCHAR2(5) PRIMARY KEY,
ENAME VARCHAR2(15),
TITLE VARCHAR2(15) DEFAULT ‘PROGRAMMER’,
CITY VARCHAR2(15),
CHECK(TITLE IN(‘MANAGER’,’PROGRAMMER’,’SUPPORT STAFF’))
);
Display 2 items having lowest current stock
Select itemname from (select itemname from item orderby qty_hand) where rownum <= 2;
Output:
ITEMNAME
———
Laptop
TV
Purchasing System
Create supplier Table
create table supp
(
suppid number(2) PRIMARY KEY,
suppname varchar(10),
city varchar(5)
)
Alter city field of supplier table
alter table supp add constraint city check(city in(‘Pattoki’,’ISB’,’Lahore’));
Alert part table add foreign key constrain
alter table part add constraint suppid foreign key(suppid)references supp;
Create order table
create table ord_tab
(
ordid number(2) primary key,
odate date,
suppid number(2) references supp on delete cascade,
partid number(2) references part on delete cascade,
orderqty number(5,2),
salesmid number(2) references salesman on delete cascade,
ordcost number(5,2)
)
Create salesman table
CREATE TABLE SALESMAN
(
SALESID NUMBER(2) PRIMARY KEY,
SALESNAME VARCHAR2(10),
CITY VARCHAR(5),
COMM NUMBER(3,2) CHECK (COMM < 1)
)
Display total number of items ordered in each invoices
selectcount(itemno)
from invitem
orderby itemno;
Display invoices having items greater than or equal to 3
selectdistinct invno from invitem where qty >= 3;
Output:
IN001
IN005
IN003
IN004
Display total of each invoices
Select invno, sum(qty_hand * itemprice) “Total Price” from item,invitem where item.itemno=invitem.itemno groupby invno;
Output:
INVNO Total Price
—– ————
IN001 180000
IN002 1250
IN003 1100
IN004 30000
IN005 87500
Display invoice dates in ‘month dd,yyyy’ format
Select to_char(invdate,’month dd,yyyy’) from invoice;
Output:
TO_CHAR(INVDATE,’
—————–
september 05,2017
september 06,2016
september 07,2015
september 18,2018
september 21,2019