WIXLIB

3.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 to1. List the titles of all movies directed by ‘Hitchcock’.2. Find the movie names where one or more actors acted in two or more movies.3. List all actors who acted in a movie before 2000 and in a movie after 2015 (use JOIN operation).4. Find the title of movies and number of stars for each movie that has at least one rating and find thehighest number of stars that movie received. Sort the result by movie title.5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.4.Consider the schema for College Database:STUDENT(USN, SName, Address, Phone, Gender)SEMSEC(SSID, Sem, Sec)CLASS(USN, SSID)COURSE(Subcode, Title, Sem, Credits)IAMARKS(USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)Write SQL queries to1. List all the student details studying in fourth semester ‘C’ section.2. Compute the total number of male and female students in each semester and in each section.3. Create a view of Test1 marks of student USN ‘1BI15CS101’ in all Courses.4. Calculate the FinalIA (average of best two test marks) and update the corresponding table for allstudents.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.5.Consider the schema for Company Database:EMPLOYEE(SSN, Name, Address, Sex, Salary, SuperSSN, DNo)DEPARTMENT(DNo, DName, MgrSSN, MgrStartDate)DLOCATION(DNo,DLoc)PROJECT(PNo, PName, PLocation, DNo)WORKS ON(SSN, PNo, Hours)Write SQL queries to1. 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.

2. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.3. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximumsalary, the minimum salary, and the average salary in this department.4. Retrieve the name of each employee who works on all the projects controlled by department number 5(use NOT EXISTS operator).5. For each department that has more than five employees, retrieve the department number and the numberof its employees who are making more than Rs. 6,00,000.Part-B: Mini ProjectNote: Use Java, C#, PHP, Python, or any other similar front-end tool. All applications must bedemonstrated on desktop/laptop as a stand-alone or web-based application (Mobile apps onAndroid/IOS are not permitted.)For any problem selected Make sure that the application should have five or moretables.Indicative areas include: health care.

Maharaja Institute of Technology MysoreDepartment of Computer Science & EngineeringIndexSubject: DBMS Laboratory with Mini ProjectSubject Code: 18CSL58SL.No.ContentsPageNo.1Basic Concepts of SQL1-42A: Lab Program-1Library Database5-153B: Lab Program-2Order Database15-224C: Lab Program-3Movie Database23-325D: Lab Program-4College Database33-476E: Lab Program-5Company Database48-607Viva Questions & Answers61-70

Maharaja Institute of Technology MysoreDepartment of CS&EBASIC CONCEPTS OF SQLIntroduction to SQLSQL stands for “Structured Query Language” and can be pronounced as “SQL” or“sequel – (Structured English Query Language)”. It is a query language used for accessing andmodifying information in the database. IBM first developed SQL in 1970s. Also it is anANSI/ISO standard. It has become a Standard Universal Language used by most of the relationaldatabase management systems (RDBMS). Some of the RDBMS systems are: Oracle, MicrosoftSQL server, Sybase etc. Most of these have provided their own implementation thus enhancingits feature and making it a powerful tool. Few of the SQL commands used in SQL programmingare SELECT Statement, UPDATE Statement, INSERT INTO Statement, DELETE Statement,WHERE Clause, ORDER BY Clause, GROUP BY Clause, ORDER Clause, Joins, Views,GROUP Functions, Indexes etc.SQL CommandsSQL commands are instructions used to communicate with the database to performspecific task that work with data. SQL commands can be used not only for searching thedatabase but also to perform various other functions like, for example, you can create tables, adddata to tables, or modify data, drop the table, set permissions for users.CREATE TABLE StatementThe CREATE TABLE Statement is used to create tables to store data. Integrity Constraints likeprimary key, unique key and foreign key can be defined for the columns while creating the table. Theintegrity constraints can be defined at column level or table level. The implementation and the syntaxof the CREATE Statements differs for different RDBMS.The Syntax for the CREATE TABLE Statement is:CREATE TABLEtable name(column name1 datatype constraint,column name2 datatype,.column nameNdatatype);DBMS Laboratory with Mini Project (17CSL58)Page 1

Maharaja Institute of Technology MysoreDepartment of CS&ESQL Data Types:char(size)Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.Varchar2(size)number(size)Variable-length character string. Max size is specified in parenthesis.Number value with a max number of column digits specified in parenthesis.or intDateDate value in „dd-mon-yy‟. Eg., ‟07-jul-2004‟number(size,Number value with a maximum number of digits of "size" total, with ad) or realmaximum number of "d" digits to the right of the decimal.SQL Integrity Constraints:Integrity Constraints are used to apply business rules for the database tables.The constraintsavailable in SQL are Foreign Key, Primary key, Not Null, Unique, Check.Constraints can be defined in two ways:1. The constraints can be specified immediately after the column definition. This is called column-leveldefinition.2. The constraints can be specified after all the columns are defined. This is called table- level definition.1) Primary key:This constraint defines a column or combination of columns which uniquely identifies each row inthe table.Syntax to define a Primary key at column level:Column namedatatype [CONSTRAINT constraint name] PRIMARY KEYSyntax to define a Primary key at table level:[CONSTRAINTconstraint name]PRIMARYKEY(column name1,column name2,.)2) Foreign key or Referential Integrity:This constraint identifies any column referencing the PRIMARY KEY in another table. Itestablishes a relationship between two columns in the same table or between different tables. For acolumn to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it isreferring. One or more columns can be defined as foreign key.DBMS Laboratory with Mini Project (17CSL58)Page 2

Maharaja Institute of Technology MysoreDepartment of CS&ESyntax to define a Foreign key at column level:[CONSTRAINT constraint name] REFERENCESreferenced table name(column name)3) Not Null Constraint:This constraint ensures all rows in the table contain a definite value for the column which isspecified as not null. Which means a null value is not allowed.Syntax to define a Not Null constraint:[CONSTRAINT constraint name] NOT NULL4) Unique Key:This constraint ensures that a column or a group of columns in each row have a distinct value.A column(s) can have a null value but the values cannot be duplicated.Syntax to define a Unique key at column level:[CONSTRAINT constraint name] UNIQUESyntax to define a Unique key at table level:[CONSTRAINT constraint name] UNIQUE(column name)5) Check Constraint:This constraint defines a business rule on a column. All the rows must satisfy this rule. Theconstraint can be applied for a single column or a group of columns.Syntax to define a Check constraint:[CONSTRAINT constraint name] CHECK (condition)ALTER TABLE StatementThe SQL ALTER TABLE command is used to modify the definition structure) of a table bymodifying the definition of its columns. The ALTER command is used to perform the followingfunctions.1) Add, drop, modify table columns2) Add and drop constraints3) Enable and Disable constraintsDBMS Laboratory with Mini Project (17CSL58)Page 3

Maharaja Institute of Technology MysoreDepartment of CS&EThe HAVING clauseThe HAVING clause can be used to restrict the display of grouped rows. The result of the groupedquery is passed on to the HAVING clause for output filtration.The INSERT INTO StatementThe INSERT INTO statement is used to insert a new row in a table.The UPDATE StatementThe UPDATE statement is used to update existing records in a table.The DELETE StatementThe DELETE statement is used to delete rows in a table. SQLDELETECommit commandCommit command is used to permanently save any transaaction into databaseRollback commandThis command restores the database to last commited state. It is also use with savepoint command tojump to a savepoint in a transaction.Savepoint commandsavepoint command is used to temporarily save a transaction so that you can rollback to that pointwhenever necessary.DBMS Laboratory with Mini Project (17CSL58)Page 4

Maharaja Institute of Technology MysoreDepartment of CS&ELAB EXPERIMENTSPART A: SQL PROGRAMMINGA. 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, Noof Copies)BOOK LENDING (Book id, Branch id, Card No, Date Out,Due Date) LIBRARY BRANCH (Branch id, Branch Name, Address)1.2.3.4.5.Write SQL queries toRetrieve details of all books in the library – id, title, name of publisher, authors,number of copies in each branch, etc.Get the particulars of borrowers who have borrowed more than 3 books, but from Jan2017 to Jun 2017Delete a book in BOOK table. Update the contents of other tables to reflect this datamanipulation operation.Partition the BOOK table based on year of publication. Demonstrate its working with asimple query.Create a view of all books and its number of copies that are currently available in theLibrary.Solution:Entity-Relationship DiagramAuthor NameBook idTitlePub YearMNBookwritten-byBook AuthorsNHasPublished-byNNo of copiesBranch idPublisher NameM1MBook CopiesNInLibrary BranchBranch NameAddressPublisherAddressNDate outBook LendingPhoneCard NoDue dateNDBMS Laboratory with Mini Project (17CSL58)CardPage 5

Maharaja Institute of Technology MysoreDepartment of CS&ESchema DiagramBookBook id Title Pub Year Publisher NameBook AuthorsBook id Author namePublisheraBook eCopiesPhonenoAddressBook BLoending Branokch iiddBookidBranch idNo ofCopiesCardnoDate outDuedateLibrary BranchBranch idAddressBranchnameTable CreationCREATE TABLE PUBLISHER(NAME VARCHAR2 (20) PRIMARYKEY, PHONE INTEGER,DBMS Laboratory with Mini Project (17CSL58)Page 6

Maharaja Institute of Technology MysoreDepartment of CS&EADDRESS VARCHAR2 (20));CREATE TABLE BOOK(BOOK ID INTEGER PRIMARYKEY, TITLE VARCHAR2 (20),PUB YEAR VARCHAR2 (20),PUBLISHER NAME REFERENCES PUBLISHER (NAME) ON DELETE CASCADE);DBMS Laboratory with Mini Project (17CSL58)Page 7

Maharaja Institute of Technology MysoreDepartment of CS&ECREATE TABLEBOOK AUTHORS(AUTHOR NAME VARCHAR2(20),BOOK ID REFERENCES BOOK (BOOK ID) ON DELETECASCADE, 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)CASCADE,PRIMARY KEY (BOOK ID, BRANCH ID));ONDELETEONDELETECREATE 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)CASCADE,CARD NO REFERENCES CARD (CARD NO) ON DELETECASCADE, PRIMARY KEY (BOOK ID, BRANCH ID, CARD NO));Table DescriptionsDESC PUBLISHER;DESC BOOK;DBMS Laboratory with Mini Project (17CSL58)Page 8

Maharaja Institute of Technology MysoreDepartment of CS&EDESC BOOK AUTHORS;DESC LIBRARY BRANCH;DESC BOOK COPIES;DESC CARD;DESC BOOK LENDING;DBMS Laboratory with Mini Project (17CSL58)Page 9

Maharaja Institute of Technology MysoreDepartment of CS&EInsertion of Values to TablesINSERT 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„, ‗MCGRAWHILL„); INSERT INTO BOOK VALUES (3,„CN„,„SEP-2016„, ‗PEARSON„);INSERT INTO BOOK VALUES (4,„CG„,„SEP-2015„, ‗GRUPOPLANETA„); 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);INSERTINTOCARDVALUES (101); INSERT INTOCARD VALUES (102); INSERTDBMS Laboratory with Mini Project (17CSL58)Page 10

Maharaja Institute of Technology MysoreINTO CARDINSERT INTO(104);Department of CS&EVALUES (103);CARD VALUESDBMS Laboratory with Mini Project (17CSL58)Page 11

Maharaja Institute of Technology MysoreDepartment of CS&EINSERT 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;DBMS Laboratory with Mini Project (17CSL58)Page 12

Maharaja Institute of Technology MysoreDepartment of CS&ESELECT * 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.SELECTB.BOOK ID, B.TITLE, B.PUBLISHER NAME, A.AUTHOR NAME,C.NO OF COPIES, L.BRANCH IDFROM BOOK B, BOOK AUTHORS A, BOOK COPIES C, LIBRARY BRANCH LWHERE B.BOOK ID A.BOOK IDAND B.BOOK ID C.BOOK IDAND L.BRANCH ID C.BRANCH ID;DBMS Laboratory with Mini Project (17CSL58)Page 13

Maharaja Institute of Technology MysoreDepartment of CS&E1. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan2017 to Jun 2017.SELECT CARD NO FROMBOOK LENDINGWHERE DATE OUT BETWEEN „01-JAN-2017„ AND „01-JUL-2017„GROUP BY CARD NOHAVING COUNT (*) 3;2. Delete a book in BOOK table. Update the contents of other tables to reflect this datamanipulation operation.DELETE FROM BOOKWHERE BOOK ID 3;3. Partition the BOOK table based on year of publication. Demonstrate its working with asimple query.CREATE VIEW V PUBLICATION ASSELECT PUB YEARFROM BOOK;DBMS Laboratory with Mini Project (17CSL58)Page 14

Maharaja Institute of Technology MysoreDepartment of CS&E4. Create a view of all books and its number of copies that are currently available in theLibrary.CREATE VIEW V BOOKS ASSELECT B.BOOK ID, B.TITLE, C.NO OF COPIESFROM BOOK B, BOOK COPIES C, LIBRARY BRANCH LWHERE B.BOOK ID C.BOOK IDAND C.BRANCH ID L.BRANCH ID;DBMS Laboratory with Mini Project (17CSL58)Page 15

Maharaja Institute of Technology MysoreDepartment of CS&EB. Consider the following schema for Order Database:1.2.3.4.5.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 toCount the customers with grades above Bangalore’s average.Find the name and numbers of all salesmen who had more than one customer.List all salesmen and indicate those who have and don’t have customers in their cities(Use UNION operation.)Create a view that finds the salesman who has the customer with the highest order of aday.Demonstrate the DELETE operation by removing salesman with id 1000. All his ordersmust also be deleted.Solution:Entity-Relationship DiagramDBMS Laboratory with Mini Project (17CSL58)Page 16

Maharaja Institute of Technology MysoreDepartment of CS&ESchema DiagramSalesmanCommissionSalesman idCustomerCustomer idOrrd rNsoCustNamePurchaseAmtGradeOrdDateSalesman idCustomer idSalesman idTable CreationCREATE TABLE SALESMAN(SALESMAN ID NUMBER (4),NAME VARCHAR2 (20),CITY VARCHAR2 (20),COMMISSION VARCHAR2 (20),PRIMARY KEY(SALESMAN ID));CREATE TABLECUSTOMER1(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);DBMS Laboratory with Mini Project (17CSL58)Page 17

Maharaja Institute of Technology MysoreDepartment of CS&ECREATE TABLEORDERS (ORD NONUMBER (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);DBMS Laboratory with Mini Project (17CSL58)Page 18

Maharaja Institute of Technology MysoreDepartment of CS&ETable DescriptionsDESC SALESMAN;DESC CUSTOMER1;DESC ORDERS;Insertion of Values to TablesINSERT 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);DBMS Laboratory with Mini Project (17CSL58)Page 19

Maharaja Institute of Technology MysoreDepartment of CS&EINSERT 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) FROMCUSTOMER1GROUP BY GRADEHAVING GRADE (SELECT AVG(GRADE)FROM CUSTOMER1WHERE CITY 'BANGALORE');DBMS Laboratory with Mini Project (17CSL58)Page 20

Maharaja Institute of Technology MysoreDepartment of CS&E2. Find the name and numbers of all salesmen who had more than one customer.SELECT SALESMAN ID, NAME FROMSALESMAN AWHERE 1 (SELECT COUNT (*) FROMCUSTOMER1WHERE SALESMAN ID A.SALESMAN ID);3. List all salesmen

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