SQLize | PHPize | SQLtest

-- Yazan Mousa Alrehailie / 4304033 DROP TABLE IF EXISTS Purchase; DROP TABLE IF EXISTS Book; DROP TABLE IF EXISTS Customer; CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(30) NOT NULL, City VARCHAR(20), Phone VARCHAR(12), UNIQUE (Phone) ); CREATE TABLE Book ( BookID INT PRIMARY KEY, BookTitle VARCHAR(40) NOT NULL, Author VARCHAR(30), Price DECIMAL(6,2), CHECK (Price >= 0) ); CREATE TABLE Purchase ( PurchaseID INT PRIMARY KEY, CustomerID INT, BookID INT, Quantity INT DEFAULT 1, PurchaseDate DATE DEFAULT (CURRENT_DATE), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID), FOREIGN KEY (BookID) REFERENCES Book(BookID) ); INSERT INTO Customer VALUES (101, 'Sarah Ali', 'Jeddah', '0501234567'); INSERT INTO Customer VALUES (102, 'Mohammed Ahmed', 'Riyadh', NULL); INSERT INTO Customer VALUES (103, 'Fatima Noor', 'Dammam', '0507654321'); INSERT INTO Book VALUES (201, 'Introduction to Databases', 'Elmasri', 120.50); INSERT INTO Book VALUES (202, 'Learning SQL', 'Alan Beaulieu', 95.00); INSERT INTO Book VALUES (203, 'Oracle Essentials', 'Rick Greenwald', 140.75); INSERT INTO Purchase VALUES (301, 101, 201, 1, '2025-03-20'); INSERT INTO Purchase VALUES (302, 102, 202, 2, '2025-03-23'); INSERT INTO Purchase VALUES (303, 103, 203, 1, '2025-03-25'); INSERT INTO Purchase VALUES (304, 101, 202, 3, '2025-04-01'); INSERT INTO Purchase VALUES (305, 103, 201, 2, '2025-04-05'); UPDATE Book SET Price = Price * 1.10 WHERE BookID = 202; UPDATE Customer SET City = 'Mecca' WHERE CustomerID = 102; DELETE FROM Purchase WHERE Quantity = 1; SELECT * FROM Customer WHERE City = 'Jeddah'; SELECT * FROM Book WHERE Price > 100; SELECT c.CustomerName, b.BookTitle, p.PurchaseDate FROM Customer c JOIN Purchase p ON c.CustomerID = p.CustomerID JOIN Book b ON b.BookID = p.BookID; SELECT BookID, SUM(Quantity) AS TotalQuantity FROM Purchase GROUP BY BookID; SELECT MAX(Price) AS MaxPrice, MIN(Price) AS MinPrice FROM Book; CREATE OR REPLACE VIEW View_Customer_Purchases AS SELECT c.CustomerName, b.BookTitle, p.PurchaseDate FROM Customer c JOIN Purchase p ON c.CustomerID = p.CustomerID JOIN Book b ON b.BookID = p.BookID; SELECT * FROM View_Customer_Purchases WHERE PurchaseDate > '2025-03-23'; DROP VIEW IF EXISTS View_Customer_Purchases;