Writing query with given very normalized tables- was asked during interview

Question

I was asked this question during the interview with one of top IT industries and I had absolutely no clue. Can anyone tell me how? Either Mysql or Oracle is fine but I am providing the example with mysql.

CREATE TABLE employee (employee_ID VARCHAR(5), manager_ID VARCHAR(5));

CREATE TABLE meeting (meeting_ID VARCHAR(1), meeting_title VARCHAR(100));

CREATE TABLE attendee (meeting_ID VARCHAR(1), employee_ID VARCHAR(5));

The relationship is pretty straight forward with the example. I think this is a pretty much normalized example up to 3rd normal form. Attendee associates the meeting and participating employee including employees and managers.

The question was to write a query that finds out the meetings which both employee and his manager are attending.

Maybe it was a test to see if you commented on their completely insane choice of data types for the ID columns. — David Aldridge, Nov 25 '13 at 22:14
Although i agree, I've seen stuff like this in real life, sadly. — user2366842, Nov 25 '13 at 22:24
I wonder how many meetings they are expecting to hold, ever. Mind you, if CHAR length semantics is being used, a VARCHAR2(1) could store up to 4,294,967,296 different values, I think. — Jeffrey Kemp, Nov 26 '13 at 03:42

Andrei B · Answer 1 · 2013-11-25T22:12:10.840

2

Something like this would work:

SELECT m.*
FROM meeting m 
INNER JOIN attendee a a.meeting_ID = m.meeting_ID
INNER JOIN employee e ON e.employee_ID = a.employee_ID
WHERE (a.meeting_ID,e.manager_ID) IN (SELECT meeting_ID,employee_ID FROM antendee)

or (probably better)

SELECT m.*
FROM meeting m 
INNER JOIN attendee a a.meeting_ID = m.meeting_ID
INNER JOIN employee e ON e.employee_ID = a.employee_ID
INNER JOIN attendee am ON am.employee_ID = e.manager_ID AND am.meeting_ID = a.meeting_ID

edited Nov 25 '13 at 22:12

answered Nov 25 '13 at 22:06

Andrei B

2,740
17
12

Thank you Andrei. Both are working probably latter is more efficient. – user3034116 Nov 25 '13 at 22:34

score 2 · Answer 2 · answered Nov 25 '13 at 22:29

If you just need the names of the meetings, you could use this query:

SELECT DISTINCT meeting.meeting_title
FROM
  attendee a1 INNER JOIN employee e ON a1.employee_ID=e.employee_ID
  INNER JOIN attendee a2 ON e.manager_ID=a2.employee_ID
  INNER JOIN meeting ON a1.meeting_ID = meeting.meeting_ID
WHERE
  a1.meeting_ID=a2.meeting_ID

score 0 · Answer 3 · answered Nov 25 '13 at 22:26

Try this, meeting attended by a and b and b is the manager of a

SELECT m.*
FROM meeting m 
    JOIN attendee a a.meeting_ID = m.meeting_ID
    JOIN attendee b b.meeting_ID = m.meeting_ID AND a.employee_ID <> b.employee_ID
    JOIN employee e ON e.employee_ID = a.employee_ID AND e.manager_ID = b.employee_ID

Writing query with given very normalized tables- was asked during interview

3 Answers3