forked from anthonydb/practical-sql
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathChapter_02.sql
More file actions
104 lines (74 loc) · 2.36 KB
/
Chapter_02.sql
File metadata and controls
104 lines (74 loc) · 2.36 KB
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
--------------------------------------------------------------
-- Practical SQL: A Beginner's Guide to Storytelling with Data
-- by Anthony DeBarros
-- Chapter 2 Code Examples
--------------------------------------------------------------
-- Listing 2-1: Querying all rows and columns from the teachers table
SELECT * FROM teachers;
-- Listing 2-2: Querying a subset of columns
SELECT last_name, first_name, salary FROM teachers;
-- Listing 2-3: Querying distinct values in the school column
SELECT DISTINCT school
FROM teachers;
-- Listing 2-4: Querying distinct pairs of values in the school and salary
-- columns
SELECT DISTINCT school, salary
FROM teachers;
-- Listing 2-5: Sorting a column with ORDER BY
SELECT first_name, last_name, salary
FROM teachers
ORDER BY salary DESC;
-- Listing 2-6: Sorting multiple columns with ORDER BY
SELECT last_name, school, hire_date
FROM teachers
ORDER BY school ASC, hire_date DESC;
-- Listing 2-7: Filtering rows using WHERE
SELECT last_name, school, hire_date
FROM teachers
WHERE school = 'Myers Middle School';
-- Examples of WHERE comparison operators
-- Teachers with first name of Janet
SELECT first_name, last_name, school
FROM teachers
WHERE first_name = 'Janet';
-- School names not equal to F.D. Roosevelt HS
SELECT school
FROM teachers
WHERE school != 'F.D. Roosevelt HS';
-- Teachers hired before Jan. 1, 2000
SELECT first_name, last_name, hire_date
FROM teachers
WHERE hire_date < '2000-01-01';
-- Teachers earning 43,500 or more
SELECT first_name, last_name, salary
FROM teachers
WHERE salary >= 43500;
-- Teachers who earn between $40,000 and $65,000
SELECT first_name, last_name, school, salary
FROM teachers
WHERE salary BETWEEN 40000 AND 65000;
-- Listing 2-8: Filtering with LIKE AND ILIKE
SELECT first_name
FROM teachers
WHERE first_name LIKE 'sam%';
SELECT first_name
FROM teachers
WHERE first_name ILIKE 'sam%';
-- Listing 2-9: Combining operators using AND and OR
SELECT *
FROM teachers
WHERE school = 'Myers Middle School'
AND salary < 40000;
SELECT *
FROM teachers
WHERE last_name = 'Cole'
OR last_name = 'Bush';
SELECT *
FROM teachers
WHERE school = 'F.D. Roosevelt HS'
AND (salary < 38000 OR salary > 40000);
-- Listing 2-10: A SELECT statement including WHERE and ORDER BY
SELECT first_name, last_name, school, hire_date, salary
FROM teachers
WHERE school LIKE '%Roos%'
ORDER BY hire_date DESC;