Page 1 :
MySQL Revision Tour -, Lab Exercise (Solutions), Consider a database LOANS with the following table:, Write SQL commands for the tasks 1 to 35 and write the output for the SQL commands 36 to 40:, SYNTAX –, SELECT [*],[COLUMN NAME1, COLUMN NAME 2… COLUMN NAME n], FROM < TABLE / RELATION NAME>, [WHERE <LOGICAL TEST EXPRESION> ], [ORDER BY < COLUMN NAME> [ASC / DESC]], [GROUP BY <COLUMN NAME> [ HAVING < LOGICAL TEST EXPRESSION >]], Create Database and use it, 1.1 Create the database LOANS., Ans: create database LOANS;, Output –, 1.2 Display list of Databases –, Ans: SHOW DATABSES;, 2. Use the database LOANS i.e. make it current or Active Database -, Ans: use LOANS;, Output:-, Create Table / Insert Into, 3.1 Display list of Tables or Database objects –, SHOW FULL TABLES;, Output: - Empty Sets, 3.2 Create the table Loan_Accounts and insert tuples in it., Create table Loan_Accounts (AccNo int, Cust_Name Varchar(30), Loan_Amount int, Instalments int, Int_Rate float, Start_Date Date, Interest int);, 3.3 Check / Display whether table is created or not –, Ans – SHOW FULL TABLES;, Output:-, 3.4 Display of structure of Loan_accounts table –, Ans – DESCRIBE LOAN_ACCOUNTS;, Output –, 3.3 Insert the above records in the Loan_Accounts -, INSERT INTO LOAN_ACCOUNTS (ACCNO, CUST_NAME, LOAN_AMOUNT, INSTALMENTS, INT_RATE, START_DATE, INTEREST) VALUES(1, 'R.K. GUPTA', 300000, 36,12.00,'2009-07-19', NULL);, INSERT INTO LOAN_ACCOUNTS VALUES(2, 'S.P. SHARMA', 500000, 48,12.00,'2008-03-22', NULL);, INSERT INTO LOAN_ACCOUNTS VALUES(3, 'K.P. JAIN', 300000, 36,NULL,'2007-03-08', NULL);, INSERT INTO LOAN_ACCOUNTS VALUES(4, 'M.P. YADAV', 800000, 60, 10.00,'2008-12-06', NULL);, INSERT INTO LOAN_ACCOUNTS VALUES(5, 'S.P. SINHA', 200000, 36, 12.50,'2010-01-03', NULL);, INSERT INTO LOAN_ACCOUNTS VALUES(6, 'P. SHARMA', 700000, 60, 12.50,'2008-06-05', NULL);, INSERT INTO LOAN_ACCOUNTS VALUES(7, 'K. S. DHALL', 800000, 48, NULL,'2008-03-05', NULL);, Simple Select, 4. Display the details of all the loans., Ans: select * from Loan_Accounts;, mysql> SELECT * FROM LOAN_ACCOUNTS;, +-------+-------------+-------------+-------------+----------+------------+----------+, | AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |, +-------+-------------+-------------+-------------+----------+------------+----------+, | 1 | R.K. GUPTA | 300000 | 36 | 12 | 2009-07-19 | NULL |, | 2 | S.P. SHARMA | 500000 | 48 | 12 | 2008-03-22 | NULL |, | 3 | K.P. JAIN | 300000 | 36 | NULL | 2007-03-08 | NULL |, | 4 | M.P. YADAV | 800000 | 60 | 10 | 2008-12-06 | NULL |, | 5 | S.P. SINHA | 200000 | 36 | 12.5 | 2010-01-03 | NULL |, | 6 | P. SHARMA | 700000 | 60 | 12.5 | 2008-06-05 | NULL |, | 7 | K. S. DHALL | 800000 | 48 | NULL | 2008-03-05 | NULL |, +-------+-------------+-------------+-------------+----------+------------+----------+, 7 rows in set (0.00 sec), 5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans., Ans: select AccNo, Cust_Name, Loan_Amount from Loan_Accounts;, Output –, Conditional Select using Where Clause -, 6. Display the details of all the loans with less than 40 instalments., Ans: select * from Loan_Accounts where Instalments < 40;, Output –, 7. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009., Ans: select AccNo, Loan_Amount from Loan_Accounts where Start_Date< ‘2009-04-01’;, (MySQL - date format as YYYY-MM-DD)Output –, 8. Display the Int_Rate of all the loans started after 01-04-2009., Ans: select Int_Rate from Loan_Accounts where Start_Date> ‘2009-04-01’;, Using NULL in MySQL Queries -, 9. Display the details of all the loans whose rate of interest is NULL., Incorrect Ans: select * from Loan_Accounts where Int_Rate = NULL;, Output – Empty Set due to = to operator WHICH IS WRONG USE WITH NULL, Correct Ans: select * from Loan_Accounts where Int_Rate is NULL;, 10. Display the details of all the loans whose rate of interest is not NULL., Incorrect Ans :- Select * from Loan_Accounts where Int_Rate <> NULL;, (Since IS NOT is correct operator used with NULL INSPITE OF <> OR !=), Ans: Select * from Loan_Accounts where Int_Rate IS NOT NULL;, Using DISTINCT Clause – It removes duplicate Values…, 11. Display the amounts of various loans from the table Loan_Accounts. A loan amount should appear only once., Ans: Select Distinct Loan_Amount from Loan_Accounts;, Output:-, 12. Display the total number of instalments of various loans from the table Loan_Accounts. An instalment should appear only once., Ans: Select COUNT( Distinct Instalments) from Loan_Accounts;, Using Logical Operators (NOT, AND, OR), 13. Display the details of all the loans started after 31-12-2008 for which the number of instalments are more than 30., Ans: Select * from Loan_Accounts where Start_Date > ‘2008-12-31’ and Instalments > 30;, Output –, 14. Display the Cust_Name and Loan_Amount for all the loans which do not have number of instalments 36., Ans: Select Cust_Name, Loan_Amount from Loan_Accounts where Instalments <> 36;, OR, Ans Select Cust_Name, Loan_Amount from Loan_Accounts where Instalments != 36;, Output:-, 15. Display the Cust_Name and Loan_Amount for all the loans for which the loan amount is less than 500000 or int_rate is more than 12., Ans: Select Cust_Name, Loan_Amount from Loan_Accounts where Loan_Amount< 500000 or Int_Rate> 12;, Output:-, 16. Display the details of all the loans which started in the year 2009., Ans: Select * from Loan_Accounts where Start_Date =’2008 – 03-05’;, Output –, 17. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000., Ans: Select * from Loan_Accounts where Loan_Amount>= 400000 and Loan_Amount<= 500000;, Output :-, 18. Display the details of all the loans whose rate of interest is in the range 11% to 12%., Ans: Select * from Loan_Accounts where Int_Rate>= 11 and Int_Rate<= 12;, Output –, Using IN Operator, 19. Display the Cust_Name and Loan_Amount for all the loans for which the number of instalments are 24, 36, or 48. (Using IN operator), Ans: Select Cust_Name, Loan_Amountfrom Loan_Accounts where Instalments in (24, 36, 48);, OUTPUT:-, Using BETWEEN Operator, 20. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000. (Using BETWEEN operator), Ans: Select * from Loan_Accounts where Loan_Amount Between 400000 and 500000;, Output –, 21. Display the details of all the loans whose rate of interest is in the range 11% to 12%., (Using BETWEEN operator), Ans: Select * from Loan_Accounts where Int_Rate Between 11 and 12;, Output:-, Using LIKE Operator, 22. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends with 'Sharma'., Ans: Select AccNo, Cust_Name, Loan_Amount from Loan_Accounts where Cust_Name Like ‘%Sharma’;, Output:-, 23. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends with 'a'., Ans: Select AccNo, Cust_Name, Loan_Amount from Loan_Accounts where Cust_Name LIKE ‘%a’;, OUTPUT:, 24. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name contains 'a', Ans: Select AccNo, Cust_Name, Loan_Amount from Loan_Accounts where Cust_Name LIKE ‘%a%’;, OUTPUT:, 25. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name does not contain 'P'., Ans: Select AccNo, Cust_Name, Loan_Amount from Loan_Accounts where Cust_Name NOT LIKE ‘%P%’;, OUTPUT:, 26. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name contains 'a' as the second last character., Ans: Select AccNo, Cust_Name, Loan_Amount from Loan_Accounts where Cust_Name LIKE ‘%a_’;, OUTPUT:, (Using ORDER BY clause), 27. Display the details of all the loans in the ascending order of their Loan_Amount., Ans: Select * from Loan_Accounts order by Loan_Amount;, 28. Display the details of all the loans in the descending order of their Start_Date., Ans: Select * from Loan_Accounts order by Start_Date Desc;, 29.1 Display the details of all the loans in the descending of their Loan_Amount;, 29.2 Display the Loan_Amount in the descending order of their Start_Date from loanaccounts table and int_rate is NULL., Ans:29.1 Select * from Loan_Accounts order by Loan_Amount;, Ans:29.2 Select loan_amount from Loan_Accounts where int_rate IS NULL order by start_date desc;, Using UPDATE, DELETE, ALTER TABLE, 30. Put the interest rate 11.50% for all the loans for which interest rate is NULL., Ans: Update Loan_Accounts set Int_Rate=11.50 where Int_Rate=NULL;, 31. Increase the interest rate by 0.5% for all the loans for which the loan amount is more than 400000., Ans: Update Loan_Accounts set IntRate=Int_Rate + 0.5 where Loan_Amount> 400000;, 32. For each loan replace Interest with (Loan_Amount*Int_Rate*Instalments)/(12*100)., Ans: Update Loan_Accounts set Interest=(Loan_Amount*Int_Rate*Installments)/(12*100);, 33. Delete the records of all the loans whose start date is before 2007., Ans: Delete from Loan_Accounts where Start_Date< ‘2007-01-01’;, 34. Delete the records of all the loans of 'K.P. Jain', Ans: Delete from Loan_Accounts where Cust_Name LIKE ‘K.P.Jain’;, 35. Add another column Category of type CHAR(1) in the Loan table., Ans: Alter table Loan_Accounts add column Category Char(1);, Find the Output of the following queries, 36. SELECT cust_name, LENGTH(Cust_Name), LCASE(Cust_Name), UCASE(Cust_Name) FROM Loan_Accounts WHERE Int_Rate< 11.00;, Ans:, 37. SELECT LEFT(Cust_Name, 3), Right(Cust_Name, 3), SUBSTR(Cust_Name, 1, 3) FROM Loan_Accounts WHERE Int_Rate> 10.00;, Ans:, 38. SELECT RIGHT(Cust_Name, 3), SUBSTR(Cust_Name, 5) FROM Loan_Accounts;, Ans:, pta Gupta, rma Sharma, ain Jain, dav Yadav, nha Sinha, rma harma, all Dhall, 39. SELECT DAYNAME(Start_Date) FROM Loan_Accounts;, Ans:, Sunday, Saturday, Thursday, Saturday, Sunday, Thursday, Wednesday, 40. SELECT ROUND(Int_Rate*110/100, 2) FROM Loan_Account WHERE Int_Rate> 10;, Ans: 13.20, 13.75, 13.75, Write the output produced by the following SQL commands:, 41. SELECT POW(4,3), POW(3,4);, Ans: 64, 81, 42. SELECT ROUND(543.5694,2), ROUND(543.5694), ROUND(543.5694,-1);, Ans: 543.57, 544, 540, 43. SELECT TRUNCATE(543.5694,2), TRUNCATE(543.5694,-1);, Ans: 543.56, 540, 44. SELECT LENGTH("Prof. M. L. Sharma");, Ans: 18, 45. SELECT CONCAT("SHEIKH", " HAROON") "FULL NAME";, Ans: SHEIKH HAROON, 46. SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());, Ans: 2019, 08, 26, 47. SELECT DAYOFYEAR(CURDATE()), DAYOFMONTH(CURDATE()),DAYNAME(CURDATE());, Ans: 2019, 2, Monday, 48. SELECT LEFT("Unicode",3), RIGHT("Unicode",4);, Ans: Uni, code, 49. SELECT INSTR("UNICODE","CO"), INSTR("UNICODE","CD");, Ans: 4, 0, 50. SELECT MID("Informatics",3,4), SUBSTR("Practices",3);, Ans: form, actices