Database and Table Creation Syntax

Question 1

What values can a Boolean datatype hold?

Answer 1

True and false

Question 2

What datatypes belong to the Character datatype?

Answer 2

char, varchar and text

Question 3

What numeric datatypes exist?

Answer 3

smallint

integer

bigint

decimal

numeric

real

double precision

smallserial

serial

bigserial

Question 4

What are temporal datatypes?

Answer 4

data, time, timestamp, and interval

Question 5

What is the UUID datatype?

Answer 5

UUID stands for Universally unique identifier.

The UUID data type is considered a subtype of the STRING data type, because UUID values are displayed in their canonical textual format and, in general, behave the same as string values in the various SQL operators and expressions.

A UUID generated by the UUID() function would look like this:

UUID_VALUE1UUID_VALUE2UUID_VALUE3e762634c-3e41-11eb-b897-0862660ccbd4e7626367-3e41-11eb-b897-0862660ccbd4e7626368-3e41-11eb-b897-0862660ccbd4

Question 6

What array datatypes are available in SQL?

Answer 6

Arrays can be used with many if not all? datatypes:

CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);

Question 7

What is a Primary Key (PK)?

Answer 7

• A primary key is a column or a group of columns used to identify a row uniquely in a table
• Primary keys are also important since they allow us to easily discern what columns should be used for joining tables together
• Primary keys are integer based and unique

Question 8

What does PK stand for?

Answer 8

Primary Key

Question 9

What is a foreign key (FK)?

Answer 9

• A foreign key is a field or group of fields in a table that uniquely identifies a row in another table.
• A foreign key is defined in a table that references the other table’s primary key.
• The table that contains the foreign key is called referencing table or child table
• A table to which the foreign key references is called a referenced table or parent table
• A table can have multiple foreign keys depending on its relationships with other tables

Question 10

What does FK stand for?

Answer 10

Foreign Key

Question 11

What is a referenced table or parent table?

Answer 11

A table to which the foreign key references is called referenced table or parent table

Question 12

Can a table have multiple foreign keys?

Answer 12

Yes, depending on its relationships with other tables

Question 13

What is a referencing table or child table?

Answer 13

The table that contains the foreign key is called referencing table or child table

Question 14

What is a child table a child of?

And what does a child table have?

Answer 14

The parent table with the Primary Key

A child table has a foreign key, whereas a parent table has the primary key referencing to the child table

Question 15

What is a constraint?

Answer 15

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Question 16

Are constraints specified to a column or table?

Answer 16

Constraints can be column level or table level. Column-level constraints apply to a column, and table-level constraints apply to the whole table.

Question 17

What are the most common constraints used in SQL

Answer 17

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfy a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly

Question 18

How can you create a table without any constraints or inheritance?

Answer 18

CREATE TABLE table_name (
column_name TYPE column_constraint,
column_name TYPE column_constraint,
);

Question 19

What’s the best datatype for a Primary Key on Postgresql?

Answer 19

Serial

• In PostgreSQL a sequence is a special king of database object that generates a sequence of integers.
• A sequence is often used as the primary key column in a table

Question 20

What’s the most commonly used datatype for Primary Keys in MySQL and SQL server etc?

Answer 20

Integer

• Not all SQL flavors support the serial data type so the AUTO_INCREMENT constraint would need to be specified when creating a table.

Question 21

How can you add a reference to a primary_key (foreign key (FK)) of a different table while creating a new table?

Answer 21

CREATE TABLE account_job(
user_id INTEGER REFERENCES account(user_id)
)

notice here that the data type for the user_id is not serial but INTEGER, the data type doesn’t need to be serial because we are only referencing the primary_key of another table.

Question 22

What are things that you need to keep in mind when inserting row values into a table?

Answer 22

1. The inserted row values must match up for the table, including constraints
2. SERIAL columns or PRIMARY KEY columns do not need to be provided a value, as they are auto incrementing

Question 23

What does INSERT INTO do?

Answer 23

INSERT INTO is used for adding a row into a table.

Question 24

What other command is used in combination with INSERT INTO?

Answer 24

INSERT INTO is used in combination with VALUES to specify values that will be inserted into the row

Question 25

Considering the following table named account:

user_id(SERIAL),username(VARCHAR),created_on(TIMESTAMP)

How would you add a row to this table?

Answer 25

INSERT INTO account(username, created_on)
VALUES (‘Jose’, CURRENT_TIMESTAMP)

Notice:

• Remember that CURRENT_TIMESTAMP can automatically insert the current Time into the TIMESTAMP column
• Notice that the user_id doesn’t need to be selected at all, it can be omitted from INSERT INTO as well as VALUES as it will be auto-created and auto-incremented by the database server automatically

Question 26

What’s the general syntax to update data in a table?

Answer 26

UPDATE table
SET column1 = value1,
column2 = value2
WHERE
condition;

Question 27

Can you run an UPDATE command without any WHERE condition?

Answer 27

Yes:

UPDATE table
SET last_login = CURRENT_TIMESTAMP

this will update all rows

Question 28

Can you update the data in a specific column based on data in a different column?

Answer 28

Yes:

UPDATE account
SET last_login = created_on

Question 29

What is an UPDATE JOIN?

Answer 29

An UPDATE JOIN is when we create the data on a table according to the data inside a different table

Question 30

What does the RETURNING command do inside a query?

Answer 30

The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an insert, delete or update. Without RETURNING, you would have to run a SELECT statement after the DML statement is completed, in order to obtain the values of the changed columns. So, RETURNING helps avoid another roundtrip to the database, another context switch in a PL/SQL block.

The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INTO form.

You can also call aggregate functions in the RETURNING clause to obtain sums, counts, and so on of columns in multiple rows changed by the DML statement.

Finally, you can also use RETURNING with EXECUTE IMMEDIATE (for dynamically constructed and executed SQL statements).

Question 31

What does the DELETE clause do?

Answer 31

We can use the DELETE clause to remove rows from a table

Question 32

What’s a simple example for a DELETE clause query

Answer 32

DELETE FROM table
WHERE row_id = 1

Question 33

How can you delete rows based on their presence in other tables?

If for example 2 tables are linked to each other using PK and FK how could you access the other table and delete data off of one table according to ids that match?

Answer 33

DELETE FROM tableA
USING tableB
WHERE tableA.id=TableB.id

Question 34

How can you delete all rows from a table?

Answer 34

DELETE FROM table

Question 35

What does the RETURNING clause do when used in conjunction with a DELETE command?

Answer 35

It will return the deleted rows if specified like so:

RETURNING id,name,password…

Question 36

What does an ALTER clause do?

Answer 36

An ALTER clause allows for changes to an existing table structure, such as:

• Adding, dropping, or renaming columns
• Changing a columns data type
• Set DEFAULT values for a column
• Add CHECK constraints
• Rename table

Question 37

How can you add columns to a table using the ALTER TABLE clause?

Answer 37

ALTER TABLE table_name
ADD COLUMN new_col TYPE

Question 38

How can you remove columns using the ALTER TABLE clause?

Answer 38

ALTER TABLE table_name
DROP COLUMN col_name

Question 39

How can you alter constraints using the ALTER TABLE clause?

Answer 39

ALTER TABLE table_name
ALTER COLUMN col_name

And then you can do multiple things, to name a few:

• To set a default value constraint: SET DEFAULT value
• Drop a default constraint: DROP DEFAULT
• Remove or add NULL constraint: SET NOT NULL or DROP NOT NULL
• Add general constraint: ADD CONSTRAINT constaint_name

Question 40

How can you change a table name using the ALTER TABLE clause?

Answer 40

ALTER TABLE information
RENAME TO new_info

Question 41

How can you change a column name using the ALTER TALBE clause?

Answer 41

ALTER TABLE new_info
RENAME COLUMN person TO people

Question 42

How can you drop a column using the ALTER TABLE clause?

Answer 42

ALTER TABLE table_name
DROP COLUMN col_name

Question 43

How can you drop a column using the ALTER TABLE clause only if the column actually exists?

Answer 43

ALTER TABLE new_info
DROP COLUMN IF EXISTS people

Question 44

What does the CHECK constraint do?

Answer 44

• The CHECK constraint allows us to create more customized constraints that adhere to a certain condition
• Such as making sure all inserted integer values fall below a certain threshold

Question 44

What does the CHECK constraint do?

Answer 44

• The CHECK constraint allows us to create more customized constraints that adhere to a certain condition
• Such as making sure all inserted integer values fall below a certain threshold

Example:

CREATE TABLE example(
ex_id SERIAL PRIMARY KEY,
age SMALLINT CHECK ( age > 21 ),
parent_age SMALLINT CHECK ( parent_age > age )
);