17198989
Description
| Question | Answer |
| UPDATE (definition) | Modify existing records in a table |
| UPDATE the first customer (CustomerID = 1) with a new contact person and a new city. | UPDATE CUSTOMERS SET ContactName = 'Janine', City= 'New York' WHERE CustomerID = 1; |
| IF() (definition) | Like a ternary: IF(condition, true result, false result) |
| Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update statement and no intermediate temp table. | UPDATE salary SET sex = IF(sex = 'm', 'f', 'm'); |
| output movies with an odd numbered ID and a description that is not 'boring'. Order the result by rating. | SELECT * from cinema WHERE MOD(id, 2) = 1 AND NOT description = 'boring' ORDER BY rating DESC; |
| GROUP BY | Often used with the COUNT() function; forms subgroups from rows based on columns data |
| SELECT email FROM Person GROUP BY email HAVING Count(email) > 1; | |
| HAVING clause | The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. |
| List the number of customers in each country, ordered by the country with the most customers first. | SELECT Count(CustomerID), Country, FROM Customers GROUP BY Country ORDER BY Count(CustomerID) DESC; |
| LEFT (outer) JOIN | Good for when you want to include all the output of the first (left) table regardless of whether it has a foreign key in the second table |
| SELECT FirstName, LastName, City, State FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId; |
0 comments
Want to create your own Flashcards for free with GoConqr? Learn more.