Essential SQL Concepts and Queries Guide
Uploaded by
Daniel RaphaelEssential SQL Concepts and Queries Guide
Uploaded by
Daniel RaphaelBasic SQL Questions
1. What is SQL, and why is it important for data analysis?
Answer:
SQL (Structured Query Language) is a programming language used to manage and manipulate
relational databases.
It is essential for data analysis as it allows analysts to retrieve, filter, aggregate, and transform
large datasets efficiently.
SQL helps in data extraction for reporting, identifying trends, and making data-driven decisions.
2. What is the difference between SQL and MySQL?
Answer:
SQL is a standard language for managing databases.
MySQL is a database management system (DBMS) that uses SQL as its query language.
SQL is universal, while MySQL is a specific implementation.
3. What are the different types of SQL commands?
Answer:
DML (Data Manipulation Language) – SELECT, INSERT, UPDATE, DELETE
DDL (Data Definition Language) – CREATE, ALTER, DROP, TRUNCATE
DCL (Data Control Language) – GRANT, REVOKE
TCL (Transaction Control Language) – COMMIT, ROLLBACK, SAVEPOINT
4. What is the difference between WHERE and HAVING clauses?
Answer:
WHERE is used to filter records before aggregation.
HAVING is used to filter aggregated results.
Example:
SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5;
SQL Queries & Joins
5. How do you retrieve all records from a table?
SELECT * FROM table_name;
6. Write a query to select the top 5 highest revenue taxpayers from a
taxpayer_records table.
Answer:
SELECT taxpayer_id, name, revenue
FROM taxpayer_records
ORDER BY revenue DESC
LIMIT 5;
7. What are JOINS in SQL? Explain the difference between INNER JOIN,
LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Answer:
INNER JOIN: Returns records with matching values in both tables.
LEFT JOIN: Returns all records from the left table and matched records from the right.
RIGHT JOIN: Returns all records from the right table and matched records from the
left.
FULL JOIN: Returns all records when there is a match in either table.
Example:
SELECT [Link], b.amount_paid
FROM taxpayer_details a
INNER JOIN tax_payments b
ON a.taxpayer_id = b.taxpayer_id;
8. Write an SQL query to join two tables:
Tables:
taxpayer_details (taxpayer_id, name, address)
tax_payments (taxpayer_id, amount_paid, payment_date)
Answer:
SELECT [Link], tp.amount_paid, tp.payment_date
FROM taxpayer_details td
INNER JOIN tax_payments tp
ON td.taxpayer_id = tp.taxpayer_id;
9. Explain SELF JOIN and CROSS JOIN with examples.
Answer:
SELF JOIN: A table joins itself.
SELECT [Link], [Link] AS colleague
FROM employees a
JOIN employees b
ON a.manager_id = b.employee_id;
CROSS JOIN: Cartesian product of two tables.
SELECT [Link], [Link]
FROM employees a
CROSS JOIN departments b;
10. What is the difference between UNION and UNION ALL?
Answer:
UNION removes duplicate records.
UNION ALL includes duplicates.
Example:
SELECT name FROM customers1
UNION
SELECT name FROM customers2;
Data Aggregation & Filtering
11. Write an SQL query to calculate the total tax collected for the year 2024 from
a tax_payments table.
Answer:
SELECT SUM(amount_paid) AS total_tax
FROM tax_payments
WHERE YEAR(payment_date) = 2024;
12. How do you find the second highest tax payment from the tax_payments table?
Answer:
SELECT MAX(amount_paid)
FROM tax_payments
WHERE amount_paid < (SELECT MAX(amount_paid) FROM tax_payments);
13. What is the difference between COUNT(), SUM(), AVG(), MIN(), and
MAX()?
Answer:
COUNT(): Counts records.
SUM(): Sums values.
AVG(): Calculates the average.
MIN(): Finds the minimum value.
MAX(): Finds the maximum value.
14. Write an SQL query to count the number of unique taxpayers in a given city.
Answer:
SELECT COUNT(DISTINCT taxpayer_id)
FROM taxpayer_details
WHERE city = 'Dar es Salaam';
15. Explain GROUP BY and how it works with HAVING.
Answer:
GROUP BY groups records based on a column.
HAVING filters aggregated results.
SELECT city, COUNT(*)
FROM taxpayers
GROUP BY city
HAVING COUNT(*) > 5;
Advanced SQL (Subqueries, Window Functions,
Optimization)
16. What is a subquery?
Answer:
A query inside another query.
SELECT name FROM taxpayers
WHERE revenue > (SELECT AVG(revenue) FROM taxpayers);
17. What is a CTE (Common Table Expression)?
Answer:
A temporary result set used in queries.
WITH top_taxpayers AS (
SELECT taxpayer_id, revenue
FROM taxpayers
ORDER BY revenue DESC
LIMIT 10
)
SELECT * FROM top_taxpayers;
18. What is a window function?
Answer:
Functions like RANK(), DENSE_RANK(), and ROW_NUMBER() operate over a subset of data.
SELECT taxpayer_id, revenue,
RANK() OVER (ORDER BY revenue DESC) AS rank
FROM taxpayers;
19. What is the difference between DELETE, TRUNCATE, and DROP?
Answer:
DELETE removes specific rows.
TRUNCATE removes all rows but keeps the table.
DROP removes the table structure.
1. What is an Index in SQL, and how does it improve
performance?
Answer:
An index is a database object that improves query performance by speeding up data retrieval. It
works like a book's index, allowing the database to find records faster instead of scanning the
entire table.
Types of Indexes:
Clustered Index: Sorts and stores data physically in the table (only one per table).
Non-Clustered Index: Stores pointers to data (multiple allowed).
Example:
CREATE INDEX idx_taxpayer_name
ON taxpayers(name);
This speeds up queries filtering by name:
SELECT * FROM taxpayers WHERE name = 'John Doe';
2. Explain ACID properties in transactions.
Answer:
ACID stands for Atomicity, Consistency, Isolation, and Durability—ensuring reliable
database transactions.
1. Atomicity: Transactions are all-or-nothing (either fully completed or fully rolled back).
2. Consistency: Ensures data integrity by maintaining valid states before and after
transactions.
3. Isolation: Transactions do not interfere with each other.
4. Durability: Changes are permanent after commit, even in case of system failure.
Example:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT; -- Ensures changes are saved permanently
3. What are stored procedures?
Answer:
A stored procedure is a precompiled SQL script that can be executed multiple times, improving
performance and security.
Example:
CREATE PROCEDURE GetTaxpayerPayments(@taxpayer_id INT)
AS
BEGIN
SELECT * FROM tax_payments WHERE taxpayer_id = @taxpayer_id;
END;
To execute:
EXEC GetTaxpayerPayments 101;
4. What is the difference between a Primary Key and a
Unique Key?
Feature Primary Key Unique Key
Uniqueness Ensures unique values Ensures unique values
NULL Allowed? No (not allowed) Yes (one NULL allowed)
Number per Table One per table Multiple allowed
Use Case Identifies a row Ensures uniqueness in a column
Example:
CREATE TABLE taxpayers (
taxpayer_id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE
);
5. How can you optimize a slow query?
Answer:
To improve performance, consider:
1. Use Indexing:
CREATE INDEX idx_taxpayer_revenue ON taxpayers(revenue);
2. Avoid SELECT * (Retrieve only required columns):
SELECT name, revenue FROM taxpayers WHERE revenue > 100000;
3. Use Joins Efficiently (Instead of Subqueries):
SELECT [Link], p.amount_paid
FROM taxpayers t
JOIN tax_payments p ON t.taxpayer_id = p.taxpayer_id;
4. Use WHERE Instead of HAVING for Filtering:
SELECT city, COUNT(*) FROM taxpayers
WHERE city = 'Dar es Salaam'
GROUP BY city;
5. Analyze Queries Using EXPLAIN PLAN:
EXPLAIN ANALYZE SELECT * FROM taxpayers WHERE revenue > 100000;