SQL

Question 1

What is a static type system?

Answer 1

You declare a column with a specific data type

Question 2

What is a dynamic type system?

Answer 2

Value stored in column determines its data type

Question 3

Give examples of static type systems:

Answer 3

• MySQL
• PostgreSQL

Question 4

Give examples of dynamic type systems:

Answer 4

• SQLite

Question 5

What are data types in SQLite?

Answer 5

5 primitive data types:
NULL
INTEGER
REAL
TEXT
BLOB

Question 6

What is the syntax for a creating a table?

Answer 6

CREATE TABLE tableName(
name type constraint
)

Question 7

What is the syntax for linking foreign keys in tables?

Answer 7

Foreign key(foreignKey) REFERENCES tableName(candidateKey)
ON UPDATE action
ON DELETE action

Question 8

What are some actions we can have for foreign key instances?

Answer 8

SET NULL
CASCADE
RESTRICT
SET DEFAULT

Question 9

What are aggregate functions?

Answer 9

Act on a single column and returns a single value (used in select)

Question 10

Give examples of aggregate functions

Answer 10

COUNT
SUM
AVG
MIN
MAX

Question 11

What are the different types of joining?

Answer 11

Cross join
Inner join
Left join

Question 12

What is cross join?

Answer 12

Computes the cartesian product of relations

Question 13

What is inner join?

Answer 13

Matches each from from 1st relation with every row from 2nd relation over a common attribute

Question 14

What is left join?

Answer 14

Matches each row from the first row with every row from the 2nd over a common attribute. If no common attribute, then NULL