L.06

Question 1

What is SQL?

Answer 1

A standardized language for interacting with relational databases.
Can be pronounced as “sequel” or “S-Q-L.”

Question 2

What are the two main parts of SQL?

Answer 2

DDL (Data Definition Language): Defines the database schema.
DML (Data Manipulation Language): Manages and queries data.

Question 3

What are some common relational database systems?

Answer 3

Open-source: MySQL, PostgreSQL
Commercial: Oracle, Microsoft SQL Server

Question 4

What is the command to create a table?

Answer 4

CREATE TABLE table_name (
column_name data_type constraints
);

Question 5

What is the difference between CREATE TABLE and CREATE VIEW?

Answer 5

CREATE TABLE: Creates a physical table stored in the database.
CREATE VIEW: Creates a virtual table based on a query.

Question 6

What is a primary key?

Answer 6

A column (or set of columns) that uniquely identifies each row in a table.
Defined using PRIMARY KEY(column_name).

Question 7

How do you define a foreign key?

Answer 7

FOREIGN KEY (column_name) REFERENCES referenced_table(primary_key)

Question 8

What are the different types of relationships in databases?

Answer 8

1:N (One-to-Many): Foreign key in the “N” table.
1:1 (One-to-One): Can be modeled as a 1:N or merged into one table.
N:M (Many-to-Many): Requires an association table.

Question 9

What are some key constraints in SQL?

Answer 9

PRIMARY KEY – Ensures uniqueness.
UNIQUE – Ensures unique values in a column.
NOT NULL – Ensures a column cannot be empty.
CHECK – Enforces a specific condition.

Question 10

Example of a CHECK constraint:

Answer 10

Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);

Question 11

How do you alter a table?

Answer 11

ALTER TABLE table_name
ADD column_name data_type;

Question 12

How do you drop a table?

Answer 12

DROP TABLE table_name;

Question 13

What happens if a referenced key is deleted?

Answer 13

NO ACTION / RESTRICT: Prevents deletion.
CASCADE: Deletes dependent rows.
SET NULL: Sets the foreign key column to NULL.
SET DEFAULT: Sets the foreign key column to a default value.

Question 14

Common data types in SQL?

Answer 14

Numeric: INTEGER, SMALLINT, FLOAT, DOUBLE PRECISION
Character: CHAR(n), VARCHAR(n)
Boolean: TRUE, FALSE, NULL
Date: DATE (format: YYYY-MM-DD)
Binary: BLOB, CLOB (for storing files)

Question 15

How do you define a domain type?

Answer 15

CREATE DOMAIN SSN_TYPE AS CHAR(8);

Useful for consistency when using the same type across multiple tables.

Question 16

How do you auto-generate a primary key?

Answer 16

CREATE TABLE table_name (
id SERIAL PRIMARY KEY
);

SERIAL automatically increments the ID value.

Question 17

How do you define a multi-attribute primary key?

Answer 17

PRIMARY KEY (column1, column2);

Question 18

How do you explicitly name constraints?

Answer 18

CONSTRAINT constraint_name PRIMARY KEY (column_name);

Question 19

What are referential integrity triggers?

Answer 19

Ensure that foreign keys reference valid values in another table.
Options include CASCADE, SET NULL, RESTRICT, SET DEFAULT.

Question 20

How do you enforce total participation in relationships?

Answer 20

Use NOT NULL in the foreign key definition.

CREATE TABLE ENROLLMENT (
student CHAR(10) NOT NULL,
FOREIGN KEY (student) REFERENCES STUDENT(personnr)
);

Question 21

How do you change a column’s default value?

Answer 21

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT value;

Question 22

How do you drop a column?

Answer 22

ALTER TABLE table_name
DROP COLUMN column_name;

Question 23

Why should you be careful when executing SQL commands?

Answer 23

Most DBMSs do not ask for confirmation before executing commands.
Always back up data before running delete or update statements.

Question 24

How do you check if a table was successfully created?

Answer 24

\dt
SELECT * FROM table_name;