Manipulation

Question 1

Data Types

Answer 1

INTEGER: positive or negative whole number
TEXT: text string
DATE: date, formatted YYYY-MM-DD
REAL: decimal value

Question 2

Statements

Answer 2

• Always end in semicolon ;
• Clause (or command): CREATE TABLE, SELECT, etc
• Parameters: list of columns, data types, or values that are passed to a clause as an argument

Question 3

CREATE Statement

Answer 3

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

• CREATE TABLE: clause
• celebs: name of table being created
• (id INTEGER,…): parameters, defining each column and its data type
• id - first column, name - second column, etc

Question 4

INSERT Statement

Answer 4

INSERT INTO celebs (id, name, age)
VALUES (1, ‘Justin Bieber’, 22);
- INSERT INTO: clause that adds specified row(s)
- celebs: name of table rows are being added to
- (id, name, age): parameter for identifying columns the data is being added to
- VALUES: clause indicating the data being inserted
- (1, ‘Justin Bieber’,…): parameter identifying the values being inserted

Question 5

SELECT Statement

Answer 5

SELECT name FROM celebs;

• SELECT statements are used to fetch data from database
• SELECT: clause meaning this statement is a query (fetching data)
• name: column to query data from
• FROM celebs: specifies name of table to query data from
• • : all columns from table queried (star is how you say it)
• SELECT statements always return a new table called the “result set”

Question 6

ALTER Statement

Answer 6

ALTER TABLE celebs
ADD COLUMN twitter_handle TEXT;
- ALTER statements: adds new column to the table
- ADD COLUMN: clause that lets you add new column to table
- twitter_handle: name of new column being added
- TEXT: data type for new column
- NULL: missing or unknown data / rows that exist before new column is added will show 0 with line representing NULL, no info for that column (yet)

Question 7

UPDATE Statement

Answer 7

UPDATE celebs
SET twitter_handle = ‘@taylorswift13’
WHERE id = 4;
- UPDATE statements: edit a row in a table
- SET: clause indicating which column to edit
- twitter_handle: column being edited
- ‘@taylorswift13’: value being inserted to the column
- WHERE: clause, indicates which row to edit with the new column value
- the row where id = 4 is where the twitter_handle ‘@taylorswift13’ will be inserted

Question 8

DELETE Statement

Answer 8

DELETE FROM celebs
WHERE twitter_handle IS NULL;
- DELETE statements: delete one or more rows from table
- WHERE twitter_handle IS NULL: delete rows where twitter_handle column has no value (is null)

Question 9

CONSTRAINTS

Answer 9

• Constraints that add information about how a column can be used are 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
  CREATE TABLE celebs (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE,
  date_of_birth TEXT NOT NULL,
  date_of_death TEXT DEFAULT ‘Not Applicable’
  );
  —part 1 of 2—

Question 10

CONSTRAINTS p.2

Answer 10

• PRIMARY KEY: column that can be used to uniquely identify a row / table can only have one primary key
• UNIQUE: column that must have different value for each row / tables can have multiple unique column constraints
• NOT NULL: column that must have a value
• DEFAULT: column takes an additional argument that will be the assumed value for an inserted row if the row does not specify a value for that column