SQL

Question 1

Calculate median in SQL

Answer 1

with t1 as (
select
row_number() as rn,
count() over () as cnt,
round(count() over () / 2) as mid
from table
)

select * from t1
where rn = mid
or (cnt % 2 = 0 and rn = mid + 1)

– Pull where rn = md, so the middle number. This works if dataset is odd.
– If dataset is even, pull where the rn is mid AND another row mid + 1

Question 2

Tips for Optimizing a Query

Answer 2

• Minimize full table scans
• Filter data
• Use CTE’s
• Temp tables
• Avoid joins
• User inner (limits result set)
• No select *
• Avoid distinct / union
• Materialize frequently used queries

Question 3

Get difference between two times

Answer 3

timestampsidff(
second,
time1,
time2
)

Question 4

Get min/max between two or more columns

Answer 4

• least()
• greatest()

Question 5

What type of language is SQL

Answer 5

Declarative (as opposed to procedural)

You describe the results you want - NOT how to do it

Question 6

Group strings

Answer 6

group_contact()
list_agg()

Question 7

Scalar function to add strings together

Answer 7

concat()

Question 8

Result of FULL OUTER JOIN where join keys are null

Answer 8

All from A and all from B but NOT where A and B intersect

Question 9

Result of FULL OUTER JOIN

Answer 9

All from A and B and intersection

Question 10

count(*) vs count(1) vs count(col)

Answer 10

• count(*): All rows including nulls
• count(1): All rows including nulls
• count(col): All rows EXCEPT nulls

Question 11

Find start and end of continuous ranges

1
2
3
7
8

Answer 11

with t1 as (
select
log_id,
log_id - row_number() over (order by log_id asc) as grp
from logs
)

select
min(log_id) as start_id,
max(log_id) as end_id

from t1
group by grp

Question 12

Recursive function in SQL

Answer 12

with recursive t1 as (

select 1 as col1
union all
select col1 + 1 from t1 where col1 < 10

)

select * from t1

Question 13

Partial Dependencies

Answer 13

When non-key is dependent on part of a candidate key

studentid | projectno | studentname | projectname

studentname and projectname are both dependent on the ids in the table.

Problem with this is if we don’t have any students assigned to a project, then we can’t get the project name from anywhere

Question 14

Pros of 3NF

Answer 14

• Fast writes
• Better organization
• Reduction of redundant data

Question 15

2nd Normal Form Rules

Answer 15

• Be in 1NF
• Have no partial dependencies

Question 16

Fan-out Trap

Answer 16

Two 1-to-many joins

Question 17

Where to use an Index

Answer 17

• Typically on columns in WHERE and JOIN clauses
• Column that appears in max number of queries

Question 18

When would Indexing fail

Answer 18

• Have too many indexes
• If index column is hardly queried
• Too many DML actions

Question 19

Nested Joins

Answer 19

For every value in OUTER, look for it in INNER

Can be extremely slow

Question 20

Cache

Answer 20

Temporarily storing an expressive operation to speed up results

Question 21

Repeatable Read Isolation Level

Answer 21

Can read within same transaction without anything changing

Question 22

Read Uncommitted Isolation Level

Answer 22

Can read uncommitted data (dirty reads)

Question 23

Read Committed Isolation Level

Answer 23

Each read gets latest data committed

Question 24

Purpose of database normalization

Answer 24

• Eliminating redundant data
• Ensure data dependencies make logical sense
• Prevent insert, update and removal anomalies

Question 25

Chasm Trap

Answer 25

When 2 many-to-one joins converge on a single table. Pathway between is broke

Question 26

Merge Join

Answer 26

Sort table on join columns

For each value in OUTER, iterate over INNER to find, but, stop when reach a value greater than what we’re searching for (since it’s in order)

Question 27

Deletion Anomaly

Answer 27

Deletion of unwanted data causes loss of desired data

If there are partial dependencies (i.e. teacher phone number in classes table), having no teachers with classes means we no longer have their phone number

Question 28

1st Normal Form Rules

Answer 28

• Each cell has a single value
• Column should be of all same type
• Unique column names

Question 29

Update Anomaly

Answer 29

Miss updating all records in a table

Question 30

Hash Join

Answer 30

Apply a hash function to both INNER and OUTER tables to create hash tables. Can then lookup each value in O(1)

Takes more time as the tables need to be hashed first

Question 31

Cardinality

Answer 31

• Uniqueness of data
• Low: More duplicates
• High: More unique

Question 32

Dirty reads

Answer 32

When a transaction reads data that is written to a concurrently uncommitted transaction

Question 33

3NF Cons

Answer 33

• Highly normalized
• Many joins
• Setup high

Question 34

Boyce-Codd Normal Form

Answer 34

• Non-prime attributes must depend on all candidate keys (super keys)

i.e. If a key is NOT the primary key, but it could have been

Question 35

3rd Normal From Rules

Answer 35

• Must be in 2NF
• No transative functional dependencies (when a non-primary key depends on another non-primary key)

Question 36

4th Normal Form

Answer 36

• Should not contain any multi-valued dependencies
• Looks like a cross join

A multi-valued dependency occurs when a single attribute determines more than one other attribute, and these attributes are independent of each other. For example, if a table stores the hobbies and skills of employees, and each employee can have multiple hobbies and skills, then there is a multi-valued dependency between the employee ID and the hobbies and skills. To achieve 4NF, we need to split the table into two separate tables, one for hobbies and one for skills, and link them with a foreign key.

Question 37

5th Normal Form

Answer 37

• Cannot look like a join of tables
• Basically, a table is in 5th normal form if it cannot be made into any smaller tables with different keys (most tables can obviously be made into smaller tables with the same key!).

Fifth normal form (5NF) is a level of database normalization that ensures that a table has no join dependencies. A join dependency occurs when a table can be decomposed into two or more smaller tables, and then reconstructed by joining them on their primary keys, without losing any information. For example, if a table stores the products, colors, and sizes that a company sells, and each product can have multiple colors and sizes, then there is a join dependency between the product ID, the color, and the size. To achieve 5NF, we need to split the table into three separate tables, one for products, one for colors, and one for sizes, and link them with a composite key. The benefit of 5NF is that it ensures that the database is fully normalized and has no redundancy or anomalies. The drawback of 5NF is that it may create too many tables and relationships, and make the database design more difficult to understand and maintain.

Question 38

Window functions RANGE vs ROW

Answer 38

Use ROWS when you want to define your window in terms of a specific number of rows, regardless of their values.

Use RANGE when you want to define your window based on the values in the ORDER BY column. But be cautious about non-uniform value distributions or duplicates!