FilesExpand file tree

 master

/

6_Set_Operators_Exercises.sql

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

66 lines (61 loc) · 1.31 KB

 master

/

6_Set_Operators_Exercises.sql

Copy path

Top

File metadata and controls

• Code
• Blame

66 lines (61 loc) · 1.31 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

-- T-SQL Fundamentals

-- Chapter 6 Set Operators Exercises

USE TSQLV4;

-- 1

SELECT 1 AS n

UNION ALL SELECT 2

UNION ALL SELECT 3

UNION ALL SELECT 4

UNION ALL SELECT 5

UNION ALL SELECT 6

UNION ALL SELECT 7

UNION ALL SELECT 8

UNION ALL SELECT 9

UNION ALL SELECT 10;

-- 2 Has order in January but not in Feburary.

SELECT custid, empid

FROM Sales.Orders

WHERE orderdate >= '20160101'

AND orderdate < '20160201'

EXCEPT

SELECT custid, empid

FROM Sales.Orders

WHERE orderdate >= '20160201'

AND orderdate < '20160301'

-- 3 Has order in both January and Feburary

SELECT custid, empid

FROM Sales.Orders

WHERE orderdate >= '20160101'

AND orderdate < '20160201'

INTERSECT

SELECT custid, empid

FROM Sales.Orders

WHERE orderdate >= '20160201'

AND orderdate < '20160301';

-- 4

SELECT custid, empid

FROM Sales.Orders

WHERE orderdate >= '20160101'

AND orderdate < '20160201'

INTERSECT

SELECT custid, empid

FROM Sales.Orders

WHERE orderdate >= '20160201'

AND orderdate < '20160301'

EXCEPT

SELECT custid, empid

FROM Sales.Orders

WHERE orderdate >= '20150101'

AND orderdate < '20160101';

-- 5

WITH C AS

(

SELECT 1 AS sortcol, country, region, city

FROM HR.Employees

UNION ALL

SELECT 2 AS sortcol, country, region, city

FROM Production.Suppliers

)

SELECT country, region, city

FROM C

ORDER BY sortcol, country, region, city;