SQL Flashcards
T/F: In SQL, if we want to impose a condition at the individual record level, we use the WHERE command. If we want to impose a condition at the group level, we use the HAVING command.
True
FROM
specific table we want to extract from
AS
renames fields
SELECT
specifies what we want to view
WHERE
filters query results (ex: WHERE (SuggestedRetailPrice-StandardCost) > 10 = only output records that have values greater than 10)
DISTINCT
removes duplicate records
ORDER BY
orders data in ascending order by default (to order in descending order - include DESC = ORDER BY (SuggestedRetailPrice-Cost) DESC)
- SQL executes ORDER BY command before SELECT
SELECT * FROM table_name
retrieves all fields from the specified table
IN
allows you to specify multiple values in a WHERE clause
ex: SELECT * FROM tblCustomers WHERE State IN (‘MA’, ‘NY’)
OR
used with WHERE to include rows where either condition is true
ex: SELECT * FROM tblCustomers WHERE State (‘MA’ OR ‘NY’)
GROUP BY
creates aggregate measure based on every record in the table (must use for aggregate functions AVG, SUM)
HAVING
filters data after aggregation has occurred (must use GROUP BY for this to work)
* processed after WHERE, GROUP BY and aggregation functions
Inner Join
only joins data that have a match in both tables
Left Join
brings all left table data + matching data from right table (excludes non-matching right table data)
Right join
brings all right table data + matching data from left table (excludes non-matching left table data)
