2: Consider the following relations for an order processing database application in a company.
CUSTOMER (custno: Int, cname: String, city: String)
ORDERS (orderno: Int, odate: Date, custno: Int, ordamt: Int)
ITEM (itemno: Int, unitprice: Int)
OREDERITEM (orderno: Int, itemno: Int, qty: Int)
WAREHOUSE (warehouseno: Int, city: String)
SHIPMENT (orderno: Int, warehouseno: Int, shipdate: Date)
1. Create above tables with properly specifying primary keys and foreign keys.
SQL> CREATE TABLE CUSTOMER(
2 CUSTNO INT,
3 CNAME VARCHAR(10),
4 CITY VARCHAR(10),
5 PRIMARY KEY(CUSTNO));
Table created.
SQL> CREATE TABLE ORDERS(
2 ORDERNO INT,
3 ODATE DATE,
4 CUSTNO INT,
5 ORDAMT INT,
6 PRIMARY KEY(ORDERNO),
7 FOREIGN KEY(CUSTNO) REFERENCES CUSTOMER(CUSTNO) ON DELETE SET NULL);
Table created.
SQL> CREATE TABLE ITEM(
2 ITEMNO INT,
3 UNITPRICE INT,
4 PRIMARY KEY(ITEMNO));
Table created.
SQL> CREATE TABLE ORDERITEM(
2 ORDERNO INT,
3 ITEMNO INT,
4 QTY INT,
5 FOREIGN KEY(ORDERNO) REFERENCES ORDERS(ORDERNO) ON DELETE SET NULL,
6 FOREIGN KEY(ITEMNO) REFERENCES ITEM(ITEMNO) ON DELETE SET NULL);
Table created.
SQL> CREATE TABLE WAREHOUSE(
2 WAREHOUSENO INT,
3 CITY VARCHAR(10),
4 PRIMARY KEY(WAREHOUSENO));
Table created.
SQL> CREATE TABLE SHIPMENT(
2 ORDERNO INT,
3 WAREHOUSENO INT,
4 SHIPDATE DATE,
5 PRIMARY KEY(ORDERNO,WAREHOUSENO),
6 FOREIGN KEY(ORDERNO) REFERENCES ORDERS(ORDERNO) ON DELETE SET NULL,
7 FOREIGN KEY(WAREHOUSENO) REFERENCES WAREHOUSE(WAREHOUSENO) ON DELETE SET
8 NULL);
Table created.
2. Enter at least 7-8 tuples for each relation.
SQL> INSERT INTO CUSTOMER VALUES(&CUSTNO,'&CNAME','&CITY');
Enter value for custno: 1
Enter value for cname: JOHN
Enter value for city: HUBLI
old 1: INSERT INTO CUSTOMER VALUES(&CUSTNO,'&CNAME','&CITY')
new 1: INSERT INTO CUSTOMER VALUES(1,'JOHN','HUBLI')
1 row created.
SQL> INSERT INTO ORDERS VALUES(&ORDERNO,'&ODATE',&CUSTNO,&ORDAMT);
Enter value for orderno: 1
Enter value for odate: 01-JAN-11
Enter value for custno: 1
Enter value for ordamt: 1000
old 1: INSERT INTO ORDERS VALUES(&ORDERNO,'&ODATE',&CUSTNO,&ORDAMT)
new 1: INSERT INTO ORDERS VALUES(1,'01-JAN-11',1,1000)
1 row created.
SQL> INSERT INTO ITEM VALUES(&ITEMNO,&UNITPRICE);
Enter value for itemno: 1
Enter value for unitprice: 50
old 1: INSERT INTO ITEM VALUES(&ITEMNO,&UNITPRICE)
new 1: INSERT INTO ITEM VALUES(1,50)
1 row created.
SQL> INSERT INTO ORDERITEM VALUES(&ORDERNO,&ITEMNO,&QTY);
Enter value for orderno: 1
Enter value for itemno: 1
Enter value for qty: 10
old 1: INSERT INTO ORDERITEM VALUES(&ORDERNO,&ITEMNO,&QTY)
new 1: INSERT INTO ORDERITEM VALUES(1,1,10)
1 row created.
SQL> INSERT INTO WAREHOUSE VALUES(&WAREHOUSENO,'&CITY');
Enter value for warehouseno: 1
Enter value for city: MUMBAI
old 1: INSERT INTO WAREHOUSE VALUES(&WAREHOUSENO,'&CITY')
new 1: INSERT INTO WAREHOUSE VALUES(1,'MUMBAI')
1 row created.
SQL> INSERT INTO SHIPMENT VALUES(&ORDERNO,&WAREHOUSE,'&SHIPDATE');
Enter value for orderno: 3
Enter value for warehouse: 1
Enter value for shipdate: 20-JUN-10
old 1: INSERT INTO SHIPMENT VALUES(&ORDERNO,&WAREHOUSE,'&SHIPDATE')
new 1: INSERT INTO SHIPMENT VALUES(3,1,'20-JUN-10')
1 row created.
3. Displaying the table contents.
SQL> SELECT * FROM CUSTOMER;
CUSTNO CNAME CITY
1 JOHN HUBLI
2 JACK HUBLI
3 DAVID DHARWAD
4 TINA DHARWAD
5 ALEN BELGAUM
6 FRED BELGAUM
6 rows selected.
SQL> SELECT * FROM ORDERS;
ORDERNO ODATE CUSTNO ORDAMT
1 01-JAN-11 1 1000
2 02-FEB-11 1 2000
3 03-MAR-11 3 1000
4 01-JAN-11 5 2000
5 02-FEB-11 5 5000
6 03-MAR-11 5 3000
7 01-JAN-11 4 2000
8 02-FEB-11 3 8000
8 rows selected.
SQL> SELECT * FROM ITEM;
ITEMNO UNITPRICE
1 50
2 100
3 150
4 200
5 250
6 300
7 350
8 400
8 rows selected.
SQL> SELECT * FROM ORDERITEM;
ORDERNO ITEMNO QTY
1 1 10
2 1 20
1 8 30
3 4 40
3 5 50
4 1 20
4 2 20
6 5 30
8 rows selected.
SQL> SELECT * FROM WAREHOUSE;
WAREHOUSENO CITY
1 MUMBAI
2 MUMBAI
3 NAGPUR
4 DELHI
5 BENGALURU
6 BENGALURU
7 BENGALURU
8 DELHI
8 rows selected.
SQL> SELECT * FROM SHIPMENT;
ORDERNO WAREHOUSENO SHIPDATE
3 1 20-JUN-10
2 1 09-MAY-11
1 1 01-MAY-11
1 2 02-MAY-11
4 2 04-MAY-11
5 4 05-MAY-11
6 4 06-MAY-11
3 2 01-MAY-11
8 rows selected.
4. Produce a listing: customer name, number of orders, average order amount where the middle column is the total number of orders by the customer and the last column is average order amount for that customer.
SQL> SELECT C.CNAME,COUNT(O.ORDERNO) AS NO_OF_ORDERS,AVG(O.ORDAMT) AS
2 AVG_AMOUNT
3 FROM CUSTOMER C,ORDERS O
4 WHERE C.CUSTNO=O.CUSTNO
5 GROUP BY C.CNAME;
CNAME NO_OF_ORDERS AVG_AMOUNT
ALEN 3 3333.33333
DAVID 2 4500
JOHN 1 2000
5. List the order number for the orders that were shift from all the warehouses that the company has in a specific city.
SQL> SELECT DISTINCT S.ORDERNO AS ORDER_NO
2 FROM SHIPMENT S, WAREHOUSE W
3 WHERE W.WAREHOUSENO=S.WAREHOUSENO
4 AND W.CITY LIKE 'MUMBAI' GROUP BY S.ORDERNO
5 HAVING COUNT(S.WAREHOUSENO)=(SELECT COUNT(W.WAREHOUSENO)
6 FROM WAREHOUSE W
7 WHERE W.CITY='MUMBAI');
ORDER_NO
1
3
6. Demonstrate how you delete item number 10 from the item table and make that field null in the orderitem table.
SQL> DELETE FROM ITEM WHERE ITEMNO=5;
1 row deleted.
SQL> SELECT * FROM ITEM;
ITEMNO UNITPRICE
1 50
2 100
3 150
4 200
6 300
7 350
8 400
7 rows selected.
SQL> SELECT * FROM ORDERITEM;
ORDERNO ITEMNO QTY
1 1 10
2 1 20
1 8 30
3 4 40
3 2 50
4 1 20
4 2 20
6 30
8 rows selected.
No comments:
Post a Comment