SQL

Question 1

SQL combines features of what 3 things?

Answer 1

1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)

DMC

Question 2

What does CREATE TABLE statement do in SQL?

Answer 2

Defines a table schema, including:
* Attributes
* Constraints
* Default values

Question 2

How does SQL handle NULL values?

Answer 2

Using three-valued logic: TRUE, FALSE, and UNKNOWN

Question 3

What is the difference between PRIMARY KEY and UNIQUE?

Answer 3

• PRIMARY KEY: ensures uniqueness and non-null values for a single attribute
• UNIQUE: Allows nulls and can apply to multiple attributes

Question 4

What is the purpose of the WHERE clause

Answer 4

Filter rows based on conditions in SELECT, UPDATE and DELETE statements

Question 5

COUNT(*) vs COUNT(column)

Answer 5

‘*’ - counts all rows
column - Excludes NULL values in the specified column

Question 6

What is an inner join?

Answer 6

Combines rows from two tables where there is a match on the join condition

Question 7

Left Outer Join

Answer 7

Combines rows from two tables including all the rows from the first table, even if there is no match in the second. (NULLs in right table)

Question 8

What is the purpose of the GROUP BY clause

Answer 8

Groups rows with identical values (in specified columns) for aggregate calculations

Question 9

How does the HAVING clause differ from WHERE?

Answer 9

WHERE filters before aggregation, HAVING filters after aggregation

Question 10

What is a view in SQL

Answer 10

Virtual table created by storing a query, dynamically evaluated each time it is referenced

Question 11

Subquery vs CTE

Answer 11

• Subquery is embedded within another query
• CTE is temporary result set with a name, used for readability and reuse

Question 12

Funcitonal dependency

Answer 12

Relationship where one attribute uniquely determines another

plantCode —> plantName

Question 13

Main goals of normalization

Answer 13

1. eliminate redundancy
2. prevent anomalies
3. ensure data consistency

Question 14

DISTINCT

Answer 14

Removes duplicate rows from query result

Question 15

How do aggregate functions handle NULL values?

Answer 15

They ignore NULLs unless explicity included using COUNT(*)