SQL (Structured Query Language) is a standardized language used to manage and manipulate relational databases. SQL allows you to create, query, update, and delete data from relational databases. It also handles the creation of database objects such as tables, views, and indexes.

1. Key Concepts in SQL :

Tables: The core components of relational databases, where data is stored in rows and columns. Each table typically represents an entity, such as a customer or an order.
Fields (or Columns): These represent the attributes of the entity, like customer names, product prices, etc.
Records (or Rows): These represent a single instance of the entity, like a specific customer or an order.
Primary Key: A unique identifier for each row in a table. For example, a "CustomerID" can be the primary key in a "Customers" table.
Foreign Key: A field that links one table to another, establishing a relationship between them. It refers to the primary key of another table.

2. SQL Operations (CRUD) :

1. CREATE (Create Database and Tables):

CREATE DATABASE: This creates a new database.

CREATE DATABASE ecommerce;

CREATE TABLE: This creates a new table within the database. Example: Create a Customers table in the ecommerce database.

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

2. READ (Retrieve Data):

SELECT: This retrieves data from the database. Example: Retrieve all rows and columns from the Customers table.

SELECT * FROM Customers;

Output:

+----------+----------+---------+---------------+------------+
|CustomerID| FirstName| LastName|  Email        |Phone       |
+----------+----------+---------+---------------+------------+
| 1        |John      | Doe     |john@example.com|123-456-789|
| 2        | Jane     | Smith   |jane@example.com|987-654-321|
+----------+----------+---------+----------------+-----------+

WHERE Clause: Filters results based on a condition. Example: Retrieve customers whose FirstName is "John."

SELECT * FROM Customers WHERE FirstName = 'John';

ORDER BY: Sorts the result set in ascending or descending order. Example: Get all customers ordered by LastName.

SELECT * FROM Customers ORDER BY LastName ASC;

3. UPDATE (Modify Data)

UPDATE: Changes existing records. Example: Update the email address for a customer.

UPDATE Customers
SET Email = 'newemail@example.com'
WHERE CustomerID = 1;

After this query runs, the email of the customer with CustomerID = 1 will be updated.

4. DELETE (Remove Data)

DELETE: Deletes data from the table. Example: Delete a customer with CustomerID = 2.

DELETE FROM Customers WHERE CustomerID = 2;

This removes the row where CustomerID equals 2.

3. SQL JOIN Operations:

JOIN operations combine rows from two or more tables based on a related column (foreign key).

1. INNER JOIN:

Returns rows that have matching values in both tables. Example: Retrieve customer orders (join Customers and Orders tables).

SELECT Customers.FirstName, Customers.LastName, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

In this query, the INNER JOIN combines records from Customers and Orders based on matching CustomerID values.

2. LEFT JOIN:

Returns all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned. Example:

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

This will return all customers, even if they haven’t placed an order.

3. RIGHT JOIN:

Similar to LEFT JOIN, but it returns all rows from the right table and matching rows from the left table.

4. SQL Functions:

1. Aggregate Functions

COUNT(): Returns the number of rows. Example: Count the number of customers.

SELECT COUNT(*) FROM Customers;

SUM(): Returns the sum of a numeric column. Example: Sum all order amounts in the Orders table.

SELECT SUM(OrderAmount) FROM Orders;

AVG(): Returns the average of a numeric column. Example: Get the average order amount.

SELECT AVG(OrderAmount) FROM Orders;

2. GROUP BY

Groups rows that have the same values into summary rows. Example: Get the total number of orders for each customer.

SELECT CustomerID, COUNT(OrderID)
FROM Orders
GROUP BY CustomerID;

This groups all orders by CustomerID and counts how many orders each customer has placed.

3. HAVING Clause:

Used to filter records after GROUP BY. Example: Get customers who have placed more than 2 orders.

SELECT CustomerID, COUNT(OrderID)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 2;

5. SQL Data Types:

VARCHAR(n): Variable-length string with a maximum length of n.
INT: Integer values.
DECIMAL(p, s): Decimal numbers with precision p and scale s.
DATE: Date format.
BOOLEAN: True or false values.

6. SQL Constraints:

1. PRIMARY KEY:

Ensures that a column or a group of columns uniquely identifies each row in the table. Example:

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

2. FOREIGN KEY:

Links the primary key of one table to a foreign key in another table. Example:

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

3. NOT NULL:

Ensures that a column cannot have a NULL value.

4. UNIQUE:

Ensures all values in a column are different.

SQL Example Workflow

Step 1: Create Database and Tables

CREATE DATABASE ecommerce;
USE ecommerce;

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

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

Step 2: Insert Data

INSERT INTO Customers (CustomerID, FirstName, LastName, Email) VALUES
(1, 'John', 'Doe', 'john@example.com'),
(2, 'Jane', 'Smith', 'jane@example.com');

INSERT INTO Orders (OrderID, OrderDate, CustomerID) VALUES
(101, '2024-10-20', 1),
(102, '2024-10-21', 2);

Step 3: Query Data

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

SQL (Structured Query Language)

Table of contents