Oracle

Hi everyone, I will share with you a schema.

This schema have multiple tables with primary key and foreign key.

this is about a delivery system which deliver food item to the foodbank on a specific route on specific date.

First Create the tables in Oracle

Food_bank Table:

CREATE TABLE FOOD_BANK     (	
FOODBANKNO NUMBER(10,0) NOTNULL ,  
NAME VARCHAR2(50)  NOTNULL ,  
STRADDR VARCHAR2(100) NOTNULL ,  	
CITY VARCHAR2(50) NOTNULL ,  	
COUNTY VARCHAR2(50) NOTNULL ,  
PHONE NUMBER(10,0) NOTNULL ,  	 
CONSTRAINT FOODBANK_PK PRIMARYKEY ( FOODBANKNO)

Employee table:

CREATE TABLE EMPLOYEE
(  EMPNO number(10) NOT NULL,
   FIRSTNAME varchar2(50) NOT NULL, 
   LASTNAME  varchar2(100) NOT NULL, 
   DOB   date NOT NULL, 
   ROLE varchar2(50) NOT NULL,
   CELLPHONE  number(10) NOT NULL,
   CONSTRAINT employee_pk PRIMARY KEY (EMPNO)  
);

Route table

CREATE TABLE ROUTE
(  ROUTENO number(10) NOT NULL,
   ROUTEDATE date NOT NULL, 
   STARTDATETIME  date NOT NULL,
   ENDDATETIME  date NOT NULL, 
     DRIVEREMPNO  number(10) NOT NULL,
     ASSTEMPNO  number(10) NOT NULL,

   CONSTRAINT route_pk PRIMARY KEY (ROUTENO), 
   CONSTRAINT route_driver_fk FOREIGN KEY(DRIVEREMPNO) REFERENCES EMPLOYEE(EMPNO),
  CONSTRAINT route_asst_fk FOREIGN KEY(ASSTEMPNO) REFERENCES EMPLOYEE(EMPNO)
);

Delivery Table:

CREATE TABLE DELIVERY  
(  DELIVERYNO number(10) NOTNULL,    
 DELIVERYDATETIME timestampNOTNULL,      
CRATES varchar2(50) NOTNULL,     
ROUTENO  number(10) NOTNULL,     
FOODBANKNO  number(10) NOTNULL,     
CONSTRAINT delivery_pk PRIMARYKEY (DELIVERYNO),     
 CONSTRAINT delivery_route_fk FOREIGNKEY(ROUTENO) REFERENCES ROUTE(ROUTENO),    CONSTRAINT  delivery_foodbank_fk FOREIGNKEY(FOODBANKNO) REFERENCES food_bank(FOODBANKNO)  )

Stop Table

CREATE TABLE STOP 
 (  STOPNO number(10) NOTNULL,     
ROUTENO  number(10) NOTNULL,     
FOODBANKNO  number(10) NOTNULL,    
 CONSTRAINT stop_pk PRIMARYKEY (STOPNO),     
 CONSTRAINT stop_route_fk FOREIGNKEY(ROUTENO) REFERENCES ROUTE(ROUTENO),    CONSTRAINT   stop_foodbank_fk FOREIGNKEY(FOODBANKNO) REFERENCES food_bank(FOODBANKNO)  )

DeliverContent Table:

CREATE TABLE DELIVERYCONTENT  
(  DELIVERYNO number(10) NOTNULL,     
ITEMNO  number(10) NOTNULL,    
 QUANTITY  number(10) NOTNULL,        
 CONSTRAINT delc_delivery_fk FOREIGNKEY(DELIVERYNO) REFERENCES DELIVERY(DELIVERYNO),    CONSTRAINT   delc_item_fk FOREIGNKEY(ITEMNO) REFERENCES ITEM(ITEMNO)  )

Inserting all rows in all tables:

INSERT ALL 
 INTO FOODBANK ( FOODBANKNO, NAME, STRADDR, CITY, COUNTY, PHONE ) VALUES(1,'foodbank1','abc','Delhi','India',1234)
 INTO FOODBANK ( FOODBANKNO, NAME, STRADDR, CITY, COUNTY, PHONE ) VALUES(2,'foodbank2','sww','Gurgaon','India',3333)
 INTO FOODBANK ( FOODBANKNO, NAME, STRADDR, CITY, COUNTY, PHONE ) VALUES(3,'foodbank3','cvc','Punjab','India',11111)
 INTO FOODBANK ( FOODBANKNO, NAME, STRADDR, CITY, COUNTY, PHONE ) VALUES(4,'foodbank4','fgff','Jaipur','India',4343)
 INTO FOODBANK ( FOODBANKNO, NAME, STRADDR, CITY, COUNTY, PHONE ) VALUES(5,'foodbank5','ggff','Rajkot','India',3433)
 INTO FOODBANK ( FOODBANKNO, NAME, STRADDR, CITY, COUNTY, PHONE ) VALUES(6,'foodbank6','ereed','Luknow','India',1211)
 INTO FOODBANK ( FOODBANKNO, NAME, STRADDR, CITY, COUNTY, PHONE ) VALUES(7,'foodbank7','sass','kathmandu','Nepal',2222)
 
 INTO ROUTE ( ROUTENO, ROUTEDATE, STARTDATETIME, ENDDATETIME, DRIVEREMPNO , ASSTEMPNO ) VALUES(1, To_DATE('14 JUN 2021','DD MON YYYY'), TO_TIMESTAMP('14-JUN-2021 1:00:00.00 PM'),TO_TIMESTAMP('14-JUN-2021 4:00:00.00 PM'),1,3)
 INTO ROUTE ( ROUTENO, ROUTEDATE, STARTDATETIME, ENDDATETIME, DRIVEREMPNO , ASSTEMPNO ) VALUES(2, To_DATE('14 JUN 2021','DD MON YYYY'), TO_TIMESTAMP('15-JUN-2021 2:00:00.00 PM'),TO_TIMESTAMP('15-JUN-2021 4:50:00.00 PM'),2,4)
 INTO ROUTE ( ROUTENO, ROUTEDATE, STARTDATETIME, ENDDATETIME, DRIVEREMPNO , ASSTEMPNO ) VALUES(3, To_DATE('14 JUN 2021','DD MON YYYY'), TO_TIMESTAMP('16-JUN-2021 3:30:00.00 PM'),TO_TIMESTAMP('16-JUN-2021 6:00:00.00 PM'),2,5)

 INTO EMPLOYEE ( EMPNO, LASTNAME, FIRSTNAME, DOB, ROLE, CELLPHONE ) VALUES(1,'Kumar','Rajesh',TO_DATE('22 JAN 1992','DD MON YYYY'),'Driver',1234567890)
 INTO EMPLOYEE ( EMPNO, LASTNAME, FIRSTNAME, DOB, ROLE, CELLPHONE ) VALUES(2,'Pal','Mukesh',TO_DATE('05 AUG 1992','DD MON YYYY'),'Driver',123453333)
 INTO EMPLOYEE ( EMPNO, LASTNAME, FIRSTNAME, DOB, ROLE, CELLPHONE ) VALUES(3,'Kaushik','Preetam',TO_DATE('07 MAY 1994','DD MON YYYY'),'Assistant',123456444)
 INTO EMPLOYEE ( EMPNO, LASTNAME, FIRSTNAME, DOB, ROLE, CELLPHONE ) VALUES(4,'Jaiswal','Rahul',TO_DATE('12 JAN 2005','DD MON YYYY'),'Assistent',1234567555)
 INTO EMPLOYEE ( EMPNO, LASTNAME, FIRSTNAME, DOB, ROLE, CELLPHONE ) VALUES(5,'Yadav','Umesh',TO_DATE('30 MAY 1993','DD MON YYYY'),'Assistant',1234567891)
 INTO EMPLOYEE ( EMPNO, LASTNAME, FIRSTNAME, DOB, ROLE, CELLPHONE ) VALUES(6,'Mishra','Ram Parshad',TO_DATE('15 MAY 1955','DD MON YYYY'),'Manager',123456666)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(1,1,1)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(1,2,2)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(1,3,3)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(2,1,4)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(2,2,5)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(2,6,6)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(2,4,7)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(3,1,8)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(3,2,9)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(3,3,10)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(3,4,11)
 INTO STOP ( ROUTENO, FOODBANKNO, STOPNO ) VALUES(3,7,12)
 INTO DELIVERY ( DELIVERYNO, DELIVERYDATETIME, CRATES, ROUTENO , FOODBANKNO ) VALUES(1,TO_TIMESTAMP('14-JUN-2021:1:30:00'),10,1,1)
 INTO DELIVERY ( DELIVERYNO, DELIVERYDATETIME, CRATES, ROUTENO , FOODBANKNO ) VALUES(2,TO_TIMESTAMP('14-JUN-2021:2:00:00'),9,1,2)
 INTO DELIVERY ( DELIVERYNO, DELIVERYDATETIME, CRATES, ROUTENO , FOODBANKNO ) VALUES(3,TO_TIMESTAMP('14-JUN-2021:3:15:00'),8,1,3)
 INTO DELIVERY ( DELIVERYNO, DELIVERYDATETIME, CRATES, ROUTENO , FOODBANKNO ) VALUES(4,TO_TIMESTAMP('15-JUN-2021:2:15:00'),7,2,1)
 INTO DELIVERY ( DELIVERYNO, DELIVERYDATETIME, CRATES, ROUTENO , FOODBANKNO ) VALUES(5,TO_TIMESTAMP('15-JUN-2021:2:55:00'),6,2,2)
 INTO DELIVERY ( DELIVERYNO, DELIVERYDATETIME, CRATES, ROUTENO , FOODBANKNO ) VALUES(6,TO_TIMESTAMP('15-JUN-2021:3:20:00'),5,2,6)
 INTO DELIVERY ( DELIVERYNO, DELIVERYDATETIME, CRATES, ROUTENO , FOODBANKNO ) VALUES(7,TO_TIMESTAMP('16-JUN-2021:2:55:00'),4,3,1)
 INTO DELIVERY ( DELIVERYNO, DELIVERYDATETIME, CRATES, ROUTENO , FOODBANKNO ) VALUES(8,TO_TIMESTAMP('16-JUN-2021:3:20:00'),3,3,2)
 INTO DELIVERY ( DELIVERYNO, DELIVERYDATETIME, CRATES, ROUTENO , FOODBANKNO ) VALUES(9,TO_TIMESTAMP('16-JUN-2021:3:40:00'),2,3,3)
 INTO DELIVERY ( DELIVERYNO, DELIVERYDATETIME, CRATES, ROUTENO , FOODBANKNO ) VALUES(10,TO_TIMESTAMP('16-JUN-2021:4:10:00'),1,3,7)
 INTO ITEM ( ITEMNO, DESCR, UNITS, QTYINSTOCK ) VALUES(1,'item1','lbs',20)
 INTO ITEM ( ITEMNO, DESCR, UNITS, QTYINSTOCK ) VALUES(2,'item2','lbs',40)
 INTO ITEM ( ITEMNO, DESCR, UNITS, QTYINSTOCK ) VALUES(3,'item3','gals',100)
 INTO ITEM ( ITEMNO, DESCR, UNITS, QTYINSTOCK ) VALUES(4,'item4','pieces',10)
 INTO ITEM ( ITEMNO, DESCR, UNITS, QTYINSTOCK ) VALUES(5,'item5','pieces',60)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(1,1,10)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(1,2,10)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(2,1,5)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(2,2,15)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(3,2,10)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(4,3,20)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(5,4,5)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(5,5,40)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(6,3,30)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(7,4,5)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(7,2,5)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(8,3,10)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(9,3,40)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(9,5,20)
 INTO DELIVERYCONTENT ( DELIVERYNO, ITEMNO, QUANTITY ) VALUES(10,1,5)
SELECT * FROM dual;  

Now all DDL statement is done. A dabase schema built.

Now perform following Queries to gain more knowledge

Query 1

1. Employees that are between 25 and 50 years as of April 1, 2021 (full name, date of birth, role,
and cellphone).

Solution:

select firstname,lastname,dob,role,cellphone
from employee
where dob > add_months(trunc(sysdate), -(50 * 12)) AND dob < add_months(trunc(sysdate), -(25 * 12));

Query 2

2. Details about each route – route no, route date, start time, end time, and driver and assistant fullname (Note: must display the times, not just the dates). **Modified *

Solution:

select  routeno,routedate,startdatetime,enddatetime,
(select firstname || ' ' || lastname 
from employee 
where empno = driverempno) AS Driver_Fullname,
(select firstname || ' ' || lastname 
from employee 
where empno = asstempno) AS Assistent_Fullname 
from route

Query 3

3. Routes that took longer than 2 hours – route no, route date, and route duration.

Solution:

select routeno, routedate ,(Extract(hour from enddatetime)- Extract(hour from startdatetime)) As Duration
from route
where (Extract(hour from enddatetime)- Extract(hour from startdatetime))>2;

Query 4

4. History of routes planned – route no, route date, stop no, foodbank name, city, and county, sorted
by route date and stop no.

Solution:

select route.routeno,routedate, stopno, food_bank.name, city, county
from route,stop,food_bank
where route.routeno = stop.routeno AND stop.foodbankno = food_bank.foodbankno
order by  routedate,stopno;

Query 5

5. Delivery composition – delivery number, delivery time, foodbank name, item description, item
no, description, and quantity delivered, sorted by delivery date, and foodbank name. (Must
display the time).

Solution:

select delivery.deliveryno, delivery.deliverydatetime, (select name from food_bank where foodbankno = delivery.foodbankno) AS foodbank_name,item.itemno ,item.descr , quantity from delivery,item,deliverycontent
where deliverycontent.itemno = item.itemno
order by delivery.deliverydatetime,foodbank_name

Query 6

6. Delivery summary for each route – route no, number of deliveries made, and average number of
crates delivered.

Solution:

select routeno,sum(foodbankno) AS number_of_deliveries_made, AVG(crates) As AVERAGE_CRATE_DELIVERED from delivery
group by routeno

Query 7

7. The number of items and their total quantity for each category of units, i.e. lbs, gals, pieces.

Solution:

select units, count(units) As NO_OF_ITEMS , sum(QTYINSTOCK) AS Total_QUENTITY
from item
group by units

Query 8

8. Foodbanks that not on any route – need the name, city, county, and phone.

Solution:

select Distinct food_bank.name, city, county,phone
from food_bank,delivery
where food_bank.foodbankno  NOT IN (select foodbankno from delivery)

Query 9

9. Foodbanks that have received less than 15 crates total – foodbank no and total crates received.

Solution:

select foodbankno , sum(crates) As total_crates
from delivery
group by  foodbankno
having sum(crates) < 15;

Query 10

10. Routes that visit foodbanks in India and Nepal counties – route no and route date needed.

Solution:

submit later

Thank you.