Manipulation

Question 1

SQL

Answer 1

Structured Query Language

Manage data stored in relational databases

Simple, declarative statements

Keeps data accurate and secure

Helps maintain the integrity of databases

Widely used across web frameworks and database applications

Question 2

Relational Database

Answer 2

Organizes information into one or more tables

Question 3

Table

Answer 3

Data organized into rows and columns

Question 4

Column

Answer 4

Set of data values of a particular type

Field (in Tableau)

Question 5

Row

Answer 5

A single record in a table

Question 6

Data Types

Answer 6

All data stored in a relational database is of a certain data type

Most common data types:

• INTEGER
• TEXT
• DATE
• REAL

Question 7

INTEGER

Answer 7

A positive or negative whole number

Question 8

TEXT

Answer 8

A text string

Question 9

DATE

Answer 9

Date formatted as YYYY-MM-DD

Dates must be surrounded by single quotes

Question 10

REAL

Answer 10

A decimal value

Question 11

Statement

Answer 11

Text that the database recognizes as a valid command

Statements always end in a semicolon

Question 12

Clause

Answer 12

Clauses perform specific tasks in SQL

By convention, clauses are written in capital letters

AKA commands

Go at the beginning of a statement

Question 13

Parameter

Answer 13

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

Within the parentheses

Question 14

EXAMPLE

CREATE TABLE table_name (
     column_1 data_type,
     column_2 data_type,
     column_3 data_type
);

Answer 14

CREATE TABLE is the clause

table_name is the name of the table the command is applied to

(column_1 data_type…column_3 data_type) is the parameter; the list of column names and associated data types

; marks the end of the statement

Question 15

Structure of SQL Statements

Answer 15

The structure varies

The number of lines DOES NOT matter

A statement can be written all on one line or split up across multiple lines to make it easier to read

Question 16

CREATE

Answer 16

Allows us to create a new table in the database, from scratch

Question 17

EXAMPLE

CREATE TABLE celebs (
     id INTEGER,
     name TEXT,
     age INTEGER
);

Answer 17

CREATE TABLE: you want to create a new table

celebs: the name of the table

(id INTEGER, name TEXT, age INTEGER): list of parameters defining each column

id INTEGER: 1st column with INTEGER data type

name TEXT: 2nd column with TEXT data type

age INTEGER: 3rd column with INTEGER data type

Question 18

INSERT

Answer 18

Inserts a new row into a table

Use when you want to add new records

Usually used as INSERT INTO follow by VALUES clause to specify the values you are inserting

Question 19

EXAMPLE

INSERT INTO celebs (id, name ,age)
VALUES (1, ‘Justin Bieber’, 22);

Answer 19

INSERT INTO: you’re adding a new row

celebs: into the celebs table

(id, name, age): parameters identifying data types

VALUES: data is being inserted

(1, ‘Justin Bieber’, 22): the values being inserted
1: integer added to id column
‘Justin Bieber’: text added to name column
22: integer added to age column

Question 20

SELECT

Answer 20

Used to fetch data from a database

Use every time you query data from a database

SELECT statements always return a new table called the “result set”

Used in conjunction with FROM to specify which table

Question 21

EXAMPLE

SELECT * FROM celebs;

Answer 21

SELECT: indicates statement is a query

*: special wildcard character that selects every column in a table

FROM celebs: from the celebs table

Question 22

ALTER

Answer 22

Adds a new column to a table

Used in conjunction with ADD COLUMN clause

Question 23

EXAMPLE

ALTER TABLE celebs
ADD COLUMN twitter_handle TEXT;

Answer 23

ALTER TABLE: you want to add a new column

celebs: name of table you want to change

ADD COLUMN: clause that lets you add a new column

twitter_handle: name of new column

TEXT: data type for new column

NULL: represents missing or unknown data; since no values are being inserted for existing rows, they all will have NULL values for this new column

Question 24

UPDATE

Answer 24

Edits a row in a table

Use when you want to change existing records

Used in conjunction with SET and WHERE

• SET indicates the column to edit
• WHERE indicates which rows to update

Question 25

EXAMPLE

UPDATE celebs
SET twitter_handle = ‘@taylorswift13’
WHERE id = 4;

Answer 25

UPDATE: you want to edit a row
celebs: table where you want to make the edit
SET: indicates the column to edit
twitter_handle: name of column that will be updated
‘@taylorswift13’: new value that will be inserted
WHERE: indicates which row yo update
4: row with id ‘4’ will be updated

Question 26

DELETE FROM

Answer 26

Deletes one or more rows from a table

Use when you want to delete existing records

Question 27

EXAMPLE

DELETE FROM celebs
WHERE twitter_handle IS NULL;

Answer 27

DELETE FROM: you want to delete rows
celebs: from the celebs table
WHERE: you're going to indicate the rows
twitter_handle ISNULL: delete all rows where twitter_handle column ISNULL
ISNULL: returns when the value is NULL

Question 28

CONSTRAINTS

Answer 28

Adds information about how a column can be used

Invoked after specifying the data type for a column

Can be used to tell the database to reject inserted data that does not adhere to a certain restriction

Question 29

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 29

PRIMARY KEY: uniquely identify the row; will not allow you insert row with an identical value already in the table

UNIQUE: columns have a different value for each row

NOT NULL: columns must have a value; rows without a value will not be inserted

DEFAULT: if no value specified, will take the assumed value in quotes

Question 30

CONSTRAINT EXAMPLES

Answer 30

PRIMARY KEY

UNIQUE

NOT NULL

DEFAULT ‘value here’