Create the following 3 tables. Identify the primary
key(s) and foreign key(s) as appropriate.
Table-1
CREATE TABLE DEPT
(DEPTNO INT,
DNAME VARCHAR(14),
LOC VARCHAR(13) );
Insert the following data into the DEPT table (data is in the same sequence as the fields defined above):
10, 'ACCOUNTING', 'NEW YORK'
20, 'RESEARCH', 'DALLAS'
30, 'SALES', 'CHICAGO'
40, 'OPERATIONS', 'BOSTON'
Table-2
CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT,
HIREDATE DATE,
SAL FLOAT,
COMM FLOAT,
DEPTNO INT REFERENCES DEPT(DEPTNO));
Insert the following
data into the EMP table (data is in the same sequence as the fields defined
above):
7369,
'SMITH', 'CLERK', 7902, '17-DEC-1980', 800,
NULL, 20
7499,
'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30
7521,
'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30
7566,
'JONES', 'MANAGER', 7839, '2-APR-1981', 2975,
NULL, 20
7654,
'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30
7698,
'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30
7782,
'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10
7788,
'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20
7839,
'KING', 'PRESIDENT', NULL,
'17-NOV-1981', 5000, NULL, 10
7844,
'TURNER', 'SALESMAN', 7698,
'8-SEP-1981', 1500, NULL, 30
7876,
'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20
7900,
'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL,
30
7902,
'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000,
NULL, 20
7934,
'MILLER', 'CLERK', 7782,
'23-JAN-1982', 1300, NULL, 10
Table-3
CREATE
TABLE SALGRADE
(GRADE INT,
LOSAL INT,
HISAL INT);
Insert the following
data into the SALGRADE table (data is in the same sequence as the fields
defined above):
2, 1201,
1400
3, 1401,
2000
4, 2001,
3000
5, 3001,
9999
Practice Questions:
1.
Write
a query to display all the details of the employees who are working for
department 10 or 20.
2.
Write a query to display the names
of the employees whose salaries are 5000 or 3000 or 800. Write the query in 2
different ways.
3.
Write a query to display all the
details of the employees whose names are:
‘SCOTT’, ’BLAKE’, ’ADAMS’.
4.
Write a query to display the
employees whose date of hire is one of the following: ’Dec 17, 1981’, ’3rd
Dec, 1981’, ’21st Jan, 1981’.
5.
Write a query to display the details
of the employees who are getting paid in the range 2000 – 5000. Write the query
in two different ways.
6.
Write a query to display the details
of all the employees who joined during the period ’Jan 01st, 1981’
and ’31st Aug, 1981’.
7.
Write a query to display all the
details of the employees whose name begins with the letter ‘S’.
8.
Write a query to display all the
details of the employees whose name is at least 4 characters long.
9.
Write a query to display names of
the employees whose name contains the letter 'A'.
10.
Write a query to display names of
the employees who have joined in the month where the name of the month starts
with the letter 'A'.
11.
Write a query to display names of
the employees whose date of joining is within the first 9 days of any calendar month.
12.
Write a query to display the names
of the employees who are not receiving any commission.
13.
Write a query to display the names
of the employees whose job title is ‘MANAGER’ and who are working for department
20.
14.
Write a query to display the details
of employees, whose name starts with letter ‘J’ and whose salary is greater
than 2500 and who work in the department 20.
15.
Write a query to display the details
of employees, whose name starts with letter ‘J’ or whose salary is greater than
2500 or who work in the department 20.
16.
Write a query to display the details
of all the employees whose job title is ‘MANAGER’ or ‘CLERK’.
17.
Write a query to display the
employees whose salary is same as that of the employees ‘FORD’ or ‘JAMES’.
18.
Write a query to display the details
of the employees who are working in the 'ACCOUNTING' department.
19.
Write a query to display the details
of all the salesmen belonging to the sales department who are receiving
commission.
20.
Write a query to display details of
the all the managers having experience not less than 20 years and working in
the location 'NEW YORK' or 'DALLAS' and working under some other manager.
21.
Write a query to display the salary
grade of the employee 'FORD'.
22.
Display the details of Managers
belonging to grades 4 or 5.
23.
Display the following data for all
the employees: Employee number, employee name, salary, grade, work location.
24.
Display the following data for all
the employees of the “Accounting” department: Employee number, employee name,
salary, work location.
25.
Display all grade 4 and grade 5
MANAGERS of the following departments: ACCOUNTING, SALES.
26.
Write a query to list the details of
all the employees who belong to the department where 'ADAMS' is working.
27.
Display the details of all employees
those who are senior (in terms of years of experience) to their own manager.
28.
Display the details of all the
departments – if the department has any employee then display the employee's
number and name as well.
29.
Display the details of employees
whose department is located in 'NEW YORK' city.
30.
Display the details of employees
whose salary is more than the salary of the employee ‘JAMES’.
31.
Display the details of all the
employees whose job title is same as that of the employee ‘JAMES’.
32.
Display the details of all the
employees who are senior to ‘JAMES’ (in terms of years of experience).
33. Display the details of all the employees who are working in the same department as that of ‘SCOTT’; do not include the details of ‘SCOTT’.
34. Display the details of the employees whose job titles are same as that of the employees of the ‘SALES’ department.
35. Display the details of all grade 3 employees whose department is same as that of the employee ‘SCOTT’
36. Display the details of all the employees working in the city of ‘NEW YORK’ or ‘DALLAS’ and whose job title is same as that of any grade 2 employee; however do not include the job title ‘SALESMAN’.
37. Display the details of all the employees whose salary is more than that of employee ‘SMITH’ but less than that of employee ‘BLAKE’; however, it should not be equal to that of ‘SCOTT’ or that of ‘FORD’.
38. Display the details of all employees to whom other people are reporting.
39. Display the details of all the employees who are working in the department where the manager of employee ‘BLAKE’ is working.
40. Display the following details of all the employees: employee number, name, salary, department name, location. If the employee is not working in any department then display just the employee number, name, salary.
41. Display the details of employees whose salaries are more than the salary of any employee belonging to department 10.
42. Display the details of all the employees working in department 10 whose salary is more than the highest salary being given in department 20.
43. Display the details of the nth highest paid employee.
44. Display the details of the highest paid employee from each department.
45. Display the details of the senior-most employee of each department.
