Scribd
Upload
176 views10 pages

Create The Following Tables With The Given Structures and Data

The document describes tasks to create tables, write queries to retrieve and manipulate data from the tables, and includes exercises on subqueries. It involves creating EMP tables for years 2016 and 2017, writing selection and projection queries on the tables, combining records from both years, and queries comparing employees' salaries to managers' salaries.

Uploaded by

rizwan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
176 views10 pages

Create The Following Tables With The Given Structures and Data

The document describes tasks to create tables, write queries to retrieve and manipulate data from the tables, and includes exercises on subqueries. It involves creating EMP tables for years 2016 and 2017, writing selection and projection queries on the tables, combining records from both years, and queries comparing employees' salaries to managers' salaries.

Uploaded by

rizwan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 10

1.

Create the following tables with the given structures and data:
EMP2016

EMPNO ENAME JOB


(PK)
AHMED CLERK
101
SAJID DBA
102
ASAD ANALYST
103
KHALID CLERK
104

EMP2017

EMPNO ENAME JOB


(PK)
AHMED CLERK
101
SAJID ANALYST
102
ASAD ANALYST
103
KHALID DBA
104
Now perform tasks 2 to 6 on tables created in task 1.
2. Write a query on EMP2016 to display EMPNO, ENAME, JOB of only those employees having JOB =
CLERK. Identify whether it is SELECTION or PROJECTION.

2 selection

3. Write a query on EMP2017 to display EMPNO, ENAME of all the employees. Identify whether it is
SELECTION or PROJECTION.

3 projection

4. Write a query to display EMPNO, ENAME, and JOB of all the employees in year 2016 and 2017
combine. The output must display the employee’s old and new status.
5. Write a query to display EMPNO, ENAME, and JOB of all the employees in year 2016 and 2017
combine. The output must display the records of employees who have not been promoted.

6. Write a query to display EMPNO, ENAME, and JOB of all the employees in year 2016 and 2017
combine. The output must display the records of employees who have been promoted.

Using EMP, DEPT and SALGRADE tables, perform tasks 7 - 11.


7. Create a unique listing of all JOBS that are in DEPTNO 30. Include the LOCATION of department in
the output.
8. Write a query to display the employeess’ ENAME, JOB, SAL and GRADE of all employees who earn a
commission.

9. Write a query to display all employees’ names and their salaries along with their manager’s names
and salaries.
10 Write a query to display all employees’ names and their department’s names irrespective employee
having deptno or not in the EMP table

Exercises for Sub queries


Using EMP, DEPT and SALGRADE tables, solve the following queries.

1. Write a query to display employee name, employee salary, manager name, manager salary of all the
employees having salary greater than their own manager’s salaries.
2. Write a query to display employee name, employee salary of all the employees having salaries
greater than the both the max salaries of deptno= 10 and deptno=20.

3. Write a query to display all the employees who do not have any subordinate or they are not
managers.
4. Create a query that displays the name, job, department name, salary, and salary grades of all
employees.

5. Create a query to display the name and hire date of any employee hired after employee
ADAMS.
6. Display the names and hire dates for all employees who were hired before their managers, along
with their manager’s names and hire dates.

7. Write a query to display all employees’ names and the names of all possible departments in which an
employee can work.
8. Write a query to display all the employee names and their department names. Employees having null
departments must all also be displayed.

You might also like