Structured Query Language (SQL)

Structured Query Language (SQL)

Structured Query Language (SQL) is the standard programming language used to manage and manipulate relational databases. It allows users to interact with a database system to perform various operations like querying data, updating records, deleting data, and managing database schemas. SQL is a declarative language, meaning that users specify what they want to achieve rather than how to achieve it.

SQL is widely used in database management systems (DBMS) like MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

---

Key Operations in SQL

SQL operations can be broadly categorized into Data Query Language (DQL), Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL).

1. Data Query Language (DQL)

• DQL involves selecting or retrieving data from a database.
• SELECT is the primary DQL command used to fetch data from tables.

SELECT Statement:

The basic syntax of the SELECT statement is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• SELECT: Specifies the columns you want to retrieve.
• FROM: Specifies the table from which you want to retrieve data.
• WHERE: Specifies the condition to filter the data.

Example:

SELECT Name, Age
FROM Employees
WHERE Age > 30;

This retrieves the names and ages of employees whose age is greater than 30.

2. Data Definition Language (DDL)

DDL deals with defining, modifying, and deleting database structures such as tables, indexes, and schemas.

• CREATE: Used to create database objects like tables, views, and indexes.
• ALTER: Used to modify the structure of an existing database object.
• DROP: Used to delete database objects like tables or views.

CREATE Table:

The CREATE statement is used to define a new table in the database, specifying columns and their data types.

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

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Department VARCHAR(50)
);

ALTER Table:

The ALTER statement is used to modify the structure of an existing table.

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE Employees
ADD Salary DECIMAL(10, 2);

This adds a new column called Salary to the Employees table.

DROP Table:

The DROP statement is used to delete an existing table and all its data from the database.

DROP TABLE table_name;

Example:

DROP TABLE Employees;

3. Data Manipulation Language (DML)

DML is used to manipulate the data stored in tables. It includes operations like inserting, updating, and deleting data.

• INSERT: Adds new records into a table.
• UPDATE: Modifies existing records in a table.
• DELETE: Removes records from a table.

INSERT Statement:

The INSERT statement adds new rows to a table.

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

Example:

INSERT INTO Employees (EmployeeID, Name, Age, Department)
VALUES (1, 'Alice', 30, 'HR');

UPDATE Statement:

The UPDATE statement is used to modify existing data in a table.

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

Example:

UPDATE Employees
SET Age = 31
WHERE EmployeeID = 1;

This updates the age of the employee with EmployeeID 1 to 31.

DELETE Statement:

The DELETE statement is used to remove records from a table.

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Employees
WHERE EmployeeID = 1;

This deletes the employee with EmployeeID 1 from the Employees table.

4. Data Control Language (DCL)

DCL is used to control access to data in the database, ensuring that only authorized users can perform certain actions.

• GRANT: Gives a user or role permission to perform a specified action.
• REVOKE: Removes a user’s or role’s permission to perform a specified action.

GRANT Statement:

The GRANT statement is used to assign privileges to a user or role.

GRANT privilege_type ON object TO user;

Example:

GRANT SELECT, INSERT ON Employees TO User1;

This grants SELECT and INSERT privileges on the Employees table to User1.

REVOKE Statement:

The REVOKE statement is used to remove privileges.

REVOKE privilege_type ON object FROM user;

Example:

REVOKE INSERT ON Employees FROM User1;

This revokes the INSERT privilege on the Employees table from User1.

---

Advanced SQL Concepts

Joins

SQL Joins allow you to combine data from multiple tables based on a related column. There are several types of joins:

1. INNER JOIN: Returns only the rows that have matching values in both tables.

   SELECT column1, column2
   FROM table1
   INNER JOIN table2
   ON table1.column = table2.column;
   

2. LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, and the matching rows from the right table. If there is no match, the result is NULL on the right side.

   SELECT column1, column2
   FROM table1
   LEFT JOIN table2
   ON table1.column = table2.column;
   

3. RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table, and the matching rows from the left table. If there is no match, the result is NULL on the left side.

   SELECT column1, column2
   FROM table1
   RIGHT JOIN table2
   ON table1.column = table2.column;
   

4. FULL OUTER JOIN: Returns rows when there is a match in either left or right table. If there is no match, the result is NULL from the table without a match.

   SELECT column1, column2
   FROM table1
   FULL OUTER JOIN table2
   ON table1.column = table2.column;
   

5. CROSS JOIN: Returns the Cartesian product of both tables, i.e., all possible combinations of rows.

   SELECT column1, column2
   FROM table1
   CROSS JOIN table2;
   

---

Subqueries

A subquery is a query nested inside another query. It is typically used to perform complex operations or filter data based on the result of another query.

• Example: Retrieve the employees who earn more than the average salary.

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

---

Aggregate Functions

SQL provides aggregate functions to perform calculations on multiple rows of data and return a single result.

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

   SELECT COUNT(*) FROM Employees;
   

2. SUM(): Returns 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 of a column.

   SELECT MAX(Salary) FROM Employees;
   

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

   SELECT MIN(Salary) FROM Employees;
   

---

Group By and Having Clauses

• GROUP BY: Groups rows that have the same values into summary rows, often used with aggregate functions.

  SELECT Department, AVG(Salary)
  FROM Employees
  GROUP BY Department;
  

• HAVING: Filters the results of a GROUP BY query, similar to the WHERE clause but used for aggregated data.

  SELECT Department, AVG(Salary)
  FROM Employees
  GROUP BY Department
  HAVING AVG(Salary) > 50000;
  

---

Conclusion

SQL is a powerful language used for managing relational databases. With SQL, you can define database structures, manipulate data, query information, and control access to the data. Understanding the core SQL operations like SELECT, INSERT, UPDATE, DELETE, and complex topics like joins, subqueries, and aggregate functions will equip you to handle a wide range of tasks in database management.