SQL

Question 1

What is SQL

Answer 1

Structured Query Language

IBM started out SQL as SEQUEL (Structured English QUEry Language) in the 1970s to query databases.

Question 2

What is a database

Answer 2

A set of data stored in a computer

Question 3

What is a relational database

Answer 3

A type of database that lets us store and identify data in relation to other data; often organized into tables

Question 4

What are tables made of

Answer 4

Rows (records) and columns (which have names and data types)

Question 5

What is an RDMS

Answer 5

Relational Database Management System; lets you create, update, and administer a relational database
Most use SQL to access the database

Question 6

What is SQLite

Answer 6

A RDMS that uses minimal SQL

Question 7

What are five popular RDMSs

Answer 7

MySQL, PostgreSQL, Oracle DB, SQL Server, SQLite

Question 8

Where is a glossary of SQL commands

Answer 8

https://www.codecademy.com/articles/sql-commands

Question 9

ALTER TABLE

Answer 9

ALTER TABLE table_name
ADD column_name datatype;

adds column to a table in a database

Question 10

What are some of the most common data types in relational databases

Answer 10

INTEGER
TEXT
DATE YYYY-MM-DD
REAL (decimal value)

Question 11

What are a statement and its components

Answer 11

Text a database recognizes as a valid command

clauses/commands: written in caps
table name to which the clause applies
parameter: list of columns, data types, or values that are passed into the clause as an argument

Question 12

What do SELECT statements return

Answer 12

A table

Question 13

What are constraints

Answer 13

add info about how a column can be used; are added after data type; tells database to reject data that does not conform

Question 14

CREATE TABLE

Answer 14

creates a new table

Question 15

INSERT INTO

Answer 15

adds a new row to a table

Question 16

SELECT

Answer 16

queries data from a table

Question 17

UPDATE

Answer 17

UPDATE table_name
SET some_column = some_value
WHERE some_column = some_value;

edits a row in a table

Question 18

DELETE FROM

Answer 18

deletes rows from a table

Question 19

what is the purpose of *

Answer 19

selects every column in the table

Question 20

SELECT DISTINCT

Answer 20

SELECT DISTINCT column_name
FROM table_name;

specifies that the statement is going to be a query that returns unique values in the specified column(s).

Question 21

LIKE

Answer 21

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

_ is wildcard
% is a wildcard character that matches zero or more missing letters in the pattern.

you can apply the LIKE operator to numerical values as well. Whenever you use LIKE however, you must always wrap the pattern within a pair of quotations, whether for matching a number or a string.

Question 22

BETWEEN

Answer 22

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;

The BETWEEN operator is used to filter the result set within a certain range. The values can be numbers, text or dates.

is inclusive (which works kinda differently for integers and text?)

Question 23

ORDER BY

Answer 23

SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;

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

Question 24

LIMIT

Answer 24

SELECT column_name(s)
FROM table_name
LIMIT number;

LIMIT is a clause that lets you specify the maximum number of rows the result set will have.

not supported in all SQL databases

Question 25

CASE

Answer 25

SELECT column_name,
  CASE
    WHEN condition THEN 'Result_1'
    WHEN condition THEN 'Result_2'
    ELSE 'Result_3'
  END
FROM table_name;

CASE statements are used to create different outputs (usually in the SELECT statement). It is SQL’s way of handling if-then logic.

Question 26

What is an aggregate

Answer 26

a calculation performed on multiple rows of a table

computes a single result set from a set of values

Question 27

COUNT ()

Answer 27

takes the name of a column as an argument and counts the number of non-empty values in that column.

Question 28

SUM ()

Answer 28

takes the name of a column as an argument and returns the sum of all the values in that column.

Question 29

If multiple rows have the minimum or maximum value, which one is returned when using MAX/MIN?

Answer 29

Typically, when you have more than one row that contains the minimum or maximum value in a column, the topmost row containing that value will be returned in the result.

Question 30

In SQL, how can we get the average of only the unique values of a column?

Answer 30

To run the AVG() function on a column such that it only averages the unique values in the column, we could use the DISTINCT clause right before the column name.

SELECT AVG(DISTINCT column)

Question 31

GROUP BY

Answer 31

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 32

HAVING

Answer 32

HAVING is very similar to WHERE. In fact, all types of WHERE clauses you learned about thus far can be used with HAVING.

When we want to limit the results of a query based on an aggregate property, use HAVING.

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

Question 33

inner join vs left join

Answer 33

inner join: results only includes rows that match on condition

left join: keeps all rows from first table, regardless of matches from second

Question 34

primary key vs foreign key

Answer 34

primary key:
not null
unique
only one primary key column per table

foreign key:
another table’s primary key in a different table

Question 35

cross join

Answer 35

join all the columns!

Question 36

union

Answer 36

stacks tables, which must have:
same number of columns, with same data types in same order

When you combine tables with UNION, duplicate rows will be excluded.

To explain why this is the case, recall a Venn Diagram, which shows the relations between sets. If we perform UNION on two sets of data (tables), say A and B, then the data returned in the result will essentially be
A + B - (A intersect B)

In the first part,
A + B
will add together all the rows of both tables, including duplicates.

The second part,
- (A intersect B)
will remove every duplicate, which is where A and B intersected.

If, however, you wanted to include duplicates, certain versions of SQL provides the UNION ALL operator.

Question 37

with

Answer 37

with xx_alias as (entireQuery)

Query