ChatGPT cards Flashcards
BEGINNER: What is the basic syntax for retrieving all columns from a table?
SELECT * FROM table_name;
BEGINNER: How do you select specific columns from a table?
SELECT column1, column2 FROM table_name;
BEGINNER: How do you filter rows where a column matches a specific value?
SELECT * FROM table_name WHERE column = ‘value’;
BEGINNER: How do you filter results using multiple conditions?
SELECT * FROM table_name WHERE column1 = ‘value’ AND column2 > 100;
BEGINNER: How do you sort query results in ascending order?
SELECT * FROM table_name ORDER BY column ASC;
BEGINNER: How do you return only the top 10 results of a query?
SELECT * FROM table_name LIMIT 10;
BEGINNER: How do you count the number of rows in a table?
SELECT COUNT(*) FROM table_name;
BEGINNER: How do you calculate the average of a numerical column?
SELECT AVG(column) FROM table_name;
BEGINNER: How would you get the names of all employees in the ‘Marketing’ department?
SELECT name FROM employees WHERE department = ‘Marketing’;
INTERMEDIATE: What is the difference between an INNER JOIN and a LEFT JOIN?
INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table and matches from the right, with NULLs for no matches.
INTERMEDIATE: How do you write an INNER JOIN between ‘orders’ and ‘customers’?
SELECT orders.order_id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
INTERMEDIATE: How do you group data and get aggregated values?
SELECT department, COUNT(*) FROM employees GROUP BY department;
INTERMEDIATE: How do you filter grouped results?
Use HAVING instead of WHERE, e.g., SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
INTERMEDIATE: What is a subquery and when is it useful?
A subquery is a query inside another query, useful for filtering based on calculations or other table lookups.
INTERMEDIATE: How do you select employees who earn more than the average salary?
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
INTERMEDIATE: How do you categorize employees into salary bands using CASE?
SELECT name, CASE WHEN salary < 40000 THEN ‘Low’ WHEN salary BETWEEN 40000 AND 70000 THEN ‘Medium’ ELSE ‘High’ END AS salary_band FROM employees;
INTERMEDIATE: How do you get total sales per region but only for regions with more than $1M in sales?
SELECT region, SUM(sales) AS total_sales FROM sales_data GROUP BY region HAVING total_sales > 1000000;
ADVANCED: What is a window function?
A function that performs calculations across a subset of rows related to the current row, without collapsing the result set.
ADVANCED: How do you rank employees based on salary within each department?
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;
ADVANCED: What is a CTE and why use it?
A Common Table Expression (CTE) is a temporary result set that improves readability and performance in complex queries.
ADVANCED: How do you create a CTE to find employees earning more than the average salary?
WITH avg_salary AS (SELECT AVG(salary) AS avg_sal FROM employees) SELECT name, salary FROM employees WHERE salary > (SELECT avg_sal FROM avg_salary);
ADVANCED: How do you improve query performance on large datasets?
Use indexes, avoid SELECT *, and use EXPLAIN ANALYZE to optimize queries.
ADVANCED: How do you create an index on the ‘email’ column in the ‘customers’ table?
CREATE INDEX idx_email ON customers(email);
ADVANCED: What is a CROSS JOIN?
A join that returns the Cartesian product of two tables, pairing every row from one table with every row from another.
ADVANCED: When would you use a SELF JOIN?
When working with hierarchical data, such as an employee reporting structure.
ADVANCED: How do you calculate a running total of sales per region, ordered by date?
SELECT region, sales_date, sales_amount, SUM(sales_amount) OVER (PARTITION BY region ORDER BY sales_date) AS running_total FROM sales_data;
