SQL Flashcards
What is a regional database
DB that organises info into one or more tables
Name 2 ways to handle duplicate data points in an SQL query?
1) use the DISTINCT keyword
2) DELETE duplicate rows using ROWID with the MAX or MIN function
Give an example of how you would use DISTINCT
SELECT DISTINCT Name, ADDRESS
FROM CUSTOMERS
ORDER BY Name;
Give an example of how you would delete duplicates using ROWID
DELETE FROM Employee
WHERE ROWID NOT IN (
SELECT MAX(ROWID)
FROM Employee
GROUP BY Name, ADDRESS
);
What does Data aggregation involve ?
involves using aggregate functions like SUM(), AVG(), COUNT(), MIN(), and MAX()
What’s an example of data aggregation
SELECT department,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;
What’s an example of data aggregation
SELECT department,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;
Name 2 ways to handle missing data in SQL
1) COALESCE function
2) CASE statements
What’s a COALESCE function and give an example of it.
It returns the first non-null value in the list
SELECT id, COALESCE(salary, 0) AS salary FROM employees;
What are CASE statements and give an example of it.
They’re used handle missing values conditionally.
SELECT id,
CASE
WHEN salary IS NULL THEN 0
ELSE salary
END AS salary
FROM employees;
What’s is the ROLLUP Operator?
= an extension of the GROUP BY clause
It can be used to create subtotals and a grand total.
SELECT
department,
product,
SUM(sales) AS total_sales
FROM sales_data
GROUP BY ROLLUP(department, product);
How do you rank data in SQL?
Data engineers commonly rank values based on parameters such as sales and profit
SELECT
id,
sales,
RANK() OVER (ORDER BY sales DESC) AS rank
FROM bill;
What are Common Table Expressions (CTEs) in SQL?
CTEs are used to simplify complex joins and run subqueries. They help make SQL queries more readable and maintainable
