Inventory Management System Database

Inventory Management System Database

by

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