· 7 years ago · Oct 13, 2018, 01:22 AM
1#### EXERCISES
2
3
4 1. Write out a generic SELECT statement.
5
6 SELECT numbers
7 FROM table1
8 WHERE numbers > 4
9
10 2. Create a fun way to remember the order of operations in a SELECT statement, such as a mnemonic.
11
12 SELECT— SEARCH
13 FROM—FOR
14 WHERE—WHITE
15 GROUP BY—GNOME’S
16 HAVING—HATS
17 ORDER BY—ONBOARD
18
19
20 3. Given this dogs table, write queries to select the following pieces of data:
21
22Intake teams typically guess the breed of shelter dogs, so the breed column may have multiple words (for example, "Labrador Collie mix").
23
24 A)Display the name, gender, and age of all dogs that are part Labrador.
25
26 SELECT name,gender,age
27 FROM dogs
28 WHERE breed LIKE '%labrador%';
29
30 B)Display the ids of all dogs that are under 1 year old.
31
32 SELECT id
33 FROM dogs
34 WHERE age < 1;
35
36 C)Display the name and age of all dogs that are female and over 35lbs.
37
38 SELECT name,age
39 WHERE gender = ‘F’
40 AND weight > 35;
41
42
43 D)Display all of the information about all dogs that are not Shepherd mixes.
44
45 SELECT *
46 FROM dogs
47 WHERE breed NOT LIKE '%shepherd%';
48
49 E) Display the id, age, weight, and breed of all dogs that are either over 60lbs or Great Danes.
50
51 SELECT id, age, weight, breed
52 FROM dogs
53 WHERE weight > 60 OR breed='great dane';
54
55
56 4 . Given this cats table, what records are returned from these queries?
57
58 SELECT name, adoption_date FROM cats
59
60 SELECT name, adoption_date
61 From cats;
62
63 | name | adoption_date |
64 | -------- | ------------------------ |
65
66 | Mushi | 2016-03-22T00:00:00.000Z |
67 | Seashell | null |
68 | Azul | 2016-04-17T00:00:00.000Z |
69 | Victoire | 2016-09-01T00:00:00.000Z |
70 | Nala | null |
71
72
73 SELECT name, age FROM cats;
74
75 SELECT name, age
76 From cats;
77
78 | name | age |
79 | -------- | --- |
80 | Mushi | 1 |
81 | Seashell | 7 |
82 | Azul | 3 |
83 | Victoire | 7 |
84 | Nala | 1 |
85
86
87
88 5. From the cats table, write queries to select the following pieces of data.
89 A)Display all the information about all of the available cats.
90
91 SELECT *
92 FROM cats
93
94 B)Display the name and sex of all cats who are 7 years old.
95
96 SELECT name,gender
97 FROM cats
98 WHERE age = 7;
99
100 C)Find all of the names of the cats, so you don’t choose duplicate names for new cats.
101
102 SELECT DISTINCT name
103 FROM cats;
104
105 6. List each comparison operator and explain when you would use it. Include a real world example for each.
106 If you can’t list these from memory, do these flashcards until you can!
107
108 = equal , to compare values ex. WHERE make = ‘ford’
109
110 > greater than, to compare values ex. WHERE age > 50
111
112< lower than , to compare values ex. WHERE number < 50
113
114>= greater or equal than , to compare values ex. WHERE number >= 100
115
116<= lower or equal than , to compare values ex. WHERE shoes <= 20(pairs)
117
118!= not equal to, to compare values ex. WHERE height != 6
119
120<> greater than or lower than ex. WHERE group_age < 40 OR >30
121
122BETWEEN returns true if the statement is within a range. ex. WHERE tickets BETWEEN 20 AND 25
123
124OR one of the expressions need to be true to be returned. ex. WHERE fav_food = ‘Lasagna’ or fav_drink= ‘sangria’
125
126NOT only returns row if the expression is false. ex. WHERE NOT num >50
127
128AND return row(s) if all the conditions separated by the AND are true. ex. WHERE age=50 AND city= ‘toronto’
129
130IS NULL checks if cell is null or not. ex. WHERE column_1 IS NULL
131
132EXISTS evaluates to true if subquery return something. Ex. WHERE EXISTS(SELECT Age FROM People WHERE Work_Experience >20)
133
134LIKE search for a specified pattern. ex. WHERE column_2 LIKE ‘%b’
135
136IS DISTINCT FROM return only distinct values without duplicates. ex. SELECT DISTINCT Birth_country FROM Applicants
137
138
139 7 .From the cats table, what data is returned from these queries?
140 A)SELECT name FROM cats WHERE gender = ‘F’
141
142
143 SELECT name
144 FROM cats
145 WHERE gender='F';
146
147 | name |
148 | -------- |
149 | Seashell |
150 | Nala |
151
152
153 B)SELECT name FROM cats WHERE age <> 3;
154
155 SELECT name
156 FROM cats
157 WHERE age <>3;
158
159 | name |
160 | -------- |
161 | Mushi |
162 | Seashell |
163 | Victoire |
164 | Nala |
165
166
167
168 C)SELECT ID FROM cats WHERE name != ‘Mushi’ AND gender = ‘M’;
169
170 SELECT id
171 FROM cats
172 WHERE name <>'Mushi'
173 AND gender='M';
174
175 | id |
176 | --- |
177 | 3 |
178 | 4 |