MYSQL SINGLE TABLE BASED QUERIES

Consider a database LOANS with the following table: Table: LOANS AccNo Cust_Name Loan_Amount Instalments Int_Rate Start_Date Interest 1 R.K.Gupta 300000 36 12.00 19-07-2009 1200 2 S.P.Sharma 500000 48 10.00 22-03-2008 1800 3 K.P.Jain 300000 36 NULL 08-03-2007 1600 4 M.P.Yadav 800000 60 10.00 06-12-2008 2250 5 S.P.Sinha 200000 36 12.50 03-01-2010 4500 6 P.Sharma 700000 60 12.50 05-06-2008 3500 7 K.S.Dhall 500000 48 NULL 05-03-2008 3800 Answer the following questions. Create Database anduse it- 1. Create the database LOANS. Mysql> Create Database LOANS; 2. Use the database LOANS. Mysql> Use LOANS; Create Table and Insert a record- 3. Create the table Loans and insert tuples in it. Mysql>Create Table Loans (AccNo int primary key, Cust_Name varchar(30), Loan_Amount int, Installment int, Int_Rate number(5,3), Start_Date date, Interest number(7,2)); Mysql> Insert into Loans values (1,'R.K. GUPTA',300000,36,12.0.'2009-07-19'); Simple Select 4. Display the details of all the loans. Mysql> Select * from Loans; 5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans. Mysql> Select AccNo,Cust_Name,Loan_Amount from Loan_Acc; Conditional Select using Where Clause 6. Display the details of all the loans with less than 40 instalments. Mysql> Select * from Loans where Instalment <40; 7. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009. Mysql> Select AccNo, Loan_Amount from Loans where Start_Date <'2009-04-01'; 8. Display the Int_Rate of all the loans started after 01-04-2009. Mysql> Select Int_Rate from Loans where Start_date>'2009-04-01'; Using NULL 9. Display the details of all the loans whose rate of interest is NULL. Mysql> Select * from Loans where Int_rate is NULL; 10. Display the details of all the loans whose rate of interest is not NULL. Mysql> Select * from LOANS where Int_rate is not NULL; Using DISTINCT Clause 11. Display the amounts of various loans from the table LOANS. A loan amount should appear only once. Mysql> Select DISTINCT Loan_Amount from LOANS; 12. Display the number of installments of various loans from the table LOANS. An instalment :: 87 :: should appear only once.. Mysql> Select DISTINCT Instalment from LOANS; 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. Mysql> Select * from LOANS where Start_Date>'2008-12-31' and Instalment>36; 14. Display the Cust_Name and Loan_Amount for all the loans which do not have number of instalments 36. Mysql> Select Cust_Name, Loan_Amount from LOANS where Instalment <>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. Mysql> Select Cust_Name, Loan_Amount from LOANS where Loan_Amount <500000 or Int_rate>12; 16. Display the details of all the loans which started in the year 2009. Mysql> Select * from LOANS where Year(Start_Date)=2009; 17. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000. Mysql> Select * from LOANS where Loan_Amount between 400000 and 50000; 18. Display the details of all the loans whose rate of interest is in the range 11% to 12%. Mysql> Select * from LOANS where Int_Rate between 11 and 12; Using IN Operator 19. Display the Cust_Name and Loan_Amount for all the loans for which the number of installments are 24, 36, or 48.(Using IN operator) Mysql> Select Cust_Name, Loan_Amount from LOANS where Instalment IN(24,36,48); Using LIKE Operator 20. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends with 'Sharma'. Mysql> Select AccNo, Cust_name from LOANS where Cust_Name like '%Sharma'; 21. Display the AccNo, Cust_Name, and Loan_Amount for allthe loans for which the Cust_Name ends with 'a'. Mysql> Select AccNo, Cust_name,Loan_Amount from LOANS where Cust_Name like '%a'; 22. Display the AccNo, Cust_Name, and Loan_Amount for allthe loans for which the Cust_Namecontains 'a'. Mysql> Select AccNo, Cust_name,Loan_Amount from LOANS where Cust_Name like '%a%'; 23. Display the AccNo, Cust_Name, and Loan_Amount for allthe loans for which the Cust_Name does not contain 'P'. Mysql> Select AccNo, Cust_name,Loan_Amount from LOANS where NOT (Cust_Name like '%P%'); 24. Display the AccNo, Cust_Name, and Loan_Amount for allthe loans for which the Cust_Name contains 'a' as the second last character. Mysql> Select AccNo, Cust_name,Loan_Amount from LOANS where Cust_Name like '%a_'; :: 88 :: Using ORDER BY clause 25. Display the details of all the loans in the ascending order of their Loan_Amount. Mysql> Select * from LOANS ORDER BY Loan_Amount; 26. Display the details of all the loans in the descending order of their Start_Date. Mysql> Select * from LOANS ORDER BY Start_date DESC; 27. 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. Mysql> Select * from LOANS ORDER BY Loan_Amount, Start_Date DESC; Using UPDATE, DELETE, ALTER TABLE 28. Put the interest rate 11.50% for all the loans for which interest rate is NULL. Mysql> Update LOANS SET Int_Rate =11.50 Where Int_Rate IS NULL: 29. Increase the interest rate by 0.5% for all the loans for which the loan amount is more than 400000. Mysql> Update LOANS SET Int_Rate= Int_Rate+0.5 Where Loan_Amount >400000; 30. For each loan replace Interest with (Loan_Amount*Int_Rate*Instalments) 12*100. Mysql> Update LOANS SET Interest=(Loan_Amount*Int_Rate*Instalments) /12*100; 31. Delete the records of all the loans whose start date is before 2007. Mysql> Delete From LOANS Where Year(Start_Date)<2007; 32. Delete the records of all the loans of 'K.P. Jain' Mysql> Delete From LOANS Where Cust_Name='K.P.Jain'; 33. Add another column Category of type CHAR(1) in the Loan table. Mysql> Alter Table LOANS ADD (Category CHAR(1)); Using Aggregate Functions 34. Display the sum of all Loan Amount for whose Interest rate is greater than 10. Mysql> Select sum(Loan_Amount) from LOANS Where Interest >10; 35. Display the Maximum Interest from Loans table. Mysql> Select Max(Interest) from LOANS; 36. Display the count of all loan holders whose name is ending with ‘Sharma’. Mysql> Select Count(*) from LOANS Where Cust_Name Like ‘%Sharma’; 37. Display the count of all loan holders whose Interest is Null. Mysql> Select Count(*) from LOANS Where Interest Is NULL; Using Group By Clause 38. Display the Interest wise details of Loan Account Holders. Mysql> Select * from LOANS Group By Interest; 39. Display the Interest wise details of Loan Account Holders with at least 10 installments remaining. Mysql> Select * from LOANS Group By Interest Having Instalment>=10; 40. Display the Interest wise count of all loan holders whose Installment due is more than 5 in each group. Mysql> Select Count(*) from LOANS Group By Interest Having Instalment>5; Q23. Consider the following tables Item and Customer. Write SQL commands for the statement (1) to (4) and give outputs for SQL queries (5) to (6) :: 89 :: Table: ITEM ItemCode ItemName Price PC01 PC HP 35000 LC01 Laptop Sony 55000 PC02 PC ASUS 32000 PC03 PC HCL 37000 LC02 Laptop Toshiba 57000 Table: CUSTOMER CustCode CustName City ItemCode C01 N.Roy Delhi LC02 C06 H.Singh Mumbai PC03 C12 R.Pandey Delhi PC02 C15 C.Sharma Delhi LC01 C16 K.Agrawal Bangalore PC01 1. To display the details of those Customers whose city is Delhi and purchased ‘PC HP’ . 2. To display the details of Item which is purchased by custCode C15. 3. To display the Customer Code Customer Name, City and Item Name and Price for all sales. 4. To display the details of customer who purchased PC of any company. 5. SELECT CustName, City from Customer, Item Where Customer.ItemCode= Item.ItemCode And ItemName=”PC HP”; 6. SELECT CustName, City FROM Customer, Item Where Item.ItemCode= Customer.ItemCode And City=”Delhi”; Ans- 1. SELECT CustCode, CustName, City FROM Customer, Item Where Customer.ItemCode= Item.ItemCode And City=”Delhi” And ItemName=”PC HP”; 2. SELECT ItemCode, ItemName, Price FROM Customer, Item Where Customer.ItemCode= Item.ItemCode And CustCode=’C15’; 3. SELECT CustCode, CustName, City, ItemName, Price FROM Customer, Item Where Customer.ItemCode= Item.ItemCode ; 4. SELECT CustCode, CustName, City FROM Customer, Item Where Customer.ItemCode= Item.ItemCode And And ItemName LIKE ”PC%”; 5. CustName City K.Agrawal Bangalore 6. CustName City N.Roy Delhi R.Pandey Delhi C.Sharma Delhi

Create table “EMP” as per the following specification:

COLUMN NAME	DATATYPE	SIZE	CONSTRAINTS
Emp_No	Integer	4	Primary Key
Emp_Name	Varchar2	20	Not Null
Job	Varchar2	10
Hiredate	Date	dd-mm-yyyy
Salary	Float	10,2	Salary>10000
Commission	Integer	4
DeptNo	Integer	2	10,20 or 30

Job Types are:

·         President

·         Manager

·         Clerk

·         Salesman

Commission given only to ‘salesman’.

QUESTIONS

1)      Create the above table including its constraints.

2)      Insert 10 records into it.

3)      Display the name , job, salary of all employee having salary between 15000 and 25000

4)      Display the employee name , department and total salary calculated as (Salary +comm.) for all employees.

5)      Display the content of the table in the descending order of salary and ascending order of name.

6)      Display Emp_no , Emp_Name, annual salary under the heading “ANNUAL SALARY” where salary is calculated as (salary + commissim*12)

7)      Display the maximum salary from the table department wise.

8)      Display the name and job of all the employees whose name starts with ‘A’ and ends with ‘A’.

9)      Display the name i.e. Emp_Name, job and hiredate of the employee having job as manager, salesman or clerk.

10)   Count the no. of employee having salary greater than 15000.

11)   Create view DEPT20 with Emp_No, Emp_Name and salary of employee for department no. 20.

12)   To display the information of all the employees who were hired during year 1995.

13)   Show the average salary for all the departments with more than three people for a job.

14)   Write a SQL command to add the column named as remark with datatype variable length string (Varchar(20)).

15)   Increase the salary of all employees by 20% who were hired before 1^st Jan 1995 and belongs to job “ANALYST”.