Consider the content of the class and student tables:
Class
Which three queries produce the same result?
A.
SELECT *
FROM class
INNER JOIN student
ON class.class_id=student.class_id
B.
SELECT *
FROM JOIN student
LEFT JOIN student
ON class. Class.class_id=student.class_id
C.
SELECT *
FROM class
INNER JOIN student
WHERE NOT ISNULL (student.class_id)
D.
SELECT *
FROM JOIN student
On class .class_id=student.class_id
WHERE NOT ISNULL (student.class_id)
E.
SELECT *
FROM student
RIGHT JOIN class
ON class.class_id=student.class_id
ABD
The question/answer is all sorts of jacked up. I tried my best to interpret it but no luck. Well if the statement wants us to get all the info and be accurate then a left and right join will accomplish this.
..so B,E have to be correct.
for the third I do not know but an Inner join would not bring up the HISTORY info so I can assume its not A. So it has to be either C or D but I don’t think they are written correctly.
create table class
(
class_id int,
topic varchar(10)
);
insert into class values (1,’Math’),(2,’Chemistry’), (3,’Music’), (4,’History’);
create table student
(
student_id int,
class_id int,
name char(10)
);
insert into student values (1,1,’Gillian’),(2,1,’Carsten’), (3,2,’Max’), (4,3,’shawn’),(5,3,’Lachlan’);
SELECT * #A Does not show HISTORY#
FROM class
INNER JOIN student
ON class.class_id=student.class_id;
SELECT * #B shows all info#
FROM class
LEFT JOIN student
ON Class.class_id=student.class_id;
SELECT * #C#
FROM class
INNER JOIN student
on Class.class_id=student.class_id #I think they wanted this here, if it wasn’t here it would produce all the info for the table but it wouldn’t be accurate (but hey maybe the question is just asking to get all the info no matter the price, so this might be correct if I left off the on statement#
WHERE student.class_id IS NOT NULL;
SELECT * #D#
FROM class
join student
On class.class_id=student.class_id
WHERE student.class_id IS NOT NULL;
SELECT * #E Shows all info#
FROM student
RIGHT JOIN class
ON class.class_id=student.class_id;
A SELECT * FROM class INNER JOIN student ON class.class_id=student.class_id
+———-+———–+————+———-+———+
| class_id | topic | student_id | class_id | topic |
+———-+———–+————+———-+———+
| 1 | math | 1 | 1 | Gillian |
| 1 | math | 2 | 1 | Caraten |
| 2 | chemistry | 3 | 2 | Max |
| 3 | music | 4 | 3 | Shawn |
| 3 | music | 5 | 3 | Lachlan |
+———-+———–+————+———-+———+
B SELECT * FROM JOIN student LEFT JOIN student ON class. Class.class_id=student.class_id
SELECT * FROM class LEFT JOIN student ON class.class_id=student.class_id;
+———-+———–+————+———-+———+
| class_id | topic | student_id | class_id | topic |
+———-+———–+————+———-+———+
| 1 | math | 1 | 1 | Gillian |
| 1 | math | 2 | 1 | Caraten |
| 2 | chemistry | 3 | 2 | Max |
| 3 | music | 4 | 3 | Shawn |
| 3 | music | 5 | 3 | Lachlan |
| 4 | history | NULL | NULL | NULL |
+———-+———–+————+———-+———+
C SELECT * FROM class INNER JOIN student WHERE NOT ISNULL (student.class_id)
+———-+———–+————+———-+———+
| class_id | topic | student_id | class_id | topic |
+———-+———–+————+———-+———+
| 1 | math | 1 | 1 | Gillian |
| 2 | chemistry | 1 | 1 | Gillian |
| 3 | music | 1 | 1 | Gillian |
| 4 | history | 1 | 1 | Gillian |
| 1 | math | 2 | 1 | Caraten |
| 2 | chemistry | 2 | 1 | Caraten |
| 3 | music | 2 | 1 | Caraten |
| 4 | history | 2 | 1 | Caraten |
| 1 | math | 3 | 2 | Max |
| 2 | chemistry | 3 | 2 | Max |
| 3 | music | 3 | 2 | Max |
| 4 | history | 3 | 2 | Max |
| 1 | math | 4 | 3 | Shawn |
| 2 | chemistry | 4 | 3 | Shawn |
| 3 | music | 4 | 3 | Shawn |
| 4 | history | 4 | 3 | Shawn |
| 1 | math | 5 | 3 | Lachlan |
| 2 | chemistry | 5 | 3 | Lachlan |
| 3 | music | 5 | 3 | Lachlan |
| 4 | history | 5 | 3 | Lachlan |
+———-+———–+————+———-+———+
D SELECT * FROM JOIN student On class .class_id=student.class_id WHERE NOT ISNULL (student.class_id)
SELECT * FROM student JOIN student On class.class_id=student.class_id WHERE NOT ISNULL (student.class_id)
+———-+———–+————+———-+———+
| class_id | topic | student_id | class_id | topic |
+———-+———–+————+———-+———+
| 1 | math | 1 | 1 | Gillian |
| 1 | math | 2 | 1 | Caraten |
| 2 | chemistry | 3 | 2 | Max |
| 3 | music | 4 | 3 | Shawn |
| 3 | music | 5 | 3 | Lachlan |
+———-+———–+————+———-+———+
E SELECT * FROM student RIGHT JOIN class ON class.class_id=student.class_id
+————+———-+———+———-+———–+
| student_id | class_id | topic | class_id | topic |
+————+———-+———+———-+———–+
| 1 | 1 | Gillian | 1 | math |
| 2 | 1 | Caraten | 1 | math |
| 3 | 2 | Max | 2 | chemistry |
| 4 | 3 | Shawn | 3 | music |
| 5 | 3 | Lachlan | 3 | music |
| NULL | NULL | NULL | 4 | history |
+————+———-+———+———-+———–+
SELECT * FROM class right JOIN student ON class.class_id=student.class_id;
+———-+———–+————+———-+———+
| class_id | topic | student_id | class_id | name |
+———-+———–+————+———-+———+
| 1 | Math | 1 | 1 | Gillian |
| 1 | Math | 2 | 1 | Carsten |
| 2 | Chemistry | 3 | 2 | Max |
| 3 | Music | 4 | 3 | shawn |
| 3 | Music | 5 | 3 | Lachlan |
+———-+———–+————+———-+———+
5 rows in set (0.00 sec)
A,C,E
A. SELECT * FROM class JOIN student
ON class.class_id=student.class_id
B. SELECT * FROM class INNER JOIN student ON class.class_id=student.class_id
C. SELECT * FROM class LEFT JOIN student ON class.class_id=student.class_id D SELECT *
FROM class JOIN student
ON class.class id=student.class id
387: C
D. SELECT * FROM class INNER JOIN student ON class.class_id=student.class_id WHE RE
NOT ISNULL(student.class_idJ
E. SELECT * FROM class LEFT JOIN student ON class.class_id=student.class_id WHERE
NOT ISNULL(student.class id)
A,D,E
SELECT * FROM class INNER JOIN student ON class.class_id=student.class_id;
SELECT * FROM class LEFT JOIN student ON class.class_id=student.class_id;
SELECT * FROM class INNER JOIN student ON class.class_id=student.class_id WHERE
NOT ISNULL(student.class_id);
SELECT * FROM class LEFT JOIN student ON class.class_id=student.class_id WHERE
NOT ISNULL(student.class_id);
A,C,E
A CE
A DE