DB SQL Interview Questions Flashcards Preview

Full Stack Interview Questions > DB SQL Interview Questions > Flashcards

Flashcards in DB SQL Interview Questions Deck (13):
1

What is a database

A database is an organized collection of data.

2

Join (inner vs left)

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

3

Aggregation Grouping using SUM

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

4

Aggregation Grouping using MAX

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

5

Aggregation Grouping using MAX with Order By

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

6

What a "having" statement does, and when to use it

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);

7

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

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

8

What is an analytic function

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

9

What is an aggregate function

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

10

Example of an analytic function

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

11

Example of an aggregate function

COUNT, MIN and MAX are examples of aggregate functions.

12

What is the difference between UNION and UNION ALL

UNION removes duplicates, UNION ALL does not.

13

Give an example of a subquery

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