SQL Tutorial Summary Notes

SQL Tutorial Summary Notes: Below is a structured SQL tutorial covering a wide range of SQL topics and concepts. This tutorial is designed to help you revise your SQL concepts before your interviews or exams.

SQL Tutorial Summary Notes

1. Introduction to SQL

What is SQL?

SQL (Structured Query Language) is a standard programming language for managing and manipulating relational databases. It is used to create, retrieve, update, and delete data in a database system.

Why Use SQL?

  • SQL provides a standardized way to interact with databases.
  • It allows you to perform complex data queries and transformations.
  • SQL databases are scalable and suitable for both small and large datasets.
  • SQL databases ensure data integrity through constraints.
  • SQL is widely supported by relational database management systems (RDBMS) like MySQL, PostgreSQL, SQL Server, and Oracle.

Types of SQL Databases

  • Relational Databases: Store data in tables with predefined schemas (e.g., MySQL, PostgreSQL).
  • NoSQL Databases: Store unstructured or semi-structured data (e.g., MongoDB, Cassandra).
  • NewSQL Databases: Combine the benefits of SQL and NoSQL databases (e.g., Google Spanner).

2. Basic SQL Syntax

SQL Statements

SQL consists of various statements, including SELECT (retrieve data), INSERT (add data), UPDATE (modify data), and DELETE (remove data).

Example:

SELECT FirstName, LastName FROM Employees;

SQL Comments

SQL supports two types of comments: single-line (--) and multi-line (/* */).

Example:

-- This is a single-line comment
/* This is
a multi-line
comment */

SQL Data Types

Data types define the type of data that can be stored in a column (e.g., INT, VARCHAR, DATE).

Example:

CREATE TABLE Students (
    StudentID INT,
    FirstName VARCHAR(50),
    Birthdate DATE
);

SQL Identifiers

Identifiers are names for tables, columns, and other database objects. They should follow specific naming rules and conventions.

Example:

SELECT FirstName AS "First Name" FROM Employees;

3. SQL Queries

SELECT Statement

The SELECT statement retrieves data from one or more tables.

Example:

SELECT FirstName, LastName FROM Employees;

WHERE Clause

The WHERE clause filters data based on specified conditions.

Example:

SELECT ProductName, Price FROM Products WHERE Price > 50;

ORDER BY Clause

The ORDER BY clause sorts the result set in ascending or descending order.

Example:

SELECT FirstName, LastName FROM Employees ORDER BY LastName ASC;

LIMIT and OFFSET

LIMIT restricts the number of rows returned, while OFFSET skips a specified number of rows.

Example:

SELECT FirstName, LastName FROM Employees LIMIT 10 OFFSET 20;

DISTINCT Keyword

DISTINCT removes duplicate rows from the result set.

Example:

SELECT DISTINCT Category FROM Products;

4. Filtering Data

Comparison Operators

Comparison operators (e.g., =, <, >, <=, >=, <>) compare values.

Example:

SELECT ProductName, Price FROM Products WHERE Price < 100;

Logical Operators

Logical operators (e.g., AND, OR, NOT) combine conditions in WHERE clauses.

Example:

SELECT ProductName, Price FROM Products WHERE Category = 'Electronics' AND Price < 500;

BETWEEN and IN Operators

BETWEEN checks if a value is within a range, and IN checks if a value matches any value in a list.

Example:

SELECT ProductName, Price FROM Products WHERE Price BETWEEN 50 AND 100;
SELECT FirstName, LastName FROM Employees WHERE Department IN ('HR', 'Finance');

LIKE Operator

LIKE is used to search for a specified pattern in a column.

Example:

SELECT ProductName FROM Products WHERE ProductName LIKE 'Apple%';

NULL Values

NULL represents missing or unknown data and requires special handling in SQL queries.

Example:

SELECT FirstName, LastName FROM Employees WHERE ManagerID IS NULL;

5. SQL Functions

Aggregate Functions

Aggregate functions (e.g., SUM, AVG, COUNT, MAX, MIN) perform calculations on groups of rows.

Example:

SELECT AVG(Salary) FROM Employees;

String Functions

String functions (e.g., CONCAT, SUBSTRING, LENGTH) manipulate text data.

Example:

SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;

Date and Time Functions

Date and time functions (e.g., DATE, TIME, NOW) handle date-related operations.

Example:

SELECT DATE_FORMAT

(Birthdate, '%Y-%m-%d') AS FormattedDate FROM Students;

Math Functions

Math functions (e.g., ROUND, CEIL, FLOOR) perform mathematical calculations.

Example:

SELECT ROUND(Price, 2) FROM Products;

6. SQL Joins

INNER JOIN

INNER JOIN combines rows from two or more tables based on a related column.

Example:

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

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns all rows from the left table and the matching rows from the right table.

Example:

SELECT Employees.FirstName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN returns all rows from the right table and the matching rows from the left table.

Example:

SELECT Employees.FirstName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

FULL JOIN (FULL OUTER JOIN)

FULL JOIN returns all rows when there is a match in either the left or right table.

Example:

SELECT Employees.FirstName, Departments.DepartmentName
FROM Employees
FULL JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Self-Join

Self-join is used to join a table with itself.

Example:

SELECT e1.FirstName AS Employee, e2.FirstName AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

7. Grouping and Aggregation

GROUP BY Clause

GROUP BY groups rows based on specified columns for aggregation.

Example:

SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;

HAVING Clause

HAVING filters groups based on aggregated values.

Example:

SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 50000;

GROUP_CONCAT and STRING_AGG Functions

These functions concatenate values from multiple rows into a single string.

Example:

SELECT DepartmentID, GROUP_CONCAT(EmployeeName) AS EmployeeList
FROM Employees
GROUP BY DepartmentID;

8. Subqueries

Subquery Types

Subqueries can be scalar (returning a single value), row (returning one or more rows), or table (returning a table).

Example (Scalar Subquery):

SELECT FirstName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Correlated Subqueries

Correlated subqueries reference columns from the outer query within the subquery.

Example:

SELECT EmployeeID, FirstName, Salary
FROM Employees e
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID);

9. SQL Constraints

PRIMARY KEY

A primary key constraint uniquely identifies each record in a table.

Example:

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

FOREIGN KEY

A foreign key constraint establishes a link between two tables, ensuring referential integrity.

Example:

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

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are unique.

Example:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50) UNIQUE,
    Price DECIMAL(10, 2)
);

NOT NULL Constraint

The NOT NULL constraint enforces that a column cannot contain NULL values.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL
);

CHECK Constraint

The CHECK constraint enforces conditions on column values.

Example:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Quantity INT,
    CHECK (Quantity >= 0)
);

10. Creating and Modifying Tables

CREATE TABLE Statement

The CREATE TABLE statement creates a new table with specified columns and data types.

Example:

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

ALTER TABLE Statement

The ALTER TABLE statement modifies an existing table, such as adding or dropping columns.

Example (Adding a Column):

ALTER TABLE Employees
ADD Email VARCHAR(100);

DROP TABLE Statement

The DROP TABLE statement deletes an existing table and all its data.

Example:

DROP TABLE Students;

11. Indexes

CREATE INDEX Statement

An index improves the speed of data retrieval operations on a table.

Example:

CREATE INDEX idx_LastName ON Employees (LastName);

Types of Indexes

  • Single-Column Index: Index on a single column.
  • Multi-Column Index: Index on multiple columns.

Example (Multi-Column Index):

CREATE INDEX idx_FirstNameLastName ON Employees (FirstName, LastName);

12. Transactions

ACID Properties

Transactions in SQL follow ACID properties: Atomicity, Consistency, Isolation, and Durability.

Example:

BEGIN TRANSACTION;
-- SQL statements
COMMIT;

13. Views

CREATE VIEW Statement

A view is a virtual table created by a query. It simplifies complex queries.

Example:

CREATE VIEW HighSalaryEmployees AS
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 75000;

14. Stored Procedures

CREATE PROCEDURE Statement

A stored procedure is a reusable SQL code block.

Example:

CREATE PROCEDURE GetEmployeeList
AS
BEGIN
    SELECT FirstName, LastName FROM Employees;
END;

15. Triggers

CREATE TRIGGER Statement

A trigger is a set of actions that automatically execute when a specified event occurs.

Example:

CREATE TRIGGER UpdateProductStock
AFTER INSERT ON OrderDetails
FOR EACH ROW
BEGIN
    UPDATE Products
    SET StockQuantity = StockQuantity - NEW.Quantity
    WHERE ProductID = NEW.ProductID;
END;

16. Normalization

Database Normal Forms

Database normalization reduces data redundancy and ensures data integrity through various normal forms (e.g., 1NF, 2NF, 3NF).

Example:

-- Table in 1st Normal Form (1NF)
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Orders TEXT
);

-- Table in 2nd Normal Form (2NF)
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50)
);

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

17. Security

GRANT and REVOKE Statements

The GRANT statement assigns permissions to users, and the REVOKE statement revokes permissions.

Example (GRANT

):

GRANT SELECT, INSERT ON Employees TO HRUser;

SQL Tutorial Summary Notes| SQL Best Practices

Proper Naming Conventions

Use meaningful names for tables, columns, and other objects.

Efficient Queries

Optimize queries for performance by using indexes and avoiding unnecessary joins.

Data Integrity

Enforce data integrity through constraints and validation.

SQL Tutorial Summary Notes | Conclusion

This comprehensive SQL tutorial covers a wide range of SQL topics and concepts, from basic SQL syntax to advanced database administration and optimization. With this knowledge, you can effectively manage and manipulate data in relational databases and build robust database applications.

Related Articles:

Leave a Comment