1
How to implement the following SQL query in MySQL
(Select s. Name
From student s, TRANSCRIPT T
Where s. StudId = T. StudId and t. CrsCode = 'cs305 ')
INTERSECT
(Select s. Name
From student s, TRANSCRIPT T
Where s. StudId = T. StudId and t. CrsCode = 'cs315 ')
Thank you ~
Solution:
Intersection
Select a. * from
(
Select s. Name
From student s, TRANSCRIPT T
Where s. StudId = T. StudId and t. CrsCode = 'cs305'
) As
Cross join
(
Select s. Name
From student s, TRANSCRIPT T
Where s. StudId = T. StudId and t. CrsCode = 'cs315'
) As B on a. Name = B. Name;
2.
SELECT * FROM (
Select distinct col1 FROM t1 WHERE...
UNION ALL
Select distinct col1 FROM t1 WHERE...
) AS tbl
Group by tbl. col1 having count (*) = 2
3.
Intersection:
SELECT * FROM table1 AS a JOIN table2 AS B ON a. name = B. name
Example:
Table:
FieldA
001
002
003
Table B:
FieldA
001
002
003
004
Ask how to obtain the following result set, that is, the intersection of Rows A and B.
FieldA
001
002
003
Answer: select a. FieldA from a inner join B on a. FieldA = B. FieldA
Difference set:
Not in indicates the difference set
SELECT * FROM table1 WHERE name not in (SELECT name FROM table2)