ASSISI CON V NT SCHOOL oda, Ullar Pragesn- 2021-22 PRACTICAL FILE AS PER CBSE GUIDLINES s.EKTA SAHNI4. Consider the table STUDENT with the above data and define the first column SID as primary key , feed all the records in the table and then answer the following queries STUDENT a)To count the number of students in each section._ ANS: SELECT section, count() FROM student GROUP BY section;b) To display the names of the student who got “Excellent” remark in their project. Ans: SELECT name FROM student WHERE proj_rem="Excellent”; C) To increase the marks of the student “Raj” in CS by 5. ANS: UPDATE student SET CS=CS+5 WHERE name='Raj’ OUTPUT: d) The display the names of the students ending with “a”. Ans: SELECT name FROM student WHERE name like ‘%a’; ) To display the details of the student whose IT marks are in the range 25 to 35. ‘Ans: SELECT * FROM student WHEREIT BETWEEN 25 AND 35; f) To display the name of the student who secured maximum marks in IT. ns: SELECT! JTFROM student ORDER BY IT limit 1; Q)To insert a new row with values (105,’Shreya’,12,’D’,40,38,’Excellent’)2 INSERTINTO student VALUES(105, ‘Shreya’,12,’D’,40,38, Excellent’) (OUT PU aera 2. Consider the following table and answer the following queries. a) To retrieve all the information about the teacher with id 104. ELECT * FROM teacher WHERE teacher_id=104; b)To find the names of all the teachers earning more than 30000. Ans: SELECT f_name,!_nameFROM teacher WHERE salary>30000; OUTPUT: rn Pra) C)To display teacher_id, first name, last name and dept_no of teachers who belong to department 1. ‘Ans: SELECTteacher_id,f_name,!_name,dept_noFROMteacher WHEREdept_no=1; OUTPUT: Corea eee ee 104 1 Rachnid) To retrieve names of all teachers and the names and numbers of their respective departments. Ans: SELECT f_name, |_name,dept_no,dept_name FROM teacher; oureur: fj Prete eee e) To display department numbers corresponding to department having male teachers. Ans: SELECT dept_no FROM teacher WHERE gender ‘OUTPUT: Ans: SELECT DISTINCT dept_name FROM teacher; Ce coreeea Hindi rete 9) To list firstname, last name in alphabetical order. Ans: SELECT? name,! name FROM teacher ORDERBY f_name ASC; ourPuT: h) To retrieve name of teachers havingat least 6 characters in firstname. Ans: SELECTf_name,!_nameFROMteacherWHEREf_nameLiKE‘ __*;Consider the following table named “GYM” with details about PrCode stores the Codes of Products PrName stores names of Products UnitPrice is inRs. TABLE:GIOL a) Display the names of all the products in the store. ans: SELECT PrName FROM gym: ‘OUTPUT: ») Display the names and unit price of all the products in the store. ‘ans: SELECT PrName,UnitPrice FROM gym; OUTPUT: ©) Display the names ofall the products in the store with unit price less than Rs20000, ‘ans: SELECT PrName FROM gym WHERE UnitPrice<20000; es Bike 4) Display details ofall the products in the store with unit price in the range 20000 to 30000, ans: SELECT’ FROM gym WHERE UnitPrice BETWEEN 20000 AND 30000;‘OUTPUT: cee @) Display names of all products by the manufacturer “Fit Express”. ‘ans: SELECT PrName FROM gym WHERE Manufacturer='Fit Express"; ‘Ans: SELECT * FROM gym ORDER BY UnitPrice DESC; ‘OUTPUT, 19) Add a new row for product with the details; P106", Vibro Exerciser” 23000, manufacturer: "AvonFitness”. ns: INSERT INTO gym VALUES('P106;, "Vibro Exerciser’,23000, ‘Avon Fitness’); hh) Change the UnitPrice ofall the rows by applying a 10% discount reduction on al the produets. ns; UPDATE gym SET UnltPrice=0.9*UnitPrice; ‘OUTPUT; 1) Display details ofall products with manufacturer name sta Ans: SELECT* FROM gym WHERE Manufacturer LIKE ‘A%' 4, Considerthe following Emplovee table: Table Name: Employeehe primary key ofthis table Is Employee 1D and Manager 1D is the foreign key that references Employee 1D. |write SaL commands for thetollowing, a) Create the above table. ars: CREATE TABLE Employee(Eimployee 1D nt(4, Employee Name varchar(7),Job Tile varchar(10) Salary int(5),Bonus int(4),Age int(2),Manager_1D int(4); ‘OUTPUT: ss _ lb) insert the values as shown above. ans: | INSERT INTO Employee VALUES(1201, ‘Divya’, ‘President’,50000,null,29,null); INSERT INTO Employee VALUES(1205, Amyra'"Manager’30000,2500,26,1201); INSERT INTO Employee VALUES(1211,Rahul,/Analyst’,20000,1500,23,1208): INSERTINTO Employee VALUES(1213,'Manish‘Salesman',16000,null, 22,1208); INSERT INTO Employee VALUES(1216,’‘Megha’ Analyst’ 22000, 1300, 25,1201); INSERTINTO Employee VALUES(1217, Mohit’ ‘Salesman’, 16000,null 22,1208) ¢} Delete the employee having Employee 1D 1217. dns: DELETE FROM Employee WHERE Employee D=1217; ourPut 4) Update the salary of ‘Amyra’ to 40000.‘ansi UPDATE Employee SET Salary=40000 WHERE Employee Name=Amyra'; ouTPUT; ¢) Alter the table Employee so that NULL values are not allowed for Age column, ‘ns: ALTER TABLE Employee MODIFY Age int(2) NOT NULL; ‘OUTPUT: {| Write a query to display names and salaries of those employees whose salary is greater than 20000, ns; SELECT Employee Name,Salaty FROM Employee WHERE Salary>20000; 9) Write a query to display details of employees who are not getting any bonus. ‘ns: SELECT * FROM Employee WHERE Bonus IS null; ©) Alter the table Employee so that NULL values are not allowed for Age column. ans: ALTER TABLE Employee MODIFY Age int(2) NOT NULL; ‘OUTPUT: {) Write a query to display names and salaries of those employees whose salary is greater than 20000. Ans: SELECT Employee Name,Salary FROM Employee WHERE Salary>20000; 9) Write a query to display details of employees who are not getting any bonus. Ans: SELECT * FROM Employee WHERE Bonus IS nullOUTPUT: h)Write a query to display the names of employees whose name contains ‘a’ as the last alphabet Ans: SELECT Employee Name FROM Employee WHERE Employee Name LIKE "%a’ Ered le andjob title of those employees whose Manager_IDis 1201. Ans: SELECT Employee _Name,Job Title FROM Employee WHERE Manager D=1201; |) Writea query to display the name and Job title of those employees whose Manager is ‘Amyra’ Ant: SELECT A.Employee Name,A.Job Tile FROM Employee A,Employee B WHERE B.Employee Name='Amyra’ AND D: ons 1) Write aquary to dsplay the name andjob tie of those employees aged between 26 and 30(both inclusive) ‘ans: SELECT Employee Name, Job_Title FROM Employee WHERE Age BETWEEN 26 AND 30; roku ees rs rr rg i ertTablesRECIPIENT es 2) To display the details of those senders whose city is "New Delhi" Ans: SELECT * FROM sender WHERE SenderCity= New Delhi’; ¢) To display the sender details in ascending order of SenderNam Ans: SELECT * FROM sender ORDER BY SC; ~ baa 4) To display the names ofthe sender starting with Ans: SELECT" FROM sender WHERE SenderName LIKE ‘R% ©) To display the sendername, sender address from table sender and: table recipient with their corresponding matching senderid. Ans: SELECT SenderName,SenderAddress,RecName,RecAddress FROM sender recipient WHERE ‘sender.SenderlD=recipient SenderiD; £) To display the number of sender belonging to each city. Ans: SELECT SenderCity.count(SenderCity) FROM sender GROUP BY SenderCity; 6, Consider the following tables CABHUB and CUSTOMER. “Table:cannus‘Write Sat commands forthe statements (a) 0) 2) To display the names of al the white coloured vehicles from the cabhub table. Ans: SELECT vehiclename FROM cabhub WHERE color="White'; ourpur: ») To display name of vehicle , make and capacity of vehicles in ascending order oftheir seating capacity. Ans: SELECT vehiclename,make.capacity FROM cabhub ORDER BY capacity ASC; OUTPUT: Jans: UPDATE cabhub SET charges=charges+5; ‘ourPUT: _) To display the vehicle name,cname from the tables cabhub,custor ans: SELECT vehiclename,cname FROM cabhub, customer WHERE cabhub.vcode=customer.vcode; ‘ouTeur: Jans: SELECT color,count(*) FROM cabhub GROUP BY color; ‘OUTPUT:9} Display veode, vehiclename,make and customer name from the table customer and vehicle with their corresponding matching id of veo Ans: SELECT cabhub vcode, veiclename,make,cname FROM cobhub,customer WHERE cabhub.veode=customer vcode; ') To Display the details ofthe product whose price isin the range of $0 t0100. [Ans: ELECT * FROM product WHERE price BETWEEN 50 AND 100; «) Totncrease the price by Rs. 200 inthe tableProduct, [Ans: UPDATE produit SET pice-price+200; ‘ouTPUT: 4) To Display the productname in the ascending order inthe table Product. ‘ans: SELECT prod. name FROM product ORDERBY prod_name; ‘OUTPUT:©) To display product name, manufacturer city from the table Product and client. ‘Ans: SELECT prod_name,manufacturercity FROM productclen 4) Todisplay the number of elients belonging to each cy. a ‘a 8) To display the maximum, minimum price from the table product. Ans: SELECT max(price) min[price) FROM product ‘OUTPUT: ‘8, Consider the following tables EMPLOYEE and SALARY, Table: EMPLOYEE Leto ay )a) To display the details of those Employees whose name's “John” Ans: SELECT * FROM employee WHERE name="lohn’ ange 40000 to 70000. Ans: SELECT * FROM employee salaty WHERE salary BETWEEN 40000 AND 70000 AND employee ssrade=salary sarade; c) Toad new row with the values "2400,45000,503" In the table salary. Ans: INSERT INTO salary VALUES(2400,45000,'S2") cot «)Toinerease al the salaries byS000. [Ans: UPDATE salary SET salary=salary15000; TablesSSuEDa} Toshow the books of FIRST PUBL. Publishers written byP. Purohit. ans: SELECT * FROM books WHERE publshers="FRST PUBL.” AND author_name~'. Purohit OUTPUT: mpty set <@.@5 sec> b) Toisplay cost of all the books published byFIRST PUBL. ns: SELECT price FROM books WHERE publishers FIRST PUBL. c) Depreciate the price ofall books of EPB publishers by 5%. ans: UPDATE books SET price=price*0.95; 4) Toshow total cost of books of eachtype. ans: SELECT type.sum(price] FROM books GROUPSY type; ourpur: |<} Toshow the details ofthe costliestbook ns: SELECT * FROM books ORDER BY price DESC limit 3 TablepRoDUCT Table:Cuient Write Sat statements forthe queries (2)t0 (4) and output for (e) and (f: yased clients in Client table. ‘Del 8) Todlsplay the ClientName and City of all Mumba and De ‘ans: SELECT clientname,cty FROM client WHERE city”™Mumbat’ OR ct ourPur:Pree i re aC eed ais Past Dsus pre |<} Todlisplaythe Productivame, Manufacturer, Expiry Date ofall the products that exp ans: SELECT productname, manufacturer, expirydate FROM product WHERE expnydate 4) To display 10, Clentame, Cty ofall he cients (including the ones that have not purchaseda product) and thel correspondingProduct Name sold, ans: SELECT c id, clentname, chy, productname FROM client LEFT JOIN product ON product. id=cllent.p i ‘OUTPUT: 6) select count{ dstint Manufacturer] from product; “Table suPPUER ‘Write Sal commands for the statements (a) to (d) and give outputs for SL. queries (eo (h)2) To display details ofall the items in the Store table in ascending order of LastBuy. [Ans: SELECT * FROM store ORDER BY lastbuy ASC; @) select count{lstinctscode) from STORE; Z a #) select Rate *aty from STORE whereltemne=2004; 28) select tem. Sname from STORE, SUPPLIER P where S Scode hy) select manftastbuy) trom STORE;‘Answer the followingauestions 4) Display the sum ofall Loan Amounts whose Interest eat ‘Ans: SELECT sum|loan) FROM loans WHERE int_fate>10.00; perers Pen »b) Display the Maximum interest from Loans table. [Ans: SELECT maxfinterest) FROM loans com €) Display the count of allan holders whose name ends with ‘Sharma’ ‘Ans: SELECT count/*] FROM loans WHERE cust nameLKE "sSharma! OUTPUT: + 4) Display the count ofall oan holders whose interestis Null ‘Ans: SELECT count) FROM loans WHERE interest NULL; ouTPUT: e}Display the Interest wise detalls of Loan Account Holders. Ans: SELECT * FROM loans ORDER BV interest: +) Display the interest-wice details of Loan Account Holders with at least 10 installments remaining Ans: SELECT * FROM loans WHERE installments>=10 ORDER BY interest, OUTPUT: cra nes mT 13. Consider the followingtables: COMPANY and MODEL be:COMPANY Note: > Comp_10 isthe Primary key. > Model 10 isthe Primary key. > Comp ID isthe Foreign Key referencing Comp 10 of Company table.‘Waite SQL commands for queries (a) to (d) and output for(e)and () 42) To dleplay details of all modelsin the Mode! table in ascending order of DateofManufacture fans: SELECT * FROM model ORDER BY dateofmanufactureASC; 1b) To display detalls of those models manufacturedin 2011 and whore Costis below 2000. ‘ns: SELECT * FROM model WHERE dateofmanufa100, eJToinsert a recordin the Stock table withthe values: (5010, enelt H8', 102,500, 10, 2010-01-26") Ans: INSERT INTO stock VALUES{S010, Pencil HB, 102, 500, 10, 2010-01-26") oureur: 4) To display Dcode, Dname from Dealer table and item, UnitPrice from Stock table ofall the Dealers (including the dealer details that have not sold anyitem) de, dname, item, unitprce FROM dealer LEFT IOIN st ON dealer dcode-stack dcode;