Questions 1. Consider the following table named SBOP with details of account holders. Write commands of MySq] for (i) to (iv) and output for (v) to (vii). TABLE SBOP Balance _ Dateofopen Transaction SB-1_—_ Mr. Anil 15000.00 2011-02-24 7 SB-2 Mr. Amit 23567.86 8 SB-3 Mr. Sakshi 45000.00 2012-02-04 5 SB-4 [Link] —__23812.35 2013-09-22 SB-5 Mr. Dennis 63459.80 2009-11-10 a5 (i) To display Accountno, Name and DateOfopen of account holders having transactions more than 8. (ii) To display all information of account holders whose transaction value is not mentioned. (iii) To add another column Address with datatype and size as VARCHAR(25). (iv) To display the month day with reference to DateOfopen for all the account holders. (v) SELECT COUNT(*) FROM SBOP; (vi) SELECT Name, Balance FROM SBOP WHERE Name LIKE "%i"; (vii) SELECT ROUND (Balance,-3) FROM SBOP WHERE Accountno="SB-5" @ scanned with OKEN Scanner2. Consider the following taby ‘ ‘© named EX, ‘i ‘i marks. Write command of ‘AM with details of M ) to (4 (v) to (vii): Table EXAM SL for (i to (iv) and output for gon‘ Adon eed Percentage Clsection Stream ROO Sushant 90.2 “DA i R001 Science 7002 Vaidyanath 80.5 2B Humanities jumanities R003 Miara 68.9 128 Science ROO4 Niara 96.0 2A Commerce ROOS Shi 88.9 120 Commerce {i) To display all information of the students of humanities in descending order of percentage. {ii) To display Adno, Name, Percentage and Stream of those students whose name is less than 6 characters long. (ii) To add another column Bus_Fees with datatype and size as Decimal(6,2). (iv) To increase percentage by 2% of all the Humanities students. (All India 2014) (v) SELECT COUNT(*) FROM EXAM; (vi) SELECT SName, Percentage FROM EXAM WHERE Name LIKE "N%"; (vii) SELECT ROUND (Percentage,0) FROM EXAM WHERE Adno="RO05"; @ scanned with OKEN ScannerSUPPLIER Pname ~raR Coffee PE ee 308 Biscuit i 200 otis i Hide and seek 199 Delhi 3 —_$6.00 lam is Skee 108 _Jom___Kissan_ "410 Kolkata 5.00 “304_Maggi_Nestle 150 wumtel a0 — 405 Chocolate Cadbury Tp pmb _20.0 106 __ Sauce Maggi 56 Mumba =m 107 Cake Britania 72 Delhi 10.00 {i)To display product name whose name start with B in order by price. (ii) To display scode, pname and city of supplier where quantity is less than 150. {ii To count distinct City in the table. (iv) To insert a new row in the table SUPPLIER. 110, "Bournvita","ABC", 170, "Delhi", 40.00 (Delhi 2012) (v) SELECT Pname FROM SUPPLIER WHERE Supname IN ('Bread, 'Maget'); (vi) SELECT COUNT(DISTINCT City) FROM SUPPLIER; (vii) SELECT MAX(Price) FROM SUPPLIER WHERE Ci @ scanned with OKEN Scanner4, Consider the table SHOPEE Biven b En belo MySQL for (i) to (iv) ang leah. Ww. Write commands in ir (v! ) to (vii). TABLE SHOPEE “$code Pname 402 __ Biscuit “40: Jam “106__Sauce_Maggi 407__Cake Britania 104 Maggi Nestle 150 Mumbai 10.00 105 Chocolate Cadbury 170 Delhi 25, i 00 (i) To display names of the product, whose name starts with” C" order of Price. in ascending (i) To display code, product name and City of the products whose quantity is less than 100. (ii) To count distinct Company in the table. (iv) To insert a new row in the table SHOPEE. 110, "Pizza", "Papa Jones", 120, "Kolkata", 50.00 (All India 2012) (v) SELECT Prame FROM SHOPEE WHERE Pname IN ("Jam", "Coffee"); (vi) SELECT COUNT(DISTINCT City) FROM SHOPEE; (vii) SELECT MAX(Qty) FROM SHOPEE WHERE City = "Mumbai" @ scanned with OKEN Scannerconsider the table PERSONS given below. Write com: ds I mands i sal for(i) to (iv) and write output for (v) to ( so .TABLE PERSONS Firstname Gender City. a ae / _Basisalary = Sender Cry" _Pincode _Beslsalary spate Geeta F Udhamwara qeziai 50000 4 —~surinder Meek oa upwal 75000 4 2 eee 193222 75000 : an —_—--— e = wan 185155 45000 arima Garim M Nagar coolangatta 390028 ~—«33000— F Udhamwara 182141 42000 he SurName, FirstName and City of people residing in Udhamwara (i pisplay tI city. ti isplay the Person IDs (PID), Cities and Pincode of persons in descending order of Pincode. (i) Display the FirstName and City of all the females getting Basic salaries above 40000. {iv) Display FirstName and BasicSalaries of all the persons whose first name start with ,,G". (Delhi 2012c) (v) SELECT SurName FROM PERSONS WHERE BasicSalary>= 50000; (vi) SELECT SUM (BasicSalary) FROM PERSONS WHERE Gender =F"; (vil) SELECT Gender, MIN (BasicSalary) FROM PERSONS GROUP BY Gender; (vil SELECT Gender, COUNT (*) FROM PERSONS GROUP By Gender; @ scanned with OKEN Scannerider the ti seu UIC EXAM Biven below, we MySQL for ()£0.¥) and output fog nn nt commands i OF (V) to (vii et (vii). TABLE Name ii). TABU EXAM wo. Stipend Subjee ——.- a 705 tae Average Division aes 0. is i Amal 680 five sbeed | athematics 72 Fae — 4 Vedant 500 eo a Accounts 67 ~FISRT 4 Shakeer 200 = Informatics 55 SECOND. 3 Anandha 400g a ha an istory 85 “FIRST 6a Upasana 500 ee “Geog sraphy 45 THIRD - raphy 45 THIRD (i) To list the Names of those students, who have obtained Division as FIRST in ascending order of Name. (ii) To display a report listing Name, Subject and Annual Stipend received assuming that the Stipend column has monthly Stipend. {iil) To count the number of students, who have either Accounts or Informatics as Subject. {iv) To insert a new row in the table EXAM. 6,"Mohan", 500, "English", 73, "SECOND" (HOTS; Delhi 2011) (\) SELECT AVG(Stipend) FROM EXAM WHERE Division = "THIRD"; (vi) SELECT COUNT (DISTINCT Subject) FROM EXAM; ject = "English"; (vi) SELECT MIN(Average) FROM EXAM WHERE Subject = "Englis @ scanned with OKEN Scanner1 consider the table DOCTOR given belo Z w. Wi er rite com: sal. for (i) to (iv) and Output for (v) to eel mands in 7 £ DOCTOR é [Link] Department Gender Salary [aera = _-—pnita Hans Pediatri 19984046 7 ee oo a se y oe F 40000 {7 Wee Thomas Surgery 1994-10-20 mM 55000 ' 5 Gurpreet Kaur Pediatrics 1999-11-24 F 52000 Anandini Burman Oncology 1994-03-16 F 31000 7 Siddharth Dang Surgery 1995-09-08 Mm 47000 3 Rama Mukherjee oncology 2000-06-27 F 54500 {) Display the names and dates of joining of doctors of Oncology department. (i) Display the names and salaries of doctors in descending order of salaries. ii) Display the names and salaries of al the female's doctors who are getting salary above S 50000. {iv) Display names of each department along with total salary being given to doctors of that department. (Delhi 2011) (v) SELECT Department FROM DOCTOR WHERE Salary >= 55000; (vi) SELECT SUM (Salary) FROM DOCTOR WHERE Department = "surgery"; (vi SELECT Doc_Name FROM DOCTOR WHERE Doc_Name UKE "J%6"; @ scanned with OKEN ScannerPrice 125 2 ——Wwindows Guide END = Networking Gui § __Oracle basics — {i)Tolist the Book Title of FND type, {i) To display @ report listing Book Title, Type and Price in descending order of price. (il) To count the number of Book Title, who have FND type. (iv) To insert a new row in the table LIBRARY, 7. "Windows 8 Basics", "FND", "McGraw", 7,150, (v) SELECT AVG(Price) FROM LIBRARY WHERE Type = "FND"; (vi) SELECT COUNT(DISTINCT Type) FROM LIBRARY: @ scanned with OKEN Scanner? consider the table STUDENT gj, mySQL for (i) to (iv) and out; Class Str ——— 0 Anubhav Gupta xi om Avgmarks Grade Prateek Jaiswal xi mene ee 3 itAgarwal yy meee 92 XI Com B —s—______“'__ Commerce "Mayank Agarwal Xil Humanities es Shailabh Khandelwal X{ Commerce 89 A2 “6._Junius Pereira Xi Science 4 a2 (1) Tolist the StudentName of Science Stream. (i) To display a report listing StudentName, Class and Stream in ascending order of StudentName, {iii) To count the number of students to Science Stream. {iv) To insert a new row in the table STUDENT. 7."Simran Kaur", "X11", "Commerce", 86, "A2" (v) SELECT AVG(AggMarks) FROM STUDENT WHERE Stream="Science"; (vi) SELECT COUNT (DISTINCT Stream) FROM STUDENT; (vil) SELECT MIN(AggMarks) FROM STUDENT: @ scanned with OKEN Scannerider the table PRODUCE oy, 0.00 See CT given bye yt oF (£04) an output for yy ots Welle commandsin © (vil). TABLE PRODUCT productName _ BrandWame 1, _MixerGrinder Maharaja Quantity Price + Washing Machine ——— 51250 3 Microwave Oven __ 8500 vacuum Cleaner 8700 (1) Tolist the ProductName where BrandName is Maharaja. {a)Todisplay @ report ProductName and g; nen randName and Pri order of price. ice in descending {i)70 count the number of ProductName, {wv} To insert a new row in the table PRODUCT, 6,"Gas Stove", "Maharaja", 7, 6150 (WSELECT AVG (Price) FROM PRODUCT WHERE BrandName = "Maharaja"; (wi) SELECT COUNT (DISTINCT BrandName) FROM PRODUCT; (vil) SELECT MAX(Price) FROM PRODUCT; @ scanned with OKEN ScannerwD consider the table SPORTS Biven below, ‘the ta Writ i jaya Fr (120 (iv) and output for ) to (vii, Hele pone: : . S je are cs Game Grad Ne ahil 9 a : Cricket, Anubhav 10 Football ii 5 gee Part ; ae 8 Skating Sm Prateek 11 cricket x Shailabh 8 Tenni -—SaeTAmit <0 Ty 3 ~~ Ctleket 3 Amit 10 Cricket, B 3 ie {a} Tolist the Name of the students whose Game is Cricket, {i} To display a report listing Name, Game and Grade in ascending order of name. {ii) To count the number of students. (iv) To insert a new row in the table SPORTS, 7.'Rishabh", 9, "Cricket", "A" {v) SELECT Name, Game FROM SPORTS WHERE Name LIKE "k%" (vi) SELECT COUNT (DISTINCT Game) FROM SPORTS; (vii) SELECT Name, Game, Grade FROM SPORTS WHERE Grade = *B", @ scanned with OKEN Scanneronsider the table INTERIORS given b ‘sot for (i) to (iv) and output for (vy a — ItemName no. Type Red Rose Double Bed a Soft Touch Baby Cot 3,__tion King Office Table 4 __RoyalTiger___Office Table = ParkSitting Sofa %__Dine Paradi e Dining table low. Write com: ; mands in to (Vii) TABLE INTERIORS DateotStock Price 23/02/2009 16000 25/09/2010 11000 30/03/2010 5000 15/09/2010 7000 15/10/2011 8000 19/03/2011 9000 __ {y list the ItemName whose Type is Office Table, price. {ii) To count the number of ItemName jy) To insert a new row in the table INTERIORS. (iT display a report listing ItemName, Type and Price in descending order of ( 7."Comfort Zone", "Double Bed", "23/03/2011", 15000 {v) SELECT ItemName, Type FROM INTERIORS WHERE ItemName LIKE "R%6"; (vi) SELECT COUNT (DISTINCT Type) FROM INTERIORS: (vii) SELECT ItemName, Type FROM INTERIORS WHERE DateofStock>"15/11/2010"; @ scanned with OKEN Scanner13. aa table STUDENTS given below. Write commands in MySQL. for (i) to (iv) and output for (v) to (vii). TABLE STUDENTS “No. StudentName Class jo. _StudentName Class Stream Score. Grade Divakar ss _ Stream Score Grade Bitar —_ IX Science 398 A i or x Commerce 397 A Dinkar IX__Humanities 376 B Dinesh x Science 350 c GS Dinanath IX Science 376 B 6. Dinmani x Commerce _ 370 B (i) To list the Student Name whose Class is IX. (ii) To display a report listing StudentName, Class and Score in descending order of Score. (iii) To count the number of Students. (iv) To insert a new row in the table STUDENTS. 7. "Divya Prakash", "Science", 355, "C"; (v) SELECT StudentName, Stream FROM STUDENTS WHERE Score >380; (vi) SELECT COUNT (DISTINCT Stream) FROM STUDENTS: (vii) SELECT StudentName, Class FROM STUDENTS WHERE Grade = @ scanned with OKEN Scanneryb dt prot end iger the following table FITNESS with details about fitness eing sold in the store. Write command of SQL for (i) to (iv) = ts D put for WM TABLE FITNESS PRICE MANUFA FACTURI ; ANUF Freadmill 21000 Coscore (i Bie___20000Aene “age P6 (Te displa’ {a} To ais (ii) To chan reduction. (wv) 78000, "Ao! (y) SELECT FROM Jay the names of all products by t Jo adda new row for pr Cross Trainer 14000 Reliable Coscore Multi Gym 34000 ‘Massage Chair 5500 Regrosene Belly Vibrator belt 6500 embawe embawe __- y the names of all the products with price more than 20000. he manufacturer "Aone". ge the price data of al the products by applying 25% discount oduct with the details: "P7". “Vibro Exerciser", ne". FITNESS WHERE MANUFACTURER LIKE "He": @ scanned with OKEN Scanner15. (i) Create a student table with th @ student ID, attributes where the student ID is th, Mendieta e primary key. (ii) Insert the details of a new student in the above table. (iii) Delete the details of a particular student in the above table. (iv) Lee select command to get the details of the students with marks more than 80. (v) Find the min, max, sum, and average of the marks in a student marks table. (vi) Write a SQL query to order the (student ID, marks) table in descending order of the marks, @ scanned with OKEN Scanner16, () Greate a new table (order 1D, customer Name, and order Date) by joining two tables (order 1D, customer 1D, and order Date) and (customer Ios Name, contact Name, country), ), customer (i) Create a foreign key in one of the two tables mentioned above {iii) Find the total number of customers from each country in the table (customer ID, customer Name, country) using group by, @ scanned with OKEN Scanner17. Given the following table TEACHER: Write SQL commands for statement (i) to (iv) and get Outputs for SQI. queries (v) to (vii). ‘No. Name ____Department Dateofloining Salary Sex 1 Raja_ Computer 21/5/1998 8000 M 2. Sangita History 21/5/1997 9000 F 3.__Ritu____ Sociology 28/8/1998 30 7 a Kumar Linguistics 13/6/1996 5.____ Venkatraman History 31/1/1999 8000 M_ 6. Sidhu Computer 21/5/1986 14000 M 7. Aishwarya Sociology 11/1/1988 12000__F __ (i) To display the name of female teachers in History department. (ii) To display names of all teachers with date of admission in ascending (iii) To display Teachers’ name, Department, and Salary of male teachers. {iv) To count the number of teachers whose salary is less than 10,000. (v) SELECT MIN(Salary) FROM TEACHER WHERE Sex = 'M'; (vi) SELECT SUM(Salary) FROM TEACHER WHERE Department = "Computer"; (vii) SELECT DISTINCT Department FROM TEACHER; @ scanned with OKEN Scannerae a 49, (a]What isthe purpose of DROP TABLE i ieaiferent from DELETE command? semmand la SOL? Hew ts (b) Consider the following table "PROJECTS", Wi sal. (i) to (iv) and output for (v) and (vi) ite corgmanus fo ProjName_ ~ 10 i e indDate ~Payrol-MMS Medium 2006-03-17 2006-09-16 60000 _ ~_IDMgmt-LTL, “RecruitiTk Me ~—1DMigme-Te smal C Medium (i) To display all Information about projects of Medium Projsize. (i) To list 1D, ProjName, Projsize and Cost of al the projects in descending order of StartDate. (ii) To display Pro]Name and Cost of projects that have cost In the range of 20000 to 50000 (both 20000 and 50000 included) (iv) Increase the cost of all projects in the given table by 10%. (v) SELECT COUNT(DISTINCT Cost) FROM PROJECTS; (vi) SELECT MAX(Cost) FROM PROJECTS; (vii) SELECT ProjName FROM PROJECTS WHERE ProjName Like "C%"; @ scanned with OKEN Scanner19 (a) write MySQL. command to create the table "CUSTOMER" with given constraints a seal PE __ CONSTRAINT ———_Int(a0)_ Primary key : ; \Varchar(20) Not Nolen 7 Unique _ = Cust Phone ____Narcantt0) (b) Ina database OFFICE, there are two tables given below: Table: GARMENT 2 Eee Price Feode ReadyDate oo Penctsit SSS _—« 20D “yoo0r Formalshie OT —qeen2010 10024 Baby Top. = FO3 07-Apr-2009, 10090 Tullp Skirt Fo2 31-Mar-2009 10019 Evening Grown 0306-2020 10009. Informal Pant FO2 20-Oct-2010 10017 Formal pant _ FOL (09-Mar-2009 30020” Frock FoR Sep 2009 “0089 Slacks 03 5-08 2010 ible: FABRIC Feode — fo Polyester r02 Cotton FO3 silk fo Teva Write SQL. queries for the following: (1) To display Geode, Description, Fcode with corresponding Type (ii) To display Description, Price and corresponding Type who have achieved Price more than 1200 (It) To display Description of GARMENT which are formal {iv) Identify the Primary key in the table GARMENT. Give reason for your choice. (v) Write SOL. command to change the Fode to FOS of the GARMENT with Geode as 10090 in the table "GARMENT" @ scanned with OKEN Scanner