This is a database schema design interview question I have attended recently. The interviewer asked me to design DB schema for the below data in an efficient and normalized way and write a query to retrieve " first name, last name, department and work phone numbers of all employees who are managed by Frank Callea".
The data is given below in the diagram.
Employee Table
Dept Table
DEPTID DEPTNAME
---------- --------------------------------
1 Turbine
2 ExtroVert
3 App
---------- --------------------------------
1 Turbine
2 ExtroVert
3 App
Phone Table
PHONEID EMPID T PHNUMBER
---------- ---------- - ------------
1 1 W 312-555-2441
2 1 C 773-555-1962
3 1 P 888-555-9740
4 2 W 312-555-2488
5 2 C 312-555-2488
6 2 P 888-555-8718
7 3 W 312-555-8233
9 3 P 888-555-2519
10 4 W 312-555-336011 4 C 708-555-2755
13 5 W 312-555-8842
15 5 P 888-555-1469
16 6 W 312-555-4484
17 7 W 312-555-4168
Please note the relationships between tables Empid is a foreign key in Phone table, each table has a unique key field introduced.
Now the query part, how to retrieve "first name, last name, department and work phone numbers of all employees who are managed by Frank Callea".
The query which I have used is given below. If there is a better query for this problem please provide it as a comment.
select e.firstname,e.lastname,d.deptname,p.phnumber,e.mgrid from employee e, dept d, phone p where p.type='W' and e.deptid=d.deptid and e.empid=p.empid and e.empid in ( select e1.empid from employee e1, employee e2 where e2.firstname='Frank' and e2.lastname='Callea' and e1.mgrid=e2.empid);
FIRSTNAME LASTNAME DEPTNAME PHNUMBER MGRID
-------------------------------- -------------------------------- -------------------------------- ------------ ----------
Trad Lin Turbine 312-555-2441 2
Fred Snyder App 312-555-8233 2
-------------------------------- -------------------------------- -------------------------------- ------------ ----------
Trad Lin Turbine 312-555-2441 2
Fred Snyder App 312-555-8233 2
select e.firstname,e.lastname,d.deptname,p.phnumber,e.mgrid from employee e, dept d, phone p where p.type='W' and e.deptid=d.deptid and e.empid=p.empid and e.mgrid in ( select empid from employee where firstname='Frank' and lastname='Callea');
ReplyDelete