EMPLOYEE DATABASE SYSTEM IN SQL

i.Display the employee table with the list of fields namely employee number,name,dob,sex,job,grade,hire date,salary and department number to be referred as foreign key

SQL>create table employee(eno int, ename varchar2(20),dob varchar2(10),sex varchar2(10),job varchar2(20),grade varchar2(10),hiredate varchar2(20),salary varchar2(10),department varchar2(20),foreign key(Salary),foreign key(department));

Table created.

SQL> insert into employee values('&eno','&ename','&dob','&sex','&grade', '&hiredate', '&salary', '&department');

Enter value for eno: 1

Enter value for ename:stefin

Enter value for dob: 2/2/88

Enter value for sex: male

Enter value for hiredate: 1/2/12

Enter value for salary: 45000

Enter value for department: programmer

1 row created.

ii.Display the department table with the list of attributes namely department number and department name where department number as primary key

SQL>create table dept(deptno number(5),depname varchar2(20) primary key(deptno));

Table created.

SQL> insert into dept values('&deptno','&depname');

Enter value for depno: 1

Enter value for depname:stefin

1 row created.

iii.Write a correlated nested query to display the employee name and department number from employee table and department table.

SQL>select  ename,deptno from  employee,dept where eno in (select  deptno from deptno where deptno>1);

ENAME    DEPTNO

-----------    ------------

2                  mac

3                  hendry

iv.Write a query to display the employees earning more than the average salary in their own department.

SQL>select ename from employee where salary > (select avg(salary) from employee);

ENAME

-----------

stefin

Mac

v. Write a query to display the details of the employee whose department number is 20.

SQL>select  * from dept where deptno=20;

DEPTNO    DEPTNAME

------------   ----------------

20                  Design