Introduction to SQL
Structured Query Language (SQL) is the standard language for interacting with relational databases. This guide provides a fundamental understanding of SQL syntax, necessary for retrieving and manipulating data stored in databases.
Setting Up a Database
Creating a Database
Before you can run SQL queries, you need to set up a database. Here is how you create a new database:
CREATE DATABASE my_database;
Using a Database
Switch to the database you’ve created:
USE my_database;
Creating Tables
Creating a Table
Tables are the primary structure in SQL for storing data. Here’s how you create a table with columns for id, name, and age:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT
);
Inserting Data into a Table
Once the table is created, you can insert data into it:
INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 25);
Basic SQL Queries
Selecting Data
To retrieve data from the table, use the SELECT statement:
SELECT * FROM users;
You can also specify particular columns:
SELECT name, age FROM users;
Filtering Data
Use the WHERE clause to filter records:
SELECT * FROM users WHERE age > 25;
Updating Data
To modify existing records, use the UPDATE statement:
UPDATE users SET age = 31 WHERE name = 'Alice';
Deleting Data
To remove records from the table, use the DELETE statement:
DELETE FROM users WHERE name = 'Bob';
Structuring Data with Constraints
Adding Constraints
Constraints ensure the accuracy and reliability of data in the table. You can add constraints when creating a table. Example of adding a NOT NULL constraint:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT CHECK (age >= 0)
);
Relationships Between Tables
Foreign Key
Define relationships between tables using foreign keys.
Creating a new table with a foreign key
Assume you have another table called orders:
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
product VARCHAR(100),
FOREIGN KEY (user_id) REFERENCES users(id)
);
Joining Tables
To combine records from two or more tables, use join operations. The following example is a simple inner join between users and orders:
SELECT users.name, orders.product
FROM users
JOIN orders ON users.id = orders.user_id;
Conclusion
This introduction covers basic SQL operations such as creating databases and tables, inserting, updating, and deleting records, filtering results, and defining relationships using foreign keys. SQL is a comprehensive language rich in features to manage and manipulate your data efficiently.
Basic SQL Queries
1. Selecting Data
-- Select all columns from a table
SELECT * FROM employees;
-- Select specific columns from a table
SELECT first_name, last_name, department FROM employees;
-- Select with a WHERE clause to filter records
SELECT first_name, last_name FROM employees WHERE department = 'Sales';
-- Select with multiple conditions using AND/OR
SELECT first_name, last_name FROM employees WHERE department = 'Sales' AND salary > 50000;
2. Using Aliases
-- Use column aliases to rename output columns
SELECT first_name AS fname, last_name AS lname FROM employees;
-- Use table alias to simplify table reference
SELECT e.first_name, e.last_name FROM employees AS e;
3. Sorting Data
-- Order results by a specific column
SELECT first_name, last_name FROM employees ORDER BY last_name;
-- Order results by multiple columns
SELECT first_name, last_name, department FROM employees ORDER BY department, last_name;
-- Order results in descending order
SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC;
4. Aggregating Data
-- Count the number of rows
SELECT COUNT(*) FROM employees;
-- Find the minimum, maximum, average, and sum of a column
SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary, AVG(salary) AS avg_salary, SUM(salary) AS total_salary FROM employees;
-- Group by a column and perform aggregation
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department;
-- Filter groups using HAVING
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 60000;
5. Joining Tables
-- Inner Join
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
-- Left Join
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
-- Right Join
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;
-- Full Outer Join
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.id;
6. Subqueries
-- Subquery in WHERE clause
SELECT first_name, last_name FROM employees
WHERE department_id = (SELECT id FROM departments WHERE department_name = 'Sales');
-- Subquery in FROM clause
SELECT avg_salary FROM (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) AS dept_avg;
7. Modifying Data
-- Insert new record
INSERT INTO employees (first_name, last_name, department_id, salary)
VALUES ('John', 'Doe', 1, 55000);
-- Update existing record
UPDATE employees
SET salary = 60000
WHERE last_name = 'Doe';
-- Delete record
DELETE FROM employees
WHERE last_name = 'Doe';
8. Creating and Managing Tables
-- Create a new table
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100),
department_id INT,
salary DECIMAL(10, 2)
);
-- Alter existing table
ALTER TABLE employees ADD COLUMN hire_date DATE;
-- Drop a table
DROP TABLE employees;
This guide covers essential SQL queries you can apply directly in your SQL database management tasks. Adapt these queries to your specific requirements.
Filtering and Sorting Data in SQL
Filtering and sorting data are crucial tasks in SQL when you need to retrieve specific sets of information and present them in a particular order.
Filtering Data
Filtering data in SQL is typically done using the WHERE clause. This clause specifies conditions that the records must meet to be selected.
Example:
Assuming we have a table employees with the following structure:
| employee_id | first_name | last_name | department | salary |
|---|---|---|---|---|
| 1 | John | Doe | Sales | 70000 |
| 2 | Jane | Smith | Marketing | 75000 |
| 3 | Emily | Johnson | IT | 65000 |
| 4 | Michael | Brown | Sales | 72000 |
| 5 | Sarah | Davis | Marketing | 71000 |
Query:
Retrieve all employees from the Sales department.
SELECT *
FROM employees
WHERE department = 'Sales';
Result:
| employee_id | first_name | last_name | department | salary |
|---|---|---|---|---|
| 1 | John | Doe | Sales | 70000 |
| 4 | Michael | Brown | Sales | 72000 |
Sorting Data
Sorting data is done using the ORDER BY clause, which sorts the result set by one or more columns.
Example:
Sort the employees by salary in descending order.
SELECT *
FROM employees
ORDER BY salary DESC;
Result:
| employee_id | first_name | last_name | department | salary |
|---|---|---|---|---|
| 2 | Jane | Smith | Marketing | 75000 |
| 4 | Michael | Brown | Sales | 72000 |
| 5 | Sarah | Davis | Marketing | 71000 |
| 1 | John | Doe | Sales | 70000 |
| 3 | Emily | Johnson | IT | 65000 |
Combining Filtering and Sorting
You can combine both filtering and sorting in a single query.
Example:
Retrieve all employees from the Marketing department and sort them by salary in ascending order.
SELECT *
FROM employees
WHERE department = 'Marketing'
ORDER BY salary ASC;
Result:
| employee_id | first_name | last_name | department | salary |
|---|---|---|---|---|
| 5 | Sarah | Davis | Marketing | 71000 |
| 2 | Jane | Smith | Marketing | 75000 |
Conclusion
By mastering filtering and sorting in SQL, you can efficiently retrieve and organize the data you need, making the results of your queries both precise and useful.
Joining Tables in SQL
Joining tables in SQL is essential for querying data that spans multiple tables. There are several types of joins, each serving a different purpose. This section outlines practical implementations for each of the main types of joins.
INNER JOIN
An INNER JOIN returns rows when there is a match in both tables.
SELECT
employees.employee_id,
employees.first_name,
employees.last_name,
departments.department_name
FROM
employees
INNER JOIN
departments
ON
employees.department_id = departments.department_id;
LEFT JOIN (or LEFT OUTER JOIN)
A LEFT JOIN returns all rows from the left table, and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.
SELECT
employees.employee_id,
employees.first_name,
employees.last_name,
departments.department_name
FROM
employees
LEFT JOIN
departments
ON
employees.department_id = departments.department_id;
RIGHT JOIN (or RIGHT OUTER JOIN)
A RIGHT JOIN returns all rows from the right table, and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.
SELECT
employees.employee_id,
employees.first_name,
employees.last_name,
departments.department_name
FROM
employees
RIGHT JOIN
departments
ON
employees.department_id = departments.department_id;
FULL JOIN (or FULL OUTER JOIN)
A FULL JOIN returns rows when there is a match in one of the tables. If there is no match, NULL values are returned for columns from the table without a match.
SELECT
employees.employee_id,
employees.first_name,
employees.last_name,
departments.department_name
FROM
employees
FULL JOIN
departments
ON
employees.department_id = departments.department_id;
CROSS JOIN
A CROSS JOIN returns the Cartesian product of the two tables, i.e., it returns all possible combinations of rows from the two tables.
SELECT
employees.employee_id,
employees.first_name,
employees.last_name,
departments.department_name
FROM
employees
CROSS JOIN
departments;
NATURAL JOIN
A NATURAL JOIN is based on all columns in the two tables that have the same name and selects rows with equal values in the relevant columns.
SELECT
employees.employee_id,
employees.first_name,
employees.last_name,
departments.department_name
FROM
employees
NATURAL JOIN
departments;
USING Clause with JOIN
The USING clause is used to specify the column to join on when the column names are the same in both tables.
SELECT
employees.employee_id,
employees.first_name,
employees.last_name,
departments.department_name
FROM
employees
INNER JOIN
departments
USING
(department_id);
These SQL join queries allow you to combine data from different tables in various ways, providing flexible and powerful means to query and analyze relational data.
Aggregation and Grouping in SQL
When working with databases, it’s often useful to summarize data and perform calculations across multiple records. This is where SQL’s aggregation and grouping capabilities come into play. This section will cover several key concepts and practical implementations concerning these capabilities.
Aggregation Functions
SQL provides various aggregation functions to perform calculations on a set of values. Some commonly used aggregation functions include:
COUNT(): Counts the number of rows.SUM(): Calculates the total sum of a numeric column.AVG(): Calculates the average value of a numeric column.MIN(): Finds the minimum value in a column.MAX(): Finds the maximum value in a column.
Examples
COUNT(): Count the total number of employees in a company.
SELECT COUNT(*) AS total_employees
FROM employees;SUM(): Calculate the total salary expense.
SELECT SUM(salary) AS total_salary
FROM employees;AVG(): Find the average age of employees.
SELECT AVG(age) AS average_age
FROM employees;MIN(): Get the lowest salary.
SELECT MIN(salary) AS lowest_salary
FROM employees;MAX(): Get the highest salary.
SELECT MAX(salary) AS highest_salary
FROM employees;
Grouping Data
Grouping data with the GROUP BY clause is essential when you want to aggregate data based on one or more columns. The GROUP BY clause groups the rows that have the same values into summary rows, like “find the number of customers in each country.”
Syntax
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
Examples
Count the number of employees in each department:
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department;Calculate the total sales for each product:
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;Find the average salary by job title:
SELECT job_title, AVG(salary) AS average_salary
FROM employees
GROUP BY job_title;
Filtering Grouped Data
To filter grouped data, the HAVING clause is used, since the WHERE clause cannot be used with aggregate functions.
Syntax
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING aggregate_function(column2) operator value;
Examples
Count departments with more than 10 employees:
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;Find products with total sales less than $1000:
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id
HAVING SUM(sales_amount) < 1000;Average salary by job title, but only include titles with an average salary above $50000:
SELECT job_title, AVG(salary) AS average_salary
FROM employees
GROUP BY job_title
HAVING AVG(salary) > 50000;
Combining Grouping with Sorting (Optional Content)
You can also combine the GROUP BY clause with ORDER BY to sort the aggregated results.
Examples
Count the number of employees in each department and sort by the number of employees in descending order:
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
ORDER BY num_employees DESC;Find the total sales for each product and sort by product ID:
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id
ORDER BY product_id;
By understanding and utilizing SQL’s aggregation functions along with the GROUP BY and HAVING clauses, you can perform complex data summarizations and analysis directly within your SQL queries.
Add these concepts to your SQL skill set to see your data analysis capabilities grow significantly.
Subqueries and Nested Queries in SQL
Definition
Subqueries, also known as nested queries, are queries within queries. A subquery is usually embedded in the SELECT, FROM, WHERE, or HAVING clause of another SQL query.
Basic Structure
Here’s a general structure:
SELECT column1, column2, ...
FROM table1
WHERE column IN (SELECT column FROM table2 WHERE condition);
Types of Subqueries
- Single-row subquery: Returns zero or one row.
- Multi-row subquery: Returns one or more rows.
- Correlated subquery: References columns from the outer query.
Examples
Single-Row Subquery
Find employees who earn above the average salary.
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Multi-Row Subquery
Find products with a price higher than any product in category “Electronics”.
SELECT product_id, product_name, price
FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'Electronics');
Correlated Subquery
Find customers who have placed at least one order.
SELECT customer_id, customer_name
FROM customers
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id);
Nested Subqueries
Sometimes, subqueries themselves contain additional subqueries.
SELECT product_id, product_name
FROM products
WHERE category_id = (SELECT category_id
FROM categories
WHERE category_name = (SELECT category_name
FROM special_categories
WHERE special_category_id = 1));
Using Subqueries in Different Clauses
Subquery in SELECT Clause
Calculate the price to sales ratio for each product.
SELECT product_id, product_name, (price / (SELECT SUM(price) FROM sales WHERE sales.product_id = products.product_id)) as price_to_sales_ratio
FROM products;
Subquery in FROM Clause
Find the maximum salary in each department and the respective department name.
SELECT d.department_name, max_salaries.max_salary
FROM
(SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id) AS max_salaries
JOIN departments AS d ON max_salaries.department_id = d.department_id;
Subquery in HAVING Clause
Get departments with a total salary budget greater than $1,000,000.
SELECT department_id, SUM(salary) as total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > (SELECT 1000000);
Conclusion
Subqueries and nested queries are powerful tools in SQL for creating more flexible and dynamic queries. Use them wisely to break complex queries into easier-to-understand parts, allowing for more efficient and effective data retrieval.
Managing Database Objects
Managing database objects involves creating, altering, and deleting tables, indexes, views, and other schema-related entities. Below are practical implementations of managing these common database objects.
Creating Tables
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
BirthDate DATE,
HireDate DATE,
DepartmentID INT,
Salary DECIMAL(10, 2)
);
Altering Tables
Adding a new column:
ALTER TABLE Employees
ADD Email VARCHAR(100);
Modifying an existing column:
ALTER TABLE Employees
MODIFY COLUMN Salary DECIMAL(12, 2);
Dropping a column:
ALTER TABLE Employees
DROP COLUMN Email;
Dropping Tables
Deleting an entire table:
DROP TABLE Employees;
Creating Indexes
Creating a simple index:
CREATE INDEX idx_lastname
ON Employees (LastName);
Creating a unique index:
CREATE UNIQUE INDEX idx_unique_email
ON Employees (Email);
Dropping Indexes
DROP INDEX idx_lastname ON Employees;
Creating Views
Creating a view:
CREATE VIEW EmployeeDetails AS
SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
FROM Employees
WHERE Salary > 50000;
Altering Views
Updating a view (note that the definition needs to be entirely recreated):
CREATE OR REPLACE VIEW EmployeeDetails AS
SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary, HireDate
FROM Employees
WHERE Salary > 50000;
Dropping Views
DROP VIEW EmployeeDetails;
Creating Stored Procedures
Creating a stored procedure:
CREATE PROCEDURE GetEmployeeDetails (IN empID INT)
BEGIN
SELECT * FROM Employees WHERE EmployeeID = empID;
END;
Altering Stored Procedures
Updating a stored procedure:
DROP PROCEDURE IF EXISTS GetEmployeeDetails;
CREATE PROCEDURE GetEmployeeDetails (IN empID INT)
BEGIN
SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
FROM Employees
WHERE EmployeeID = empID;
END;
Dropping Stored Procedures
DROP PROCEDURE IF EXISTS GetEmployeeDetails;
Creating Triggers
Creating a trigger:
CREATE TRIGGER BeforeInsertEmployee
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
IF NEW.Salary < 30000 THEN
SET NEW.Salary = 30000;
END IF;
END;
Dropping Triggers
DROP TRIGGER BeforeInsertEmployee;
By following this guide, you can efficiently manage database objects using SQL. This includes creation, alteration, and deletion of tables, indexes, views, stored procedures, and triggers to maintain and optimize database performance and integrity.
