Creating Tables

Question 1

CREATE TABLE syntax

Answer 1

CREATE TABLE table_name
(
column_name1 data_type1,
column_name2 data_type2
)

SQL for Data Science Week 1

Question 2

How do you designate if a column allows nulls or not?

Answer 2

1. If you don’t specify, it is assumed nulls are allowed.
2. If you want to exclude nulls put NOT NULL after the data_type for the column in the CREATE TABLE statement
3. If you want to explicitly include nulls, put NULL after the data_type for the column in the CREATE TABLE statement

SQL for Data Science Week 1

Question 3

Can a primary key column have a null value in it?

Answer 3

No

SQL for Data Science Week 1

Question 4

How do you designate a column as the primary key?

Answer 4

Put Primary Key after the data_type for the column in the CREATE TABLE statement

SQL for Data Science Week 1

Question 5

What happens if you try to insert a null value in a column that does not allow nulls?

Answer 5

You will receive an error

SQL for Data Science Week 1

Question 6

Baseic INSERT INTO syntax

Answer 6

INSERT INTO table_name
VALUES
(
column1_value,
column2_value,
column3_value,
column4_value
)

SQL for Data Science Week 1

Question 7

Better INSERT INTO syntax

Answer 7

INSERT INTO table_name
(
column1_name,
column2_name,
column3_name,
column4_name
)
VALUES
(
column1_value,
column2_value,
column3_value,
column4_value
)

This is better because it specifies which columns the values are going into and reduces the liklihood values will go into the incorrect columns

SQL for Data Science Week 1

Question 8

What should you use for a value if you want to insert a null value in a column that allows nulls?

Answer 8

NULL

For example:
INSERT INTO table_name
VALUES
(
column1_value,
column2_value,
NULL,
column4_value
)

SQL for Data Science Week 1

Question 9

How long does a temporary table last?

Answer 9

Only during your current session in the database

SQL for Data Science Week 1

Question 10

Why use a temporary table?

Answer 10

1. They are faster than creating a real table
2. Allows you to simplify complex queries

SQL for Data Science Week 1

Question 11

CREATE TEMPORARY TABLE syntax to get data from an existing table into your temporary table

Answer 11

CREATE TEMPORARY TABLE Sandals AS
(
SELECT *
FROM shoes
WHERE shoe_type='sandals'
)

SQL for Data Science Week 1

Question 12

UPDATE TABLE syntax

Answer 12

UPDATE table_name
SET 
column_1 = new_value_1,
column_2 = new_value_2

--Optional
WHERE
where_condition (ex. employeeid=3)

If you don’t include a WHERE clause, every value in the columns indicated will be updated.

SQL for Data Science Week 1

Question 13

DELETE TABLE syntax

Answer 13

DELETE FROM table_name

--Optional
WHERE where_condition

If you omit the WHERE condition, all rows in the table will be deleted

SQL for Data Science Week 1