Introduction to Data Selection
Selecting data from databases is a fundamental task in data management and analysis. SQL (Structured Query Language) is commonly used to perform various data manipulations, including data selection. This chapter will provide practical examples to select data from databases using SQL.
Setting Up
To follow along with these examples, you should have access to an SQL database. Below is a demonstration of how to create a sample table and insert data into it:
-- Create a sample table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2),
HireDate DATE
);
-- Insert sample data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate) VALUES
(1, 'John', 'Doe', 'Engineering', 75000.00, '2020-01-15'),
(2, 'Jane', 'Smith', 'HR', 62000.00, '2019-03-23'),
(3, 'Emily', 'Jones', 'Marketing', 54000.00, '2018-08-30'),
(4, 'Michael', 'Brown', 'Engineering', 81000.00, '2017-11-12'),
(5, 'Sarah', 'Davis', 'Sales', 65000.00, '2021-06-19');
Selecting Data
Basic Selection
To select all columns from the Employees table:
SELECT * FROM Employees;
Selecting Specific Columns
To select specific columns, list the columns after the SELECT keyword:
SELECT FirstName, LastName, Department FROM Employees;
Using WHERE Clause
To filter data, use the WHERE clause:
SELECT * FROM Employees WHERE Department = 'Engineering';
Using Comparison Operators
You can use comparison operators like =, >, <, <=, >=, <>:
SELECT * FROM Employees WHERE Salary > 60000;
Using Logical Operators
Combine multiple conditions using AND, OR, and NOT:
-- Employees in Engineering with a salary greater than 70000
SELECT * FROM Employees
WHERE Department = 'Engineering' AND Salary > 70000;
-- Employees in HR or Marketing departments
SELECT * FROM Employees
WHERE Department = 'HR' OR Department = 'Marketing';
-- Employees not in Sales department
SELECT * FROM Employees
WHERE NOT Department = 'Sales';
Using Pattern Matching
To search for a pattern in column values, use the LIKE operator:
-- Employees whose first names start with 'J'
SELECT * FROM Employees
WHERE FirstName LIKE 'J%';
-- Employees whose last names contain 'o'
SELECT * FROM Employees
WHERE LastName LIKE '%o%';
Ordering Results
To order the results, use the ORDER BY clause:
-- Order by Salary ascending
SELECT * FROM Employees
ORDER BY Salary ASC;
-- Order by HireDate descending
SELECT * FROM Employees
ORDER BY HireDate DESC;
Limiting Results
To limit the number of rows returned, you can use LIMIT:
-- Return only the first 3 employees
SELECT * FROM Employees
LIMIT 3;
Conclusion
This introduction illustrates how to select and filter data from a relational database using SQL. These basic queries form the foundation for more advanced data manipulation techniques, which we will explore in subsequent chapters. Feel free to modify the provided examples to practice and understand how data selection works in SQL.
Basic SQL Select Queries
2.1. Selecting All Columns
To select all columns from a table, use the SELECT * FROM syntax.
SELECT * FROM employees;
This query retrieves every column from the employees table.
2.2. Selecting Specific Columns
You can specify particular columns to retrieve.
SELECT first_name, last_name, email FROM employees;
This query selects only the first_name, last_name, and email columns from the employees table.
2.3. Selecting with Column Aliases
Sometimes, it’s helpful to rename columns in your result.
SELECT first_name AS fname, last_name AS lname, email AS contact FROM employees;
This query renames the first_name to fname, last_name to lname, and email to contact in the result set.
2.4. Filtering Rows with WHERE
To filter the rows returned by your query, use the WHERE clause.
SELECT * FROM employees WHERE department_id = 10;
This query retrieves all columns from the employees table where the department_id is 10.
2.5. Using Logical Operators
Combine multiple conditions using logical operators AND, OR, and NOT.
SELECT * FROM employees
WHERE department_id = 10 AND salary > 50000;
This query returns employees from department 10 with a salary greater than 50,000.
2.6. Sorting Results
To sort the results, use the ORDER BY clause.
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
This query selects the first names, last names, and salaries from the employees table and orders the results by salary in descending order.
2.7. Limiting Results
To limit the number of rows returned, use the LIMIT clause.
SELECT * FROM employees LIMIT 5;
This query retrieves only the first 5 rows from the employees table.
2.8. Combining Multiple Clauses
You can combine multiple clauses for more complex queries.
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 10
ORDER BY salary DESC
LIMIT 3;
This query retrieves the first names, last names, and salaries of the top 3 highest-paid employees in department 10.
Conclusion
This guide covered the basics of SQL SELECT queries, including selecting columns, filtering rows, sorting, and limiting the results. You should now be able to perform basic data selection tasks in any SQL-supported database with these examples.
Filtering Results with WHERE Clauses
The WHERE clause is used in SQL to filter records. It helps in selecting only those records that fulfill a specified condition. Here is a set of practical implementations to demonstrate how to use WHERE clauses effectively.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Practical Examples
Example 1: Filtering by a Numeric Value
Assume we have a table named Employees with columns EmployeeID, FirstName, LastName, Age, and Department.
-- Retrieve employees who are 30 years old
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Age = 30;
Example 2: Filtering by a String Value
-- Retrieve employees who work in the 'HR' department
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'HR';
Example 3: Using Comparison Operators
-- Retrieve employees older than 25
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Age > 25;
Example 4: Using Logical Operators
-- Retrieve employees who are in the 'Sales' department and older than 28
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'Sales' AND Age > 28;
-- Retrieve employees who are in the 'Sales' department or older than 50
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'Sales' OR Age > 50;
Example 5: Filtering with LIKE Operator
The LIKE operator is used for pattern matching.
-- Retrieve employees whose first name starts with 'A'
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE FirstName LIKE 'A%';
-- Retrieve employees whose last name contains 'son'
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE LastName LIKE '%son%';
Example 6: Filtering with IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
-- Retrieve employees who are in the 'Sales', 'HR', or 'IT' department
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department IN ('Sales', 'HR', 'IT');
Example 7: Filtering with BETWEEN Operator
The BETWEEN operator selects values within a given range.
-- Retrieve employees whose age is between 25 and 35
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Age BETWEEN 25 AND 35;
Example 8: Filtering with NULL Values
-- Retrieve employees who don't have a department assigned
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department IS NULL;
These examples should provide comprehensive coverage of how to use the WHERE clause to filter data in SQL. You can tailor these examples to fit your specific database schema and requirements.
Sorting Data with ORDER BY
In this section, we’ll explore how to sort data returned from a SQL query using the ORDER BY clause. The ORDER BY clause is used to arrange the result set in either ascending or descending order, based on one or more columns.
Basic Syntax
The basic syntax of the ORDER BY clause is as follows:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
Where:
ASCis for ascending order (default).DESCis for descending order.
Practical Examples
Simple Sorting (Single Column)
Suppose we have a table
employeeswith the columnsemployee_id,first_name,last_name, andsalary.To sort the employees by
last_namein ascending order:SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY last_name ASC;Sorting in Descending Order
To sort the employees by
salaryin descending order:SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC;Sorting by Multiple Columns
To sort the employees first by
last_namein ascending order, and then byfirst_namein ascending order:SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY last_name ASC, first_name ASC;Sorting with Mixed Order
To sort the employees by
last_namein ascending order and thensalaryin descending order:SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY last_name ASC, salary DESC;Sorting by Column Alias
Suppose we use an alias for one of the columns in the
SELECTstatement. We can also sort by this alias:SELECT employee_id, first_name, last_name, salary*12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;
Practical Use Case
Consider a real-world scenario where you want to list the top 5 highest-paid employees in each department. Here’s how you might write such a query:
SELECT department_id, first_name, last_name, salary
FROM employees
ORDER BY department_id ASC, salary DESC
LIMIT 5;
The above query will sort the employees by their department_id in ascending order and then by their salary in descending order within each department.
By mastering the ORDER BY clause, you can effectively control the order in which query results are presented, making your data more readable and useful for analysis.
Part 5: Joining Tables for Comprehensive Selection
In SQL, joining tables allows you to combine rows from two or more tables based on a related column between them. This section covers different types of JOIN operations and practical examples.
Types of Joins
- INNER JOIN: Returns records with matching values in both tables.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. The result is NULL from the right side, if there is no match.
- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table. The result is NULL from the left side, when there is no match.
- FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table. Records which do not match will be filled with NULL values.
Practical Examples
INNER JOIN Example
Suppose you have two tables, Customers and Orders:
-- Customers Table
+----+-----------+
| ID | Name |
+----+-----------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
+----+-----------+
-- Orders Table
+----+------------+--------+
| ID | CustomerID | Amount |
+----+------------+--------+
| 1 | 1 | 100 |
| 2 | 1 | 150 |
| 3 | 3 | 200 |
+----+------------+--------+
Performing an INNER JOIN to get a list of customers with their respective orders:
SELECT Customers.Name, Orders.Amount
FROM Customers
INNER JOIN Orders ON Customers.ID = Orders.CustomerID;
LEFT JOIN Example
Using the same Customers and Orders tables, suppose you want a list of all customers along with their orders, including customers without orders:
SELECT Customers.Name, Orders.Amount
FROM Customers
LEFT JOIN Orders ON Customers.ID = Orders.CustomerID;
RIGHT JOIN Example
Assuming the same tables, if you want a list of all orders along with the customer names, including orders without customer information:
SELECT Customers.Name, Orders.Amount
FROM Customers
RIGHT JOIN Orders ON Customers.ID = Orders.CustomerID;
FULL JOIN Example
If you need a list that includes all customers and their orders, even if either side has missing information:
SELECT Customers.Name, Orders.Amount
FROM Customers
FULL OUTER JOIN Orders ON Customers.ID = Orders.CustomerID;
Conclusion
This section demonstrates how to join tables to retrieve comprehensive datasets using different types of SQL JOIN clauses. Understanding these joins is crucial for performing advanced queries on relational databases. Applying these examples to your specific database schema will enable you to harness the full potential of your data relationships.
Advanced Query Techniques in SQL
Subqueries
Inline View Subquery
Select employees who are earning above the average salary.
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
Correlated Subquery
Find all employees whose salary is higher than the average salary of their department.
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees e1
WHERE salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);
Common Table Expressions (CTEs)
Calculate the cumulative sum of salaries for employees, ordered by their IDs.
WITH Salary_CTE AS (
SELECT employee_id, salary,
SUM(salary) OVER (ORDER BY employee_id) AS cumulative_salary
FROM employees
)
SELECT employee_id, salary, cumulative_salary
FROM Salary_CTE;
Window Functions
Rank Employees by Salary within each Department
SELECT employee_id, first_name, last_name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
Grouping Sets
Calculate different aggregates across multiple groupings.
SELECT department_id, job_id, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY GROUPING SETS (
(department_id, job_id),
(department_id),
(job_id),
()
);
Pivoting Data
Transform rows to columns to summarize sales data across regions.
SELECT *
FROM (SELECT region, product, sales
FROM sales_data)
PIVOT (
SUM(sales)
FOR region IN ('North' AS North, 'South' AS South, 'East' AS East, 'West' AS West)
);
Recursive Queries
Generate a Hierarchical Employee List
WITH RECURSIVE EmployeeHierarchy AS (
SELECT employee_id, manager_id, first_name, last_name, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.first_name, e.last_name, eh.level + 1 AS level
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, manager_id, first_name, last_name, level
FROM EmployeeHierarchy
ORDER BY level, manager_id, employee_id;
Lateral Join
Utilize lateral join to process each row with a subquery.
SELECT e.employee_id, e.first_name, e.last_name, d.department_name,
sub.avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN LATERAL (
SELECT AVG(salary) AS avg_salary
FROM employees e2
WHERE e2.department_id = e.department_id
) sub ON TRUE;
JSON Functions
Extract and manipulate JSON data stored in the database.
SELECT employee_id, personal_info::json ->> 'phone' AS phone_number
FROM employees
WHERE personal_info::json ->> 'city' = 'New York';
This content covers advanced SQL query techniques and provides practical implementations that you can directly apply to enhance data selection and manipulation in real-life scenarios.
