Meta DE Technical Screen

Question 1

What is SQL?

Answer 1

Structured Query Language is used to store, retrieve, filter, and analyze data in relational databases.

Question 2

What is PostgreSQL?

Answer 2

An open-source relational database system that uses SQL.

Question 3

What is a database?

Answer 3

A collection of tables that store related data.

Question 4

What is a table?

Answer 4

A structured set of rows and columns.

Question 5

What is a row?

Answer 5

one record

Question 6

What is a column?

Answer 6

one attribute of the data

Question 7

What is the basic SQL query order?

Answer 7

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

Question 8

What does SELECT do?

Answer 8

chooses which columns to return

SELECT name, age FROM users;

Question 9

What does FROM do?

Answer 9

specifies the table to query

Question 10

What does SELECT * do?

Answer 10

returns all columns

Question 11

What does WHERE do?

Answer 11

filters rows based on conditions

WHERE age > 21

Question 12

What are the comparison operators?

Answer 12

=, !=, <, >, <=, >=

Question 13

What are the logical operators?

Answer 13

AND, OR, NOT

Question 14

What does BETWEEN do?

Answer 14

WHERE age BETWEEN 18 AND 30

Question 15

What does IN do?

Answer 15

specifies constraints
WHERE country IN (‘US’, ‘CA’)

Question 16

What does LIKE do?

Answer 16

contains portions of strings to match

WHERE email LIKE ‘%@gmail.com’

Question 17

What is a NULL?

Answer 17

missing or unknown value

Question 18

Why can’t we use = NULL?

Answer 18

NULL is not equal to anything, even NULL

Question 19

What is the correct way to check NULL?

Answer 19

WHERE column IS NULL
WHERE column IS NOT NULL

Question 20

What is COALESCE?

Answer 20

replace NULL with a default value

ex: COALESCE(score, 0)

Question 21

What does ORDER BY do?

Answer 21

sorts results

ex: ORDER BY created_at DESC

Question 22

What is ASC vs DESC?

Answer 22

ascending vs descending

Question 23

What does LIMIT do?

Answer 23

limits the number of rows returned

ex: LIMIT 10

Question 24

What is aggregation?

Answer 24

combining multiple rows into a single value

Question 25

What are some common aggregation functions?

Answer 25

COUNT() SUM() AVG() MIN() MAX()

Question 26

What does COUNT(*) do?

Answer 26

counts all rows, including NULLs

Question 27

What does COUNT(column) do?

Answer 27

counts non-NULL values

Question 28

What does GROUP BY do?

Answer 28

groups rows so aggregations can be applied per group

Question 29

What is an example of GROUP BY?

Answer 29

SELECT country, COUNT(*) FROM users GROUP BY country;

Question 30

What is the rule of using GROUP BY?

Answer 30

ever column in SELECT must be either in GROUP BY or inside an aggregation function

Question 31

What does HAVING do?

Answer 31

filters aggregated results ex: HAVING COUNT(*) > 5

Question 32

When do you use WHERE vs HAVING?

Answer 32

WHERE - before aggregation HAVING - after aggregation

Question 33

Why do joins exist?

Answer 33

data is split across tables to reduce duplication

Question 34

What is an INNER JOIN?

Answer 34

only matching rows

Question 35

What is an example of an INNER JOIN?

Answer 35

FROM orders o JOIN customers c ON o.customer_id = c.customer_id

Question 36

What is a LEFT JOIN?

Answer 36

all rows from left table + matches from right

Question 37

What is a RIGHT JOIN?

Answer 37

all rows from right table + matches from left

Question 38

What is a FULL OUTER JOIN?

Answer 38

all rows from both tables

Question 39

What happens when no match exists?

Answer 39

NULL values appear

Question 40

What is a table alias?

Answer 40

a way to identify a table usually using a single letter ex: FROM users u

Question 41

What is a column alias?

Answer 41

renaming a column name when performing some type of operation ex: SELECT COUNT(*) AS user_count

Question 42

What is UNION?

Answer 42

combines results and removes duplicates

Question 43

What is UNION ALL?

Answer 43

combines results and keeps duplicates(faster)

Question 44

What is the requirement for UNION?

Answer 44

same number of columns and compatible types

Question 45

What is a subquery?

Answer 45

a query inside another query

Question 46

What is an example of a subquery?

Answer 46

SELECT * FROM users WHERE age > ( SELECT AVG(age) FROM users );

Question 47

Why do we use subqueries?

Answer 47

when logic is easier to express step by step

Question 48

What is a CASE?

Answer 48

conditional logic in SQL ex: CASE WHEN score >= 90 THEN 'High' ELSE 'Low' END

Question 49

What is a window function?

Answer 49

performs calculations across rows without collapsing them

Question 50

What does OVER() do?

Answer 50

defines the window

Question 51

How is ROW_NUMBER used?

Answer 51

ROW_NUMBER() OVER (ORDER BY score DESC) unique ranking, no ties

Question 52

How do we use PARTITION BY?

Answer 52

PARTITION BY user_id

Question 53

How do we do string concatenation?

Answer 53

first_name || ' ' || last_name

Question 54

What is a primary key?

Answer 54

A column (or set of columns) that uniquely identifies each row

Question 55

What is a foreign key?

Answer 55

a column that references a primary key in another table

Question 56

What is an example of performing an inner join?

Answer 56

SELECT * FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id;

Question 57

What is a CROSS JOIN?

Answer 57

a cartesian product (every row paired with every row)

Question 58

Why is a cross join dangerous?

Answer 58

it has exponential growth

Question 59

What is the memory trick for WHERE vs HAVING?

Answer 59

WHERE -> rows HAVING -> groups

Question 60

What requires GROUP BY?

Answer 60

any non-aggregated column in SELECT

Question 61

What is a correlated subquery?

Answer 61

a subquery that references columns from the outer query

Question 62

What is an example of a correlated subquery?

Answer 62

SELECT * FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department );

Question 63

What is RANK?

Answer 63

ties allowed, gaps in ranking

Question 64

What is DENSE_RANK?

Answer 64

ties allowed, no gaps ex: DENSE_RANK() OVER (PARTITION BY user_id ORDER BY score DESC)