xX68dgsV

11. Write out a generic SELECT statement.
2
3SELECT title
4    FROM books
5    WHERE id=63546;
6
7
82. Create a fun way to remember the order of operations in a SELECT statement, such as a mnemonic.
9
10SELECT - Sweaty
11FROM - Feet
12WHERE - Will
13GROUP - Give
14HAVING - Horrible
15ORDER BY - Odours
16
173. Given this dogs table, write queries to select the following pieces of data:
18
19-Display the name, gender, and age of all dogs that are part Labrador.
20SELECT name, gender, age
21FROM dogs
22WHERE  breed LIKE 'labrador%';
23
24-Display the ids of all dogs that are under 1 year old.
25SELECT id
26FROM dogs
27WHERE  age<1;
28
29-Display the name and age of all dogs that are female and over 35lbs.
30SELECT name, age
31FROM dogs
32WHERE  gender='F' AND weight>35;
33
34-Display all of the information about all dogs that are not Shepherd mixes.
35SELECT *
36FROM dogs
37WHERE  breed NOT LIKE '%shepherd%';
38
39-Display the id, age, weight, and breed of all dogs that are either over 60lbs or Great Danes.
40SELECT id, age, weight, breed
41FROM dogs
42WHERE  weight>65 OR breed LIKE '%great dane%';
43
44
454. Given this cats table, what records are returned from these queries?
46
47SELECT name, adoption_date FROM cats;
48Name and adoption columns for all cats table
49| name     | adoption_date   |
50| -------- | ------------------------ |
51| Mushi    | 2016-03-22T00:00:00.000Z |
52| Seashell |   null   |
53| Azul     | 2016-04-17T00:00:00.000Z |
54| Victoire | 2016-09-01T00:00:00.000Z |
55| Nala     |    null  |
56
57SELECT name, age FROM cats;
58Name and age columns for all cats table
59| name     | age |
60| -------- | --- |
61| Mushi    | 1   |
62| Seashell | 7   |
63| Azul     | 3   |
64| Victoire | 7   |
65| Nala     | 1   |
66
675. From the cats table, write queries to select the following pieces of data.
68
69Display all the information about all of the available cats.
70SELECT DISTINCT name
71FROM cats
72WHERE adoption_date IS NULL;
73
74Display the name and sex of all cats who are 7 years old.
75SELECT name,gender
76FROM cats
77WHERE age = 7;
78
79Find all of the names of the cats, so you don’t choose duplicate names for new cats.
80SELECT DISTINCT name
81FROM cats;
82
83
846. List each comparison operator and explain when you would use it. Include a real world example for each.
85
86= (equal) - compares values  ex. WHERE price = 22
87
88> (greater than), compares values ex. WHERE weight > 100
89
90<  (lower than) , compares values ex. WHERE  age < 18
91
92>= (greater or equal than) ,  compares values ex. WHERE  number >= 2200
93
94<= (lower or equal than) , compares values ex. WHERE  number <= 2200
95
96!= (not equal to), compares values ex. WHERE price != 22
97
98<> (greater than or lower than) compares values ex. WHERE weight < 140 OR >130
99
100BETWEEN   returns true if the statement is within a range.  ex. WHERE number  BETWEEN 20 AND 25
101
102OR  one of the expressions need to be true to be returned. ex. WHERE fav_food = ‘Lasagna’ or fav_drink= ‘sangria’
103
104NOT only returns row if the expression is false. ex. WHERE NOT num >50
105
106AND return row(s) if all the conditions separated by the AND are true. ex. WHERE  age=50 AND weight > 100
107
108IS NULL checks if cell is null or not.  ex. WHERE adoption_date IS NULL
109
110EXISTS   evaluates to true and return something. Ex.  WHERE EXISTS(SELECT Age FROM People WHERE Work_Experience >20)         
111
112LIKE  search for a specified pattern. ex. WHERE  breed LIKE '%great dane%'
113
114IS DISTINCT FROM return  only distinct values without duplicates. ex. SELECT DISTINCT name FROM cats;  
115
1167. From the cats table, what data is returned from these queries?
117
118SELECT name FROM cats WHERE gender = ‘F’;
119Selects all names for female cats
120| name     |
121| -------- |
122| Seashell |
123| Nala     |
124
125
126SELECT name FROM cats WHERE age <> 3;
127Selects name of all cats greater or less than 3 years old
128| name     |
129| -------- |
130| Mushi    |
131| Seashell |
132| Victoire |
133| Nala     |
134
135SELECT ID FROM cats WHERE name != ‘Mushi’ AND gender = ‘M’;
136Selects entire ID column for all male cats with names other than Mushi
137| id  |
138| --- |
139| 3   |
140| 4   |