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

17dec80

800

0

20

7499

Allen

Salesman

7698

1600

300

30


7521

Ward

Salesman

7698

1250

500

30


7566

Jones

Manager

7839

02apr81

2975

0

20

7654

Martin

Salesman

7698

1250

1400

30


7698

Blake

Manager

7839

01may81

2850

0

30

7782

Manager

7839

2450

0

10


7788

Scott

Analyst

7566

19apr87

3000

0

20

7839

King

President

0

5000

0

10


7844

Turner

Salesman

7698

1500

0

30


7876

Clerk

7788

23may87

1100

0

20


7900

James

Clerk

7698

03dec81

950

0

30

7902

Ford

Analyst

7566

03dec81

3000

0

20

7934

Miller

Clerk

7782

1300

0

10

Table : DEPT
Deptno

Dname

Location

10

Accounting

Newyork

20

Research


30

Sales


40

Operations

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
twomonth 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
12mar1981.
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 '12mar98'
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 30mar81 to 01jun82 .
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 22mar81.
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:
where is the answers?
Thanks for ur post....
Post a Comment