Sign In
Get Started

Mastering Database Selection Techniques

by | SQL

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:

Sales Now On Advertisement 
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:

Power BI Tools Advertisement 
-- 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.

Data Mentor Advertisement

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:

  • ASC is for ascending order (default).
  • DESC is for descending order.

Practical Examples


  1. Simple Sorting (Single Column)


    Suppose we have a table employees with the columns employee_id, first_name, last_name, and salary.


    To sort the employees by last_name in ascending order:


    SELECT employee_id, first_name, last_name, salary
    FROM employees
    ORDER BY last_name ASC;


  2. Sorting in Descending Order


    To sort the employees by salary in descending order:


    SELECT employee_id, first_name, last_name, salary
    FROM employees
    ORDER BY salary DESC;


  3. Sorting by Multiple Columns


    To sort the employees first by last_name in ascending order, and then by first_name in ascending order:


    SELECT employee_id, first_name, last_name, salary
    FROM employees
    ORDER BY last_name ASC, first_name ASC;


  4. Sorting with Mixed Order


    To sort the employees by last_name in ascending order and then salary in descending order:


    SELECT employee_id, first_name, last_name, salary
    FROM employees
    ORDER BY last_name ASC, salary DESC;


  5. Sorting by Column Alias


    Suppose we use an alias for one of the columns in the SELECT statement. 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

  1. INNER JOIN: Returns records with matching values in both tables.
  2. 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.
  3. 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.
  4. 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:

EDNA AI Advertisement 
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.

Related Posts