Consider the following relational schema. (you may have to zoom in to see the sc

Question

Consider the following relational schema. (you may have to zoom in to see the schemas)

Consider the following relational schema employee fname CHARACTER VARYING(15) minit CHARACTER VARYING(1) Iname CHARACTER VARYING(16) essn CHARACTERo) bdate DATE address CHARACTER VARYING(50) dependent works on CHARACTERB) dependent name CHARACTER VARYING (15) essn CHARACTERR) CHARACTER(1) DATE CHARACTER VARYING(B) pno NUMERIC bdate hours NUMERIC CHARACTER1) dept locations relationship salary NUMERIC super ssn CHARACTER() dno dno NUMERIC dlocation CHARACTER VARYING(15) NUMERI department CHARACTER VARYINGQ5) NUMERI dname project mgrssn CHARACTER) mgstartdate DATE pname CHARACTER VARYING(25) pno plocation CHARACTER VARYING(15) dno NUMERIc NUMERIC Write SQL statements for the following queries 1) (10 points) Retrieve essns of employees who worked more than 45 hours on projects 2) (10 points) Retrieve fnames of employees who have no working experience 3) (10 points) Retrieve fnames of employees who have the highest salary 4) (10 points) Retrieve the minimum, maximum and average salaries of employees whose department is 'Research' 5) (10 points) Retrieve fnames of employees who have more than two dependents

Explanation / Answer

Firstly we have to create all the tables methoned in diagram.

SQL> create table employee
(
fname varchar2(20),
lname varchar2(20),
essn number(6) primary key,
bdate varchar2(20),
address varchar2(50),
sex varchar2(5),
salary number(8,2),
superssn varchar2(10),
dno number(3),
foreign key(dno) references department(dno)
);

Table created.

SQL> select * from employee;

FNAME LNAME ESSN BDATE
-------------------- -------------------- ---------- --------------------
ADDRESS SEX SALARY SUPERSSN
-------------------------------------------------- ----- ---------- ----------
DNO
----------
Tony Stark 101 12-12-1985
USA Male 85000 aa
11

Captan America 102 02-10-1996
UK Male 25800.25 aa
12

FNAME LNAME ESSN BDATE
-------------------- -------------------- ---------- --------------------
ADDRESS SEX SALARY SUPERSSN
-------------------------------------------------- ----- ---------- ----------
DNO
----------

Akshay Bhosale 103 26-03-1997
India Male 25800.25 aa
11

Virat Kohli 104 26-03-1997
India Male 20000.25 aa

FNAME LNAME ESSN BDATE
-------------------- -------------------- ---------- --------------------
ADDRESS SEX SALARY SUPERSSN
-------------------------------------------------- ----- ---------- ----------
DNO
----------
13

Rohit Sharma 105 02-03-1994
India Male 60000.25 aa
13

Axer Patel 106 02-03-1994

FNAME LNAME ESSN BDATE
-------------------- -------------------- ---------- --------------------
ADDRESS SEX SALARY SUPERSSN
-------------------------------------------------- ----- ---------- ----------
DNO
----------
India Male 12000.25 aa
13

MS Dhoni 107 02-03-1994
India Male 99000.25 aa
13

7 rows selected.

SQL> create table department(
dname varchar2(20),
dno number(3) primary key,
mdssn varchar2(20),
mgstartdate varchar2(20)
);

Table created.

SQL> select * from depaRTment;

DNAME DNO MDSSN MGSTARTDATE
-------------------- ---------- -------------------- --------------------
Marketing 11 abc 11-11-2017
Finance 12 abc 10-01-2017
Research 13 bb 22-05-1999

SQL> create table dependent
(
essn number(4),
dependentname varchar2(20) primary key,
sex varchar2(5),
bdate varchar2(15),
relationship varchar2(40),
foreign key(essn) references employee(essn)
);

Table created.

SQL> select * from dependent;

ESSN DEPENDENTNAME SEX BDATE
---------- -------------------- ----- ---------------
RELATIONSHIP
----------------------------------------
101 Ram Male 15-15-1997
friend

103 Bhuvi Male 15-15-1997
assistant

101 Ak Male 15-15-1997
assistant

ESSN DEPENDENTNAME SEX BDATE
---------- -------------------- ----- ---------------
RELATIONSHIP
----------------------------------------
101 Hulk Male 15-15-1997
brother

SQL> create table dept_location
(
dno number(4),
dlocation varchar2(20) primary key,
foreign key(dno) references department(dno)
);

Table created.

SQL> select * from dept_location;

DNO DLOCATION
---------- --------------------
11 Californai
12 London

SQL> create table project
(
pno number(3) primary key,
pname varchar2(20),
plocation varchar2(25),
dno number(4),
foreign key(dno) references department(dno)
);

Table created.

SQL> select * from project;

PNO PNAME PLOCATION DNO
---------- -------------------- ------------------------- ----------
111 Proj1 Cali 11
112 Proj2 Lon 12

SQL> create table works_on
(
essn number(4),
pno number(4),
hours number(5),
foreign key(essn) references employee(essn),
foreign key(pno) references project(pno)
);

Table created.

SQL> select * from works_on;

ESSN PNO HOURS
---------- ---------- ----------
101 111 95
102 112 15
103 112 0

1}

SQL> select essn from works_on where hours>45;

ESSN
----------
101

2}

SQL> select fname
from works_on,employee
where employee.essn=works_on.essn and hours=0;

FNAME
--------------------
Akshay

3}

SQL> select fname from employee where salary=(select max(salary) from employee);

FNAME
--------------------
Tony

4}
SQL> select fname from employee,
department where department.dno=employee.dno
and dname='Research'
and salary=(select max(salary) from employee);

FNAME
--------------------
MS

SQL> select fname from employee,
department where department.dno=employee.dno
and dname='Research'
and salary=(select min(salary) from employee);

FNAME
--------------------
Axer

5}
SQL> select fname from employee where (select count(*) from dependent where employee.essn=dependent.essn)>=2;

FNAME
--------------------
Tony

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.