SQL Aggregate Functions:
CREATE TABLE students (
SId INT PRIMARY KEY,
FName VARCHAR(50),
LName VARCHAR(50),
Gender VARCHAR(10),
Address VARCHAR(100),
phone VARCHAR(15),
Stage VARCHAR(20),
Grade INT,
DOB DATE
);
INSERT INTO students VALUES
(1, 'John', 'Smith', 'Male', '20 Jorge ST WDC', '07901397639', 'Second', 16, '2000-07-01'),
(2, 'Daniel', 'White', 'Male', '6 Indro Rd TX', '07901296629', 'Second', 19, '1999-12-02'),
(3, 'Brian', 'Lee', 'Male', '7 Mars Rd Newyork', '07902397619', 'First', 20, '2001-04-13'),
(4, 'Sarah', 'Mars', 'Female', '8 Kent Rd TX', '07903497618', 'Third', 17, '1998-03-01'),
(5, 'Jim', 'Clark', 'Male', '20 George ST MO', '07904797678', 'Fourth', 16, '2000-10-01'),
(6, 'Phoebe', 'Wong', 'Female', '30 John ST WDC', '07905397529', 'Second', 20, '1998-11-02'),
(7, 'Phoebe', 'Smith', 'Female', '100 Orange Rd TX', '07906497569', 'First', 20, '1999-09-15'),
(8, 'Joyce', 'Brown', 'Female', '30 Dutton ST Newyork', '07906767539', 'Second', 17, '2000-01-10'),
(9, 'Joey', 'Chan', 'Male', '100 Orange Rd TX', '07905197939', 'Fourth', 20, '2001-07-22'),
(10, 'April', 'Webster', 'Female', '30 Linda ST WDC', '07906497438', 'First', 18, '1997-05-30');
INNER JOIN
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(50),
CategoryID INT,
Price DECIMAL
);
CREATE TABLE Categories (
CategoryID INT PRIMARY KEY,
CategoryName VARCHAR(50),
Description VARCHAR(255)
);
INSERT INTO Products VALUES
(1, 'Chais', 1, 18),
(2, 'Chang', 1, 19),
(3, 'Aniseed Syrup', 2, 10);
INSERT INTO Categories VALUES
(1, 'Beverages', 'Soft drinks, coffees, teas, beers, and ales'),
(2, 'Condiments', 'Sweet and savory sauces, relishes and spreads'),
(3, 'Confections', 'Desserts, candies, and sweet breads');
LEFT JOIN
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
EmployeeID INT,
OrderDate DATE,
ShipperID INT );
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
ContactName VARCHAR(50),
Address VARCHAR(100),
City VARCHAR(50),
PostalCode VARCHAR(20),
Country VARCHAR(50) );
INSERT INTO Orders VALUES
(10308, 2, 7, '1996-09-18', 3),
(10309, 37, 3, '1996-09-19', 1),
(10310, 77, 8, '1996-09-20', 2);
INSERT INTO Customers VALUES
(1, 'Alfreds', 'Maria', 'Ob Str.57', 'Berlin', '12209', 'Germany'),
(2, 'Ana Trujillo', 'Ana Trujillo', 'Avda.2222', 'México', '05021', 'Mexico'),
(3, 'Antonio Moreno', 'Antonio Moreno', 'deros23', 'México', '05023', 'Mexico');
RIGHT JOIN
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
LastName VARCHAR(50),
FirstName VARCHAR(50),
BirthDate DATE,
Photo VARCHAR(50)
);
INSERT INTO Employees VALUES
(1, 'Davolio', 'Nancy', '1968-12-08', '[Link]'),
(2, 'Fuller', 'Andrew', '1952-02-19', '[Link]'),
(3, 'Leverling', 'Janet', '1963-08-30', '[Link]');
SELF JOIN
CREATE TABLE Suppliers (
SupplierID INT PRIMARY KEY,
SupplierName VARCHAR(50),
ContactName VARCHAR(50),
Address VARCHAR(100),
City VARCHAR(50),
PostalCode VARCHAR(20),
Country VARCHAR(50)
);
INSERT INTO Suppliers VALUES
(1, 'Exotic Liquid', 'Cooper', '[Link].', 'London', 'EC1 4SD', 'UK'),
(2, 'New Orleans', 'Burke', 'Box 78934', 'Orleans', '70117', 'USA'),
(3, 'Grandma Kelly', 'Murphy', '[Link]', 'Arbor', '48104', 'USA');