Introduction to SQL

SQL, or Structured Query Language, is a standardized language used to interact with databases. It enables users to perform a wide array of operations, including querying data, inserting records, updating existing data, and deleting records. SQL operates seamlessly across various relational database systems like MySQL, PostgreSQL, Oracle Database, SQL Server, and more.

SQL Basics

Data Manipulation Language (DML) Commands

1. SELECT: Retrieves data from one or more tables.

   SELECT column1, column2 FROM table_name WHERE condition;

2. INSERT: Adds new records into a table.

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

3. UPDATE: Modifies existing records in a table.

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

4. DELETE: Removes records from a table.

   DELETE FROM table_name WHERE condition;

Data Definition Language (DDL) Commands

1. CREATE: Creates database objects like tables, indexes, views, or schemas.

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

2. ALTER: Modifies the structure of existing database objects.

   ALTER TABLE table_name ADD column_name datatype;

3. DROP: Deletes existing database objects.

   DROP TABLE table_name;

Data Control Language (DCL) Commands

1. GRANT: Provides user access privileges to database objects.

   GRANT SELECT, INSERT ON table_name TO user_name;

2. REVOKE: Withdraws previously granted permissions from users.

   REVOKE SELECT ON table_name FROM user_name;

Transaction Control Commands

1. COMMIT: Saves all changes made during the current transaction to the database.

   COMMIT;

2. ROLLBACK: Undoes changes made during the current transaction and restores the database to its original state since the last COMMIT.

   ROLLBACK;

Querying and Schema Management Commands

1. USE: Specifies which database to use in multi-database environments.

   USE database_name;

2. DESCRIBE (or DESC): Provides metadata about a table's structure.

   DESC table_name;

3. SHOW: Displays information about databases, tables, or other database objects.

   SHOW DATABASES;
   SHOW TABLES;
   

Other Useful SQL Commands

1. TRUNCATE: Deletes all records from a table quickly, but cannot be rolled back.

   TRUNCATE TABLE table_name;

2. GRANT: Assigns specific privileges to database users.

   GRANT SELECT ON table_name TO user_name;

3. REVOKE: Withdraws specific privileges from database users.

   REVOKE SELECT ON table_name FROM user_name;

NOTE:

Query performance can be increased by using indexs and Stored Procedures.

EXAMPLES:

1. Creating Tables

In SQL, tables are created using the CREATE TABLE statement, defining columns along with their data types and constraints:

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

2. Inserting Data

To add data into a table, use the INSERT INTO statement:

INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, DepartmentID)
VALUES (1, 'John', 'Doe', '1990-05-15', 101);

3. Querying Data

Retrieve data from a table using the SELECT statement:

SELECT FirstName, LastName, DepartmentID
FROM Employees
WHERE DepartmentID = 101;

SQL Queries

4. Filtering Data

Filter records using the WHERE clause:

SELECT *
FROM Employees
WHERE BirthDate >= '1990-01-01';

5. Joining Tables- JOINS

Combine data from multiple tables using JOIN clauses:

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

6. Aggregating Data

Aggregate functions summarize data:

SELECT DepartmentID, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID;

Advanced SQL Concepts

7. Subqueries

Nested queries within another query:

SELECT FirstName, LastName
FROM Employees
WHERE DepartmentID IN (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'IT'
);

8. Views(Advanatages)

Virtual tables based on SQL statements:

CREATE VIEW EmployeeDetails AS
SELECT FirstName, LastName, DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

9. Transactions

Manage sequences of SQL operations:

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 123;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 456;
COMMIT;