Flashcards in DB SQL Interview Questions Deck (13):
What is a database
A database is an organized collection of data.
Join (inner vs left)
Inner gets the intersection of both tables, left gets that plus all of table a.
Aggregation Grouping using SUM
SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department;
Aggregation Grouping using MAX
SELECT working_area, MAX(commission)
GROUP BY working_area;
Aggregation Grouping using MAX with Order By
SELECT cust_city, cust_country,
GROUP BY cust_country, cust_city
ORDER BY cust_city;
What a "having" statement does, and when to use it
Having was added to allow for aggregate functions:
SELECT COUNT(CustomerID), Country
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
GROUP BY column_name(s)
ORDER BY column_name(s);
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.
What is an analytic function
Computes values over a group of rows. Returns 1 value per row.
What is an aggregate function
Computes values over a group of rows. Returns 1 value per group.
Example of an analytic function
PARTITION BY: divides rows into partitions
SELECT firstname, department, startdate,
RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
Example of an aggregate function
COUNT, MIN and MAX are examples of aggregate functions.
What is the difference between UNION and UNION ALL
UNION removes duplicates, UNION ALL does not.