ttass sant Munnnpearner aurwriee ieee ‘ SECTION-A 1X5=5 Table :STOCK Table: ITEM. ERASER Write the Query and Output for the following based on the above given tables: 1, CROSS JOIN 2. EQUI JOIN 3. UNION 4, Differentiate between char and Varchar datatype. 5. Differentiate the Primary and unique constraints. SECTION-B 3X2=6 6. Define the terms: a) Database b) DBMS c) Degree d) Cardinality 7. How are SQL Commands classified? 8. What are the different keys in SQL? Explain with example. SECTION-C 2X3=6 9. Consider the following table named “Product”, showing detalls of products being sold in a grocery shop. Table: Product _ _ _ PCode _| PName _| uPrice Manufacturer _| Po. Washing Powder _| 120_ [Surf PO2 Toothpaste 54 Colgate Scanned with CamScannerWrite SQL commands for (a) to (d) and write output for (e). a) Add anew column Discount in the table Product b) Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all ‘those products where the UPrice is more than 100, otherwise the discount will be 0. ©) Increase the price by 12 per cent for all the products manufactured by Dove 4) Display the total number of products manufactured by each manufacturer ) Give the output of the following SQL Statements: i. Select PName, Average(UPrice) from Product GROUP BY PName; li, SELECT DISTINCT Manufacturer FROM Product; ili, SELECT COUNT (DISTINCT PName) FROM Product; iv. SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName; 10. Write SQL Queries based on the following tables: Table: Product PID Product Name | Manufacturer_| Price Discount TPO1 Talcum LAK 40 L Powder Fwos Face Wash ABC [45 5 BSO1 Bath Soap ABC 35 SHO6 ‘Shampoo XYZ 120 —_-|10- 7 Fwiz Face Wash. _| XYZ 95 Table: CLIENT CID Client Name City PID 01 Cosmetic Soap Delhi POL | 02 Total Health Mumbai FWOS 3 live Life | Delhi BSO1 04 Pretty Woman Delhi SHO6 fl 05, Dreams Delhi Fwi2 a) Write SL Query to display ProductName and Price for all products whose Price is in the range 50 to 150. b) Write SQL Query to display details of products whose manufacturer is either XYZ or ABC. ) Write SQL Query to display ProductName, Manufacturer and Price for all products that are not giving any discount. d) Write SQL Query to display ProductName and Price for all products whose ProductName ends with ‘h’. e) Write SQL Query to display ClientName, City, P_ID and ProductName for all clients whose city is Delhi. f) Display the count of Products Manufacturer wise. Scanned with CamScanner11.Given the following student relation: SECTION-D [No [Name | Age [Department | Da 7 | Pankaj _| 24 _| Computer 10/01/97 _ Shalini 21 History 24/03/98 Sanjay 2 Hin ~_ | 32/12/96 [Sudha 25 [History _—(| 02/07/99 _ | Rakesh 22 | Hindi _| 05/09/97 jshakeet/30 [History _—| 27/06/98 _ “Tsurya [34 [Computer _[ 25/02/87 8 Shika | 23 [Hindi 31/07/97 Write SQL commands for (a) to (f) and write output for (g)- a) To show all information about b) To list the names of female stu ¢) Tolist names of all students wit d) To display student’s Name, Fee, Age for the students of History department dents who are in Hindi department h their date of admission in ascending order. male Students only e) To count the number of student with Age<23. f) Toinsert anew row inthe student table with the following data: 9,"Zaheer”,36,"Computer”,{12/03/97},230,"M" g) Give the output of the following SQL Statements: iv. 12. Given the Select COUNT(distinct department) from STUDENT; Select MAX(Age) from STUDENT where Sex="F"; Select AVG(Fee) from STUDENT where Dateofadm<{01/01/98}; Select SUM(Fee) from STUDENT where Dateofadm<{01/01/98}; following tables for a database LIBRARY: Table:BOOKS Book Id | Book Name | Author Name |Publishers | Price Type Qty. Fco001 |FastCook | LataKapoor _| EPB 355 Cookery [5 F0001 |The Tears | William First Publ. | 650 Fiction 20 Hopkins T0001 | MyFirstc++ |Brain& Broke _| EPB 350 Text 10 Tooo2 | CH AW.Rossaine | TOH 350 Text 15 Brainworks 0002 | Thunderbolts | Anna Roberts | First Publ. _| 750 Fiction | 50 Table: ISSUED Book_Id ‘Quantity_Issued - 7 Tooo1 4 cooo1 a 5 _ | F000. = 2 Scanned with CamScannerWeite SQt commands for (a) to (1) and write output for (n). 4) To show ook name, author name and Priee of books of Fitst Publ publishers. D) To list the names trom books of Text type, €) To display the names ani 4) To increase the i! price from books in ascending order of their price. ‘0) To display the price of all books of EPB Publishers by 50, Book ld, Book_Name and Quantity. Issued for all hooks which have been issued, j {) To insert a new row in the table Issued having the following data: "F0003", 1 8) Give the output of the following SQL Statements: |, SELECT COUNT(*) FROM Books; li, SELECT MAX(Price) FROM Books WHERE Quantity>=15; iii, SELECT Book_Name. »Author_Name FROM Books WHERE Publishers="EPB”; iv. SELECT COUNT(Distinct Publishers) FROM BOOKS WHERE Price>=400; Scanned with CamScanner