MCQ:
1. MySQL is a RDBMS
2. USE <database name> command opens a database
3. Number of columns in a table is called Degree
4. A database contains tables
5. Number of rows in a table is called Cardinality
6. ORDER BY clause is used to sort data in Ascending
order
7. A Table can have only one Primary key
8. Wild card characters are used in Like clause
9. DDL is a part of SQL
10. Like clause is used for pattern matching
LAB EXERCISES
Consider a Database LOANS with the Following table:
Table: Loan_Accounts
Write SQL commands for the tasks 1 to 35 and write the output
for the SQL commands 36 to 40:
1. Create the database LOANS.
2. Use the Database LOANS.
3. Create the table Loan_Accounts and insert tuples in it.
4. Display the details of all the loans.
5. Display the AccNo, Cust_Name and Loan_Amount of all the
loans.
6. Display the details of all the loans with less than 40
instalments.
7. Display the AccNo and Loan_Amount of all the loans started
before 01-04-2009.
8. Display the Int_Rate of all the loans started after 01-04-2009.
9. Display the details of all the loans whose rate of interest is
NULL.
10. Display the details of all loans whose rate of interest is not
NULL.
11. Display the amounts of various loans from the table
Loan_Accounts. A loan amount should appear only once.
12. Display the number of instalments of various loans from
the table Loan_Accounts. An instalment should appear only
once.
13. Display the details of all the loans started after 31-12-2008
for which the number of instalments are more than 36.
14. Display the Cust_Name and Loan_Amount for all the loans
which do not have number of instalments 36.
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.
16. Display the details of all the loans which started in the year
2009.
17. Display the details of all the loans whose Loan_Amount is in
the range 400000 to 500000.
18. Display the details of all the loans whose rate of interest is
in the range 11% to 12%.
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)
20. Display the details of all the Loans whose Loan_Amount is
in range 400000 to 500000. (Using BETWEEN operator)
21. Display the details of all the loans whose rate of interest is
in the range 11% to 12%. (Using BETWEEN operator)
22. Display the AccNo, Cust_Name and Loan_Amount for all
the loans for which the Cust_Name ends with ‘Sharma’.
23. Display the AccNo, Cust_Name and Loan_Amount for all
the loans for which the Cust_Name ends with ‘a’.
24. Display the AccNo, Cust_Name and Loan_Amount for all
the loans for which the Cust_Name contains ‘a’.
25. Display the AccNo, Cust_Name and Loan_Amount for all
the loans for which the Cust_Name does not contain ‘P’.
26. Display the AccNo, Cust_Name and Loan_Amount for all
the loans for which the Cust_Name ends with ‘a’ as the second
last character.
27. Display the details of all the loans in the ascending order of
their Loan_Amount.
28. Display the details of all the loans in the descending order of
their Start_Date.
29. Display the details of all the loans in the ascending order of
their Loan_Amount and within Loan_Amount in the descending
order of their Start_Date.
30. Put the interest rate 11.50% for all the loans for which
interest rate is NULL.
31. Increase the interest rate by 0.5% for all the loans for which
the loan amount is more than 400000.
32. For each loan replace Interest with
(Loan_Amount*Int_Rate*Instalments)12*100.
33. Delete the records of all the loans whose start date is before
2007.
34. Delete the recorders of all the loans of ‘K.P. Jain’.
35. Add another column Category of type CHAR(1) in the Loan
table.
36. SELECT cust_name, LENGTH(Cust_Name), LCASE(Cust_Name),
UCASE(Cust_Name) FROM Loan_Accounts WHERE
Int_Rate<11.00;
37. SELECT LEFT (Cust_Name,3), Right(Cust_Name,3)
SUBSTR(Cust_Name,1,3) FROM Loan_Accounts WHERE
Int_Rate>10.00;
38. SELECT RIGHT(Cust_Name,3), SUBSTR(Cust_Name,5) FORM
Loan_Accounts;
39. SELCT DAYNAME(Start_Date) FROM Loan_Accounts;
40. SELECT ROUND(Int_Rate*110/100,2) FROM Loan_Accounts
WHERE Int_Rate>10;
41. SELECT POW(4,3), POW(3,4);
42. SELECT ROUND(543.5694,2), TRUNCATE(543.5694,-1);
43. SELECT TRUNCATE(543.5694,2), TRUNCATE(543.5694,-1);
44. SELECT LENGTH(“Prof.M.L.Sharma”);
45. SELECT CONCAT(“SHIEKH”,” HAROON”)”FULLNAME”;
46. SELECT YEAR(CURDATE()),MONTH(CURDATE()),
DAY(CURDATE());
47. SELECT DAYOFYEAR(CURDATE()), DAYOFMONTH(CURDATE()),
DAYNAME(CURDATE());
48. SELECT LEFT(“Unicode”,3),RIGHT(“Unicode”,4);
49. SELECT INSTR(“UNICODE”,”CO”),INSTR(“UNICODE”,”CD”);
50. SELECT MID(“Informatics”,3,4),SUBSTR(“Practice”,3);
