SQL Introduction

SQL is the language we use to talk to databases. It helps us manage and get information from them.

SQL stands for Structured Query Language. Think of it as a special-purpose language designed for asking questions and getting data from a structured collection of information, which we call a database.

What can you do with SQL?

• Ask for data: You can ask the database to show you specific information.
• Add new data: You can insert new records into the database.
• Change existing data: You can update information that's already there.
• Remove data: You can delete records you no longer need.
• Build the database itself: You can create new databases and tables to hold the data.
• Control who sees what: You can set permissions for users.

SQL Syntax

Databases are made up of tables. A table is like a spreadsheet, with rows and columns. For an e-commerce site, you might have a Customers table.

To get data, you write an SQL "statement" or "query". This command tells the database to show you all the records from the "Customers" table:

SELECT * FROM Customers;

SQL SELECT Statement

The SELECT command is used to get data from a database. The result is shown as a temporary table.

Selecting Specific Columns

If you only want to see certain columns, just list their names.

-- This shows only the name and city for each customer.
SELECT CustomerName, City FROM Customers;

Selecting All Columns

To see every column in a table, use the asterisk * (often called a wildcard).

-- This shows all information for every customer.
SELECT * FROM Customers;

SQL SELECT DISTINCT

The SELECT DISTINCT command shows you only the unique values in a column. It removes any duplicate entries.

For example, to see a list of all the cities your customers are from, without seeing "Mumbai" multiple times, you would use this:

-- Show me a list of unique cities from the Customers table.
SELECT DISTINCT City FROM Customers;

SQL WHERE Clause

The WHERE clause is used to filter your results and get only the records that match a specific condition. Think of it as adding a filter to your search.

Example with Text

-- Show me all customers who are from Delhi.
SELECT * FROM Customers
WHERE City = 'Delhi';

Example with Numbers

Let's say we have a Products table with a `Price` column in Rupees (₹).

-- Show me all products that cost more than ₹1000.
SELECT * FROM Products
WHERE Price > 1000;

SQL AND, OR, and NOT Operators

You can combine multiple conditions in your WHERE clause using these operators.

• AND: All conditions must be true.
• OR: At least one of the conditions must be true.
• NOT: The condition must be false.

AND Example

-- Find products in the 'Electronics' category that cost less than ₹5000.
SELECT * FROM Products
WHERE Category = 'Electronics' AND Price < 5000;

OR Example

-- Find customers who live in either Mumbai or Bengaluru.
SELECT * FROM Customers
WHERE City = 'Mumbai' OR City = 'Bengaluru';

Combining AND & OR

You can use parentheses () to group conditions, just like in math.

-- Find customers from Mumbai who are named Priya OR Rahul.
SELECT * FROM Customers
WHERE City = 'Mumbai' AND (CustomerName = 'Priya Sharma' OR CustomerName = 'Rahul Kumar');

SQL ORDER BY

The ORDER BY command sorts your results. By default, it sorts in ascending order (A-Z, 1-100).

-- Show all customers, sorted alphabetically by their name.
SELECT * FROM Customers
ORDER BY CustomerName;

To sort in descending order (Z-A, 100-1), use the DESC keyword.

-- Show all products, with the most expensive ones first.
SELECT * FROM Products
ORDER BY Price DESC;

Ordering by Multiple Columns

You can sort by more than one column. It will sort by the first column, and then sort any ties by the second column.

-- Sort by City, then by CustomerName within each city.
SELECT * FROM Customers
ORDER BY City, CustomerName;

SQL INSERT INTO

The INSERT INTO command adds a new row of data to a table.

-- Add a new customer to the table.
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Rohan Das', 'Kolkata', 'India');

SQL UPDATE

The UPDATE command changes existing data in a table.

-- Priya Sharma moved from Mumbai to Pune. Let's update her city.
UPDATE Customers
SET City = 'Pune'
WHERE CustomerID = 1;

SQL DELETE

The DELETE command removes rows from a table.

-- Delete the customer named 'Amit Patel'.
DELETE FROM Customers WHERE CustomerName='Amit Patel';

SQL TOP / LIMIT

This command lets you specify the maximum number of records to return. It's useful for seeing a quick preview of your data.

MySQL / PostgreSQL Example

-- Show me the first 5 products from the table.
SELECT * FROM Products LIMIT 5;

SQL MIN() and MAX()

These "aggregate functions" find the smallest (MIN) or largest (MAX) value in a column.

You can also rename the result column using AS to make it more readable.

-- Find the price of the cheapest product we sell.
SELECT MIN(Price) AS CheapestPrice FROM Products;

-- Find the price of the most expensive product.
SELECT MAX(Price) AS MostExpensivePrice FROM Products;

SQL COUNT(), AVG(), SUM()

These are more aggregate functions that perform calculations on your data.

• COUNT(): Counts the number of rows.
• AVG(): Calculates the average of a numeric column.
• SUM(): Adds up all the values in a numeric column.

-- How many products do we have in stock?
SELECT COUNT(ProductID) FROM Products;

-- What is the average price of our products?
SELECT AVG(Price) FROM Products;

-- What is the total value of all sales?
SELECT SUM(OrderAmount) FROM Orders;

SQL LIKE Operator

The LIKE operator lets you search for patterns in text data. It uses "wildcards" to represent unknown characters.

• %: Represents zero, one, or many characters.
• _: Represents a single character.

-- Find any customer whose name starts with 'A'. (e.g., Amit, Anjali)
SELECT * FROM Customers WHERE CustomerName LIKE 'A%';

-- Find any product with 'Kurta' in its name.
SELECT * FROM Products WHERE ProductName LIKE '%Kurta%';

SQL IN Operator

The IN operator is a shortcut for multiple OR conditions. It lets you specify a list of values to match.

-- Find all customers from Mumbai, Delhi, or Chennai.
SELECT * FROM Customers
WHERE City IN ('Mumbai', 'Delhi', 'Chennai');

SQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The start and end values are included.

-- Find products with a price between ₹500 and ₹1500.
SELECT * FROM Products
WHERE Price BETWEEN 500 AND 1500;

SQL Joins

Often, the data you need is spread across multiple tables. A JOIN is used to combine rows from two or more tables based on a related column between them.

For example, an Orders table might have a CustomerID, which links to the CustomerID in the Customers table.

SQL INNER JOIN

An INNER JOIN shows you only the records that have a matching value in both tables. If a customer has never placed an order, they won't show up in the result.

-- Show a list of orders and the name of the customer who placed it.
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SQL LEFT JOIN

A LEFT JOIN shows you all records from the left table (the first one mentioned), and any matching records from the right table. If there's no match, the columns from the right table will be empty (NULL).

This is useful for finding things that don't have a match. For example, which customers have never placed an order?

-- Show all customers and any orders they might have placed.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

SQL RIGHT JOIN

A RIGHT JOIN is the opposite of a LEFT JOIN. It shows you all records from the right table (the second one), and any matching records from the left table.

-- Show all employees and the orders they have handled.
SELECT Orders.OrderID, Employees.EmployeeName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID;

SQL FULL OUTER JOIN

A FULL OUTER JOIN shows all records when there is a match in either the left or the right table. It combines the results of both LEFT and RIGHT joins.

-- Show all customers and all orders, linking them where possible.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID;

SQL GROUP BY

The GROUP BY command groups rows that have the same values into summary rows. It's almost always used with aggregate functions like COUNT(), SUM(), etc.

-- Count how many customers we have in each city.
SELECT COUNT(CustomerID), City
FROM Customers
GROUP BY City;

SQL HAVING Clause

The HAVING clause is used to filter the results of a GROUP BY query. It's like a WHERE clause, but for groups.

-- Show only the cities that have more than 10 customers.
SELECT COUNT(CustomerID), City
FROM Customers
GROUP BY City
HAVING COUNT(CustomerID) > 10;

SQL CASE Statement

The CASE statement allows you to add if-then-else logic to your query. It goes through conditions and returns a value when the first condition is met.

-- Categorize products by price.
SELECT ProductName, Price,
CASE
    WHEN Price > 2000 THEN 'Premium'
    WHEN Price > 500 THEN 'Mid-Range'
    ELSE 'Budget'
END AS PriceCategory
FROM Products;

SQL Databases

These commands are for managing the databases themselves.

-- Creates a new, empty database for our e-commerce site.
CREATE DATABASE IndiaMartDB;

-- Deletes the database. This cannot be undone!
DROP DATABASE IndiaMartDB;

SQL Tables

These commands are for creating and changing the structure of tables.

CREATE TABLE

Creates a new table, where you define the columns and the type of data each column will hold.

CREATE TABLE Employees (
    EmployeeID int,
    LastName varchar(255),
    FirstName varchar(255),
    HireDate date
);

ALTER TABLE

Changes the structure of an existing table.

-- Add a new 'Department' column to the Employees table.
ALTER TABLE Employees
ADD Department varchar(255);

SQL Constraints

Constraints are rules for the data in a table. They are used to ensure data accuracy and reliability.

CREATE TABLE Users (
    UserID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    Email varchar(255) UNIQUE,
    Age int CHECK (Age>=18),
    City varchar(255) DEFAULT 'Mumbai'
);