SQL syntax for the following Select statements based on the below: --Select the
ID: 3820237 • 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.
--Select everyone who lives in a house without any pet.
--Select the names and cell phone numbers of everyone who lives with the pet, dog.
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
1) SELECT M.NAME,P.PNAME FROM MEMBER M, PET P , HOUSEHOLD H WHERE P.LIVES_IN = P.HOUSE_IN AND H.STREET_ADDRESS = '123 2ND AVE';
2) SELECT * FROM MEMBER WHERE NOT LIVES_IN IN(1,2,3,5);
3) SELECT M.NAME, M.CELL FROM MEMBER M , PET P WHERE M.LIVES_IN = P.HOUSE_IN AND P.PNAME ='DOG';
THANKS...
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.