SQL

Question 1

Database

Answer 1

A set of data stored in a computer and is usually structured into tables

Question 2

Relational database

Answer 2

A type of database that uses a structure that allows us to identify and access data in relation to another piece of data in the database

Question 3

Schema

Answer 3

The set of columns and data types for those columns that make up the table

Question 4

Relational Database Management System

Answer 4

A program that allows you to create, update, and administer a relational database

Most use SQL as the language

Question 5

Popular RDBMS

Answer 5

MySQL
PostgreSQL
OracleDB
SQL Server
SQLite

Each using slight variations of the core SQL language

Question 6

Common data types

Answer 6

INTEGER (positive or negative whole number)
TEXT (text string)
DATE (YYYY-MM-DD)
REAL (decimal)

Question 7

Statement

Answer 7

A text that the database recognizes as a valid command

Question 8

Clauses / commands

Answer 8

Written in capital letters and perform a specific task

Question 9

Parameter

Answer 9

A list of columns, data types, or values that are passed into a clause as an argument

Question 10

SELECT

Answer 10

statement used to fetch data from a database

Every query will begin with SELECT

Question 11

CREATE TABLE

Answer 11

creates a new table in a database. It allows you to specify the name of the table and the name of each column

The parameters must include each column name and data type

Question 12

INSERT

Answer 12

used to insert new rows into a table

Must be structured as follows

INSERT INTO table name (columns to add data)

VALUES (the values to be added to the specific columns)

Question 13

*

Answer 13

Wildcard used with SELECT that allows us to pull all columns from a table

Question 14

ALTER TABLE

Answer 14

lets you add columns to a table in a database. Must be followed with ADD COLUMN

ALTER TABLE table name
ADD COLUMN column name data type

Question 15

NULL

Answer 15

A special value in SQL that represents missing / unknown / blank data

Question 16

UPDATE

Answer 16

edits a row in a table. Must be used with SET and WHERE

UPDATE table name
SET column name = new update
WHERE identifier column name = specific row

Question 17

DELETE FROM

Answer 17

statement deletes one or more rows from a table. Must be used with WHERE

DELETE FROM table name
WHERE column name IS row identifier

Can be used with IS NULL

Question 18

Constraints

Answer 18

Are added after specifying the data type when creating columns for a table

CREATE TABLE table name ( column name, data type, constraint)

Question 19

Common constraints

Answer 19

PRIMARY KEY (same as unique but can only have one primary key column in a table)
UNQIUE (data entry must be unique, multiple columns can have unique constraints)
NOT NULL (data must be entered)
DEFAULT ‘text’ (if no entry provided insert a default)

Question 20

Create table

Answer 20

CREATE TABLE ( )

Question 21

Add a new row to table

Answer 21

INSERT INTO
VALUES

Question 22

Queries data from a table

Answer 22

SELECT
FROM

Question 23

Changes an existing table

Answer 23

ALTER TABLE
ADD

Question 24

Edits a row in a table

Answer 24

UPDATE
SET
WHERE

Question 25

Deletes rows from a table

Answer 25

DELETE FROM WHERE

Question 26

AS

Answer 26

Added with SELECT, it will return the column you want renamed to what you’ve defined The columns are not being renamed in the table but rather appearing only in the result

Question 27

DISTINCT

Answer 27

is used to return only unique values in the output, it will filter out all duplicate values in a specific column SELECT DISTINCT FROM

Question 28

WHERE

Answer 28

can be used to restrict the results of our queries SELECT FROM WHERE

Question 29

Operators

Answer 29

Used to create a condition that can be evaluated (typically with WHERE) = equal to != not equal to > greater than < less than >= greater than or equal to <= less than or equal to

Question 30

LIKE

Answer 30

a special operator used with WHERE to search for a specific pattern in a column SELECT FROM WHERE LIKE

Question 31

Wildcard characters used with LIKE

Answer 31

_ %

Question 32

BETWEEN

Answer 32

operator used with WHERE to filter the result within a certain range. It accepts two values that are either numbers, text, or dates. must be used with AND it will go up to the second value defined in the range SELECT FROM WHERE BETWEEN AND

Question 33

AND

Answer 33

Operator used with WHERE to make the result more specific to multiple conditions SELECT FROM WHERE BETWEEN AND AND

Question 34

OR

Answer 34

Same use as AND however OR tests if any of the conditions are true. Whereas AND all conditions must be true SELECT FROM WHERE OR

Question 35

ORDER BY

Answer 35

Sorts our results either alphabetically or numerically based upon the column type. Can also define ascending (ASC) or descending (DESC) SELECT FROM ORDER BY ASC;

Question 36

LIMIT

Answer 36

used to limit the max number of rows returned in a query It always goes at the end of a query SELECT FROM LIMIT

Question 37

CASE

Answer 37

Allows us to create different outputs. It is how to do If-Then logic. It takes the place of an argument entered into a SELECT It is used with WHEN and THEN to create the If Then logic ELSE closes the logic END closes the Case Use AS to return the new output with a clean name SELECT CASE WHEN THEN WHEN THEN ELSE END AS FROM

Question 38

Aggregates

Answer 38

Calculations performed on multiple rows of a table

Question 39

Count the number of rows

Answer 39

COUNT( ) SELECT COUNT FROM

Question 40

Sum the values in a column

Answer 40

SUM( )

Question 41

Return the largest / smallest value

Answer 41

MAX( ) / MIN( )

Question 42

Get the average of the values in a column

Answer 42

AVG( )

Question 43

Round the values in the column

Answer 43

ROUND( )

Question 44

SUM( )

Answer 44

Takes the name of a column as an argument and returns the sum of all values in that column SELECT SUM FROM

Question 45

ROUND( )

Answer 45

Takes two arguments 1. Column name 2. Integer It will round the values to the number of decimal places specified by the integer

Question 46

GROUP BY

Answer 46

a clause used with aggregate functions in collaboration with SELECT to arrange data into groups. This can be used instead of multiple WHERE statements It comes after WHERE but before ORDER BY or LIMIT SELECT xxxx, AVG(yyyy) FROM GROUP BY xxxx ORDER BY xxxx

Question 47

Column references

Answer 47

We can use simple column reference numbers (1, 2, 3 etc) with GROUP BY and ORDER BY when we want to organize our result when our columns are aggregated

Question 48

HAVING

Answer 48

performs a filter or limiting function similar to WHERE but on aggregated data When we want to limit a query based upon values of individual rows - WHERE When we want to limit results based on aggregate - HAVING Always comes after GROUP BY but before ORDER BY and LIMIT SELECT xxxx, yyyy, COUNT(zzzz) FROM table GROUP BY 1, 2 HAVING COUNT(zzzz) > 10

Question 49

JOIN

Answer 49

combines two tables. When specifying the column to join on you need to do table then column This is an inner join that will only keep the records that are matched in both tables SELECT FROM JOIN ON table.column = table.column

Question 50

LEFT JOIN

Answer 50

same as JOIN but will keep all records from the left table and drop any records that don’t match in the right table SELECT FROM left LEFT JOIN right ON left.column = right.column

Question 51

Primary keys

Answer 51

Uniquely identifies each row for a table None of the values can be null Each value must be unique A table cannot have more that one primary key column

Question 52

Foreign key

Answer 52

When the primary key of another table appears in a given table

Question 53

CROSS JOIN

Answer 53

will combine all rows of one table with all rows of another table. Does not require ON SELECT FROM table1 CROSS JOIN table2

Question 54

UNION

Answer 54

Stacks one dataset on top of the other Tables must have the same number of columns and the same data types SELECT FROM table1 UNION SELECT FROM table2

Question 55

WITH

Answer 55

allows us to combine two tables but one of the tables is the result of another calculation WITH calculation table name AS ( SELECT … … … ) SELECT * FROM calculation table name JOIN table ON