SQL Question For Pratice--III

Consider the following tables GAMES and PLAYER. Write SQL commands for the

statements Table: GAMES

GCode	GameName	Number	PrizeMoney	ScheduleDate
101	Carom Board	2	5000	23-Jan-2004
102	Badminton	2	12000	12-dec-2003
103	Table Tennis	4	8000	14-feb-2004
105	Chess	2	9000	01-jan-2004
108	Lawn Tennis	4	25000	19-mar-2004

Table: PLAYER

PCode	Name	Gcode
1	Nabi Ahmad	101
2	Ravi Sahai	108
3	Jatin	101
4	Nazneen	103

1. Display  the name , Game name, prize money  of the all players
2. To display sum of prize money for  each of number of participants
3. Show the sum of the  prize money  of all  games  with number more than 3 for a game
4. Display  all games  with their schedule date in descending  order
5. Display all players whose name start with ‘N’

INTERNATIONAL INDIAN SCHOOLDAMMAM

DEPARTMENT OF COMPUTER SCIENCE

EMP

Empno	Ename	Job	Mgr	Hiredate	Sal	Comm.	Deptno
7839	King	President		17-nov-81	5000		10
7698	Blake	Manager	7839	01-may-81	2850		30
7782	Clark	Manager	7839	09-jun-81	2450		10
7566	Jones	Manager	7839	02-apr-81	2975		20
7654	Martin	salesman	7698	28-sep-81	1250	1400	30
7499	Allen	Salesman	7698	20-feb-81	1600	300	30
7844	Turner	Salesman	7698	08-sep-81	1500	0	30
7900	James	Clerk	7698	03-dec-81	950		30
7521	Ward	Salesman	7698	22-feb-81	1250	500	30
7902	Ford	Analyst	7566	03-dec-81	3000		20
7369	Smith	Clerk	7902	17-dec-80	800		20
7788	Scott	Analyst	7566	09-dec-82	3000		20
7876	Adams	Clerk	7788	12-jan-83	1100		20
7934	Miller	Clerk	7782	23-jan-82	1300		10

2. ALTER TABLE command

i. To alter the structure of table EMP by adding a column spouses name

ii. To alter a table structure DEPT  by adding  a column  noofstaff,  phno

3. INSERT INTO command

i.  insert a new employee  with your own data

ii. insert a new employee with your own data and comm. as  NULL

iii. Insert  a record  with suitable data in the table EMP , taking system  date as the hiredate

4. DELETE command

i. delete all salesman   ii. delete all employees who has no commission

iii. delete  all clerks whose salary is more than 1000

5. UPDATE  command

1. increase  salary of all clerks by 20% of salary
2. increase   sal of  managers  by 12% of salary  whose salary is more than 2500
3. increase salary of all salesman by 10% of salary and commission by 10
4. increase  salary of analyst by 100
5. change  blake job as vice president

 SELECT, ORDER BY, GROUP BY , HAVING  command

1.   Display all managers with their salary in descending order

2.   Display deptno, job, enme in reverse order of  salary from the EMP table

3.   .Display  ename. Job ,  hiredate in descending order of hiredate

4.   Display  ename , empno, job and deptno of all clerks

5.   Display  all employees who have no manager

6.   Display  all clerks who earn  between 1000 and 2000

7.   Display  the whole table  alphabetical order of name

8.   find out  number of employees having  manager as a jon

9.   List  different jobs either  department 10 or 20

10.        Show  the average salary of all departments with more than  3 people  for a job

11.        List minimum and maximum  salary  of each job type

12.        Display all  the jobs  with  maximum salary greater than or equal to 3000

13.        Display  all employees who were hired  during 1981

14.        Display all   employees  whose names include either of substring “TH” or “LL”

15.        Give dept no for all those employees whose  name start with “s”

16.        To find  all those employees whose job does not start with “M”

17.        Display  ename joined with job with  heading “employee”, sal *12 as “ total salary from  table emp

18.        Show the structure of the table dept.

19.        Display  all employees those who don’t have  their salary  in range 1500 to 2000

20.        Display  the name  of  employee whose name contains ‘A’ as third alphabet

21.        Display the name  of  employee whose name contains ‘T’ as the last alphabet

22.        Display  the name  of  employee whose name contains ‘M’ as first alphabet and ‘L’ as the last alphabet

23.        Display  the ename  and deptno and danem for all employees using tables emp and dept.

24.        Display  the sum , average, highest , lowest salary of all employees

25.        Display  the sum , average, highest , lowest salary of all employees in job wise

26.        Display the number of employees with same job;

27.        Display  details of those employees who have four letter names

28.        List  the details of employees who earn more than commission  their salary

29.        Display ename, job, salary and  hiredate of employees whose  sal is between 1000 and 2000 order the query ascending order of hire date

30.        Display employee number, name , salary, salary increase by 15%. Label the column  as new salary

REFER EMP table  and write the output of the following  SAL commands

31. SELECT  COUNT(JOB) FROM EMP  GROUP BY JOB ;

32. SELECT   COUNT(*)FROM EMP WHERE COMM IS  NULL ;

33. SELECT AVG(SAL) FROM EMP  GROUP BY JOB  HAVING   COUNT( * )  > 2;

34. SELECT   SUM ( DISTINCT  SAL ) FROM EMP WHERE JOB = ‘CLERK’ ;

35. SELECT AVG(COMM) FROM EMP;

TABLE :  salespeople

SNUM	SNAME	CITY	COMM
100	PEEL	LONDON	.12
1002	SERRES	SAN JOSE	.13
1004	MOTIKA	LONDON	.11
1007	RIFKIN	BARCELONA	.15
1003	AXELORD	NEW YORK	.10

CUSTOMERS

CNUM	CNAME	CITY	RATING	SNUM
2001	HOFFMAN	LONDON	100	1001
2002	GIOVANNI	ROME	200	1003
2003	LIU	SAN JOSE	200	1002
2004	GRASS	BERLIN	300	1002
2006	CLEMENS	LONDON	100	1001
2008	CISNEROS	SAN JOSE	300	1007
2007	PRREIRA	ROME	100	1004

ORDERS:

ONUM	AMT	ODATE	CNUM	SNUM
3001	18.69	10/03/1990	2008	1007
3003	767.19	10/03/1990	2001	1001
3002	1900.10	10/03/1990	2007	1004
3005	5160.45	10/03/1990	2003	1002
3006	1098.16	10/03/1990	2008	1007
3009	1713.23	10/04/1990	2002	1003
3007	75.75	10/04/1990	2004	1002
3008	4723.00	10/05/1990	2006	1001
3010	1309.95	10/06/1990	2004	1002
3011	9891.88	10/06/1990	2006	1001

TABLES : SALESPEOPLE, CUSTOMER, ORDERS

1. Write  a query that will create our ORDERS table
2. Create a  CUSTOMER  table so that  all  cnum values  are different from one another
3. Create a SALESPEOPLE  table  with snum as PRIMARY KEY
4. Write a command  that puts  new salespeople  into SALESPEOPLE table
5. Write a command  that puts  the following values , in their given order, into CUSTOMER table : city –Delhi , rating –700, cnum – 2007, snum – 1004, cname -  priya
6. Write a query  that removes  all orders from  customer  LIU from ORDERS table
7. Write a command that increases the rating of all customer by 300
8. Write a command  that increases  rating of  all customer in rome by 700
9. Sales person motika left the company. Assign her customer to Peel
10. To display all LONDON sales pupil names and their customer names 
11. To display maximum amount  for each salesperson 
12. MOTIKA left the company assign her customer to PEEL
13. To display all names of LONDON customers in alphabetical order 
14. To count the number of  sales people  registering orders for each day

  ( if salesperson  has more than  one order  on a given day  he or she  should be counted only once)

15. Write the output of the following commands
16. SELECT  SNUM , MAX(AMT) FROM ORDERS GROUP BY SNUM  HAVING SNUM IN(1002, 1007)
17. SELECT  SNUM, MAX(AMT) FROM ORDERS WHERE ODATE = 10/03/1990 GROUP BY SNUM
18. SELECT  ‘FOR’, ODATE, ‘THERE ARE ‘, COUNT(DISTINCT ONUM), ‘ ORDERS’ FROM ORDERS GROUP BY ODATE
19. SELECT COUNT(DISTINCT SNUM) FROM ORDERS
20. SELECT *  FROM CUSTOMERS WHERE CITY = ‘SAN JOSE’ OR  NOT RATING >200;

21. Write a query  that produces  all rows  from  the CUSTOMER  table for which  salesperson’s number is 1001
22. Write a query  that produces  all rows from  CUSTOMERS table in the following order : city, cname,rating, snum, cnum
23. Write a query  that produces  the rating followed by  the name of each customer in London
24. Write a query  that will produce snum values of  all salespeople  with  orders currently in the ORDERS  table  without any repeats .
25. Write a query  that will give you al orders for more than  $ 5000
26. Write a query  that will give you  the names  and cities of  all sales people  in San jose   with a commission below  0.9
27. write a query  on CUSTOMERS table  whose output  will exclude  all customers with rating >=100, unless they are located in London
28. Write a two queries [ using IN  & BETWEEN ]  that will roduce  all orders  taken  on October  3^rd or 4^th , 1990
29. Write a query  that  selects all of customers serviced by  peel or motika
30. Write a query  that will produce  all of the customers whose names begin with a letter form  S to V
31. Write a query  that selects  all orders  save those with zeros or NULL in the AMT field
32. Write a  query  that counts  all orders for October 4, 1990
33. Write a query  that counts  the no of  different non-NULL city values  in the customers table
34. Write a query  that selects each customers ‘a smallest order [ use GROUP BY COMMAND]
35. Write  query  that selects the highest rating  in each city[use GROUP BY COMMAND]
36. write a query  the produces  highest  of  commission  all sales people in city wise .  
37. Write a query  that the first  customer in alphabetical order , whose name begin with  G.
38. write  query  that selects  all salespeople  sorted by  comm.  In ascending  order
39. Write  a query  that selects  all orders  sorted  by ONUM  in descending  order.
40. Delete all sales people in London

      TABLE :TEACHER

No	Name	Department	Dateofjoining	Salary	Sex
1	Raja	Computer	21/5/98	8000	M
2	Sangita	History	21/5/97	9000	F
3	Ritu	Sociology	29/8/98	8000	F
4	Kumar	Linguistics	13/6/96	10000	M
5	Venkatraman	History	31/10/99	8000	M
6	Sidhu	Computer	21/5/86	14000	M
7	Aishwarya	Sociology	11/2/88	12000	F

1.    display  maximum salary of each department

2.    Add a  new column  named  ‘experience ‘

3.    increase the salary of  all history teachers  by 20% of their salary

4.    To display all, teachers  with their salary and name  who joined after 21^st may1997

5.    To display  all teacher  and their  annual salary (Assume that salary is paid every month)