Common Commands

Question 1

HAVING

where does it fall in relation to a GROUP BY clause?

Answer 1

used to filter groups with aggregate functions; used after GROUP BY clause

Question 2

WHERE

where does it fall in relation to a GROUP BY clause?

Answer 2

filter rows on a condition, applying the filter to every row; used before a GROUP BY

Question 3

CROSS JOIN

Answer 3

cartesian product of rows between two or more tables; there is no matching condition in the join clause; all columns from T1 and T2 will be present including a cartesian product of rows

Question 4

FETCH

Answer 4

SQL-standard for the LIMIT clause

Question 5

LIMIT

Answer 5

limits the number of rows returned

Question 6

INTERSECT

definition
(list the conditions required to perform the query)

Answer 6

combines the results of two or more SELECT clauses;

only shows results that match both SELECT statements;

must have the same number of columns and the columns need to be of the same data type;

SELECT column1, column2
FROM table1
INTERSECT
SELECT column1, column2
FROM table2

Question 7

EXCEPT

definition
(list the conditions required to perform the query)

Answer 7

returns rows in query1 that do not appear in query2;

must have the same number of columns and the columns need to be of the same data type;

SELECT column1, column2
FROM table1
EXCEPT
SELECT column1, column2
FROM table2

Question 8

UNION

definition
(list the conditions required to perform the query)

Answer 8

combines result sets of multiple queries;

must have the same columns

SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2

Question 9

UNION ALL

what are the conditions of to be able to use a UNION ALL?

Answer 9

UNION query except it does not remove duplicate rows; must have the same columns

Question 10

ROLLUP

(definition & when is it often used)

(where is it found in relation to the GROUP BY clause?)

Answer 10

shorthand for defining multiple grouping sets; often used to create subtotals and grand totals for reports

postgresql syntax:
GROUP BY
ROLLUP (column1, column2)

MySQL syntax:
GROUP BY column1, column2 WITH ROLLUP

Question 11

EXTRACT

Answer 11

extracts a portion from a date attribute;
example syntax:
EXTRACT (YEAR FROM rental_date)

Question 12

CASE

Answer 12

IF/ELSE statement for SQL

CASE
WHEN condition1 THEN result_1
ELSE
END

Question 13

MINUS

definition
(list the conditions required to perform the query)

Answer 13

MySQL & ORACLE EXCEPT clause;

returns rows in query1 that do not appear in query2;

must have the same number of columns and the columns need to be of the same data type;

SELECT column1, column2
FROM table1
MINUS
SELECT column1, column2
FROM table2

Question 14

COMMIT

Answer 14

SQL transaction command used to save changes invoked by a transaction

Question 15

ROLLBACK

Answer 15

SQL transaction command used to undo transactions that have not been saved to the database

Question 16

SAVEPOINT

Answer 16

SQL transaction command that creates a point to rollback transactions without rolling back the entire transaction

Question 17

SET/BEGIN

Answer 17

SQL transaction command that initiates a transaction

Question 18

COALESCE

equivalents in ORACLE & MySQL

Answer 18

returns the first non-null argument from an unlimited number of arguments; coverts a NULL value into a user defined value

equivalent to NVL (ORACLE) or IFNULL (MySQL) - verified this was correct

COALESCE (argument_1, argument_2, …);

Question 19

CAST

Answer 19

convert one data type into another

Question 20

EXIST

Answer 20

test for the existence of rows in a subquery; the results of the EXISTS depends on whether any row is returned and not the content of the row

often used in correlated subqueries

example: customers who’ve paid greater than $11 dollars to rent a dvd

SELECT first_name,
       last_name
FROM customer c
WHERE EXISTS
    (SELECT 1
     FROM payment p
     WHERE p.customer_id = c.customer_id
     AND amount > 11 )
ORDER BY first_name,
         last_name;

Question 21

OVER(PARTITION BY …)

Answer 21

invokes a window function, which allows for aggregations over distinct rows in a SELECT statement unlike the GROUP BY function with requires aggregation over all selected rows

SELECT column1, column2,
COUNT(*) OVER(PARTITION BY column2) as cnt
FROM emp
ORDER BY 2

Question 22

COUNT(*)

definition
(when is it used?)

Answer 22

counts the rows instead of the column value values in a SELECT statement; helpful when trying to count the number of rows with a NULL value

Question 23

IFNULL

what are it’s equivalent’s?

Answer 23

Return a specified value if the expression is NULL, otherwise returns the expression.

example: SELECT IFNULL(“Hello”, “W3Schools.com”) would return Hello, since it is not NULL.

Similar to NVL (ORACLE) and COALESCE.