SQL Commands and Functions

Question 1

SELECT

Answer 1

The SELECT block specifies which columns you want to output. Its format is SELECT , , …. Each column must be separated by a comma, but the space following the comma is optional.

Question 2

SQL Comments

Answer 2

The contents between /* and */ are taken as comments and aren’t run as code.

– is another way of indicating comments. It is used to mark the rest of a single line as a comment. When we have multiple lines we want to comment out, /* … */ is more convenient than prepending – to each line.

Question 3

FROM

Answer 3

The FROM block specifies which table we’re querying from. Its format is FROM . It always comes after the SELECT block.

Question 4

WHERE

Answer 4

The WHERE block allows us to filter the table for rows that meet certain conditions. Its format is WHERE and it always goes after the FROM block.

Question 5

Quotes

Answer 5

In SQL, strings are denoted by single quotes. Backticks (ie `) can be used to denote column and table names. This is useful when the column or table name is the same as a SQL keyword and when they have a space in them.

Question 6

LIKE

Answer 6

It allows us to use wildcards such as % and _ to match various characters.

Question 7

% Wildcard

Answer 7

Allows us to find strings that contain a string. May be used at the beginning of a string, at the end, or both. Used with LIKE.

Question 8

_ Wildcard

Answer 8

Allows us to find strings that start or end with a string. ‘roy’ and ‘Tro’ both return Troy. Used with LIKE.

Question 9

COUNT()

Answer 9

COUNT is probably the most widely-used aggregate function. As the name suggests, it counts things! For instance, COUNT() returns the number of non-null rows in the column.

COUNT(*) counts rows as long as any one of their columns is non-null.

Question 10

NULL

Answer 10

In SQL, NULL is the value of an empty entry. This is different from the empty string ‘ ‘ and the integer 0, both of which are not considered NULL. To check if an entry is NULL, use IS and IS NOT instead of = and !=.

Question 11

LIMIT

Answer 11

Limits the number of results returned.

LIMIT

Question 12

CASE WHEN block

Answer 12

Acts as an if/else statement.

CASE
    WHEN  THEN 
    WHEN  THEN 
    ...
    ELSE 
END

Question 13

MIN()

Answer 13

The MIN() function returns the smallest value of the selected column.

MIN(column_name)

Question 14

MAX()

Answer 14

The MAX() function returns the largest value of the selected column.

MAX(column_name)

Question 15

AVG()

Answer 15

The AVG() function returns the average value of a numeric column.

AVG(column_name)

Question 16

SUM()

Answer 16

The SUM() function returns the total sum of a numeric column.

SUM(column_name)

Question 17

LENGTH()

Answer 17

For a string value str, the length(str) function returns the number of characters (not bytes) in str prior to the first NUL character.

LENGTH(string)

Question 18

SELECT DISTINCT

Answer 18

The SELECT DISTINCT statement is used to return only distinct (different) values.

SELECT DISTINCT column1, column2, …

Question 19

GROUP BY

Answer 19

It allows us to split up the dataset and apply aggregate functions within each group, resulting in one row per group. Its most basic form is GROUP BY , , … and comes after the WHERE block.

Question 20

AS

Answer 20

AKA Aliasing. In the SELECT block, AS provides an alias that can be referred to later in the query. This saves us from rewriting long expressions, and allows us to clarify the purpose of the expression.

Question 21

ROUND()

Answer 21

The ROUND() function rounds a number to a specified number of decimal places.

ROUND(number, decimals, operation)

Question 22

HAVING

Answer 22

Like WHERE but can be used on aggregate functions. You can think of it as a post-aggregation WHERE block. Modifies GROUP_BY.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Question 23

JOIN

Answer 23

The JOIN block takes the form of JOIN ON . The clause works the same way as in WHERE . That is, it is a statement that evaluates to true or false, and anytime a row from the first table and another from the second line up with the clause being true, the two are matched

By default this is an INNER JOIN which drops unmatched rows.

Question 24

LEFT JOIN

Answer 24

LEFT JOIN ON

Preserves unmatched rows in the left table.

Question 25

UNION

Answer 25

The UNION operator is used to combine the result-set of two or more SELECT statements and stack the columns without returning any duplicate rows. To use this UNION clause, each SELECT statement must have: - The same number of columns selected - The same number of column expressions - The same data type and - Have them in the same order ``` SELECT column_name(s) FROM table1 WHERE ... UNION SELECT column_name(s) FROM table2; WHERE ... ```

Question 26

UNION ALL

Answer 26

UNION statement that allows duplicate values Each SELECT statement must have: - The same number of columns selected - The same number of column expressions - The same data type and - Have them in the same order ``` SELECT column_name(s) FROM table1 WHERE ... UNION ALL SELECT column_name(s) FROM table2; WHERE ... ```

Question 27

INTERSECT

Answer 27

The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements. Each SELECT statement must have: - The same number of columns selected - The same number of column expressions - The same data type and - Have them in the same order SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] INTERSECT SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

Question 28

EXCEPT

Answer 28

The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement. Each SELECT statement must have: - The same number of columns selected - The same number of column expressions - The same data type and - Have them in the same order SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] EXCEPT SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

Question 29

SQL Order Of Operations and Readability

Answer 29

ORDER: SELECT, FROM, (AS), (JOIN), (ON), WHERE, (Set Op & second query) GROUP BY, HAVING, ORDER BY, LIMIT SELECT DISTINCT AS FROM AS JOIN AS ON (. = .) WHERE (Set operation and Second Query if necessary) GROUP BY HAVING ORDER BY LIMIT ;

Question 30

Set Operations

Answer 30

UNION, UNION ALL, EXCEPT, INTERSECT

Question 31

Subquery/Inner Query

Answer 31

Subqueries are queries within queries. A subquery can also be called an inner query with the "parent" query being called the outer query. There are two main ways to use a subquery: - In an IN condition - As a derived or temporary table A subquery in an IN condition must only have one column. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, , >=, <=, IN, BETWEEN, etc. Rules: - Subqueries must be enclosed within parentheses. - A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. - An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery. - Subqueries that return more than one row can only be used with multiple value operators such as the IN operator. - The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB. - A subquery cannot be immediately enclosed in a set function. - The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be used within the subquery.

Question 32

Subqueries with SELECT statements

Answer 32

``` SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name column_name IN (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE]) ``` ``` SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500) ; ```

Question 33

Subqueries with INSERT statements

Answer 33

INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ] INSERT INTO CUSTOMERS_BKP SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS) ;

Question 34

Subqueries with UPDATE statements

Answer 34

``` UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ] ``` ``` UPDATE CUSTOMERS SET SALARY = SALARY * 0.25 WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 ); ```

Question 35

Subqueries with DELETE statements

Answer 35

``` DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ] ``` ``` DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 ); ```

Question 36

Derived Tables

Answer 36

Temporary tables created as Subqueries nested inside FROM clauses. May be used for Joins but you must alias it. ``` SELECT FROM WHERE JOIN (SELECT FROM WHERE ) AS ON ; ``` ``` SELECT * FROM Sale as sale INNER JOIN (SELECT CarID FROM Car WHERE ModelYear = 2015) AS temp ON sale.CarID = temp.CarID; ```

Question 37

Create cards for operators

Answer 37

do that

Question 38

Create cards for interview questions

Answer 38

do that

Question 39

Add cards on Normalization

Answer 39

1NF, 2NF, 3NF, 3.5NF, Normalization