Sql imp Queries

 ●Que1 :-Create following table :-

1.create employee table. 

Eg. Create table employee(Eno integer primary key, Ename varchar(10), job   

    Varchar(10), Mgr_no integer, Jdate  date, Sal integer, comm integer, Deptno Integer,foreign key(deptno) references dept(deptno));

2. Create Dept table.

Eg. Create table dept(deptno integer primary key, dname varchar(10), Location Varchar(10));                                                                  

3. Insert record in employee table.

Eg. Insert into employee values(7369,Smith,Clerk,7902,1980-12-17,800,--,20);

4. Insert record in dept table.

Eg. Insert into dept values(10,P.A,Bangalore);

5. Find the name of all employee having L second letter in their name.

Eg. Select name from employee where ename like _l%;

6. Find the list of employee who stay in Bombay & Delhi.

Eg. Select * from employee,dept where location in (Bombay,Delhi); 

7. List the name and city of employee who are not in surat.

Eg. Selece ename,location from employee,dept 

    where location not in (Surat); 

8. Display the salary for employee whose salary 10001 & 10002.

Eg. Select sal from employee where sal between 10001 and 10002; 

9. Count the total no of employee.

Eg. Slect count(ename) of employee;

10.Fild the employee whose salary is > 5000.

Eg. Select * from employee where sal>5000;

11.Calculate the average salary of all employees.

Eg. Select avg(sal) from employee;

 

12.Count the no of  employee having salary > 5000.

Eg. Select count(ename) from employee where sal>5000;

13.Calculate the salary 0.30 for those employee whose salary is >= 5000.

Eg. Select (sal*0.30) from employee where sal>5000;

14.Write a query that counts the no of employee whose second character 

   names are r & h.   

Eg. Select count(ename) from employee where ename like _r% and 

    Ename like _h%; 

15.Find the employee name have join date 23-jan-82

Eg.select ename from employee where jdate=1982-01-23’; 

16.Find the employee name working in department other than 20.

Eg. Select ename from employee where deptno not in 20;

17.Find the employee name who do not receive commission.

Eg. select ename from employee where comm=--;

18.Find the employee name whose name start with j and ends with s.

Eg. Select emane from employee where ename like j% and ename like %s;

19.Find the employee name who have join date 1982.

Eg.select ENAME from EMPLOYEE where TO_CHAR(JDATE,YYYY)=’1982;

20.Find the total no of clerk.

Eg.select count(ename) from employee where ename=clerk;

21.Find the maximum, minimum salary of various category of employee.

Eg.select max(sal),min(sal) from employee;

22.List all the  clerks and managers from department no 20. 

Eg.select ename from employee where (ename=clerks or ename=manager)

   And deptno=20;

Que 2: ( Create the following tables, which have following structure & insert minimum 5 records:

Item_mast (ino, iname,qty,purch_rate)

Bill_mast (ino,billno,qty_sold,sale_price,sale_date)

Q-1 Display all the item no, item name, quantity, selling price.

Q-2 Display all items that have highest selling price.

Q-3 Display all item whose quantity sold is less than 25.

Q-4 Change the purchase rate of items which name start from Ab and quantity 

greater than 20.

Q-5 Display the name of items which was sold on 11/11/2006.

Q-6 Display the item name whose qty sold is greater then 20.

Q-7 Display the name of item has an 'a' and also 'b' somewhere in their name.

Q-8 Display the average quantity of item.

Q-9 Display total sum of sell price whose quantity is greater than 25.

Q-10 List all item information name wise in ascending order.

    Table :-1 Item master 

    Table :— 2 Bill master 

Q-1  Display all the item no,item name ,quantity,selling price.

SELECT a.ino, iname, qty, sale_price FROM item_mast AS a, bill_mast AS b

WHERE a.ino=b.ino;

Q-2 Display all items that have higest selling price.

SELECT iname FROM item_mast WHERE ino in (SELECT ino FROM bill_mast

WHERE sale_price in(SELECT max(sale_price) FROM bill_mast));

Q-3 Display all the item whose quantity sold is less than 25.

SELECT item_mast.ino, iname AS item_name FROM item_mast, bill_mast WHERE qty_sold <25 and  item_mast.ino=bill_mast.ino;

Q-4 Change the purachase rate of item s which name  start from Ab and quantity gratyer than 20.

UPDATE item_mast  SET purch_rate = purch_rate+25 WHERE iname like 'ab*' and qty>20;

Q-5 Display the name of which was sold on 11/11/2006.

SELECT a.ino, inameoo FROM item_mast AS a, bill_mast AS b WHERE a.ino=b.ino and sale_date=2011-11-06;

Q-6 Display the item name whose qty sold is greater then 20.

SELECT item_mast.ino, iname AS item_name FROM item_mast, bill_mast

WHERE qty_sold > 20 and  item_mast.ino=bill_mast.ino;

Q-7 Display the name of  item have an 'a' and also 'b' somewhere in their name. 

SELECT iname FROM item_mast WHERE iname like "*a*" and  iname like  "*b*";

Q-8 Display  the average quantity of item.

SELECT avg(qty) FROM item_mast;

Q-9 Display total sum of sell price whose quantity is greater than 25.

SELECT sum(sale_price) FROM item_mast AS a, bill_mast AS b WHERE qty>25 and a.ino=b.ino;

Q-10 List all item information name wise in ascending order.

SELECT * FROM item_mast ORDER BY iname;