SQL

Question 1

What does SQL stand for?

Answer 1

Structured Query Language

Question 2

True or False: PostgreSQL is an open-source relational database management system.

Answer 2

True

Question 3

Fill in the blank: In SQL, the command to retrieve data from a database is ______.

Answer 3

SELECT

Question 4

What is the purpose of normalization in databases?

Answer 4

To reduce data redundancy and improve data integrity.

Question 5

What does the acronym ACID stand for in database transactions?

Answer 5

Atomicity, Consistency, Isolation, Durability

Question 6

Which SQL command is used to add new records to a table?

Answer 6

INSERT

Question 7

True or False: A primary key can contain duplicate values.

Answer 7

False

Question 8

What type of relationship is represented by a foreign key in a database?

Answer 8

A relationship between two tables.

Question 9

Multiple choice: Which of the following is NOT a type of JOIN in SQL? A) INNER JOIN B) OUTER JOIN C) CROSS JOIN D) LINK JOIN

Answer 9

D) LINK JOIN

Question 10

What is a database schema?

Answer 10

The structure that defines the organization of data in a database.

Question 11

Fill in the blank: The command to delete records from a table in SQL is ______.

Answer 11

DELETE

Question 12

True or False: A unique constraint allows for duplicate values in a column.

Answer 12

False

Question 13

What is the purpose of the GROUP BY clause in SQL?

Answer 13

To arrange identical data into groups.

Question 14

Which SQL command is used to modify existing records in a table?

Answer 14

UPDATE

Question 15

What is the result of a LEFT JOIN?

Answer 15

It returns all records from the left table and matched records from the right table.

Question 16

What is the difference between a primary key and a foreign key?

Answer 16

A primary key uniquely identifies a record in its own table, while a foreign key links to a primary key in another table.

Question 17

Multiple choice: Which SQL function is used to count the number of rows in a result set? A) COUNT() B) SUM() C) AVG() D) TOTAL()

Answer 17

A) COUNT()

Question 18

Fill in the blank: The SQL command ______ is used to create a new table.

Answer 18

CREATE TABLE

Question 19

True or False: In PostgreSQL, data types can be customized by the user.

Answer 19

True

Question 20

What is a composite key?

Answer 20

A key that consists of two or more attributes that together uniquely identify a record.

Question 21

What does the term ‘denormalization’ refer to?

Answer 21

The process of combining tables to reduce the complexity of queries and improve performance.

Question 22

Multiple choice: Which of the following is a valid SQL data type? A) INTEGER B) NUMBER C) TEXT D) All of the above

Answer 22

D) All of the above

Question 23

What is the SQL command to create an index?

Answer 23

CREATE INDEX

Question 24

Fill in the blank: The ______ clause is used to filter records in a SQL query.

Answer 24

WHERE

Question 25

True or False: The SQL command ALTER TABLE can be used to add a new column to an existing table.

Answer 25

True

Question 26

What is the purpose of the HAVING clause?

Answer 26

To filter records after aggregation.

Question 27

What is an ERD? Why would you use one?

Answer 27

Entity Relationship Diagram * To understand the structure and relationships between entities in a database system.

Question 28

What is a database used for?

Answer 28

A centralised and structured way to store data so it can be organised, accessed and managed easily.

Question 29

What is an entity in the context of databases?

Answer 29

A real-world object or concept about which information is stored e.g. person, event.

Question 30

What is an attribute in the context of databases?

Answer 30

A quality, property or characteristic that defines an entity or entity type (columns in database tables).

Question 31

What is a relational database?

Answer 31

A method of storing related data in table(s) made up of columns and rows. Tables can be connected using primary or foreign keys.

Question 32

True or False: there is a `DATETIME` data type in PostgreSQL.

Answer 32

False - the `TIMESTAMP` data type is used to store combined date and time values in PostgreSQL.

Question 33

What is a primary key in a relational database?

Answer 33

A column or a set of columns in a table whose values uniquely identify a row in a database table.

Question 34

What is a `JOIN`?

Answer 34

An action that combines rows from two or more database tables based on a related column.

Question 35

How does a `LEFT JOIN` differ from an `INNER JOIN`?

Answer 35

`INNER JOIN` only displays records with matching values in both database tables; `LEFT JOIN` displays all records from left table, with matching values from right table and `NULL` values for non-matching records.

Question 36

What is a view in SQL?

Answer 36

A virtual table with contents defined by a query which is stored and can be referenced in later queries.

Question 37

* No duplicate rows * Each cell only contains a single value * Values should be non-divisible (context dependent) These are features of `____` Normal Form?

Answer 37

1st (Normal Form)

Question 38

* No partial dependencies - every non-key attribute is fully dependent on the primary key. Feature of `___` Normal Form?

Answer 38

Second (Normal Form)

Question 39

3rd Normal Form has the feature of 'no transitive dependencies' - what does this mean?

Answer 39

All fields must only be determinable by the primary key, not by non-key attributes. ## Footnote Example - Consider a table for employees with columns: EmployeeID (primary key), Department, Manager. If Department is dependent on EmployeeID, and Manager is dependent on Department, then there's a transitive dependency. To eliminate this, you'd create a new table for Departments with columns: DepartmentID (primary key), Department, Manager.

Question 40

What is a CTE? | In database context.

Answer 40

Common Table Expression - The result set of a query which exists temporarily for use in the context of a larger subsequent query.

Question 41

What is a window function?

Answer 41

A function type in SQL that allows calculations across multiple rows that are related to the current row.

Question 42

What are the relationship types in ERDs?

Answer 42

* one-to-one * one-to-many/many-to-one * many-to-many

Question 43

Which notation is typically used to denote relationship type in ERDs?

Answer 43

Crow's foot notation

Question 44

How do you auto-increment the primary key with the addition of a new entry in a database table? | PostgreSQL

Answer 44

`GENERATED ALWAYS AS IDENTITY` when creating the primary key column in a `CREATE TABLE` statement.