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
|
1600
|
300
|
30
|
|
7521
|
Ward
|
Salesman
|
7698
|
1250
|
500
|
30
|
|
7566
|
Jones
|
Manager
|
7839
|
02-apr-81
|
2975
|
0
|
20
|
7654
|
Martin
|
Salesman
|
7698
|
1250
|
1400
|
30
|
|
7698
|
Blake
|
Manager
|
7839
|
01-may-81
|
2850
|
0
|
30
|
7782
|
Manager
|
7839
|
2450
|
0
|
10
|
||
7788
|
Scott
|
Analyst
|
7566
|
19-apr-87
|
3000
|
0
|
20
|
7839
|
King
|
President
|
0
|
5000
|
0
|
10
|
|
7844
|
Turner
|
Salesman
|
7698
|
1500
|
0
|
30
|
|
7876
|
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
|
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
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.
8 comments:
where is the answers?
Thanks for ur post....
Where is the answer
brother please give assignment 4,5,6 answers please i beg you
list all records as follows smith is clerk and he is working in dept no 20 since 12-MAR-1981
Please send me 🙏
take it
here
Post a Comment