StoreProject.sql

create database StoreProject;
use StoreProject;

create table Customers (
CustomerID int primary key identity(1,1),
CustomerName nvarchar(50),
Email nvarchar(100),    -- change to be phone num
C_Location nvarchar(50)
);

create table Products(
ProductID int primary key identity(1,1),
ProductName nvarchar(100),
Category nvarchar(100),
price decimal (10,2)
);

create table Orders (
OrderID int primary key identity(1,1),
CustomerID int Foreign key references Customers (CustomerID),
ProductID int Foreign key references Products (ProductID),
OrderDate DateTime default getdate(),
Quantity int
);
alter table Orders alter column Quantity int not null
alter table Orders add TotalPrice Decimal (10,2);
alter table  Customers Drop column Email
alter table  Customers add  PhoneNumber int 
alter table  Customers alter column  PhoneNumber int 
select * from Customers
select * from Products
select * from Orders
delete from Orders
delete from Customers
delete from Products


INSERT INTO Products (ProductName, Category, Price) VALUES
  ('Samsung Refrigerator',        'Electronics',   859.50),
  ('LG Washing Machine',          'Electronics',   679.00),
  ('Sony 55" OLED TV',            'Electronics',  1299.99),
  ('Apple MacBook Air M2',        'Smart Devices', 1199.00),
  ('Samsung Galaxy S23 Ultra',    'Smart Devices', 1099.99),
  ('Apple iPad Pro 11"',          'Smart Devices',  799.00),
  ('Harry Potter and the Philosopher''s Stone', 'Books',  24.99),
  ('The Witcher: The Last Wish',  'Books',         18.95),
  ('Rich Dad Poor Dad',           'Books',         16.50),
  ('Dyson V11 Cordless Vacuum',   'Home Appliances', 599.99),
  ('Nikon D3500 DSLR Camera',     'Photography',    499.95),
  ('Instant Pot Duo 7-in-1',      'Kitchen',        89.99);


INSERT INTO Customers (CustomerName, PhoneNumber, C_Location) VALUES
  ('Fatima Khaled',  '0102356789',     'Cairo'),
  ('Amir Sayed',     '0109875432',     'Alexandria'),
  ('John Smith',     '1415555261', 'San Francisco'),
  ('Maria García',   '349112467', 'Madrid'),
  ('Li Wei',         '801235678', 'Beijing'),
  ('Aisha Mohammed', '074012456',     'London'),
  ('Carlos Ruiz',    '525535678', 'Mexico City'),
  ('Sophie Müller',  '493012456',     'Berlin');
  INSERT INTO Orders (CustomerID, ProductID, Quantity, TotalPrice) VALUES
  (41,  23,  1, 1299.99),   -- Customer 31 buys one Sony 55" OLED TV
  (42,  24,  2, 2398.00),   -- Customer 32 buys two Apple MacBook Air M2
  (43,  22,  1,  859.50),   -- Customer 33 buys one Samsung Refrigerator
  (44,  26,  3, 2397.00),   -- Customer 34 buys three Apple iPad Pro 11"
  (45,  29,  5,   82.50),   -- Customer 35 buys five copies of Rich Dad Poor Dad
  (46, 22,  1,  499.95),   -- Customer 36 buys one Nikon D3500 DSLR Camera
  (47,  25,  2, 179.98)   -- Customer 37 buys two Instant Pot Duo 7-in-1

-- 1. Simple WHERE filter
SELECT
  ProductName,
  Category,
  Price
FROM Products
WHERE Price > 20;

--aliase name
SELECT
  p.ProductName   AS [Product Name],
  p.Category      AS [Category],
  p.Price         AS [Unit Price]
FROM Products AS p;

-- Number of products in each category
SELECT
  Category,
  COUNT(*) AS [Number of Products]
FROM Products
GROUP BY Category;

-- Sum of prices by category
SELECT
  Category,
  SUM(Price) AS [Total Price]
FROM Products
GROUP BY Category;

-- Maximum price in each category
SELECT
  Category,
  MAX(Price) AS [Highest Price]
FROM Products
GROUP BY Category;

-- Minimum price in each category
SELECT
  Category,
  MIN(Price) AS [Lowest Price]
FROM Products
GROUP BY Category;

-- Average price in each category
SELECT
  Category,
  AVG(Price) AS [Average Price]
FROM Products
GROUP BY Category;

-- having 
SELECT 
  Category, 
  MAX(Price) AS [Highest Price in the Category]
FROM Products
GROUP BY Category
HAVING MAX(Price) >= 1000;

--order by  
SELECT 
  Category,
  MAX(Price) AS [Highest Price in the Category]
FROM Products
GROUP BY Category
ORDER BY Max(price)

--List every order along with its customer:
SELECT
  c.CustomerID,
  c.CustomerName,
  o.OrderID,
  o.OrderDate
FROM Customers AS c
INNER JOIN Orders AS o
  ON c.CustomerID = o.CustomerID;

  --Show every order’s product details:
  SELECT
  p.ProductName,
  p.ProductID,
  o.Quantity,
  o.TotalPrice
FROM Orders AS o
INNER JOIN Products AS p
  ON o.ProductID = p.ProductID;

  --Find which customers bought which products:
  SELECT
  c.CustomerName,
  p.ProductName,
  o.Quantity
FROM Customers AS c
INNER JOIN Orders   AS o ON c.CustomerID = o.CustomerID
INNER JOIN Products AS p ON o.ProductID = p.ProductID;

--Show every customer—even those with no orders:
SELECT
  c.CustomerName,
  c.CustomerID,
  o.OrderID       AS MaybeOrderID,
  o.OrderDate     AS MaybeOrderDate
FROM Customers AS c
LEFT JOIN Orders AS o
  ON c.CustomerID = o.CustomerID;

  --List all products, including those that have never been ordered:
  SELECT
  p.ProductID,
  p.ProductName,
  o.OrderID       AS MaybeOrderID,
  o.Quantity      AS MaybeQuantity
FROM Products AS p
LEFT JOIN Orders AS o
  ON p.ProductID = o.ProductID;

  --Get every customer and every order, matched where they correspond:
  SELECT
  c.CustomerID,
  c.CustomerName,
  o.OrderID,
  o.OrderDate
FROM Customers AS c
FULL OUTER JOIN Orders AS o
  ON c.CustomerID = o.CustomerID;

  --Show every order and every product, matched when an order refers to that product:
  SELECT
  o.OrderID,
  p.ProductID,
  p.ProductName,
  o.Quantity
FROM Orders AS o
FULL OUTER JOIN Products AS p
  ON o.ProductID = p.ProductID;