SQL Flashcards
Name the types of SQL operation
CRUD
Joins
inner, outer (left/right, full), cross, self
Aggregate functions
count, sum,avg,max, min, group by
subqueries
CTEs (common table expressions)
Set Operations
Union, Union all, Intersect, Except (or minus)
Window Functions
what are CRUD statements?
The 4 most basic SQL operations
create/Insert
Read/select
Update
delete
What are the aggregate functions?
COUNT, SUM, AVG, MAX, MIN, GROUP BY
What are set operations?
Union, Union all, Intersect, Except (or minus)
What are windows functions?
In SQL, a window function or analytic function[1] is a function which uses values from one or multiple rows to return a value for each row. (This contrasts with an aggregate function, which returns a single value for multiple rows.)
What are subqueries?
A subquery is a SQL query nested inside a larger query
What are CTEs?
A common table expression, or CTE, (in SQL) is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement.
CTEs can be thought of as alternatives to derived tables (subquery), views, and inline user-defined functions.
what is an (INNER) join?
Returns records that have matching values in both tables
SELECT *
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
what is an LEFT (OUTER) JOIN
Returns all records from the left table, and the matched records from the right table
SELECT *
FROM left table
LEFT JOIN right table
ON left table common column
=
right table common column
;
what is a RIGHT (OUTER) JOIN
Returns all records from the right table, and the matched records from the left table
what is a FULL (OUTER) JOIN
Returns all records when there is a match in either left or right table
what are join operations syntax?
SELECT
FROM table
JOIN table
ON common col
SELECT *
FROM TABLE1
INNER, LEFT, RIGHT, FULL JOIN TABLE2
ON TAB1.col = TAB2.col
what is a CROSS join?
The CROSS JOIN keyword matches ALL records from the “left” table with EACH record from the “right” table.
That means that all records from the “right” table will be returned for each record in the “left” table.
SELECT testproduct_id, product_name, category_name
FROM testproducts
CROSS JOIN categories;
what is a SELF join?
A self join is a regular join, but the table is joined with itself.
There is NO SELF expression
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
what is the UNION operator?
The UNION operator is used to combine the result-set of two or more SELECT statements.
Every SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
