SQL (Structured Query Language) is essential for managing and manipulating relational databases. Mastery of SQL is crucial for software developers, particularly in data-driven roles. This guide covers fundamental and advanced SQL concepts, providing a thorough foundation for interview preparation.
Basic SQL Operations
SELECT: Used to query data from one or more tables.
SELECT first_name, last_name FROM employees;WHERE: Filters records that meet specific conditions.
SELECT first_name, last_name FROM employees WHERE department = 'Sales';ORDER BY: Sorts the result set of a query.
SELECT first_name, last_name FROM employees ORDER BY last_name ASC;LIMIT: Restricts the number of records returned, which is useful for pagination.
SELECT first_name FROM employees LIMIT 10;OFFSET: Skips a specific number of rows before beginning to return rows, which is useful for pagination.
SELECT first_name FROM employees LIMIT 10 OFFSET 5;Advanced SQL Operations
JOINs: Combine rows from two or more tables based on related columns.
INNER JOIN: Returns records with matching values in both tables.
SELECT e.first_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id;LEFT JOIN: Returns all records from the left table and matched records from the right table.
SELECT e.first_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;RIGHT JOIN: Returns all records from the right table and matched records from the left table.
ELECT e.first_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;FULL JOIN: Returns all records when there is a match in either left or right table records.
SELECT e.first_name, d.department_name FROM employees e FULL JOIN departments d ON e.department_id = d.id;
Filtering and Aggregation
DISTINCT: Returns unique values.
SELECT DISTINCT department FROM employees;GROUP BY: Groups rows that have the same values in specified columns.
SELECT department, COUNT(*)
FROM employees
GROUP BY department;HAVING: Filters groups based on a condition after aggregation.
SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department
HAVING COUNT(employee_id) > 5;COUNT: Counts the number of rows.
SELECT COUNT(employee_id) FROM employees;SUM: Calculates the total sum.
SELECT SUM(salary) FROM employees;AVG: Calculates the average value.
SELECT AVG(salary) FROM employees;MAX/MIN: Returns the maximum or minimum value.
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;Advanced Filtering with ANY, ALL, NONE, and IN
ANY: Compares a value to any value in a list or subquery.
SELECT first_name FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'HR');ALL: Compares a value to all values in a list or subquery.
SELECT first_name FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'HR');IN: Checks if a value matches any value in a list or subquery.
SELECT first_name FROM employees
WHERE department IN ('Sales', 'Marketing');NOT IN: Checks if a value does not match any value in a list or subquery.
SELECT first_name FROM employees
WHERE department NOT IN ('Sales', 'Marketing');Advanced Concepts
Aliases: Temporary names for tables or columns to improve readability.
SELECT first_name AS fname FROM employees;Subqueries: Nested queries used within another query.
SELECT first_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);UNION: Combines result sets of two or more SELECT statements, removing duplicates.
SELECT first_name FROM employees
UNION
SELECT first_name FROM contractors;Indexes: Used to speed up the retrieval of rows. Understanding the creation and use of indexes can optimize query performance.
Transactions: Execute a sequence of SQL statements as a single unit of work.
BEGIN;
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';
COMMIT;Data Definition Language (DDL)
CREATE: Creates a new table or database.
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50)
);ALTER: Modifies an existing database object.
ALTER TABLE employees ADD hire_date DATE;DROP: Deletes a table or database.
DROP TABLE employees;Constraints
PRIMARY KEY: Uniquely identifies each record in a table.
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);FOREIGN KEY: Ensures referential integrity for a record in another table using refereces.
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(100)
);
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);UNIQUE: Ensures all values in a column are different.
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);NOT NULL: Ensures a column cannot have a NULL value.
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);CHECK: Ensures that all values in a column satisfy a specific condition.
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
salary INT CHECK (salary > 0)
);Final Thoughts
Understanding SQL can open the doors to new opportunities and is a valuable skill in many technology-related fields. Mastering these SQL concepts will prepare you for a wide range of questions and scenarios in interviews. Practice writing and optimizing queries regularly and improve your skills. Good luck with your interview preparation!