· 7 years ago · Oct 13, 2018, 10:26 AM
1> Write out a generic SELECT statement.
2A:
3 SELECT *
4 FROM table
5 WHERE id > 1;
6
7> Create a fun way to remember the order of operations in a SELECT statement, such as a mnemonic.
8A: SELECT: Sally
9 FROM: Flew
10 WHERE: West
11
12> Given this dogs table, write queries to select the following pieces of data:
13A:
14 1. Display the name, gender, and age of all dogs that are part Labrador.
15
16 SELECT name, gender, age
17 FROM dogs
18 WHERE breed LIKE '%labrador%';
19
20 2. Display the ids of all dogs that are under 1 year old.
21
22 SELECT id
23 FROM dogs
24 WHERE age < 1;
25
26 3. Display the name and age of all dogs that are female and over 35lbs.
27
28 SELECT name, age
29 FROM dogs
30 WHERE gender = 'F'
31 AND weight > 35;
32
33 4. Display all of the information about all dogs that are not Shepherd mixes.
34
35 SELECT *
36 FROM dogs
37 WHERE breed NOT LIKE '%shepherd%';
38
39 5. Display the id, age, weight, and breed of all dogs that are either over 60lbs or Great Danes.
40
41 SELECT id, age, weight, breed
42 FROM dogs
43 WHERE weight > 60
44 OR breed = 'great dane';
45
46> Given this cats table, what records are returned from these queries?
47A:
48 1. SELECT name, adoption_date FROM cats;
49
50 | name | adoption_date |
51 | -------- | ------------------------ |
52 | Mushi | 2016-03-22T00:00:00.000Z |
53 | Seashell | null |
54 | Azul | 2016-04-17T00:00:00.000Z |
55 | Victoire | 2016-09-01T00:00:00.000Z |
56 | Nala | null |
57
58 2. SELECT name, age FROM cats;
59
60 | name | age |
61 | -------- | --- |
62 | Mushi | 1 |
63 | Seashell | 7 |
64 | Azul | 3 |
65 | Victoire | 7 |
66 | Nala | 1 |
67
68> From the cats table, write queries to select the following pieces of data.
69A:
70 1. Display all the information about all of the available cats.
71
72 SELECT *
73 FROM cats;
74
75 2. Display the name and sex of all cats who are 7 years old.
76
77 SELECT name, gender
78 FROM cats
79 WHERE age = 7;
80
81 3. Find all of the names of the cats, so you don’t choose duplicate names for new cats.
82
83 SELECT name
84 FROM cats;
85
86> List each comparison operator and explain when you would use it. Include a real world example for each.
87A:
88 1. > : Greater than symbol is used to find values greater than the value compared to. I would use this to find cats older than 3 years old in cats table.
89 2. < : Less than symbol is to used to find values less than the value compared to. I would use this to find cats younger than 3 years old in cats table.
90 3. = : Equal to symbol to compare if two values are equal to each other. I would use to find a cat whose age is equal to 4 years old.
91 4. <= : Less than or equal to symbol to compare all values less than or equal to the value compared to. I would use this to find cats that are younger than or are 3 years old.
92 5. >= : Greater than or equal to symbol to compare all values greater than or equal to the value compared to. I would use this to find cats that are older than or are 3 years old.
93 6. != : Not equal to symbol to check if two values are not equal to each other. I would use this to get data on all cats who are not 3 years old.
94 7. <> : This is equivalent to the not equal to symbol. I would rarely use this. I would use this to get names of all cats who are not adopted yet.
95 8. LIKE: This is used to compare parts of a string to the value being match. I would use this to find all dogs who are mixed breed and contain the string 'labrador.'
96 9. BETWEEN: This is used to find a value between two certain values. I would use this find to find dogs whose birth date is between 2015 and 2018.
97 10. IS DISTINCT FROM: This is similar to <> where one of the input could be null. It used to compare two values. I would use this to find a specific dog of the same breed who has already been adopted.
98 11. IS TRUE/IS FALSE: Used when input could be null. It is used to determine whether given input is true or false. I would use this to find if a specific dog has been adopted or not.
99 12. IS NULL: This is used to find out if a given value exists in the table. I would use this find if a dog's name already exists so that name won't be used twice.
100 13. AND: This is used when two inputs both need to be true for data to be returned. I would use this find dogs who are older than 6 years old and over 40lbs.
101 14. OR: This is used when either of the inputs can be true. I would use this find dogs who are older than 6 years old or over 40 lbs.
102 15. NOT: This is used when the input needs to be false for data to be returned. I would use this to find dogs who are not older than 6 years.
103
104> From the cats table, what data is returned from these queries?
105A:
106 1. SELECT name FROM cats WHERE gender = ‘F’;
107
108 | name |
109 | -------- |
110 | Seashell |
111 | Nala |
112
113 2. SELECT name FROM cats WHERE age <> 3;
114
115 | name |
116 | -------- |
117 | Mushi |
118 | Seashell |
119 | Victoire |
120 | Nala |
121
122 3. SELECT ID FROM cats WHERE name != ‘Mushi’ AND gender = ‘M’;
123
124 | id |
125 | --- |
126 | 3 |
127 | 4 |