Flashcards
SQL flash cards
Cards in this deck (11)
- 1UPDATE (definition)Modify existing records in a table
- 2UPDATE 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;
- 3IF() (definition)Like a ternary: IF(condition, true result, false result)
- 4Given 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');
- 5output 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;
- 6GROUP BYOften used with the COUNT() function; forms subgroups from rows based on columns data
- 7—SELECT email FROM Person GROUP BY email HAVING Count(email) > 1;
- 8HAVING clauseThe HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
- 9List 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;
- 10LEFT (outer) JOINGood 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
- 11—SELECT FirstName, LastName, City, State
FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId;
Information
- Description
- computer science Flashcards on SQL flash cards, created by Janine Love on 09/03/2019.
- Created by
- Janine Love · Mar 2019
- Updated
- Mar 9, 2019
- Views
- 8
- Rating