DB SQL Interview Questions

Question 1

What is a database

Answer 1

A database is an organized collection of data.

Question 2

Join (inner vs left)

Answer 2

Inner gets the intersection of both tables, left gets that plus all of table a.

Question 3

Aggregation Grouping using SUM

Answer 3

SELECT department, SUM(sales) AS “Total sales” FROM order_details GROUP BY department;

Question 4

Aggregation Grouping using MAX

Answer 4

SELECT working_area, MAX(commission)
FROM agents
GROUP BY working_area;

Question 5

Aggregation Grouping using MAX with Order By

Answer 5

SELECT cust_city, cust_country, 
MAX(outstanding_amt) 
FROM customer 
GROUP BY cust_country, cust_city 
ORDER BY cust_city;

Question 6

What a “having” statement does, and when to use it

Answer 6

Having was added to allow for aggregate functions:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
e.g.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Question 7

Coalesce (what it does and when you’d use it)

Answer 7

Returns first non null value in list. Use when you need the first element that is available in list of possible null values.

Question 8

What is an analytic function

Answer 8

Computes values over a group of rows. Returns 1 value per row.

Question 9

What is an aggregate function

Answer 9

Computes values over a group of rows. Returns 1 value per group.

Question 10

Example of an analytic function

Answer 10

PARTITION BY: divides rows into partitions
e.g.
SELECT firstname, department, startdate,
RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
FROM Employees;

Question 11

Example of an aggregate function

Answer 11

COUNT, MIN and MAX are examples of aggregate functions.

Question 12

What is the difference between UNION and UNION ALL

Answer 12

UNION removes duplicates, UNION ALL does not.

Question 13

Give an example of a subquery

Answer 13

SELECT column-names
  FROM table-name1
 WHERE value IN (SELECT column-name
                   FROM table-name2 
                  WHERE condition)