SQL (Structured Query Language)

What is SQL?

SQL refers to Structured Query Language to interact with the database. With SQL we can add data, retrieve data, change/ modify data in the data and also we can delete the data in the database.

So let's know what is data and database?

Data:

Data refers to the organized collection of information so that is easy to read data.

Database:

Database refers to the collection of data that can be easily accessed using SQL commands.

DBMS:

It is a software application used to manage our database.

Types of Databases

There are two types of databases.

1.Relational

In relational database , the data is stored in the form of tables. we use SQL to work with this data.

2.Non relational

In this data is not stored in the form of tables. example for this is NoSQL in which data is stored in the form of key value pairs.

Creating a database:

Create database database name;

exapmle:

Create database myDatabase;

To use a particular database,

Use myDatabase;

What is table?

So a table refers to the collection of data stored in the form of rows and columns.

• Rows: Represent individual records or entries (e.g., each customer's details).

• Columns (Fields): These define the data type and structure of the table. Each column has a name and a data type (e.g., INT, VARCHAR, DATE).

• Creating a table

•   CREATE TABLE table_name (
        column1 datatype constraints,
        column2 datatype constraints,
        column3 datatype constraints,
        ...
    );
  

  • table_name: The name of the table.

  • column1, column2, ...: The names of the columns you want to include in the table.

  • datatype: The type of data that will be stored in the column (e.g., INT, VARCHAR, DATE).

  • constraints: Optional rules like PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY.

example:

•   CREATE TABLE Employees (
        EmployeeID INT PRIMARY KEY,   -- Unique identifier for each employee
        FirstName VARCHAR(50) NOT NULL, -- Employee's first name, cannot be null
        LastName VARCHAR(50) NOT NULL,  -- Employee's last name, cannot be null
        Email VARCHAR(100),             -- Employee's email
        HireDate DATE,                  -- Employee's hire date
        Salary DECIMAL(10, 2)           -- Employee's salary with two decimal places
    );
  

• SQL commands

• SQL commands are divided into five main categories:

  1. Data Definition Language (DDL): Defines and manages database structure.

     • CREATE: Creates a table or database object.

     • ALTER: Modifies an existing table.

     • DROP: Deletes a table.

     • TRUNCATE: Removes all data from a table but keeps its structure.

  2. Data Manipulation Language (DML): Modifies data in tables.

     • INSERT: Adds new data.

     • UPDATE: Modifies existing data.

     • DELETE: Removes data.

  3. Data Query Language (DQL): Queries data.

     • SELECT: Retrieves data from the database.

  4. Transaction Control Language (TCL): Manages transactions.

     • COMMIT: Saves changes.

     • ROLLBACK: Reverts changes.

     • SAVEPOINT: Sets a save point for partial rollback.

  5. Data Control Language (DCL): Manages access to data.

     • GRANT: Gives user permissions.

     • REVOKE: Removes user permissions.

Aggregate Functions

Aggregate functions in SQL perform calculations on multiple rows of data and return a single result. They are often used with the GROUP BY clause to summarize data.

Common Aggregate Functions:

Aggregate functions in SQL perform calculations on multiple rows of data and return a single result. They are often used with the GROUP BY clause to summarize data.

Common Aggregate Functions:

1. COUNT(): Counts the number of rows.

    SELECT COUNT(*) FROM Employees;
   

2. SUM(): Calculates the total sum of a numeric column.

    SELECT SUM(Salary) FROM Employees;
   

3. AVG(): Returns the average value of a numeric column.

    SELECT AVG(Salary) FROM Employees;
   

4. MAX(): Returns the highest value in a column.

    SELECT MAX(Salary) FROM Employees;
   

5. MIN(): Returns the lowest value in a column.

    SELECT MIN(Salary) FROM Employees;
   

These functions help in summarizing data, such as calculating totals, averages, or counts across a table.

SQL joins are used to combine rows from two or more tables based on a related column between them. There are several types of joins:

1. INNER JOIN:

Returns only the rows that have matching values in both tables.

SELECT *
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

2. LEFT (OUTER) JOIN:

Returns all rows from the left table and the matched rows from the right table. If no match, NULL values are returned for columns from the right table.

SELECT *
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

3. RIGHT (OUTER) JOIN:

Returns all rows from the right table and the matched rows from the left table. If no match, NULL values are returned for columns from the left table.

SELECT *
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

4. FULL (OUTER) JOIN:

Returns all rows when there is a match in either table, filling NULL values for non-matching rows.

SELECT *
FROM Orders
FULL JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

5. CROSS JOIN:

Returns the Cartesian product of the two tables (every combination of rows).

SELECT *
FROM Orders
CROSS JOIN Customers;

These joins allow you to query data across multiple tables in relational databases based on logical relationships.

SQL constraints are rules applied to columns in a table to ensure data integrity and enforce data consistency. They restrict the type of data that can be entered into a table and ensure that the data meets specific criteria.

Common SQL Constraints:

1. PRIMARY KEY

   • Uniquely identifies each record in a table.

   • Must contain unique values and cannot be NULL.

   • Each table can have only one primary key.

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

2. FOREIGN KEY

   • Ensures referential integrity by linking columns in one table to the primary key of another table.

   • Enforces a relationship between two tables.

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

3. UNIQUE

   • Ensures that all values in a column or a set of columns are unique across the table.

   • Can be applied to one or more columns.

    CREATE TABLE Users (
        UserID INT PRIMARY KEY,
        Email VARCHAR(100) UNIQUE
    );

4. NOT NULL

   • Ensures that a column cannot have NULL values.

   • Guarantees that every row must have a value for the specified column.

    CREATE TABLE Products (
        ProductID INT PRIMARY KEY,
        ProductName VARCHAR(100) NOT NULL
    );

5. CHECK

   • Ensures that all values in a column satisfy a specific condition or set of conditions.

   • Used to enforce domain integrity.

    CREATE TABLE Employees (
        EmployeeID INT PRIMARY KEY,
        Age INT CHECK (Age >= 18)
    );

6. DEFAULT

   • Provides a default value for a column when no value is specified during insertion.

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

Example Usage:

Here's a complete table creation example applying several constraints:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,         -- Primary Key
    FirstName VARCHAR(50) NOT NULL,     -- Not Null
    LastName VARCHAR(50) NOT NULL,      -- Not Null
    Email VARCHAR(100) UNIQUE,          -- Unique
    Age INT CHECK (Age >= 0)            -- Check Constraint
);

These constraints help maintain data accuracy and integrity in relational databases by enforcing rules and relationships between data.

Group By and Having Clause

GROUP BY and HAVING are SQL clauses used to group and filter data in combination with aggregate functions.

GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns into aggregated data, often used with aggregate functions like COUNT(), SUM(), AVG(), etc. It allows you to perform operations on groups of rows rather than individual rows.

Syntax:

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

Example:

To find the total sales amount for each product:

SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID;

In this example, rows in the Sales table are grouped by ProductID, and the SUM() function calculates the total sales amount for each product.

HAVING Clause

The HAVING clause is used to filter groups based on a condition, similar to the WHERE clause, but it works on aggregated data created by the GROUP BY clause.

Syntax:

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

Example:

To find products with total sales greater than $1,000:

SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID
HAVING SUM(SalesAmount) > 1000;

In this example, after grouping the data by ProductID and calculating the total sales, the HAVING clause filters out groups where the total sales are $1,000 or less.

Key Points:

• GROUP BY: Used to group rows into summary rows based on specified columns.

• HAVING: Used to filter groups based on conditions applied to aggregate functions. It is typically used after GROUP BY.

These clauses are crucial for summarizing and analyzing data effectively in SQL.

Sub queries in SQL

Subqueries (or inner queries) are SQL queries embedded within another query. They allow you to perform operations that require multiple steps or need to retrieve data based on the results of another query.

Types of Subqueries:

1. Single-Row Subquery

   • Returns a single row and is used with comparison operators like =, >, <, etc.

Example:

Find employees who earn more than the average salary:

    SELECT EmployeeName
    FROM Employees
    WHERE Salary > (SELECT AVG(Salary) FROM Employees);

2. Multiple-Row Subquery

   • Returns multiple rows and is used with operators like IN, ANY, or ALL.

Example:

Find employees who work in departments where the average salary is greater than $50,000:

    SELECT EmployeeName
    FROM Employees
    WHERE DepartmentID IN (
        SELECT DepartmentID
        FROM Departments
        WHERE AVG(Salary) > 50000
        GROUP BY DepartmentID
    );

3. Multiple-Column Subquery

   • Returns multiple columns and is used with operators like =, IN when comparing multiple columns.

Example:

Find employees who have the same department and job as a specific employee:

    SELECT EmployeeName
    FROM Employees
    WHERE (DepartmentID, JobTitle) = (
        SELECT DepartmentID, JobTitle
        FROM Employees
        WHERE EmployeeID = 1
    );

4. Correlated Subquery

   • References columns from the outer query and is evaluated once for each row processed by the outer query.

Example:

Find employees whose salary is greater than the average salary in their own department:

    SELECT EmployeeName
    FROM Employees E1
    WHERE Salary > (
        SELECT AVG(Salary)
        FROM Employees E2
        WHERE E1.DepartmentID = E2.DepartmentID
    );

Key Points:

• Subqueries can be used in SELECT, WHERE, and FROM clauses.

• They allow for more complex queries and help in breaking down large queries into manageable parts.

• Subqueries must be enclosed in parentheses.

Subqueries enhance the flexibility and capability of SQL queries, allowing for more sophisticated data retrieval and manipulation.

Conclusion

SQL is vital for managing and querying relational databases, providing essential tools for data definition, manipulation, and analysis