SQL Databases

Question 1

What does First Normal Form mean?

Answer 1

All data values in the data are atomic
There are no repeating columns

Question 2

What does Second Normal Form mean?

Answer 2

Non-key attributes are fully dependent on the primary key.
(If a non-key attribute is dependent on some but not all of the columns in a composite primary key, the table violates 2NF.)

Question 3

What does Third Normal Form mean?

Answer 3

Non-key attributes are non-transitively dependent on primary key

Question 4

Define a table in a relational database

Answer 4

a set of data elements organized into columns and rows

Question 5

Define a record in a relational database

Answer 5

a row of data in a table consisting of single value in each column in the table

Question 6

Arrange the following SQL keywords in the correct syntax order

ON
SELECT
WHERE
JOIN
ORDER BY
GROUP BY
FROM

Answer 6

SELECT
FROM
JOIN
ON
WHERE
GROUP BY
ORDER BY

Question 7

What are the five supported data types in SQLite?

Answer 7

NULL
INTEGER
REAL
TEXT
BLOB

Question 8

How are booleans represented in SQLite?

Answer 8

SQLite represents Booleans as integers
(0 = False, 1 = True)

Question 9

When is a junction table required?

Answer 9

Junction tables are needed to represent many-to-many relationships in a relational database

Question 10

Write SQL code to create a junction table to join the following two tables:

Student (studentID, class, …)
CCA (ccaID, teacher_in_charge, …)

Assume the first column is the PK and PKs are INTEGERs

Answer 10

CREATE TABLE StudentCCA (
studentID INTEGER,
ccaID INTEGER,
PRIMARY KEY (studentID, ccaID),
FOREIGN KEY (studentID) REFERENCES Student(studentID),
FOREIGN KEY (ccaID) REFERENCES CCA(ccaID)
)

Question 11

How should quotes be used in SQL?

Answer 11

Double-quotes (“) for identifiers (table and column names)
Single-quotes (‘) for literals (strings, values to match in a WHERE clause, etc)

Question 12

What is the syntax to create an SQL table?

Answer 12

CREATE TABLE (
Col1 TYPE,
Col2 TYPE,
…,
PRIMARY KEY (Col1)
)

Question 13

The following SQL code raises an error. Why?

CREATE TABLE Student (
ID INTEGER,
Name TEXT,
Class TEXT,
PRIMARY KEY (ID),
);

Answer 13

Last line (PRIMARY KEY) should not have comma

Question 14

The following SQL code raises an error. Why?

CREATE TABLE ‘Student’ (
‘ID’ INTEGER,
‘Name’ TEXT,
‘Class’ TEXT,
PRIMARY KEY (‘ID’)
);

Answer 14

Table and column names are identifiers and should be double-quoted (“), not single-quoted (‘)