ASSISI CON V NT SCHOOL oda, Ullar Pragesn- 2021-22 PRACTICAL FILE AS PER CBSE GUIDLINES s.EKTA SAHNI4. 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 Rachnid) 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 UnitPrice20000; 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 nullOUTPUT: 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 ertTablesRECIPIENT 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; TablesSSuEDa} 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 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: |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;