SQL syntax for the following Select statements based on the below: --Select the
ID: 3821124 • Letter: S
Question
SQL syntax for the following Select statements based on the below:
--Select the names of both members and pets that live at 123 2nd Ave. (USE A UNION STATEMENT)
--Select everyone who lives in a house without any pet. (USE A NESTED SUB QUERY and NOT IN)
MEMBER
Member_ID
Name
Cell
Birthday
Gender
Lives_In
1
Nelson Freeman
614-601-5147
1-Jun-1922
M
1
2
Mercy Mader
614-624-6628
12-Jan-1918
F
1
3
William Mack
614-949-2522
12-Aug-1923
M
2
4
Mary Burbank
614-512-9725
15-Feb-1922
F
2
5
Enoch Whitman
216-848-9837
16-Dec-1918
M
3
6
Rebecca Gardner
25-May-1923
F
3
7
Samuel Hunt
513-395-7045
26-Aug-1922
M
4
8
Lucerna Harlow
513-786-2650
3-Sep-1918
F
4
9
David Freeman
513-545-8812
28-Nov-1948
M
5
10
Desiah Mack
513-802-5776
14-Oct-1947
F
5
11
Nathanial Whitman
216-596-6123
17-Jun-1943
M
3
12
Jane Hunt
216-596-6321
29-Jan-1948
F
3
13
Joseph Freeman
312-893-2254
27-Dec-1972
M
6
14
Abigale Whitman
773-254-8919
3-Feb-1968
F
6
15
Samuel Freeman
15-Mar-1998
M
6
16
Jessica Freeman
614-719-9213
22-Jul-1952
F
1
17
Alex Whitman
216-596-6240
23-Jan-1948
M
3
PARENT
Mother_ID
Father_ID
Child_ID
1
2
9
1
2
16
3
4
10
5
6
11
5
6
17
7
8
12
9
10
13
11
12
14
13
14
15
PET
Pet_ID
PName
Type
Year_born
House_In
1
Murfee
Dog
2008
1
2
Rufus
Dog
2015
1
3
Whiskers
Cat
2010
2
4
Jingles
Potbelly Pig
2014
3
5
Tweet
Canary
2013
5
HOUSEHOLD
House_ID
Street_Address
City
State
Zip
1
321 High St.
Columbus
OH
43050
2
123 2nd Ave.
Columbus
OH
43052
3
444 Main Dr.
Cleveland
OH
44101
4
999 Freeland St.
Cincinnati
OH
45220
5
265 Iota Ave.
Cincinnati
OH
45220
6
102 Michigan Ave.
Chicago
IL
60632
Member_ID
Name
Cell
Birthday
Gender
Lives_In
1
Nelson Freeman
614-601-5147
1-Jun-1922
M
1
2
Mercy Mader
614-624-6628
12-Jan-1918
F
1
3
William Mack
614-949-2522
12-Aug-1923
M
2
4
Mary Burbank
614-512-9725
15-Feb-1922
F
2
5
Enoch Whitman
216-848-9837
16-Dec-1918
M
3
6
Rebecca Gardner
25-May-1923
F
3
7
Samuel Hunt
513-395-7045
26-Aug-1922
M
4
8
Lucerna Harlow
513-786-2650
3-Sep-1918
F
4
9
David Freeman
513-545-8812
28-Nov-1948
M
5
10
Desiah Mack
513-802-5776
14-Oct-1947
F
5
11
Nathanial Whitman
216-596-6123
17-Jun-1943
M
3
12
Jane Hunt
216-596-6321
29-Jan-1948
F
3
13
Joseph Freeman
312-893-2254
27-Dec-1972
M
6
14
Abigale Whitman
773-254-8919
3-Feb-1968
F
6
15
Samuel Freeman
15-Mar-1998
M
6
16
Jessica Freeman
614-719-9213
22-Jul-1952
F
1
17
Alex Whitman
216-596-6240
23-Jan-1948
M
3
Explanation / Answer
SELECT M.NAME from MEMBER M,HOUSEHOLD H where M.LIVES_IN=H.HOUSEID and Street_Address= '123 2nd Ave.' UNION SELECT P.PNAME from PET P,HOUSEHOLD H WHERE P.House_In=H.HOUSEID and Street_Address= '123 2nd Ave.';
_
select name from MEMBER where MEMBER_ID not in (select NAME from MEMBER,PET where MEMBER.Lives_In=PET.House_In);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.