SQL

Question 1

What do SQL statements always end with?

Answer 1

Semi colon (;)

Question 2

In this statement. What would you put in place of the #, * and the +?

CREATE TABLE # (
* +,
* +
);

Answer 2

#  = Table Name
* = Column Name
\+ = Date Type

Question 3

In this statement. What would you put in place of the #, * and the +?

INSERT # celebs (*)
VALUES (+);

Answer 3

# = INTO
* = Column Name
\+ = Value being inserted

Question 4

In this statement. What would you put in place of the # and the +?

SELECT # FROM +;

Answer 4

# = Column name or * for all columns in table
\+ = Table name

Question 5

What is the clause that updates rows in a table?

Answer 5

UPDATE {Table name} SET {column name = new value} WHERE {column name = known value}

Question 6

How do you add columns to a table?

Answer 6

Eg.
ALTER TABLE celebs
ADD COLUMN twitter_handle TEXT;

The ALTER TABLE statement added a new column to the table. You can use this command when you want to add columns to a table.

1. ALTER TABLE is a clause that lets you make the specified changes.
2. celebs is the name of the table that is being changed.
3. ADD COLUMN is a clause that lets you add a new column to a table.

twitter_handle is the name of the new column being added
TEXT is the data type for the new column

Question 7

What does NULL represent?

Answer 7

NULL is a special value in SQL that represents missing or unknown data.

Question 8

When creating a table, constraints can be specified on the data. What does applying the PRIMARY KEY, UNIQUE, NOT NULL and DEFAULT constraints do?

Statement Example:
CREATE TABLE celebs (
   id INTEGER PRIMARY KEY, 
   name TEXT UNIQUE,
   date_of_birth TEXT NOT NULL,
   date_of_death TEXT DEFAULT 'Not Applicable',
);

Answer 8

PRIMARY KEY columns can be used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.

UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.

NOT NULL columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.

DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

Question 9

SQL is a programming language designed to manage and manipulate data stored in what kind of database?

Answer 9

Relational databases

Question 10

What does AS do?

Answer 10

AS is a keyword in SQL that allows you to rename a column or table using an alias. The new name can be anything you want as long as you put it inside of single quotes.

Question 11

What does DISTINCT do?

Answer 11

DISTINCT is used to return unique values in the output. It filters out all duplicate values in the specified column(s).
e.g. SELECT DISTINCT

Question 12

What statement would you use if you wanted to select only rows from a table called movies where the genre was ‘Comedy’

Answer 12

SELECT *
FROM movies
WHERE genre = ‘Comedy’;

Question 13

What character can be used with LIKE to represent zero or more missing characters from a pattern

Statement example:
SELECT *
FROM movies
WHERE name LIKE ‘?A’

Answer 13

%

Question 14

When using BETWEEN in SELECT queries, for what value is the 2nd input inclusive and not inclusive

Answer 14

Inclusive - Numbers

Not Inclusive - Letters

Question 15

What operator is used to link multiple conditions for a SELECT query

Answer 15

AND

Question 16

What operator is used to search for different conditions for a SELECT query

Answer 16

OR

Question 17

What does ORDER BY do and what are the key words to order by?

Answer 17

ORDER BY is a clause that indicates you want to sort the result set by a particular column.

ASC is used to order ascending
DESC is used to order descending

**Note ORDER by always goes after WHERE if present

Question 18

What is used to handle if-when login in SQL?

Answer 18

A CASE statement allows us to create different outputs (usually in the SELECT statement).

eg 
SELECT name,
 CASE
  WHEN imdb_rating > 8 THEN 'Fantastic'
  WHEN imdb_rating > 6 THEN 'Poorly Received'
  ELSE 'Avoid at All Costs'
 END
FROM movies;

Each WHEN tests a condition and the following THEN gives us the string if the condition is true.
The ELSE gives us the string if all the above conditions are false.
The CASE statement must end with END

Question 19

What does this statement do?

SELECT COUNT(*)
FROM table_name;

Answer 19

Counts the number of rows in a table

Question 20

What does this statement do?

SELECT SUM(downloads)
FROM fake_apps;

Answer 20

SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column.

Question 21

How would you find the minimum or maximum value in a column?

Answer 21

SELECT MAX/MIN(column)

FROM table_name;

Question 22

How would you find the average of a column?

Answer 22

SELECT AVG(column)
FROM table_name;

Question 23

What are the arguments input into the ROUND function?

Answer 23

ROUND() function takes two arguments inside the parenthesis:

1. a column name
2. an integer (i.e. 0 for 0 decimal places)

Question 24

What does GROUP BY do?

Answer 24

GROUP BY is a clause in SQL that is used with aggregate functions. It is used in collaboration with the SELECT statement to arrange identical data into groups.

The GROUP BY statement comes after any WHERE statements, but before ORDER BY or LIMIT.

Question 25

What class do you use to filter groups of data?

Answer 25

HAVING

eg. SELECT year,
   genre,
   COUNT(name)
FROM movies
GROUP BY 1, 2
HAVING COUNT(name) > 10;

HAVING statement always comes after GROUP BY, but before ORDER BY and LIMIT.

Question 26

How do you link two tables by an id?

Answer 26

Using a table JOIN

e.g. 
SELECT *
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id;

Question 27

What does a LEFT JOIN do?

Answer 27

A left join will keep all rows from the first table, regardless of whether there is a matching row in the second table.

The final result will keep all rows of the first table but will omit the un-matched row from the second table.

Question 28

What is a CROSS JOIN and what is its main use?

Answer 28

A CROSS JOIN combines all rows of one table with all rows of another table.

The most common usage of CROSS JOIN is when we need to compare each row of a table to a list of values.

Question 29

What does UNION do and what criteria must be filled?

Answer 29

UNION ‘stacks’ two tables together

• Tables must have the same number of columns.
• The columns must have the same data types in the same order as the first table.

Question 30

What do you use to create a temporary table to query?

Answer 30

WITH allows us to define a bunch of temporary tables that can be used in the final query.