1.Create and use the following database schema to answer
the given queries
|
EMPLOYEE Schema
|
|
DEFAUL
|
|
Field
|
Type
|
NULL KEY
|
T
|
|
Eno
|
Char(3)
|
NO
|
PRI
|
NIL
|
|
Ename
|
Varchar(50)
|
NO
|
NIL
|
|
Job_type
|
Varchar(50)
|
NO
|
NIL
|
|
Manager
|
Char(3)
|
Yes
|
FK
|
NIL
|
|
Hire_date
|
Date
|
NO
|
NIL
|
|
Dno
|
Integer
|
YES
|
FK
|
NIL
|
|
Commission
|
Decimal(10,2)
|
YES
|
NIL
|
|
Salary
|
Decimal(7,2)
|
NO
|
NIL
|
|
DEPARTMENT Schema
|
|
DEFAUL
|
|
Field
|
Type
|
NULL KEY
|
T
|
|
Dno
|
Integer
|
No PRI
|
NULL
|
|
Dname
|
Varchar(50)
|
Yes
|
NULL
|
Query List
1. Query to display Employee Name, Job, Hire Date,
Employee Number; for each employee with the Employee Number appearing first.
2. Query to display unique Jobs from the Employee Table.
3. Query to display the Employee Name concatenated by a
Job separated by a comma.
4. Query to display all the data from the Employee Table.
Separate each Column by a comma and name the said column as THE_OUTPUT.
5. Query to display the Employee Name and Salary of all
the employees earning more than ì 2850.
6. Query to display Employee Name and Department Number
for the Employee No= 7900.
7. Query to display Employee Name and Salary for all
employees whose salary is not in the range of ì 1500 and ì 2850.
8. Query to display Employee Name and Department No. of
all the employees in Dept 10 and Dept 30 in the alphabetical order by name.
9. Query to display Name and Hire Date of every Employee
who was hired in 1981.
10. Query to display Name and Job of all employees who
don‘t have a current Manager.
11. Query to display the Name, Salary and Commission for
all the employees who earn commission.
12. Sort the data in descending order of Salary and
Commission.
13. Query to display Name of all the employees where the
third letter of their name is ‗A‘.
14. Query to display Name of all employees either have
two ‗R‘s or have two ‗A‘s in their name and are either in Dept No = 30 or their
Manger‘s Employee No = 7788.
15. Query to display Name, Salary and Commission for all
employees whose Commission Amount is 14 greater than their Salary increased by
5%.
16. Query to display the Current Date.
17. Query to display Name, Hire Date and Salary Review
Date which is the 1st Monday after six months of employment.
18. Query to display Name and calculate the number of
months between today and the date each employee was hired.
19. Query to display the following for each employee
<E-Name> earns < Salary> monthly but wants < 3 * Current Salary
>. Label the Column as Dream Salary.
20. Query to display Name with the 1st letter capitalized
and all other letter lower case and length of their name of all the employees
whose name starts with ‗J‘, ‘A‘ and ‗M‘.
21. Query to display Name, Hire Date and Day of the week
on which the employee started.
22. Query to display Name, Department Name and Department
No for all the employees.
23. Query to display Unique Listing of all Jobs that are
in Department # 30.
24. Query to display Name, Dept Name of all employees who
have an ‗A‘ in their name.
25. Query to display Name, Job, Department No. And
Department Name for all the employees working at the Dallas location.
26. Query to display Name and Employee no. Along with
their Manger‘s Name and the Manager‘s employee no; along with the Employees‘
Name who do not have a Manager.
27. Query to display Name, Dept No. And Salary of any
employee whose department No. and salary matches both the department no. And
the salary of any employee who earns a commission.
28. Query to display Name and Salaries represented by
asterisks, where each asterisk (*) signifies ì 100.
29. Query to display the Highest, Lowest, Sum and Average
Salaries of all the employees
30. Query to display the number of employees performing
the same Job type functions.
31. Query to display the no. of managers without listing
their names.
32. Query to display the Department Name, Location Name,
No. of Employees and the average salary for all employees in that department.
33. Query to display Name and Hire Date for all employees
in the same dept. as Blake.
34. Query to display the Employee No. And Name for all
employees who earn more than the average salary.
35. Query to display Employee Number and Name for all
employees who work in a department with any employee whose name contains a ‗T‘.
36. Query to display the names and salaries of all
employees who report to King.
37. Query to display the department no, name and job for
all employees in the Sales department.
Software Lab Based on SQL/PL-SQL:
[SQL COMMANDS]
1) SQL* formatting commands
2) To create a table, alter and drop table.
3) To perform select, update, insert and delete operation
in a table.
4) To make use of different clauses viz where, group by,
having, order by, union and intersection,
5) To study different constraints.
[SQL FUNCTION]
6) To use oracle function viz aggregate, numeric,
conversion, string function.
7) To understand use and working with joins.
8) To make use of transaction control statement viz
rollback, commit and save point.
9) To make views of a table.
10) To make indexes of a table.
[PL/SQL]
11) To understand working with PL/SQL
12) To implement Cursor on a table.
13) To implement trigger on a table
