1.
Write the output of the following SQL commands:
(i) SELECT 10 + Round(10.5,0);
(ii) SELECT SUBSTR(‘KENdriya vidyaLAYA’, 4, 8);
(iii) SELECT LCASE(LEFT(‘INDIAN’, 5));
(iv) SELECT POWER(2,3) + ROUND(20.369,1);
(v) SELECT ROUND(1023.462,1);
(vi) SELECT LENGTH(‘Ramesh Sharma’);
(vii) SELECT UPPER(‘dinjan’);
(viii) SELECT CURDATE()+10;
(ix) SELECT Mod(ROUND(20.60,0),5);
(x) SELECT 1000+POW(25,3);
(xi) SELECT TRIM(‘ Kendriya Vidyalaya ‘);
(xii) SELECT POW(10,2)+MOD(100,6);
(xiii) SELECT LOWER(‘ABS Public School’);
(xiv) SELECT SUBSTR(‘Informatics’,3,5);
(xv) SELECT ROUND(10.66,1);
(xvi) SELECT DAYOFWEEK(CURDATE());
(xvii) SELECT MONTHNAME(CURDATE());
(xviii) SELECT MOD(DAY(CURDATE()),4);
(xix) SELECT LENGTH(SUBSTR(“I AM STUDENT OF KV NO. 1 BHOPAL”,8));
(xx) SELECT MONTH(CURDATE()+10);
(xxi) SELECT ROUND(543.5694,2), ROUND(543.5694), ROUND(543.5694,-1);
(xxii) SELECT INSTR("[Link]@[Link]",”@”);
(xxiii) SELECT SUBSTR("SHEIKH HAROON",4,8) as "PART OF NAME";
(xxiv) SELECT INSTR("UNICODE","CO"), INSTR("UNICODE","CD");
(xxv) SELECT MID("Informatics",3,4), SUBSTR("Practices",3);
2. Consider a table LOANS with the following table and write down SQL command from
i to xii and write output form xiii to XX.
AccNo Cust_Name Loan_Amount Installments Int_Rate Start_Date
1 R.K. Gupta 300000 36 12.76 19-07-2009
2 S.P. Sharma 500000 48 10.35 22-03-2008
3 K.P. Jain 300000 36 NULL 08-03-2007
4 M.P. Yadav 800000 60 10.42 06-12-2008
5 S.P. Sinha 200000 36 12.77 03-01-2010
6 P. Sharma 700000 60 12.46 05-06-2008
7 K.S. Dhall 500000 48 NULL 05-03-2008
(i) Display the first 4 letters from the customer name of loans with less than 40
installments.
(ii) Display the AccNo and Loan_Amount of all the loans started before year 2009.
(iii) Display the last 4 letters of customer name of loans whose rate of interest is not
NULL.
(iv) Display all the details from the table Loans whose loan start month is March.
(v) Display all the customer name in capital letter from loans whose rate of interest
is in the range of 11% to 12%.
(vi) Display the Cust_Name and Loan_Amount for all the loans for which the
customer name contain “P” on 3rd position using function.
(vii) Display the name and int. rate for all the loans with 1-digit decimal fraction of
int. rate.
(viii) Display the cust. Name in lower case from loan for those loans whose start in 7th
month.
(ix) Display the AccNo, Cust_Name in lower case, and Loan_Amount for all the loans.
(x) Display the details of all the loans those started in “January”.
(xi) Display all the installments of with mod of 5.
(xii) Display Cust. name from loan whose name contain “S” on 1st position or 3rd
Position using function.
(xiii) SELECT RIGHT(Cust_Name, 3), SUBSTR(Cust_Name, 5) FROM Loans;
(xiv) SELECT ROUND(Int_Rate*100, 10) FROM Loans WHERE Int_Rate > 10.50;
(xv) SELECT DAY(Start_Date) FROM Loans;
(xvi) SELECT MOD(installments,3) from Loans where int_rate is null;
(xvii) SELECT LENGTH(cust_name) from Loans where cust_name like “__P%”;
(xviii) SELECT Cust_Name, Month(Start_Date) from Loans where installments > 50 ;
(xix) SELECT INSTR(CUST_NAME,"S") from Loans where Loan_Amount > 500000;
(xx) SELECT Cust_Name, Loan_Amount from Loans where Month(Start_Date)>5;