M4 - SQL (Core Skill)

Database Concepts

Relational Databases

A relational database is a type of database that stores data in structured formats using tables. Each table consists of rows and columns, where each row represents a unique record and each column represents a specific attribute of that record. The relational model allows for easy data retrieval and manipulation using Structured Query Language (SQL). Key features include:

• Data Integrity: Ensures accuracy and consistency of data.
• Relationships: Tables can be linked through keys, enabling complex queries across multiple tables.
• ACID Properties: Ensures reliable transactions through Atomicity, Consistency, Isolation, and Durability.

For example, consider a database for a library where you have a table for Books and another for Authors. You can link these tables using a foreign key, allowing you to query books by their authors efficiently.

Tables and Records

In a relational database, tables are the primary structure for storing data. Each table consists of records (or rows) and fields (or columns). A record represents a single entry in the table, while fields represent the attributes of that entry. For example, in a Customers table:

• Fields: CustomerID, FirstName, LastName, Email
• Records: Each row would contain data for a different customer.

Key points to remember:

• Tables must have a unique name within the database.
• Each table should represent a single entity (e.g., Customers, Orders).
• Records can be added, updated, or deleted using SQL commands such as INSERT, UPDATE, and DELETE.

Example SQL to create a Customers table:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

Columns and Data Types

Each column in a table represents a specific attribute of the data stored in that table. Columns have defined data types that dictate what kind of data can be stored. Common data types include:

• INT: Integer values
• VARCHAR(n): Variable-length string with a maximum length of n
• DATE: Date values
• FLOAT: Floating-point numbers

Choosing the correct data type is crucial for optimizing storage and ensuring data integrity. For example, using VARCHAR(100) for an email address allows for flexibility in length, while INT for an ID ensures efficient indexing. Here’s an example of defining columns with data types in SQL:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price FLOAT,
    CreatedAt DATE
);

Primary Keys

A primary key is a unique identifier for each record in a table. It ensures that no two records can have the same value in the primary key column(s). This is crucial for maintaining data integrity and enabling efficient data retrieval. Characteristics of primary keys include:

• Uniqueness: Each value must be unique across the table.
• Non-null: A primary key cannot contain NULL values.
• Immutable: The value of a primary key should not change.

For example, in a Students table, the StudentID can serve as a primary key:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Foreign Keys

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. It establishes a relationship between the two tables, allowing for data integrity and referential integrity. Key points about foreign keys include:

• They can contain duplicate values.
• They can be NULL, allowing for optional relationships.
• They enforce referential integrity by ensuring that the value in the foreign key column matches a value in the primary key column of the referenced table.

For example, in an Orders table, the CustomerID can be a foreign key referencing the Customers table:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Database Design Basics

Normalization Fundamentals

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The goal is to divide large tables into smaller, related tables and define relationships between them. The main forms of normalization are:

• First Normal Form (1NF): Ensures that all columns contain atomic values and each record is unique.
• Second Normal Form (2NF): Requires that all non-key attributes are fully functional dependent on the primary key.
• Third Normal Form (3NF): Ensures that all attributes are only dependent on the primary key, eliminating transitive dependencies.

For example, if a Students table contains both student information and course details, it should be split into two tables to achieve 1NF. This reduces redundancy and improves data integrity.

Entity Relationships

Entity relationships define how tables relate to one another in a database. Understanding these relationships is crucial for effective database design. The main types of relationships are:

• One-to-One: A record in one table is linked to a single record in another table.
• One-to-Many: A record in one table can be associated with multiple records in another table.
• Many-to-Many: Records in one table can relate to multiple records in another table and vice versa, often requiring a junction table.

For example, in a Courses and Students scenario, a student can enroll in multiple courses (One-to-Many), while a course can have many students (Many-to-Many). This relationship can be managed using an Enrollments table.

Referential Integrity

Referential integrity is a property of data stating that all its references are valid. In relational databases, this means that a foreign key must either be NULL or match a primary key in another table. Maintaining referential integrity ensures that relationships between tables remain consistent. Key points include:

• It prevents orphan records, which occur when a foreign key points to a non-existent record.
• It can be enforced through database constraints, ensuring that any operation (insert, update, delete) maintains the integrity of the data.

For example, if a Customer is deleted from the Customers table, any associated Orders should also be deleted or updated to maintain referential integrity. This can be achieved using cascading actions in SQL:

ALTER TABLE Orders
ADD CONSTRAINT fk_Customer
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE;

SELECT Statements

SELECT

The SELECT statement is the cornerstone of SQL queries, used to retrieve data from one or more tables. The basic syntax is:

SELECT column1, column2
FROM table_name;

In this example, column1 and column2 represent the fields you want to retrieve, while table_name is the source table. If you want to select all columns, you can use the asterisk (*) wildcard:

SELECT *
FROM table_name;

Key Points:

• The SELECT statement can retrieve data from multiple tables using JOINs.
• You can filter the results using the WHERE clause (covered in later units).
• The order of columns in the SELECT statement determines the order of the output.

DISTINCT

The DISTINCT keyword is used to return only unique values from a column. This is particularly useful when you want to eliminate duplicates from your results. The syntax is:

SELECT DISTINCT column_name
FROM table_name;

For example, if you have a table of customer orders and want to find unique customer IDs, you would write:

SELECT DISTINCT customer_id
FROM orders;

Key Points:

• DISTINCT applies to all columns listed in the SELECT statement.
• If multiple columns are specified, the combination of values must be unique.
• Use DISTINCT judiciously, as it can impact performance on large datasets.

Aliases

Aliases are temporary names given to tables or columns for the duration of a query. They improve readability and can simplify complex queries. The syntax for creating an alias is:

SELECT column_name AS alias_name
FROM table_name;

For example:

SELECT customer_id AS ID, customer_name AS Name
FROM customers;

Key Points:

• Aliases are especially useful in JOIN operations to avoid ambiguity.
• You can create table aliases using the same AS keyword:

SELECT c.customer_name FROM customers AS c;

- Aliases are not stored in the database; they exist only for the duration of the query.

### TOP / LIMIT

The **TOP** (in SQL Server) or **LIMIT** (in MySQL and PostgreSQL) clause is used to restrict the number of rows returned by a query. The syntax varies slightly between SQL dialects. For SQL Server, use: 

```sql
SELECT TOP number_of_rows column_name
FROM table_name;

For MySQL or PostgreSQL, use:

SELECT column_name
FROM table_name
LIMIT number_of_rows;

For example, to get the top 5 customers by sales:

SELECT TOP 5 customer_id, sales
FROM orders
ORDER BY sales DESC;

Key Points:

• Use TOP or LIMIT to improve performance by reducing the dataset size.
• Always combine with ORDER BY to ensure you get the desired rows.
• The behavior may differ slightly between SQL dialects, so always check the documentation.

Filtering Data

WHERE Clause

The WHERE clause is used to filter records based on specified conditions. It is essential for narrowing down results to meet specific criteria. The syntax is:

SELECT column_name
FROM table_name
WHERE condition;

For example, to find customers from a specific city:

SELECT customer_name
FROM customers
WHERE city = 'New York';

Key Points:

• Conditions can include comparisons, logical operators, and functions.
• The WHERE clause can be combined with other clauses like ORDER BY and GROUP BY.
• Always ensure that conditions are correctly formatted to avoid syntax errors.

Comparison Operators

Comparison operators are used in the WHERE clause to compare values. The most common operators include:

• =: Equal to
• != or <>: Not equal to
• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to

For example:

SELECT product_name
FROM products
WHERE price > 100;

This query retrieves products with a price greater than 100.
Key Points:

• Use parentheses to group conditions when combining multiple comparisons.
• Be mindful of data types when comparing values.
• Ensure to use the correct operator for the intended comparison.

IN

The IN operator allows you to specify multiple values in a WHERE clause, making queries more concise. The syntax is:

SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2, ...);

For example, to find customers in specific cities:

SELECT customer_name
FROM customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago');

Key Points:

• The IN operator can also be used with subqueries.
• It simplifies queries that would otherwise require multiple OR conditions.
• Be cautious with large lists, as they may impact performance.

BETWEEN

The BETWEEN operator is used to filter records within a specified range. It is inclusive of the boundary values. The syntax is:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

For example, to find products within a price range:

SELECT product_name
FROM products
WHERE price BETWEEN 50 AND 100;

Key Points:

• BETWEEN can be used with dates, numbers, and text.
• Ensure that the values are in the correct order (lower to higher).
• It is equivalent to using >= and <= operators.

LIKE

The LIKE operator is used for pattern matching in string data. It allows you to search for a specified pattern in a column. The syntax is:

SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;

Common wildcard characters include:

• %: Represents zero or more characters
• _: Represents a single character

For example, to find customers whose names start with 'A':

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'A%';

Key Points:

• LIKE is case-insensitive in some databases, but case-sensitive in others.
• Use wildcards carefully to avoid performance issues.
• It is useful for searching text fields.

NULL Handling

Handling NULL values is crucial in SQL, as they represent missing or unknown data. To filter out NULL values, use the IS NULL or IS NOT NULL operators. The syntax is:

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

For example, to find customers without an email address:

SELECT customer_name
FROM customers
WHERE email IS NULL;

Key Points:

• NULL is not the same as an empty string or zero.
• Be cautious when using comparison operators with NULL values, as they will not return true.
• Use functions like COALESCE to handle NULLs in calculations.

Sorting Results

ORDER BY

The ORDER BY clause is used to sort the result set of a query by one or more columns. By default, it sorts in ascending order. The syntax is:

SELECT column_name
FROM table_name
ORDER BY column_name [ASC|DESC];

For example, to sort customers by name in descending order:

SELECT customer_name
FROM customers
ORDER BY customer_name DESC;

Key Points:

• You can sort by multiple columns by separating them with commas.
• The order of columns in the ORDER BY clause determines the sorting priority.
• Always consider performance implications when sorting large datasets.

Multi-Column Sorting

Multi-column sorting allows you to sort results based on more than one column, providing a more refined ordering. The syntax is:

SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

For example, to sort customers first by city and then by name:

SELECT customer_name, city
FROM customers
ORDER BY city ASC, customer_name ASC;

Key Points:

• The first column listed in ORDER BY takes precedence.
• You can mix ascending and descending orders for different columns.
• Be aware of how sorting affects performance, especially on large datasets.

Aggregate Functions

COUNT

The COUNT function in SQL is used to count the number of rows that match a specified condition. It can be applied to any column or to all rows in a table. The syntax is as follows:

SELECT COUNT(column_name) FROM table_name WHERE condition;

Key Points:

• COUNT(*) counts all rows, including duplicates and NULLs.
• COUNT(column_name) counts only non-NULL values in the specified column.

Example:

To count the number of employees in the 'employees' table:

SELECT COUNT(*) FROM employees;

This query returns the total number of employees. If you want to count only those employees in a specific department:

SELECT COUNT(*) FROM employees WHERE department = 'Sales';

This will give you the count of employees in the Sales department.

SUM

The SUM function calculates the total sum of a numeric column. It is particularly useful for financial data analysis. The syntax is:

SELECT SUM(column_name) FROM table_name WHERE condition;

Key Points:

• SUM ignores NULL values.
• It can be used in conjunction with GROUP BY to get totals for each group.

Example:

To find the total sales from the 'sales' table:

SELECT SUM(amount) FROM sales;

If you want to find the total sales for each product:

SELECT product_id, SUM(amount) FROM sales GROUP BY product_id;

This will return the total sales amount for each product.

AVG

The AVG function computes the average value of a numeric column. This function is useful for analyzing trends and performance metrics. The syntax is:

SELECT AVG(column_name) FROM table_name WHERE condition;

Key Points:

• AVG ignores NULL values.
• It can also be used with GROUP BY to find averages for different groups.

Example:

To calculate the average salary from the 'employees' table:

SELECT AVG(salary) FROM employees;

To find the average salary by department:

SELECT department, AVG(salary) FROM employees GROUP BY department;

This will return the average salary for each department.

MIN

The MIN function retrieves the smallest value from a specified column. This is useful for identifying the lowest values in datasets. The syntax is:

SELECT MIN(column_name) FROM table_name WHERE condition;

Key Points:

• MIN can be used with numeric, date, and string columns.
• It is often combined with GROUP BY to find minimum values for each group.

Example:

To find the minimum salary in the 'employees' table:

SELECT MIN(salary) FROM employees;

To find the minimum salary by department:

SELECT department, MIN(salary) FROM employees GROUP BY department;

This will return the lowest salary in each department.

MAX

The MAX function is used to find the largest value in a specified column. This function is beneficial for identifying the highest values in datasets. The syntax is:

SELECT MAX(column_name) FROM table_name WHERE condition;

Key Points:

• MAX can be applied to numeric, date, and string columns.
• It can also be used with GROUP BY to find maximum values for each group.

Example:

To find the maximum salary in the 'employees' table:

SELECT MAX(salary) FROM employees;

To find the maximum salary by department:

SELECT department, MAX(salary) FROM employees GROUP BY department;

This will return the highest salary in each department.

Grouping Data

GROUP BY

The GROUP BY clause is used to arrange identical data into groups. It is often used with aggregate functions to perform calculations on each group. The syntax is:

SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name;

Key Points:

• GROUP BY must follow the WHERE clause in a SQL statement.
• All columns in the SELECT statement that are not part of an aggregate function must be included in the GROUP BY clause.

Example:

To group employees by department and count the number of employees in each department:

SELECT department, COUNT(*) FROM employees GROUP BY department;

This query returns the number of employees in each department.

HAVING

The HAVING clause is used to filter records after the GROUP BY operation. It is similar to the WHERE clause but is applied to groups rather than individual rows. The syntax is:

SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name HAVING condition;

Key Points:

• HAVING is used to filter groups based on aggregate values.
• It is executed after the GROUP BY clause.

Example:

To find departments with more than 10 employees:

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

This query returns only those departments that have more than 10 employees.

Multi-Level Grouping

Multi-level grouping allows you to group data by multiple columns. This is useful for more complex analyses. The syntax is:

SELECT column1, column2, aggregate_function(column_name) FROM table_name GROUP BY column1, column2;

Key Points:

• You can group by multiple columns to create a hierarchy.
• The order of columns in the GROUP BY clause affects the grouping result.

Example:

To group employees by department and job title:

SELECT department, job_title, COUNT(*) FROM employees GROUP BY department, job_title;

This query returns the count of employees for each job title within each department.

Join Fundamentals

INNER JOIN

INNER JOIN is used to combine rows from two or more tables based on a related column between them. It returns only the rows where there is a match in both tables. For example, consider two tables: Customers and Orders. To retrieve a list of customers who have placed orders, you can use the following SQL query:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

This query will return only those customers who have orders. Key Points:

• INNER JOIN eliminates non-matching rows.
• It is the most common type of join.
• Always specify the join condition to avoid Cartesian products.

LEFT JOIN

LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table. For instance, to get a list of all customers and their orders, including those who haven't placed any orders:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

This will show all customers, and those without orders will have NULL in the OrderID column. Key Points:

• Useful for identifying unmatched records.
• Maintains all records from the left table.
• NULL values indicate no match in the right table.

RIGHT JOIN

RIGHT JOIN (or RIGHT OUTER JOIN) is the opposite of LEFT JOIN. It returns all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table. For example, to retrieve all orders and the customers who placed them:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

This query will return all orders, and if an order does not have a corresponding customer, the CustomerID and CustomerName will be NULL. Key Points:

• Useful for identifying unmatched records in the right table.
• Maintains all records from the right table.
• NULL values indicate no match in the left table.

FULL OUTER JOIN

FULL OUTER JOIN combines the results of both LEFT JOIN and RIGHT JOIN. It returns all records when there is a match in either left or right table records. If there is no match, NULL values are returned for non-matching rows. For instance:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

This will return all customers and all orders, with NULLs where there are no matches. Key Points:

• Combines results from both tables.
• Useful for comprehensive data analysis.
• NULL values indicate no match in either table.

CROSS JOIN

CROSS JOIN produces a Cartesian product of two tables, meaning it returns all possible combinations of rows from both tables. This type of join does not require a condition. For example:

SELECT Customers.CustomerName, Products.ProductName
FROM Customers
CROSS JOIN Products;

This query will return every customer paired with every product. Key Points:

• Can lead to large result sets.
• Use with caution, especially with large tables.
• Typically used in scenarios where all combinations are needed.

SELF JOIN

A SELF JOIN is a regular join but the table is joined with itself. This is useful for comparing rows within the same table. For example, if you have an Employees table and want to find employees who report to the same manager:

SELECT A.EmployeeName AS Employee, B.EmployeeName AS Manager
FROM Employees A, Employees B
WHERE A.ManagerID = B.EmployeeID;

This query retrieves a list of employees alongside their managers. Key Points:

• Useful for hierarchical data.
• Requires table aliasing to differentiate between instances of the same table.
• Helps in self-referencing scenarios.

Set Operations

UNION

UNION is used to combine the results of two or more SELECT statements. It removes duplicate records from the result set. For example, if you want to combine customer names from two different regions:

SELECT CustomerName FROM Customers_USA
UNION
SELECT CustomerName FROM Customers_Europe;

This will return a list of unique customer names from both tables. Key Points:

• Requires the same number of columns in each SELECT statement.
• Column data types must be compatible.
• Use UNION to eliminate duplicates.

UNION ALL

UNION ALL is similar to UNION but includes all records, including duplicates. This is useful when you want to retain all entries from multiple tables. For instance:

SELECT CustomerName FROM Customers_USA
UNION ALL
SELECT CustomerName FROM Customers_Europe;

This will return all customer names, including duplicates. Key Points:

• Faster than UNION since it does not check for duplicates.
• Useful for aggregating data from multiple sources.
• Requires the same number of columns and compatible data types.

INTERSECT

INTERSECT returns only the rows that are common to both SELECT statements. This is useful for finding overlapping data. For example:

SELECT CustomerName FROM Customers_USA
INTERSECT
SELECT CustomerName FROM Customers_Europe;

This query will return customer names that exist in both tables. Key Points:

• Requires the same number of columns and compatible data types.
• Only returns distinct rows.
• Useful for identifying common records.

EXCEPT

EXCEPT returns distinct rows from the first SELECT statement that are not present in the second SELECT statement. For example:

SELECT CustomerName FROM Customers_USA
EXCEPT
SELECT CustomerName FROM Customers_Europe;

This will return customer names that are in the USA table but not in the Europe table. Key Points:

• Similar to a LEFT JOIN with a NULL check.
• Requires the same number of columns and compatible data types.
• Useful for identifying unique records in one dataset.

String Functions

Concatenation

Concatenation in SQL is the process of joining two or more strings together to form a single string. This is commonly used to create full names from first and last names or to combine various fields into a single output. In SQL, the CONCAT() function is often used for this purpose. For example:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

This query retrieves the first and last names from the employees table and combines them with a space in between.

Key Points:

• The || operator can also be used for concatenation in some SQL dialects, such as PostgreSQL.
• Be cautious of NULL values; if any string in the concatenation is NULL, the result will be NULL unless handled with functions like COALESCE().
• Concatenation can improve readability and formatting in reports.

String Manipulation

String manipulation involves various operations that can be performed on string data types. Common string functions include SUBSTRING(), LENGTH(), UPPER(), and LOWER().
For instance, to extract a substring from a string, you can use the SUBSTRING() function:

SELECT SUBSTRING(full_name, 1, 5) AS short_name
FROM employees;

This query retrieves the first five characters of the full_name.

Key Points:

• LENGTH() returns the number of characters in a string.
• UPPER() and LOWER() convert strings to uppercase and lowercase, respectively.
• String manipulation functions are essential for data cleaning and formatting before analysis.

Date and Time Functions

Date Functions

Date functions are crucial for handling and manipulating date values in SQL. Common functions include CURRENT_DATE, DATEADD(), and DATEDIFF(). For example, to find the difference in days between two dates, you can use:

SELECT DATEDIFF(end_date, start_date) AS days_difference
FROM projects;

This query calculates the number of days between start_date and end_date in the projects table.

Key Points:

• CURRENT_DATE retrieves the current date.
• DATEADD() allows you to add a specified interval to a date.
• Understanding date functions is essential for time-based analysis and reporting.

Time Functions

Time functions in SQL are used to manipulate and retrieve time values. Common functions include CURRENT_TIME, TIME_FORMAT(), and TIMESTAMPDIFF(). For instance, to get the current time, you can use:

SELECT CURRENT_TIME AS current_time;

This query returns the current time of day.

Key Points:

• TIMESTAMPDIFF() calculates the difference between two time values.
• TIME_FORMAT() allows formatting of time values for better readability.
• Mastering time functions is essential for scheduling and time-sensitive data analysis.

Conditional Logic

CASE Expressions

The CASE expression in SQL allows for conditional logic within queries, enabling you to return different values based on specific conditions. It can be used in SELECT, UPDATE, and ORDER BY clauses. For example:

SELECT employee_id,  
       CASE  
           WHEN salary < 30000 THEN 'Low'  
           WHEN salary BETWEEN 30000 AND 70000 THEN 'Medium'  
           ELSE 'High'  
       END AS salary_category  
FROM employees;

This query categorizes employees based on their salary.

Key Points:

• CASE can be nested for more complex conditions.
• It enhances the readability of query results by providing meaningful labels.

Derived Columns

Derived columns are calculated fields that are created in a SQL query using expressions or functions. They allow for dynamic calculations without altering the underlying data. For example:

SELECT employee_id, salary, salary * 0.1 AS bonus  
FROM employees;

This query calculates a bonus as 10% of the salary for each employee.

Key Points:

• Derived columns can simplify complex calculations in reports.
• They are useful for aggregating data without modifying the original dataset.
• Always ensure that derived columns are clearly labeled for better understanding.

Data Modification

INSERT

The INSERT statement is used to add new records to a table in a database. It can insert a single row or multiple rows at once. The basic syntax for inserting a single row is as follows:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Key Points:

• Ensure that the values match the data types of the columns.
• If you want to insert multiple rows, you can do so by separating each set of values with a comma:

INSERT INTO table_name (column1, column2)
VALUES (value1a, value2a), (value1b, value2b);

Example:

To insert a new employee into an employees table:

INSERT INTO employees (first_name, last_name, hire_date)
VALUES ('John', 'Doe', '2023-10-01');

This command adds a new employee named John Doe with a hire date of October 1, 2023.

UPDATE

The UPDATE statement is used to modify existing records in a table. It is crucial to specify which records to update using the WHERE clause to avoid updating all records unintentionally. The basic syntax is:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Key Points:

• Always use the WHERE clause to target specific records.
• If the WHERE clause is omitted, all records will be updated.

Example:

To update the last name of an employee with an ID of 1:

UPDATE employees
SET last_name = 'Smith'
WHERE employee_id = 1;

This command changes the last name of the employee with ID 1 to Smith.

DELETE

The DELETE statement is used to remove existing records from a table. Similar to the UPDATE statement, it is essential to use the WHERE clause to specify which records to delete. The syntax is:

DELETE FROM table_name
WHERE condition;

Key Points:

• Omitting the WHERE clause will delete all records from the table.
• Always ensure that you have a backup or are certain before executing a delete operation.

Example:

To delete an employee with an ID of 1:

DELETE FROM employees
WHERE employee_id = 1;

This command removes the employee record with ID 1 from the employees table.

Constraints

NOT NULL

The NOT NULL constraint ensures that a column cannot have a NULL value. This is crucial for maintaining data integrity. When defining a table, you can specify which columns should not accept NULL values. The syntax is:

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype,
    ...
);

Key Points:

• Use NOT NULL for columns that must always have a value.
• Attempting to insert a NULL value into a NOT NULL column will result in an error.

Example:

To create a users table where the username cannot be NULL:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

This ensures that every user must have a username.

UNIQUE

The UNIQUE constraint ensures that all values in a column are different from one another. This is useful for columns that require distinct values, such as email addresses or usernames. The syntax for adding a UNIQUE constraint is:

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

Key Points:

• A table can have multiple UNIQUE constraints.
• Unlike the PRIMARY KEY, a UNIQUE column can accept NULL values, but only one NULL per column.

Example:

To create a products table where the product_code must be unique:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_code VARCHAR(20) UNIQUE
);

This ensures that no two products can have the same product code.

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table. A table can have only one PRIMARY KEY, which can consist of one or more columns. The syntax is:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

Key Points:

• A PRIMARY KEY cannot contain NULL values.
• It ensures that each record is unique and can be used to establish relationships between tables.

Example:

To create a customers table with a PRIMARY KEY on customer_id:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

This ensures that each customer has a unique identifier.

FOREIGN KEY

The FOREIGN KEY constraint is used to link two tables together. It ensures referential integrity by enforcing that a value in one table must exist in another table. The syntax is:

CREATE TABLE child_table (
    column1 datatype,
    column2 datatype,
    FOREIGN KEY (column1) REFERENCES parent_table(column);
);

Key Points:

• A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
• It helps maintain the relationship between tables and prevents orphaned records.

Example:

To create an orders table that references the customers table:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

This ensures that every order is associated with a valid customer.

Subqueries

Scalar Subqueries

A scalar subquery is a type of subquery that returns a single value. This value can be used in the main query as if it were a constant. Scalar subqueries are often used in the SELECT, WHERE, or HAVING clauses. For example, consider the following SQL query:

SELECT employee_id, first_name, last_name,
       (SELECT MAX(salary) FROM employees) AS max_salary
FROM employees;

In this example, the scalar subquery (SELECT MAX(salary) FROM employees) returns the maximum salary from the employees table, which is then included in the main query's result set. Key Points:

• Scalar subqueries must return exactly one value.
• They can simplify complex queries by encapsulating logic.
• Use them judiciously, as they can impact performance if not optimized.

Correlated Subqueries

Correlated subqueries are subqueries that depend on the outer query for their values. Unlike scalar subqueries, they are executed once for each row processed by the outer query. This can lead to performance issues if not used carefully. An example of a correlated subquery is:

SELECT e1.employee_id, e1.first_name,
       (SELECT COUNT(*) FROM employees e2 WHERE e2.manager_id = e1.employee_id) AS num_reports
FROM employees e1;

In this case, the inner query counts the number of reports for each employee in the outer query. Key Points:

• Correlated subqueries reference columns from the outer query.
• They can be powerful but may lead to slower performance.
• Always consider alternatives like joins for better efficiency.

Nested Queries

Nested queries involve placing one query inside another, which can be either scalar or correlated. They allow for complex data retrieval and manipulation. For instance, the following SQL demonstrates a nested query:

SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1400);

Here, the inner query retrieves department IDs based on a location, and the outer query fetches employee names from those departments. Key Points:

• Nested queries can simplify complex logic.
• They can be scalar, correlated, or non-correlated.
• Performance can be an issue; consider using joins when appropriate.

Common Table Expressions

CTE Fundamentals

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve the readability and organization of complex queries. The basic syntax is:

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

For example:

WITH department_sales AS (
    SELECT department_id, SUM(sales) AS total_sales
    FROM sales
    GROUP BY department_id
)
SELECT * FROM department_sales WHERE total_sales > 10000;

Key Points:

• CTEs can be recursive or non-recursive.
• They enhance query organization and can be reused multiple times.
• CTEs can replace complex subqueries for better readability.

Recursive CTEs

Recursive CTEs are a special type of CTE that references itself to perform operations like traversing hierarchical data. They consist of two parts: the anchor member and the recursive member. The anchor member provides the base result set, while the recursive member references the CTE itself. Here’s an example:

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, first_name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.first_name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

In this example, the CTE retrieves all employees and their hierarchy starting from the top-level manager. Key Points:

• Recursive CTEs are useful for hierarchical data.
• They require careful termination conditions to avoid infinite loops.
• Performance can be a concern; optimize queries when necessary.

Window Functions

OVER Clause

The OVER clause is fundamental in SQL for defining a window of rows for window functions. It allows you to perform calculations across a set of table rows that are somehow related to the current row. The basic syntax is:

SELECT column1, column2, function() OVER (PARTITION BY column3 ORDER BY column4) AS alias_name
FROM table_name;

Key Points:

• The OVER clause can be used with various window functions like ROW_NUMBER(), RANK(), and SUM().
• It does not change the number of rows returned by the query.
• You can specify an ORDER BY clause to define the order of rows in the window.

Example:

SELECT employee_id, salary, SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;

PARTITION BY

The PARTITION BY clause is used within the OVER clause to divide the result set into partitions to which the window function is applied. Each partition is processed independently.

Key Points:

• It is similar to a group by, but it does not reduce the number of rows returned.
• You can partition by one or more columns.

Example:

SELECT department_id, employee_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS department_rank
FROM employees;

ROW_NUMBER

The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set. This is particularly useful for pagination or when you need to identify unique records.

Key Points:

• The numbering resets for each partition defined by PARTITION BY.
• It can be used to filter results to get the top N records per category.

Example:

SELECT employee_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

RANK

The RANK() function provides a rank to each row within a partition of a result set, with gaps in the ranking for ties. This means if two rows are tied for rank 1, the next rank will be 3.

Key Points:

• Use RANK() when you need to account for ties in your ranking.
• It is often used in competitive scenarios, such as sports rankings.

Example:

SELECT employee_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

DENSE_RANK

The DENSE_RANK() function is similar to RANK(), but it does not leave gaps in the ranking sequence. If two rows are tied for rank 1, the next rank will be 2.

Key Points:

• Use DENSE_RANK() when you want a continuous ranking without gaps.
• It is useful in scenarios where you want to display rankings without skipping numbers.

Example:

SELECT employee_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;

LAG

The LAG() function allows you to access data from a previous row in the same result set without the need for a self-join. This is useful for comparing values in a row with previous rows.

Key Points:

• You can specify the number of rows to look back and provide a default value if there is no previous row.
• It is often used for calculating differences or trends over time.

Example:

SELECT employee_id, salary, LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS previous_salary
FROM employees;

LEAD

The LEAD() function is the counterpart to LAG(), allowing you to access data from the next row in the result set. This function is useful for comparing current values with future values.

Key Points:

• Similar to LAG(), you can specify how many rows ahead to look and provide a default value.
• It is useful for forecasting or trend analysis.

Example:

SELECT employee_id, salary, LEAD(salary, 1, 0) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

Analytical Query Patterns

Running Totals

Running totals are cumulative sums that provide insights into trends over time. They can be calculated using the SUM() function along with the OVER clause.

Key Points:

• Running totals are useful for financial analysis, sales trends, and performance metrics.
• They help visualize cumulative data over a specified period.

Example:

SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Moving Averages

A moving average smooths out fluctuations in data by creating averages over a specified number of periods. This is particularly useful in time series analysis.

Key Points:

• Moving averages can be simple or weighted.
• They help identify trends and patterns over time.

Example:

SELECT order_date, amount, AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_average
FROM orders;

Top-N Analysis

Top-N analysis identifies the top N records based on a specific metric, such as sales or performance. This can be achieved using ROW_NUMBER() or RANK() functions.

Key Points:

• Useful for identifying high performers or best-selling products.
• Can be applied across various dimensions, such as time, categories, or regions.

Example:

SELECT product_id, sales, ROW_NUMBER() OVER (ORDER BY sales DESC) AS rank
FROM products
WHERE rank <= 10;

Views

View Fundamentals

In SQL, a view is a virtual table that is based on the result set of a SELECT query. Views do not store data physically; instead, they provide a way to simplify complex queries and enhance security by restricting access to specific data. Key points about views include:

• Creation: Views are created using the CREATE VIEW statement.
• Usage: They can be used in SELECT statements just like regular tables.
• Updatability: Some views are updatable, allowing users to modify data through them, while others are read-only.

Example

To create a simple view that selects specific columns from a table:

CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName
FROM Employees;

This view can now be queried as follows:

SELECT * FROM EmployeeView;

Understanding views is essential for organizing data access and enhancing query performance.

View Usage

Views are widely used in SQL for various purposes, including data abstraction, security, and simplifying complex queries. Here are some common use cases:

• Data Abstraction: Views can hide the complexity of underlying table structures, presenting a simplified interface to users.
• Security: By granting access to views rather than base tables, sensitive data can be protected. For instance, a view can exclude salary information from an employee table.
• Simplifying Queries: Complex joins and aggregations can be encapsulated in a view, making it easier for users to retrieve data without needing to understand the underlying SQL.

Example

Creating a view that excludes sensitive data:

CREATE VIEW PublicEmployeeView AS
SELECT EmployeeID, FirstName, LastName
FROM Employees;

Key Points

• Views can be joined with other tables or views.
• Performance can be impacted if views are overly complex or nested.
• Always consider the performance implications when using views in large datasets.

Indexes

Index Fundamentals

An index in SQL is a database object that improves the speed of data retrieval operations on a table. Indexes are crucial for optimizing query performance. Key concepts include:

• Types of Indexes: There are various types of indexes, including clustered and non-clustered indexes.
• Creation: Indexes are created using the CREATE INDEX statement.
• Impact on Performance: While indexes speed up read operations, they can slow down write operations (INSERT, UPDATE, DELETE) due to the additional overhead of maintaining the index.

Example

Creating a simple index on the LastName column:

CREATE INDEX idx_LastName
ON Employees(LastName);

This index will improve the performance of queries filtering by LastName.

Clustered Indexes

A clustered index determines the physical order of data in a table. Each table can have only one clustered index, as the data rows can only be sorted in one way. Key points include:

• Primary Key: By default, a primary key constraint creates a clustered index.
• Performance: Clustered indexes are beneficial for range queries, as they allow for faster data retrieval.
• Storage: The data is stored in the index itself, making it efficient for read operations.

Example

Creating a clustered index on the EmployeeID column:

CREATE CLUSTERED INDEX idx_EmployeeID
ON Employees(EmployeeID);

This index will optimize queries that search by EmployeeID.

Non-Clustered Indexes

A non-clustered index is a separate structure from the data rows, containing pointers to the actual data. Multiple non-clustered indexes can be created on a table. Key points include:

• Structure: Non-clustered indexes store a copy of the indexed columns and a pointer to the data.
• Usage: They are useful for optimizing queries that filter on non-primary key columns.
• Performance: While they improve read performance, they can increase the overhead for write operations.

Example

Creating a non-clustered index on the FirstName column:

CREATE NONCLUSTERED INDEX idx_FirstName
ON Employees(FirstName);

This index will enhance the performance of queries filtering by FirstName.

Query Optimization Fundamentals

Execution Plans

An execution plan is a detailed roadmap that SQL Server uses to execute a query. It provides insights into how the database engine processes a query and can help identify performance bottlenecks. Key points include:

• Types of Plans: There are estimated execution plans and actual execution plans.
• Analysis: Execution plans can be analyzed to understand the efficiency of queries and to spot areas for improvement.
• Tools: SQL Server Management Studio (SSMS) provides tools to view and analyze execution plans.

Example

To view the execution plan for a query:

SET SHOWPLAN_XML ON;
SELECT * FROM Employees WHERE LastName = 'Smith';
SET SHOWPLAN_XML OFF;

This command will display the execution plan in XML format.

Query Performance Basics

Understanding query performance is essential for optimizing SQL queries. Key concepts include:

• Query Design: Writing efficient SQL queries by selecting only necessary columns and using WHERE clauses to filter data.
• Joins: Using appropriate join types (INNER, LEFT, RIGHT) can significantly impact performance.
• Subqueries vs. Joins: In many cases, using joins instead of subqueries can improve performance.

Example

A well-optimized query:

SELECT FirstName, LastName
FROM Employees
WHERE DepartmentID = 1;

This query efficiently retrieves only the necessary data, improving performance.

Programmable Database Objects

Stored Procedures Overview

A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. They are used to encapsulate business logic and improve performance. Key points include:

• Creation: Stored procedures are created using the CREATE PROCEDURE statement.
• Parameters: They can accept parameters, making them flexible for various operations.
• Security: Stored procedures can enhance security by restricting direct access to tables.

Example

Creating a simple stored procedure:

CREATE PROCEDURE GetEmployeeByID
@EmployeeID INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

This procedure can be executed with a specific EmployeeID to retrieve employee details.

User Defined Functions Overview

A user-defined function (UDF) is a function created by users to encapsulate reusable logic. UDFs can return a single value or a table. Key points include:

• Types: Scalar functions return a single value, while table-valued functions return a table.
• Usage: UDFs can be called in SELECT statements, WHERE clauses, and other SQL expressions.
• Performance: While UDFs promote code reuse, they can sometimes lead to performance issues if not used judiciously.

Example

Creating a scalar UDF:

CREATE FUNCTION GetFullName
(@FirstName VARCHAR(50), @LastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
RETURN @FirstName + ' ' + @LastName;
END;

This function can be used to concatenate first and last names in queries.

Common Analytics Use Cases

Programmable database objects, such as stored procedures and UDFs, are essential for analytics in SQL. Common use cases include:

• Data Transformation: Using stored procedures to clean and transform data before analysis.
• Reporting: Generating reports through stored procedures that aggregate data from multiple tables.
• Complex Calculations: Implementing complex business logic in UDFs to calculate metrics like averages, totals, or custom KPIs.

Example

A stored procedure for generating a sales report:

CREATE PROCEDURE GenerateSalesReport
AS
BEGIN
SELECT ProductID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY ProductID;
END;

This procedure can be executed to produce a summary of sales by product, aiding in business decision-making.