MAP

sql query exercise and assignment


DBMS Queries



Create Table : EMP

Column Name

Data Type

Size

Attributes

Empno
Number
4
Primary key
Ename
Varchar2
7
Not null
Job
Varchar2
9

Mgr
Number
4

Hiredate
Date


Sal
Number
7,2

Comm.
Number
7,

deptno
Number
2
Foreign key


Create Table : DEPT

Column Name
Data Type
Size
Attribute
Deptno
Number
2
Primary key
Dname
Varchar2
12
Not null
location
Varchar2
10


Create Table : SalGrade

Column Name

Data Type

Size

Grade
Number
4
Losal
Number
7,2
Hisal
Number
7,2


Insert the values:

Table:  EMP

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

Table : DEPT

Deptno
Dname
Location
10
Accounting
Newyork
20
Research
Dallas
30
Sales
Chicago
40
Operations
Boston

Table : SalGrade

Grade
Losal
Hisal
1
700
1200
2
1201
1400
3
1401
2000
4
2001
3000
5
3001
9999


Assignment #1


1. List all rows of the table emp.
2. Display the structure of table emp.
3. List all table.
4. List all employees’ number from emp.
5. List all employees name and designation from emp.
6. List all commission from emp.
7. List all mgr from emp.
8. List all departments’ number from emp.
9. List all employees number,name,jobs,mgr from emp.
10.List all employees name,mgr from emp.
11.List all job,commission from emp.
12.List all employees name,department number from emp.
13.List all department number,employee name,salary,job from emp.
14.List all department number,employee name from emp.
15.List all grades from Sal grade.
16.List all losal,hisal,grade from salgrade.

Assignment #2


1. List all employees who are managers.
2. List all employees who are presidents.
3. List all employees who are either manager or clerk.
4. List all employees who are managers of department number 10.
5. List all employees who are working in department number 10.
6. List all employees who are not belonging to department number 20,30,40 and are not manager.
7. List all employees who are either manager of department number 10 or are analyst.
8. List all employees who are not president, analyst or salesman.
9. List all employees who work in department number 10 and their job is clerk.

Assignment #3


1. Change the column heading ename into your name and display it.
2. Change the column heading sal into salaries.
3. Change the column heading ename into 'employee name 'and job into designation
4. List all 'manager' and add 100 rupees in each salary.
5. List all employee and add 1000 rupees and subtract 4500 rupees in each salary.
6. If employee name is scott then add 100 rupees and divided by 100 rupees.
7. List all employee after adding 500 rupees,subtract 300 and divided by 100 rupees.
8. List all employes and find five percent from each salary.
9. Find eight percent of salesmen salary.
10.Find the two-month salary of each manager.
11.Find the daily wages of each employee.
12.Display the column heading annsal instead of sal * 12.
13.Combine empno and ename then change the column heading as employee.
14.Join ename and job then change the column heading as comp_name.
15.List all records as follows smith is clerk and he is working in dept no 20 since 12-mar-1981.
16.Find annual salary and add comm for all employees.
17.List all distinct department number from emp table.
18.Dispaly distinct value of department number and jobs.
19.List all employees who are not eligible for receiving commission and are 'manager'.
20.List all employees who are eligible of receiving commission.
21.List all distinct jobs from the table emp.
22.How many jobs do we have?

Assignment #4


1. List hiredate in descending order.
2. List hiredate in ascending order.
3. Find all department names with their department number while numbers greter then 20.
4. Find those employees whose commission is greter then their salaries.
5. Find those employees whose commission is less then their salaries.
6. Find all employees those salaries are between 500 and 1500.
7. Find those employees whose mgr is 7902, 7566, 7788.
8. To find those employees whose mgr is between 7788 and 7092.
9. List all employees whose name starts with 's'.
10.List all employees having first name as 'scott'.
11.List of those employees having only 5 character long name and job as a manager.
12.List all employees who does not have a manager (whose manager is null).
13.Find those employees whose job do not start with 'a'.
14.List of those employees whose mgr is not null.
15.List of those employees whose jobs are 'manager or clerk' of department number 10.
16.Find all manager and salesman their salaries over 1500.
17.Find the average salaries of different jobs.
18.Find the average salaries of all jobs excluding manager.
19.Find the average salaries of each job within department.
20.Find the average salaries of each department.
21.Find average and sum of the salaries of each job excluding 'salesman','clerk' and 'manager'.


Assignment #5


1. Find the absolute value of -18.
2. Find the absolute value of 30.
3. Find the celing value of 22.5.
4. Find the celing value of 31.2.
5. Find the Floor value of 37.2.
6. Display your name in lowercase.
7. Find the square root of 6.
8. Find the first and second character of Ename.
9. Display the year only from the hiredate.
10.Find the soundex of all ename.
11.Find the soundex of those employee whose job soundex to clark.
12.Find the minimum salary from the table emp.
13.Find the maximum salary from the table emp.
14.Find the length of all ename.
15.Find the length of job.
16.Find the ascii value of all employee name.
17.Find the value of 2 through ascii value.
18.Find the position of character ‘a’ in the job.
19.Find the user id which assigned by oracle.
21.Replace the value of deptno 10 with ‘ten no dept’,20 with ’20 dept no’, 30 with ’30 dept no’ else ‘not assigned’
22.Find the sum of all salary.
23.Find the all employees whose hire date is after Jan 80.
24.Find those employees whose department location is newyork.
25.Find those employees who are working in accounting department.
26.Define the the nvl substitution in comm. Field  as zero if any null  value in this field.
27.Calculate annual salary of employee and display the result as annual.
28.List all employees who are working in 'sales' department.
29.List all employees whose location is newyork and department name is accounting.
30.List all employees whose location is chicago.
31.Find the manager of 'Scott'.
32.Find the manager of 'Blake'
33.Find the salary of those employees who earn more then jones.
34.List those department who doesnot have any employees.
35.Find the union of department number 10 and department number 30.
36.Find the intersect of department number 10 and department number 30.
37.Find the minus of department number 10 and department number 30.
38.Find the minimum salary with employee name.
39.Find all employees who have the same job as blake.
40.Find the minimum salaries in each department.
41.Find all the department, which have an average salary is greater then department number 30.
42.Find all employees in department number 10 whose jobs the same as the employees job in the sales department.
43.Find all employees who earn more than any employees in department 30.
44.Find the job which has the highest average salary.
45. Find the grade of salary for each employee.

Assignment #6


1. Display the structure of the table emp.
2. Display system date
3. Count all employees.
4. Display the sum of all employees’ salaries.
5. How many managers do we have?
6. How many departments do we have?
7. List average salary of each department.
8. List average salary of each job.
9. Find the maximum minimum sal of all employees.
10.Find the day of all employees when they were hired.
11.Find the next Friday after the '12-mar-98'
12.Find the last day of each hiredate.
13.List all employees with their joining month only.
14.List all employees who were hired in year 1987.
15.List all employees who were hired from 30-mar81 to 01-jun-82.
16.Find the income tax of all employees 5% of its annual salary.
17.Display all records in following format (for all records) on employee <ename> work in department <name> and appointed on <date>.
18.Find the duration of service of all employees.
19.Count all employees who were hire after 22-mar-81.
20.List the most experienced employee.
21.Display the department number and their given names using decode () function.
          10 accounting.
          20 researches.
          30 sales.
22.Find the first occurrence of the letter 'l' in employee name and change 'l' with 'x'.
23.Find all employees whose department is not in the dept table.

2 comments:

Post a Comment

Twitter Delicious Facebook Digg Stumbleupon Favorites More