Create A Table Called 'EMPLOYEE' With The Following Structure
Create A Table Called 'EMPLOYEE' With The Following Structure
Create A Table Called 'EMPLOYEE' With The Following Structure
+-------------------------+-----------------------+-----------+---------+
| Field
| Type
| Null
| Key |
+-------------------------+-----------------------+-----------+---------+
| Emp_No
| number(5)
| NO
| PRI |
| Emp_Name
| varchar(30)
| YES
|
|
| Designation
| varchar(10)
| YES
|
|
| Date_of_Join
| date
| YES
|
|
| Salary
| float
| YES
|
|
| Dept_No
| number(11)
| YES
|
|
+-------------------------+-----------------------+-----------+---------+
+------------------+----------------------------+
| Field
| Type
|
+------------------+----------------------------+
| Dept_No
| number(11)(PRI)
|
| Dept_Name | varchar(30)
|
| Dept_Loc
| varchar(20)
|
+------------------+----------------------------+
1 row created.
SQL> insert into employee values(10005,'John','Analyst','22-july-1990',4900,30);
1 row created.
SQL> insert into employee values(10006,'Johnes','clerk','16-apr-1986',950,30);
1 row created.
SQL> select * from employee;
EMP_NO EMP_NAME DESIGNATION DATE_OF_J SALARY
-------------- ------------------ -------------------- ----------------- -----------10001
Robert
officer
01-DEC-85
1000
DEPT_NO
-------------10
10002
Allan
clerk
14-MAY-82
5000
10
10003
Martin
Manager
23-DEC-84
3500
20
10004
James
Analyst
22-JUL-90
5000
30
10005
John
Analyst
22-JUL-90
4900
30
clerk
16-APR-86
950
30
10006
Johnes
6 rows selected.
DEPARTMENT TABLE
+------------+-------------------------+------------------+
| Dept_No | Dept_Name
| Dept_Loc
|
+------------+-------------------------+------------------+
| 10
| Marketing
| London
|
| 20
| Accounts
| America
|
| 30
| Sales
| New York
|
| 40
| Software
| Boston
|
| 50
| Production
| Boston
|
+-------------+------------------------+------------------+
Questions:
Q1. List the Employees belonging to department 20.
SQL> select emp_name from employee where dept_no=20;
EMP_NAME
---------------Martin
Q2. List the Employees who are earning more than 1200 but less than 4000.
SQL> select * from employee where salary between 1200 and 4000;
EMP_NO EMP_NAME DESIGNATION
DATE_OF_J
SALARY
DEPT_NO
----------------23-DEC-84
-----------3500
-------------20
Q3. List the Employees who have joined after 1st Jan 84 in the order of
the joining date.
SQL> select * from employee where date_of_join > '01-jan-1934' order by date_of_join;
EMP_NO EMP_NAME DESIGNATION
DATE_OF_J
SALARY
DEPT_NO
-----------------
------------
--------------
10002
Allan
clerk
14-MAY-82
5000
10
10003
Martin
Manager
23-DEC-84
3500
20
10001
Robert
officer
01-DEC-85
1000
10
10006
Johnes
clerk
16-APR-86
950
30
10004
James
Analyst
22-JUL-90
5000
30
10005
John
Analyst
22-JUL-90
4900
30
6 rows selected.
Q4. List the Employees who are either in Officer or Manager position.
DATE_OF_J
SALARY
DEPT_NO
-----------------
------------
--------------
10001
Robert
officer
01-DEC-85
1000
10
10003
Martin
Manager
23-DEC-84
3500
20
SQL> select emp_name from employee where salary>(select salary from employee where
emp_name='Robert');
EMP_NAME
----------------Allan
Martin
James
John
Q9. Find out how many employees are there in the organization.
Grade
Manager
Officer
Analyst
Clerk
DOJ
-----------------
-----------------------------
Robert
Allan
14, may
Martin
James
22, july
1990
John
22, july
1990
1982
Johnes
16, april
1986
6 rows selected.
Q16. Find out how long an employee has worked in the organization in
terms of number of:
- Year
- Months
- Days
SQL>
SQL>select sum(salaries),dept_name
from employee e,department d
where e.emp_no=d.dept_no group by d.dept_name.
Q18. Display the maximum salaries in each department. Along with the
name of the department. The column headings should be like this:
Dept_Name
Max(sum)
SQL>