Please consider the following

Interview Question for Software Engineer in Tests

0

of 0 votes

5
Answers
Please consider the following tables:

Code:

Table Name: Person

Person_Id Name

Table Name: DVD

DVD_ID Owner_ID Title

Here is the query:
Write a query that returns the list of DVDs that belong to owners who own “Superman”

Here is my solution:

Code:

SELECT p.name,d.dvd_id
FROM DVD d,Person p
WHERE p.person_id = d.dvd_id
AND Title = 'Superman' ;

I answered above answer. But interviewer was expecting me to do using Subquery. Is that possible?Please let me know if the above query is correct or not.
- zealoftoday May 16, 2013 in United States | Report Duplicate | Flag | PURGE
Software Engineer in Test

Email me when people comment.

An error occurred in subscribing you.

Country: United States
Interview Type: Phone Interview

Email me when people comment.

An error occurred in subscribing you.

Comment hidden because of low score. Click to expand.

of 2 vote

Write a query that returns the list of DVDs that belong to owners who own “Superman”

--> the list of DVDs should be distinct, and you only want the DVD Titles (At least when I was teaching assistance for a DB class, I make my student not to output useless stuff).

This line only give you SUPERMAN and nothing else. It is wrong. All, the where clause is wrong-.-

You should do subquery. first get the people with SUPERMAN, kindof like your query:

SELECT Owner_ID
FROM DVD d
WHERE Title = 'Superman' ;

Then join them back and get all DVD title

SELECT DISTINCT Title
FROM DVD d,Person p INNERJOIN Person p ON p.Person_id = d.Owner_ID
WHERE p.Person_ID IN 
(SELECT Owner_ID
FROM DVD d
WHERE Title = 'Superman') ;

- Mo Lam May 16, 2013 | Flag Reply

Comment hidden because of low score. Click to expand.

of 2 votes

Also, fix the cases in my SQL.

If you do not use subquery, you will need a 3 table join (DVD a <--> Person <--> DVD b). both on OWNER_ID = PERSON_ID, where a.Title = 'superman' and select DISTINCT b.Title

SELECT DISTINCT b.Title
FROM (DVD a,Person p INNER JOIN Person p ON p.Person_id = a.Owner_ID) INNER JOIN DVD b ON p.PersonID = b.Owner_ID
WHERE a.Title = "Superman"

- Mo Lam May 16, 2013 | Flag

Comment hidden because of low score. Click to expand.

of 0 votes

First solution (subquery) should be faster since the where happened eariler.

- Mo Lam May 16, 2013 | Flag

Comment hidden because of low score. Click to expand.

of 0 vote

I third 3rd line should be 'WHERE p.person_id = d.Owner_id

- Anonymous May 16, 2013 | Flag Reply

Comment hidden because of low score. Click to expand.

-1

of 1 vote

SELECT TITLE FROM DVD WHERE OWNER_ID IN
(SELECT PERSON_ID FROM PERSON WHERE NAME = 'SUPERMAN')

- rizwan.amd May 17, 2013 | Flag Reply

CareerCup

Interview Question for Software Engineer in Tests

Books

Videos

Resume Review

Mock Interviews