SQL Flashcards
Benefits of IN
IN executes faster than OR
Can contain another SELECT
NOT
WHERE NOT city = ‘London’ AND NOT city = ‘Dallas’
OR
WHERE name = ‘ali’ OR ‘mamad’
IN
WHERE id IN (9,10,11)
Wildcards
%
‘%Pizza’
‘Pizza%’
‘%Pizza%’
‘S%E’
WHERE size LIKE ‘%pizza’
ORDER BY position
Must always be the last clause in a select statement
Aggregate Functions
AVG() COUNT() MIN() MAX() SUM()
Count all the rows in a table containing values and NULL values
SELECT COUNT (*) AS total_cust FROM customers;
Count all the rows in a specific column ignoring NULL vales
SELECT COUNT (customerID) AS total_cust FROM customers;
DISTINCT on aggregate functions
SELECT COUNT(DISTINCT customerID) FROM customers
Every column in your SELECT statement must be present in a GROUP BY clause, except for
aggregated calculations
WHERE filters on rows, instead use … to filter for groups
HAVING
WHERE vs HAVING
WHERE filters before data is grouped
HAVING filters after data is grouped
Rows eliminated by the WHERE clause will not be included in the group
HAVING
SELECT customerID, COUNT() AS orders
FROM orders
GROUP BY customerID
HAVING COUNT ()>=2;
WHERE clause operators
= <> != >= > <= < BETWEEN AND IS NULL
Subquery selects can only retrieve a single column
.
Cartesian (Cross) join
Each row from the first table joins with all the rows of another table.
Output will be the number of joins in the 1st table multiplied by the number of rows in the 2ed table
Ex.
SELECT *
FROM table1 CROSS JOIN table2
inner joins
SELECT suppliers.CompanyName, ProductName, UnitPrice
FROM Suppliers INNER JOIN Products
ON Suppliers.supplierid = Products.supplierid
Inner join in multiple tables?
Yes, no limit
Inner join with multiple tables example
SELECT o.order_id, c.company_name ,e.last_name
FROM ((Orders o INNER JOIN Customers c ON o.customer_id = c.customer_id)
INNER JOIN Employees e ON o.employee_id = e.employee_id);
Aliases
/ˈālēəs/
SELECT vendor_name ,product_name, product_price
FROM Vendors AS v, Products as p
WHERE v.vendor_id = p.vendor_id;
Self Joins
SELECT
a.customer_name AS c_name1
,b.customer_name AS c_name2
a.city
FROM Customers AS a, Customers as b
WHERE a.customer_id = b.customer_id
AND a.city = b.city
ORDER BY a.city;
FULL OUTER JOIN
SELECT C.customer_name , O.order_id
FROM Customers AS C
FULL OUTER JOIN Orders AS O
ON C.customer_id = O.customer_id
ORDER BY C.customer_name;
Unions
A UNION is used to combine the result-set of multiple SELECT statements.
To create a union:
each SELECT statement must have the same number of columns
the columns in each SELECT statement must be in the same order
the matching columns must have similar data types
Union example
SELECT city, country
FROM Customers
WHERE country = ‘Germany’
UNION
SELECT city, country
FROM Suppliers
WHERE country = ‘Germany’
ORDER BY city
Concatenation
SELECT
company_name
,contact_name
,compaany_name || ‘ (‘ || contact_name || ‘)’
FROM Customers
Trimming String
TRIM
RTRIM
LTRIM
SELECT TRIM(“ You the best “) AS trimmed_string;
Substring
– SUBSTR([string name], [start pos], [num return chars])
SELECT first_name, SUBSTR(first_name, 2, 3)
FROM Employees
WHERE department_id = 69;
Upper and Lower
-- make string uppercase SELECT UPPER(first_name) FROM Customers -- or SELECT UCASE(first_name) FROM Customers
-- make string lowercase SELECT LOWER(first_name) FROM Customers
STRFTIME
– get the year, month, and day of each employee
SELECT
birthdate
, STRFTIME('%Y', birthdate) AS year
, STRFTIME('%m', birthdate) AS month
, STRFTIME('%d', birthdate) AS day
FROM employees
Compute current date
SELECT DATE(‘now’)
Compute dates and times for the current date
-- date
SELECT STRFTIME('%Y %m %d', 'now')
-- time
SELECT STRFTIME('%H %M %S %s', 'now')
-- compute employees' ages
SELECT
birthdate
,STRFTIME('%Y', birthdate) AS year
,STRFTIME('%m', birthdate) AS month
,STRFTIME('%d', birthdate) AS day
,DATE(('now') - birthdate) AS age
FROM employees
Case Statements
Case statements in SQL mimic if-else statements in general programming.
Case statements can be used in any clause that accepts a valid expression, e.g.
WHERE
ORDER BY
HAVING
and in statements:
SELECT
INSERT
UPDATE
DELETE
Case structure
CASE [input_expression] WHEN c1 THEN e1 WHEN c2 THEN e2 ELSE [e3] END [column_name]
Case example
SELECT
employee_id
,firstname
,lastname
,CASE city
WHEN ‘Calgary’ THEN ‘Calgary’
ELSE ‘Other’
END calgary
FROM Employees
ORDER BY lastname, firstname;
If ELSE is omitted and no WHEN statements evaluate to true,
NULL will be returned.
As with programming languages, CASE in SQL will short-circuit on a true statement, and no further statements will be evaluated.
Search case statement
By omitting the optional input expression that directly follows the CASE keyword one can use the WHEN statements to evaluate and compare values:
SELECT
track_id
,name
,bytes
,CASE
WHEN bytes < 30000 THEN 'small'
WHEN bytes >= 30001 AND bytes <= 50000 THEN 'medium'
WHEN bytes > 50000 THEN 'large'
ELSE 'Other'
END bytes_category
FROM Tracks;
Views
With a view, one can add or remove columns without changing the schema.
– create a view called my_view where…
CREATE VIEW my_view AS
SELECT
r.region_description
,t.territory_description
,e.lastname
,e.firstname
,e.hire_date
,e.reports_to
FROM Regions r
INNER JOIN Territories t ON r.region_id = t.region_id
INNER JOIN EmployeeTerritories et ON t.territory_id = et.territory_id
INNER_JOIN Employees e on et.employee_id = e.employee_id
To actually use the view, one treats it as if it were an existing table:
SELECT COUNT(territory_description) ,firstname ,lastname FROM my_view GROUP BY lastname, firstname; --------------------------------------------------------------------------- If a view is no longer required, one should clean up by DROPping the view.
DROP VIEW my_view;
Views can be useful in environments where you’re unable to write data to tables.
