LAB EXPERIMENTS

PART B: SQL PROGRAMMING

Q7. Consider the following schema for a Library Database:

BOOK (Book_id, Title, Publisher_Name, Pub_Year) BOOK_AUTHORS (Book_id, Author_Name) PUBLISHER (Name, Address, Phone) BOOK_COPIES (Book_id, Branch_id, No-of_Copies)
BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date) LIBRARY_BRANCH (Branch_id, Branch_Name, Address)

Write SQL queries to
1.	Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each branch, etc.
2.	Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017
3.	Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.
4.	Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.
5.	Create a view of all books and its number of copies that are currently available in the Library.

Solution:
Entity-Relationship Diagram


 
Schema Diagram
Book







	Book_id	Author_name




Name	Phone_no	Address




Book_id	Branch_id	No_of_Copies
		



Book_id	Branch_id	Card_no	Date_out	Due_date

Library_Branch	

Branch_id	Address	Branch_name




Table Creation

CREATE TABLE PUBLISHER
(NAME VARCHAR2 (20) PRIMARY KEY, PHONE INTEGER,
ADDRESS VARCHAR2 (20));

CREATE TABLE BOOK
(BOOK_ID INTEGER PRIMARY KEY, TITLE VARCHAR2 (20),
PUB_YEAR VARCHAR2 (20),
PUBLISHER_NAME REFERENCES PUBLISHER (NAME) ON DELETE CASCADE);
 
CREATE TABLE BOOK_AUTHORS (AUTHOR_NAME VARCHAR2 (20),
BOOK_ID REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE, PRIMARY KEY (BOOK_ID, AUTHOR_NAME));

CREATE TABLE LIBRARY_BRANCH (BRANCH_ID INTEGER PRIMARY KEY, BRANCH_NAME VARCHAR2 (50),
ADDRESS VARCHAR2 (50));

CREATE TABLE BOOK_COPIES (NO_OF_COPIES INTEGER,
BOOK_ID REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE,
BRANCH_ID	REFERENCES	LIBRARY_BRANCH	(BRANCH_ID)	ON	DELETE CASCADE,
PRIMARY KEY (BOOK_ID, BRANCH_ID));

CREATE TABLE CARD
(CARD_NO INTEGER PRIMARY KEY);

CREATE TABLE BOOK_LENDING (DATE_OUT DATE,
DUE_DATE DATE,
BOOK_ID REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE,
BRANCH_ID	REFERENCES	LIBRARY_BRANCH	(BRANCH_ID)	ON	DELETE CASCADE,
CARD_NO REFERENCES CARD (CARD_NO) ON DELETE CASCADE, PRIMARY KEY (BOOK_ID, BRANCH_ID, CARD_NO));

Table Descriptions

DESC PUBLISHER;
DESC BOOK;
 

 

DESC BOOK_AUTHORS;



DESC LIBRARY_BRANCH;



DESC BOOK_COPIES;



DESC CARD;



DESC BOOK_LENDING;


 
Insertion of Values to Tables

INSERT INTO PUBLISHER VALUES (‗MCGRAW-HILL‘, 9989076587, ‗BANGALORE‘); INSERT INTO PUBLISHER VALUES (‗PEARSON‘, 9889076565, ‗NEWDELHI‘);
INSERT INTO PUBLISHER VALUES (‗RANDOM HOUSE‘, 7455679345, ‗HYDRABAD‘); INSERT INTO PUBLISHER VALUES (‗HACHETTE LIVRE‘, 8970862340, ‗CHENAI‘); INSERT INTO PUBLISHER VALUES (‗GRUPO PLANETA‘, 7756120238, ‗BANGALORE‘);

INSERT INTO BOOK VALUES (1,‘DBMS‘,‘JAN-2017‘, ‗MCGRAW-HILL‘); INSERT INTO BOOK VALUES (2,‘ADBMS‘,‘JUN-2016‘, ‗MCGRAW-HILL‘); INSERT INTO BOOK VALUES (3,‘CN‘,‘SEP-2016‘, ‗PEARSON‘);
INSERT INTO BOOK VALUES (4,‘CG‘,‘SEP-2015‘, ‗GRUPO PLANETA‘); INSERT INTO BOOK VALUES (5,‘OS‘,‘MAY-2016‘, ‗PEARSON‘);

INSERT INTO BOOK_AUTHORS VALUES (‘NAVATHE‘, 1); INSERT INTO BOOK_AUTHORS VALUES (‘NAVATHE‘, 2); INSERT INTO BOOK_AUTHORS VALUES (‘TANENBAUM‘, 3); INSERT INTO BOOK_AUTHORS VALUES (‘EDWARD ANGEL‘, 4); INSERT INTO BOOK_AUTHORS VALUES (‘GALVIN‘, 5);

INSERT INTO LIBRARY_BRANCH VALUES (10,‘RR NAGAR‘,‘BANGALORE‘); INSERT INTO LIBRARY_BRANCH VALUES (11,‘RNSIT‘,‘BANGALORE‘);
INSERT INTO LIBRARY_BRANCH VALUES (12,‘RAJAJI NAGAR‘, ‘BANGALORE‘); INSERT INTO LIBRARY_BRANCH VALUES (13,‘NITTE‘,‘MANGALORE‘);
INSERT INTO LIBRARY_BRANCH VALUES (14,‘MANIPAL‘,‘UDUPI‘);

INSERT INTO BOOK_COPIES VALUES (10, 1, 10);
INSERT INTO BOOK_COPIES VALUES (5, 1, 11);
INSERT INTO BOOK_COPIES VALUES (2, 2, 12);
INSERT INTO BOOK_COPIES VALUES (5, 2, 13);
INSERT INTO BOOK_COPIES VALUES (7, 3, 14);
INSERT INTO BOOK_COPIES VALUES (1, 5, 10);
INSERT INTO BOOK_COPIES VALUES (3, 4, 11);

INSERT INTO CARD VALUES (100); INSERT INTO CARD VALUES (101); INSERT INTO CARD VALUES (102); INSERT INTO CARD VALUES (103); INSERT INTO CARD VALUES (104);
 
INSERT INTO BOOK_LENDING VALUES (‘01-JAN-17‘,‘01-JUN-17‘, 1, 10, 101);
INSERT INTO BOOK_LENDING VALUES (‘11-JAN-17‘,‘11-MAR-17‘, 3, 14, 101);
INSERT INTO BOOK_LENDING VALUES (‘21-FEB-17‘,‘21-APR-17‘, 2, 13, 101);
INSERT INTO BOOK_LENDING VALUES (‘15-MAR-17‘,‘15-JUL-17‘, 4, 11, 101);
INSERT INTO BOOK_LENDING VALUES (‗12-APR-17‘,‘12-MAY-17‘, 1, 11, 104); SELECT * FROM PUBLISHER;

SELECT * FROM BOOK;



SELECT * FROM BOOK_AUTHORS;


SELECT * FROM LIBRARY_BRANCH;
SELECT * FROM BOOK_COPIES;
 

 


SELECT * FROM CARD;

SELECT * FROM BOOK_LENDING;

Queries:

1.	Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each branch, etc.

SELECT	B.BOOK_ID,	B.TITLE,	B.PUBLISHER_NAME,	A.AUTHOR_NAME, C.NO_OF_COPIES, L.BRANCH_ID
FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L WHERE B.BOOK_ID=A.BOOK_ID
AND B.BOOK_ID=C.BOOK_ID
AND L.BRANCH_ID=C.BRANCH_ID;
 

 


1.	Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017.

SELECT CARD_NO FROM BOOK_LENDING
WHERE DATE_OUT BETWEEN ‘01-JAN-2017‘ AND ‘01-JUL-2017‘ GROUP BY CARD_NO
HAVING COUNT (*)>3;

2.	Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.

DELETE FROM BOOK WHERE BOOK_ID=3;

3.	Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.

CREATE VIEW V_PUBLICATION AS SELECT PUB_YEAR
FROM BOOK;
 

 

4.	Create a view of all books and its number of copies that are currently available in the Library.

CREATE VIEW V_BOOKS AS
SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
FROM BOOK B, BOOK_COPIES C, LIBRARY_BRANCH L WHERE B.BOOK_ID=C.BOOK_ID
AND C.BRANCH_ID=L.BRANCH_ID;


 
Q8. Consider the following schema for Order Database:

SALESMAN (Salesman_id, Name, City, Commission) CUSTOMER (Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id) Write SQL queries to
1.	Count the customers with grades above Bangalore’s average.
2.	Find the name and numbers of all salesmen who had more than one customer.
3.	List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION operation.)
4.	Create a view that finds the salesman who has the customer with the highest order of a day.
5.	Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.

Solution:

Entity-Relationship Diagram


 
Schema Diagram

Salesman


Salesman_id	Name	City	Commission

Customer	
Customer_id	Cust_Name	City	Grade	Salesman_id

	Ord_No	Purchase_Amt	Ord_Date	Customer_id	Salesman_id



Table Creation

CREATE TABLE SALESMAN (SALESMAN_ID NUMBER (4),
NAME VARCHAR2 (20),
CITY VARCHAR2 (20),
COMMISSION VARCHAR2 (20), PRIMARY KEY	(SALESMAN_ID));

CREATE TABLE CUSTOMER1 (CUSTOMER_ID NUMBER (4),
CUST_NAME VARCHAR2 (20),
CITY VARCHAR2 (20),
GRADE NUMBER (3),
PRIMARY KEY (CUSTOMER_ID),
SALESMAN_ID REFERENCES SALESMAN (SALESMAN_ID) ON DELETE SET NULL);

CREATE TABLE ORDERS (ORD_NO NUMBER (5),
PURCHASE_AMT NUMBER (10, 2), ORD_DATE DATE,
PRIMARY KEY (ORD_NO),
CUSTOMER_ID REFERENCES CUSTOMER1 (CUSTOMER_ID) ON DELETE CASCADE, SALESMAN_ID REFERENCES SALESMAN (SALESMAN_ID) ON DELETE CASCADE);
 
Table Descriptions

DESC SALESMAN;

DESC CUSTOMER1;


DESC ORDERS;

Insertion of Values to Tables

INSERT INTO SALESMAN VALUES (1000, ‗JOHN‘,‘BANGALORE‘,‘25 %‘); INSERT INTO SALESMAN VALUES (2000, ‗RAVI‘,‘BANGALORE‘,‘20 %‘); INSERT INTO SALESMAN VALUES (3000, ‗KUMAR‘,‘MYSORE‘,‘15 %‘); INSERT INTO SALESMAN VALUES (4000, ‗SMITH‘,‘DELHI‘,‘30 %‘); INSERT INTO SALESMAN VALUES (5000, ‗HARSHA‘,‘HYDRABAD‘,‘15 %‘);

INSERT INTO CUSTOMER1 VALUES (10, ‗PREETHI‘,‘BANGALORE‘, 100, 1000);
INSERT INTO CUSTOMER1 VALUES (11, ‗VIVEK‘,‘MANGALORE‘, 300, 1000);
INSERT INTO CUSTOMER1 VALUES (12, ‗BHASKAR‘,‘CHENNAI‘, 400, 2000);
INSERT INTO CUSTOMER1 VALUES (13, ‗CHETHAN‘,‘BANGALORE‘, 200, 2000);
INSERT INTO CUSTOMER1 VALUES (14, ‗MAMATHA‘,‘BANGALORE‘, 400, 3000);

INSERT INTO ORDERS VALUES (50, 5000, ‗04-MAY-17‘, 10, 1000);
INSERT INTO ORDERS VALUES (51, 450, ‗20-JAN-17‘, 10, 2000);
 
INSERT INTO ORDERS VALUES (52, 1000, ‗24-FEB-17‘, 13, 2000);
INSERT INTO ORDERS VALUES (53, 3500, ‗13-APR-17‘, 14, 3000);
INSERT INTO ORDERS VALUES (54, 550, ‗09-MAR-17‘, 12, 2000); SELECT * FROM SALESMAN;





SELECT * FROM CUSTOMER1;

SELECT * FROM ORDERS;

Queries:

1.	Count the customers with grades above Bangalore’s average. SELECT GRADE, COUNT (DISTINCT CUSTOMER_ID) FROM CUSTOMER1
GROUP BY GRADE
HAVING GRADE > (SELECT AVG(GRADE) FROM CUSTOMER1
WHERE CITY='BANGALORE');

 
2.	Find the name and numbers of all salesmen who had more than one customer.

SELECT SALESMAN_ID, NAME FROM SALESMAN A
WHERE 1 < (SELECT COUNT (*) FROM CUSTOMER1
WHERE SALESMAN_ID=A.SALESMAN_ID);

3.	List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION operation.)

SELECT SALESMAN.SALESMAN_ID, NAME, CUST_NAME, COMMISSION FROM SALESMAN, CUSTOMER1
WHERE SALESMAN.CITY = CUSTOMER1.CITY UNION
SELECT SALESMAN_ID, NAME, 'NO MATCH', COMMISSION FROM SALESMAN
WHERE NOT CITY = ANY (SELECT CITY
FROM CUSTOMER1) ORDER BY 2 DESC;


4.	Create a view that finds the salesman who has the customer with the highest order of a day.

CREATE VIEW ELITSALESMAN AS
SELECT B.ORD_DATE, A.SALESMAN_ID, A.NAME FROM SALESMAN A, ORDERS B
 
WHERE A.SALESMAN_ID = B.SALESMAN_ID
AND B.PURCHASE_AMT=(SELECT MAX (PURCHASE_AMT)
FROM ORDERS C
WHERE C.ORD_DATE = B.ORD_DATE);



5.	Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.

Use ON DELETE CASCADE at the end of foreign key definitions while creating child table orders and then execute the following:
Use ON DELETE SET NULL at the end of foreign key definitions while creating child table customers and then executes the following:
DELETE FROM SALESMAN WHERE SALESMAN_ID=1000;



 
Q9. Consider the schema for Movie Database:

ACTOR (Act_id, Act_Name, Act_Gender) DIRECTOR (Dir_id, Dir_Name, Dir_Phone)
MOVIES (Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id) MOVIE_CAST (Act_id, Mov_id, Role)
RATING (Mov_id, Rev_Stars) Write SQL queries to
a.	List the titles of all movies directed by ‘Hitchcock’.
b.	Find the movie names where one or more actors acted in two or more movies.
c.	List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).
d.	Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title.
e.	Update rating of all movies directed by ‘Steven Spielberg’ to 5.

Solution:

Entity-Relationship Diagram


 
Schema Diagram
Actor
Act_id	Act_Name	Act_Gender



Dir_id	Dir_Name	Dir_Phone

Movies	
Mov_id	Mov_Title	Mov_Year	Mov_Lang	Dir_id



	Act_id	Mov_id	Role

Rating	
Mov_id	Rev_Stars


Table Creation

CREATE TABLE ACTOR ( ACT_ID NUMBER (3),
ACT_NAME VARCHAR (20),
ACT_GENDER CHAR (1), PRIMARY KEY (ACT_ID));

CREATE TABLE DIRECTOR ( DIR_ID NUMBER (3),
DIR_NAME VARCHAR (20),
DIR_PHONE NUMBER (10), PRIMARY KEY (DIR_ID));

CREATE TABLE MOVIES ( MOV_ID NUMBER (4),
MOV_TITLE VARCHAR (25),
MOV_YEAR NUMBER (4),
MOV_LANG VARCHAR (12),
DIR_ID NUMBER (3), PRIMARY KEY (MOV_ID),
FOREIGN KEY (DIR_ID) REFERENCES DIRECTOR (DIR_ID));
 
CREATE TABLE MOVIE_CAST ( ACT_ID NUMBER (3),
MOV_ID NUMBER (4),
ROLE VARCHAR (10),
PRIMARY KEY (ACT_ID, MOV_ID),
FOREIGN KEY (ACT_ID) REFERENCES ACTOR (ACT_ID), FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

CREATE TABLE RATING ( MOV_ID NUMBER (4),
REV_STARS VARCHAR (25), PRIMARY KEY (MOV_ID),
FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

Table Descriptions

DESC ACTOR;



DESC DIRECTOR;



DESC MOVIES;



 

DESC MOVIE_CAST;



DESC RATING;



Insertion of Values to Tables

INSERT INTO ACTOR VALUES (301,‘ANUSHKA‘,‘F‘); INSERT INTO ACTOR VALUES (302,‘PRABHAS‘,‘M‘); INSERT INTO ACTOR VALUES (303,‘PUNITH‘,‘M‘); INSERT INTO ACTOR VALUES (304,‘JERMY‘,‘M‘);

INSERT INTO DIRECTOR VALUES (60,‘RAJAMOULI‘, 8751611001); INSERT INTO DIRECTOR VALUES (61,‘HITCHCOCK‘, 7766138911); INSERT INTO DIRECTOR VALUES (62,‘FARAN‘, 9986776531);
INSERT INTO DIRECTOR VALUES (63,‘STEVEN SPIELBERG‘, 8989776530);

INSERT INTO MOVIES VALUES (1001,‘BAHUBALI-2‘, 2017, ‗TELAGU‘, 60); INSERT INTO MOVIES VALUES (1002,‘BAHUBALI-1‘, 2015, ‗TELAGU‘, 60); INSERT INTO MOVIES VALUES (1003,‘AKASH‘, 2008, ‗KANNADA‘, 61); INSERT INTO MOVIES VALUES (1004,‘WAR HORSE‘, 2011, ‗ENGLISH‘, 63);

INSERT INTO MOVIE_CAST VALUES (301, 1002, ‗HEROINE‘); INSERT INTO MOVIE_CAST VALUES (301, 1001, ‗HEROINE‘); INSERT INTO MOVIE_CAST VALUES (303, 1003, ‗HERO‘); INSERT INTO MOVIE_CAST VALUES (303, 1002, ‗GUEST‘); INSERT INTO MOVIE_CAST VALUES (304, 1004, ‗HERO‘);

INSERT INTO RATING VALUES (1001, 4);
INSERT INTO RATING VALUES (1002, 2);
 
INSERT INTO RATING VALUES (1003, 5);
INSERT INTO RATING VALUES (1004, 4);

SELECT * FROM ACTOR;

SELECT * FROM DIRECTOR;



SELECT * FROM MOVIES;



SELECT * FROM MOVIE_CAST;


 

SELECT * FROM RATING;


Queries:

1.	List the titles of all movies directed by ‘Hitchcock’.

SELECT MOV_TITLE FROM MOVIES
WHERE DIR_ID IN (SELECT DIR_ID
FROM DIRECTOR
WHERE DIR_NAME = ‗HITCHCOCK‘);


2.	Find the movie names where one or more actors acted in two or more movies.

SELECT MOV_TITLE
FROM MOVIES M, MOVIE_CAST MV
WHERE M.MOV_ID=MV.MOV_ID AND ACT_ID IN (SELECT ACT_ID
FROM MOVIE_CAST GROUP BY ACT_ID HAVING COUNT (ACT_ID)>1)
GROUP BY MOV_TITLE HAVING COUNT (*)>1;


3.	List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).

SELECT ACT_NAME, MOV_TITLE, MOV_YEAR
 
FROM ACTOR A JOIN MOVIE_CAST C
ON A.ACT_ID=C.ACT_ID JOIN MOVIES  M
ON C.MOV_ID=M.MOV_ID
WHERE M.MOV_YEAR NOT BETWEEN 2000 AND 2015; OR
SELECT A.ACT_NAME, A.ACT_NAME, C.MOV_TITLE, C.MOV_YEAR FROM ACTOR A, MOVIE_CAST B, MOVIES C
WHERE A.ACT_ID=B.ACT_ID AND B.MOV_ID=C.MOV_ID
AND C.MOV_YEAR NOT BETWEEN 2000 AND 2015;




4.	Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title.

SELECT MOV_TITLE, MAX (REV_STARS) FROM MOVIES
INNER JOIN RATING USING (MOV_ID) GROUP BY MOV_TITLE
HAVING MAX (REV_STARS)>0 ORDER BY MOV_TITLE;

 
5.	Update rating of all movies directed by ‘Steven Spielberg’ to 5
KL
UPDATE RATING SET REV_STARS=5
WHERE MOV_ID IN (SELECT MOV_ID FROM MOVIES
WHERE DIR_ID IN (SELECT DIR_ID
FROM DIRECTOR
WHERE DIR_NAME = ‗STEVEN SPIELBERG‘));

exp-10n11;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Q10. Consider the schema for College Database:

STUDENT (USN, SName, Address, Phone, Gender) SEMSEC (SSID, Sem, Sec)
CLASS (USN, SSID)
SUBJECT (Subcode, Title, Sem, Credits)
IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA) Write SQL queries to
a.	List all the student details studying in fourth semester ‘C’ section.
b.	Compute the total number of male and female students in each semester and in each section.
c.	Create a view of Test1 marks of student USN ‘1NH15CS011’ in all subjects.
d.	Calculate   the FinalIA   (average   of   best   two   test	marks) and update the corresponding table for all students.
e.	Categorize students based on the following criterion:
 If FinalIA = 17 to 20 then CAT = Outstanding’
If FinalIA = 12 to 16 then CAT = ‘Average’ If FinalIA< 12 then CAT = ‘Weak’
Give these details only for 8th semester A, B, and C section students.
Solution:

Entity - Relationship Diagram

 
Schema Diagram


Table Creation

CREATE TABLE STUDENT (
USN VARCHAR (10) PRIMARY KEY, SNAME VARCHAR (25),
ADDRESS VARCHAR (25),
PHONE NUMBER (10),
GENDER CHAR (1));

CREATE TABLE SEMSEC (
SSID VARCHAR (5) PRIMARY KEY, SEM NUMBER (2),
SEC CHAR (1));

CREATE TABLE CLASS ( USN VARCHAR (10),
SSID VARCHAR (5), PRIMARY KEY (USN, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN), FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID));
 
CREATE TABLE SUBJECT ( SUBCODE VARCHAR (8),
TITLE VARCHAR (20),
SEM NUMBER (2),
CREDITS NUMBER (2), PRIMARY KEY (SUBCODE));

CREATE TABLE IAMARKS ( USN VARCHAR (10),
SUBCODE VARCHAR (8),
SSID VARCHAR (5),
TEST1 NUMBER (2),
TEST2 NUMBER (2),
TEST3 NUMBER (2),
FINALIA NUMBER (2),
PRIMARY KEY (USN, SUBCODE, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN),
FOREIGN KEY (SUBCODE) REFERENCES SUBJECT (SUBCODE), FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID));

Table Descriptions

DESC STUDENT;


DESC SEMSEC;


 
DESC CLASS;



DESC SUBJECT;


DESC IAMARKS;

Insertion of values to tables

INSERT INTO STUDENT VALUES ('1NH13CS020','AKSHAY','BELAGAVI', 8877881122,'M');
INSERT INTO STUDENT VALUES ('1NH13CS062','SANDHYA','BENGALURU', 7722829912,'F');
INSERT INTO STUDENT VALUES ('1NH13CS091','TEESHA','BENGALURU', 7712312312,'F');
INSERT INTO STUDENT VALUES ('1NH13CS066','SUPRIYA','MANGALURU', 8877881122,'F');
INSERT INTO STUDENTVALUES ('1NH14CS010','ABHAY','BENGALURU', 9900211201,'M');
INSERT INTO STUDENT VALUES ('1NH14CS032','BHASKAR','BENGALURU', 9923211099,'M');
INSERT INTO STUDENTVALUES ('1NH14CS025','ASMI','BENGALURU', 7894737377,'F'); INSERT INTO STUDENT VALUES ('1NH15CS011','AJAY','TUMKUR', 9845091341,'M');
 
INSERT INTO STUDENT VALUES ('1NH15CS029','CHITRA','DAVANGERE', 7696772121,'F');
INSERT INTO STUDENT VALUES ('1NH15CS045','JEEVA','BELLARY', 9944850121,'M'); INSERT INTO STUDENT VALUES ('1NH15CS091','SANTOSH','MANGALURU', 8812332201,'M');
INSERT INTO STUDENT VALUES ('1NH16CS045','ISMAIL','KALBURGI', 9900232201,'M');
INSERT INTO STUDENT VALUES ('1NH16CS088','SAMEERA','SHIMOGA', 9905542212,'F');
INSERT INTO STUDENT VALUES ('1NH16CS122','VINAYAKA','CHIKAMAGALUR', 8800880011,'M');

INSERT INTO SEMSEC VALUES ('CSE8A', 8,'A'); INSERT INTO SEMSEC VALUES (‗CSE8B', 8,'B'); INSERT INTO SEMSEC VALUES (‗CSE8C‘, 8,‘C‘);

INSERT INTO SEMSEC VALUES ('CSE7A', 7,‘A‘); INSERT INTO SEMSEC VALUES (‗CSE7B‘, 7,'B‘); INSERT INTO SEMSEC VALUES ('CSE7C', 7,'C');

INSERT INTO SEMSEC VALUES (‗CSE6A', 6,'A'); INSERT INTO SEMSEC VALUES (‗CSE6B‘, 6,‘B‘); INSERT INTO SEMSEC VALUES ('CSE6C‘, 6,‘C‘);

INSERT INTO SEMSEC VALUES (‗CSE5A‘, 5,'A‘); INSERT INTO SEMSEC VALUES ('CSE5B', 5,'B'); INSERT INTO SEMSEC VALUES (‗CSE5C', 5,'C');

INSERT INTO SEMSEC VALUES (‗CSE4A‘, 4,‘A‘); INSERT INTO SEMSEC VALUES ('CSE4B', 4,‘B‘); INSERT INTO SEMSEC VALUES (‗CSE4C‘, 4,'C‘);

INSERT INTO SEMSEC VALUES ('CSE3A', 3,'A'); INSERT INTO SEMSEC VALUES (‗CSE3B', 3,'B'); INSERT INTO SEMSEC VALUES (‗CSE3C‘, 3,‘C‘);

INSERT INTO SEMSEC VALUES ('CSE2A', 2,‘A‘); INSERT INTO SEMSEC VALUES (‗CSE2B‘, 2,'B‘); INSERT INTO SEMSEC VALUES ('CSE2C', 2,'C'); INSERT INTO SEMSEC VALUES (‗CSE1A', 1,'A');
 
INSERT INTO SEMSEC VALUES (‗CSE1B‘, 1,‘B‘); INSERT INTO SEMSEC VALUES ('CSE1C', 1,‘C‘);

INSERT INTO CLASS VALUES (‗1NH13CS020‘,‘CSE8A‘); INSERT INTO CLASS VALUES (‗1NH13CS062‘,‘CSE8A‘); INSERT INTO CLASS VALUES (‗1NH13CS066‘,‘CSE8B‘); INSERT INTO CLASS VALUES (‗1NH13CS091‘,‘CSE8C‘);

INSERT INTO CLASS VALUES (‗1NH14CS010‘,‘CSE7A‘); INSERT INTO CLASS VALUES (‗1NH14CS025‘,‘CSE7A‘); INSERT INTO CLASS VALUES (‗1NH14CS032‘,‘CSE7A‘);

INSERT INTO CLASS VALUES (‗1NH15CS011‘,‘CSE4A‘); INSERT INTO CLASS VALUES (‗1NH15CS029‘,‘CSE4A‘); INSERT INTO CLASS VALUES (‗1NH15CS045‘,‘CSE4B‘); INSERT INTO CLASS VALUES (‗1NH15CS091‘,‘CSE4C‘);

INSERT INTO CLASS VALUES (‗1NH16CS045‘,‘CSE3A‘); INSERT INTO CLASS VALUES (‗1NH16CS088‘,‘CSE3B‘); INSERT INTO CLASS VALUES (‗1NH16CS122‘,‘CSE3C‘);

INSERT INTO SUBJECT VALUES ('10CS81','ACA', 8, 4); INSERT INTO SUBJECT VALUES ('10CS82','SSM', 8, 4); INSERT INTO SUBJECT VALUES ('10CS83','NM', 8, 4); INSERT INTO SUBJECT VALUES ('10CS84','CC', 8, 4); INSERT INTO SUBJECT VALUES ('10CS85','PW', 8, 4);

INSERT INTO SUBJECT VALUES ('10CS71','OOAD', 7, 4); INSERT INTO SUBJECT VALUES ('10CS72','ECS', 7, 4); INSERT INTO SUBJECT VALUES ('10CS73','PTW', 7, 4); INSERT INTO SUBJECT VALUES ('10CS74','DWDM', 7, 4); INSERT INTO SUBJECT VALUES (‗10CS75','JAVA', 7, 4); INSERT INTO SUBJECT VALUES ('10CS76','SAN', 7, 4);

INSERT INTO SUBJECT VALUES ('15CS51', 'ME', 5, 4); INSERT INTO SUBJECT VALUES ('15CS52','CN', 5, 4); INSERT INTO SUBJECT VALUES ('15CS53','DBMS', 5, 4); INSERT INTO SUBJECT VALUES ('15CS54','ATC', 5, 4); INSERT INTO SUBJECT VALUES ('15CS55','JAVA', 5, 3); INSERT INTO SUBJECT VALUES ('15CS56','AI', 5, 3);
 
INSERT INTO SUBJECT VALUES ('15CS41','M4', 4, 4); INSERT INTO SUBJECT VALUES ('15CS42','SE', 4, 4); INSERT INTO SUBJECT VALUES ('15CS43','DAA', 4, 4); INSERT INTO SUBJECT VALUES ('15CS44','MPMC', 4, 4); INSERT INTO SUBJECT VALUES ('15CS45','OOC', 4, 3); INSERT INTO SUBJECT VALUES ('15CS46','DC', 4, 3);

INSERT INTO SUBJECT VALUES ('15CS31','M3', 3, 4); INSERT INTO SUBJECT VALUES ('15CS32','ADE', 3, 4); INSERT INTO SUBJECT VALUES ('15CS33','DSA', 3, 4); INSERT INTO SUBJECT VALUES ('15CS34','CO', 3, 4); INSERT INTO SUBJECT VALUES ('15CS35','USP', 3, 3); INSERT INTO SUBJECT VALUES ('15CS36','DMS', 3, 3);

INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1NH13CS091','10CS81','CSE8C', 15, 16, 18);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1NH13CS091','10CS82','CSE8C', 12, 19, 14);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1NH13CS091','10CS83','CSE8C', 19, 15, 20);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1NH13CS091','10CS84','CSE8C', 20, 16, 19);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1NH13CS091','10CS85','CSE8C', 15, 15, 12);


SELECT * FROM CLASS;

 

Queries:

1.	List all the student details studying in fourth semester ‘C’ section.
SELECT S.*, SS.SEM, SS.SEC
FROM STUDENT S, SEMSEC SS, CLASS C WHERE S.USN = C.USN AND
SS.SSID = C.SSID AND SS.SEM = 4 AND SS.SEC=‘C‘;

2.	Compute the total number of male and female students in each semester and in each section.

SELECT SS.SEM, SS.SEC, S.GENDER, COUNT (S.GENDER) AS COUNT FROM STUDENT S, SEMSEC SS, CLASS C
WHERES.USN = C.USN AND SS.SSID = C.SSID
GROUP BY SS.SEM, SS.SEC, S.GENDER ORDER BY SEM;














3.	Create a view of Test1 marks of student USN ‘1NH15CS011’ in all subjects.
CREATE VIEW STU_TEST1_MARKS_VIEW AS
SELECT TEST1, SUBCODE FROM IAMARKS
WHERE USN = '1NH15CS011';

4.	Calculate the FinalIA (average of best two test marks) and update the corresponding table for all students.

UPDATE IAMARKS
SET FinalIA = (TEST1 + TEST2 + TEST3 - LEAST(TEST1, TEST2, TEST3)) / 2;



5.	Categorize students based on the following criterion:
If FinalIA = 17 to 20 then CAT = ‘Outstanding’ If FinalIA = 12 to 16 then CAT = ‘Average’
If FinalIA< 12 then CAT = ‘Weak’
Give these details only for 8th semester A, B, and C section students.
SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER, (CASE
WHEN IA.FINALIA BETWEEN 17 AND 20 THEN 'OUTSTANDING' WHEN IA.FINALIA BETWEEN 12 AND 16 THEN 'AVERAGE' ELSE 'WEAK'
END) AS CAT
FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB WHERE S.USN = IA.USN AND
SS.SSID = IA.SSID AND SUB.SUBCODE = IA.SUBCODE AND SUB.SEM = 8;



Q11. Consider the schema for Company Database:

EMPLOYEE (SSN, Name, Address, Gender, Salary, SuperSSN, DNo)      DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)
DLOCATION (DNo,DLoc)
PROJECT (PNo, PName, PLocation, DNo) WORKS_ON (SSN, PNo, Hours)
Write SQL queries to
a.	Make a list of all project numbers for projects that involve an employee whose last name is ‘Scott’, either as a worker or as a manager of the department that controls the project.
b.	Show the resulting salaries if every employee working on the ‘IoT’ project is given a  10 percent raise.
c.	Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximum salary, the minimum salary, and the average salary in this department
d.	Retrieve the name of each employee who works on all the projects controlled by department number 5 (use NOT EXISTS operator). 
e.	For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than Rs. 6,00,000.

Entity-Relationship Diagram

 
Schema Diagram
                                                            






Table Creation

--Create Table DEPARTMENT with PRIMARY KEY as DNO

CREATE TABLE DEPARTMENT
(DNO VARCHAR(20) PRIMARY KEY,
DNAME VARCHAR(20),
MGR_SSN VARCHAR(20),
MGR_START_DATE DATE);

DESC DEPARTMENT;

--Create Table EMPLOYEE with PRIMARY KEY as SSN

CREATE TABLE EMPLOYEE
(SSN VARCHAR(20) PRIMARY KEY,
NAME VARCHAR(20),
ADDRESS VARCHAR(20),
SEX CHAR(1),
SALARY INTEGER,
SUPERSSN VARCHAR(20),
DNO VARCHAR(20),
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE (SSN),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNO));

DESC EMPLOYEE;

-- ADD FOREIGN KEY Constraint to DEPARTMENT table

ALTER TABLE DEPARTMENT
ADD FOREIGN KEY (MGR_SSN) REFERENCES EMPLOYEE(SSN);

--Create Table DLOCATION with PRIMARY KEY as DNO and DLOC and FOREIGN KEY DNO referring DEPARTMENT table

CREATE TABLE DLOCATION
(DLOC VARCHAR(20),
DNO VARCHAR(20),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNO),
PRIMARY KEY (DNO, DLOC));

DESC DLOCATION;

--Create Table PROJECT with PRIMARY KEY as PNO and FOREIGN KEY DNO referring DEPARTMENT table

CREATE TABLE PROJECT
(PNO INTEGER PRIMARY KEY,
PNAME VARCHAR(20),
PLOCATION VARCHAR(20),
DNO VARCHAR(20),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNO));

DESC PROJECT;

--Create Table WORKS_ON with PRIMARY KEY as PNO and SSN and FOREIGN KEY SSN and PNO referring EMPLOYEE and PROJECT table

CREATE TABLE WORKS_ON
(HOURS INTEGER,
SSN VARCHAR(20),
PNO INTEGER,
FOREIGN KEY (SSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (PNO) REFERENCES PROJECT(PNO),
PRIMARY KEY (SSN, PNO));

DESC WORKS_ON;



Table Descriptions

DESC EMPLOYEE;

DESC DEPARTMENT;

DESC DLOCATION;


DESC PROJECT;

DESC WORKS_ON;




Insertion of values to tables

--Inserting records into EMPLOYEE table

INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC01','BEN SCOTT','BANGALORE','M', 450000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC02','HARRY SMITH','BANGALORE','M', 500000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC03','LEAN BAKER','BANGALORE','M', 700000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC04','MARTIN SCOTT','MYSORE','M', 500000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC05','RAVAN HEGDE','MANGALORE','M', 650000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC06','GIRISH HOSUR','MYSORE','M', 450000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC07','NEELA SHARMA','BANGALORE','F', 800000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC08','ADYA KOLAR','MANGALORE','F', 350000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC09','PRASANNA KUMAR','MANGALORE','M', 300000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC10','VEENA KUMARI','MYSORE','M', 600000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES
 ('ABC11','DEEPAK RAJ','BANGALORE','M', 500000);

 SELECT * FROM EMPLOYEE;

--Inserting records into DEPARTMENT table

INSERT INTO DEPARTMENT VALUES ('1','ACCOUNTS','ABC09', '2016-01-03');
INSERT INTO DEPARTMENT VALUES ('2','IT','ABC11', '2017-02-04');
INSERT INTO DEPARTMENT VALUES ('3','HR','ABC01', '2016-04-05');
INSERT INTO DEPARTMENT VALUES ('4','HELPDESK', 'ABC10', '2017-06-03');
INSERT INTO DEPARTMENT VALUES ('5','SALES','ABC06', '2017-01-08');

SELECT * FROM DEPARTMENT;

--Updating EMPLOYEE records

UPDATE EMPLOYEE SET
SUPERSSN=NULL, DNO='3'
WHERE SSN='ABC01';

UPDATE EMPLOYEE SET
SUPERSSN='ABC03', DNO='5'
WHERE SSN='ABC02';

UPDATE EMPLOYEE SET
SUPERSSN='ABC04', DNO='5'
WHERE SSN='ABC03';

UPDATE EMPLOYEE SET
SUPERSSN='ABC06', DNO='5'
WHERE SSN='ABC04';

UPDATE EMPLOYEE SET
DNO='5', SUPERSSN='ABC06'
WHERE SSN='ABC05';

UPDATE EMPLOYEE SET
DNO='5', SUPERSSN='ABC07'
WHERE SSN='ABC06';

UPDATE EMPLOYEE SET
DNO='5', SUPERSSN=NULL
WHERE SSN='ABC07';

UPDATE EMPLOYEE SET
DNO='1', SUPERSSN='ABC09'
WHERE SSN='ABC08';

UPDATE EMPLOYEE SET
DNO='1', SUPERSSN=NULL
WHERE SSN='ABC09';

UPDATE EMPLOYEE SET
DNO='4', SUPERSSN=NULL
WHERE SSN='ABC10';

UPDATE EMPLOYEE SET
DNO='2', SUPERSSN=NULL
WHERE SSN='ABC11';

SELECT * FROM EMPLOYEE;

--Inserting records into DLOCATION table

INSERT INTO DLOCATION VALUES ('BENGALURU', '1');
INSERT INTO DLOCATION VALUES ('BENGALURU', '2');
INSERT INTO DLOCATION VALUES ('BENGALURU', '3');
INSERT INTO DLOCATION VALUES ('MYSORE', '4');
INSERT INTO DLOCATION VALUES ('MYSORE', '5');

SELECT * FROM DLOCATION;

--Inserting records into PROJECT table

INSERT INTO PROJECT VALUES (1000,'IOT','BENGALURU','5');
INSERT INTO PROJECT VALUES (1001,'CLOUD','BENGALURU','5');
INSERT INTO PROJECT VALUES (1002,'BIGDATA','BENGALURU','5');
INSERT INTO PROJECT VALUES (1003,'SENSORS','BENGALURU','3');
INSERT INTO PROJECT VALUES (1004,'BANK MANAGEMENT','BENGALURU','1');
INSERT INTO PROJECT VALUES (1005,'SALARY MANAGEMENT','BANGALORE','1');
INSERT INTO PROJECT VALUES (1006,'OPENSTACK','BENGALURU','4');
INSERT INTO PROJECT VALUES (1007,'SMART CITY','BENGALURU','2');

SELECT * FROM PROJECT;

--Inserting records into WORKS_ON table

INSERT INTO WORKS_ON VALUES (4, 'ABC02', 1000);
INSERT INTO WORKS_ON VALUES (6, 'ABC02', 1001);
INSERT INTO WORKS_ON VALUES (8, 'ABC02', 1002);
INSERT INTO WORKS_ON VALUES (10,'ABC03', 1000);
INSERT INTO WORKS_ON VALUES (3, 'ABC05', 1000);
INSERT INTO WORKS_ON VALUES (4, 'ABC06', 1001);
INSERT INTO WORKS_ON VALUES (5, 'ABC07', 1002);
INSERT INTO WORKS_ON VALUES (6, 'ABC04', 1002);
INSERT INTO WORKS_ON VALUES (7, 'ABC01', 1003);
INSERT INTO WORKS_ON VALUES (5, 'ABC08', 1004);
INSERT INTO WORKS_ON VALUES (6, 'ABC09', 1005);
INSERT INTO WORKS_ON VALUES (4, 'ABC10', 1006);
INSERT INTO WORKS_ON VALUES (10,'ABC11', 1007);

SELECT * FROM WORKS_ON;
Queries:

1.	Make a list of all project numbers for projects that involve an employee whose last name is ‘Scott’, either as a worker or as a manager of the department that controls the project.

SELECT DISTINCT P.PNO
FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
WHERE E.DNO=D.DNO
AND D.MGR_SSN=E.SSN
AND E.NAME LIKE '%SCOTT'
UNION
SELECT DISTINCT P1.PNO
FROM PROJECT P1, WORKS_ON W, EMPLOYEE E1
WHERE P1.PNO=W.PNO
AND E1.SSN=W.SSN
AND E1.NAME LIKE '%SCOTT';



2.	Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.
SELECT E.NAME, 1.1*E.SALARY AS INCR_SAL
FROM EMPLOYEE E, WORKS_ON W, PROJECT P
WHERE E.SSN=W.SSN
AND W.PNO=P.PNO
AND P.PNAME='IOT';

3.	Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximum salary, the minimum salary, and the average salary in this department

SELECT SUM(E.SALARY), MAX(E.SALARY), MIN(E.SALARY), AVG(E.SALARY)
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO=D.DNO
AND D.DNAME='ACCOUNTS';


4.	Retrieve the name of each employee who works on all the projects Controlled by department number 5 (use NOT EXISTS operator).

SELECT E.NAME
FROM EMPLOYEE E
WHERE NOT EXISTS(SELECT PNO FROM PROJECT WHERE DNO='5' AND PNO NOT IN (SELECT
PNO FROM WORKS_ON
WHERE E.SSN=SSN));

5.	For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than Rs. 6, 00,000.

SELECT D.DNO, COUNT(*)
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.DNO=E.DNO
AND E.SALARY > 600000
AND D.DNO IN (SELECT E1.DNO
FROM EMPLOYEE E1
GROUP BY E1.DNO
HAVING COUNT(*)>5)
GROUP BY D.DNO;

exp 6;;;;;;;;;;;;;;;;;;;;;;;;
Experiment 5A
Creating Triggers
Aim:
To create appropriate triggers for the given database to monitor the database continuously.
Create a trigger to set order_quantity to default value 3 before update on orders if
order_quantity is greater than 5.
Create a trigger to count total no: of orders under ordername ‘OldYear’ in a new table
count_order on every insertion.
Result:
Thus, the triggers are created to ensure the continuous monitoring of the database and it
responses were verified.
Experiment – 6
Queries on Employee Database
Aim :
To create a given employee relation and query it using SQL constructs.
Employee (SSN , Name , Birthdate , Address , Gender , Salary , Dnumber , DName ,
DLocation)
SQL constructs to create table Employee:
SQL Constructs to Insert Values
Query 1: Calculate the average salary for each different job.
Query 2: Find the average salary of all departments with more than 3 people
Query 3: Display age between birth date to current date
Result:
Thus, an employee database has been created and the database was queried for the given
requirements.
Experiment – 7
Queries on Library Database
Aim:
To create a given library database and query it is using SQL constructs.
BOOK(Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS(Book_id, Author_Name)
PUBLISHER(Name, Address, Phone)
BOOK_COPIES(Book_id, Programme_id, No-of_Copies)
BOOK_LENDING(Book_id, Programme_id, Card_No, Date_Out, Due_Date)
LIBRARY_PROGRAMME(Programme_id, Programme_Name, Address)
SQL queries to create relations:
SQL constructs to insert values:




Query 1 : --Retrieve details of all books in the library – id, title, name of publisher, authors,
number of copies in each Programme, etc.
Query 2: --Get the particulars of borrowers who have borrowed more than 3 books, but
from Jan 2017 to Jun 2017.
Query 3: Delete a book in BOOK table. Update the contents of other tables to reflect this
data manipulation operation.
Query 4: Partition the BOOK table based on year of publication. Demonstrate its working
with a simple query.
Query 5 : Create a view of all books and its number of copies that are currently available in
the Library.
Result:
Thus, a Library database has been created and he database was queried for the given
requirements.
Experiment – 8
Queries on Order Database
Aim:
To create a given library database and query it is using SQL constructs
SALESMAN (Salesman_id, Name, City, Commission)
CUSTOMER (Customer_id, Cust_Name, City, Grade,Salesman_id)
ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)
SQL queries to create relations:
SQL queries to insert values:
Query 1 : Count the customers with grades above Bangalore’s average.
Query 2: Find the name and numbers of all salesman who had more than one customer
Query 3: List all the salesman and indicate those who have and don’t have customers in
their cities (Use UNION operation.)
Query 4: Create a view that finds the salesman who has the customer with the highest
order of a day.
Query 5: Demonstrate the DELETE operation by removing salesman with id 1000. All his
orders must also be deleted.
Result:
Thus, an order database has been created and the database was queried for the given
requirements.