MySQL Revision CBSE Questions

Class XI C (Informatics Practices)

MySql Revision

Consider a database LOANS with the following table:

Table: Loan_Accounts

AccNo	Cust_Name	Loan_Amount	Instalments	Int_Rate	Start_Date	Interest
1	R.K. Gupta	300000	36	12.00	19-07-2009
2	S.P. Sharma	500000	48	10.00	22-03-2008
3	K.P. Jain	300000	36	NULL	08-03-2007
4	M.P. Yadav	800000	60	10.00	06-12-2008
5	S.P. Sinha	200000	36	12.50	03-01-2010
6	P. Sharma	700000	60	12.50	05-06-2008
7	K.S. Dhall	500000	48	NULL	05-03-2008

Write SQL commands for the tasks 1 to 35 and write the output for the SQL commands 36

to 40:

Create Database and use it

1. Create the database LOANS.

2. Use the database LOANS.

Create Table / Insert Into

3. Create the table Loan_Accounts and insert tuples in it.

Simple Select

4. Display the details of all the loans.

5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans.

Conditional Select using Where Clause

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.

MySQL - REVISION TOUR

Using NULL

9. Display the details of all the loans whose rate of interest is NULL.

10. Display the details of all the loans whose rate of interest is not NULL.

Using DISTINCT Clause

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..

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 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%.

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)

Using BETWEEN Operator

20. Display the details of all the loans whose Loan_Amount is in the 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)

268

MySQL - REVISION TOUR

Using LIKE 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 contains 'a' as the second last character.

Using ORDER BY clause

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.

Using UPDATE, DELETE, ALTER TABLE

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 records of all the loans of 'K.P. Jain'

35. Add another column Category of type CHAR(1) in the Loan table.

269

MySQL - REVISION TOUR

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;

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) FROM Loan_Accounts;

39. SELECT DAYNAME(Start_Date) FROM Loan_Accounts;

40. SELECT ROUND(Int_Rate*110/100, 2) FROM Loan_Account WHERE Int_Rate > 10;

Write the output produced by the following SQL commands:

41. SELECT POW(4,3), POW(3,4);

42. SELECT ROUND(543.5694,2), ROUND(543.5694), ROUND(543.5694,-1);

43. SELECT TRUNCATE(543.5694,2), TRUNCATE(543.5694,-1);

44. SELECT LENGTH("Prof. M. L. Sharma");

45. SELECT CONCAT("SHEIKH", " HAROON") "FULL NAME";

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("Practices",3);