SQLize | PHPize | SQLtest

-- Yazan Mousa Alrehailie / 4304033 -- Drop tables if they exist (MySQL syntax) DROP TABLE IF EXISTS Purchase; DROP TABLE IF EXISTS Book; DROP TABLE IF EXISTS Customer; -- Create Customer table (MySQL syntax) CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(30) NOT NULL, City VARCHAR(20), Phone VARCHAR(12), UNIQUE (Phone) ); -- Create Book table CREATE TABLE Book ( BookID INT PRIMARY KEY, BookTitle VARCHAR(40) NOT NULL, Author VARCHAR(30), Price DECIMAL(6,2), CHECK (Price >= 0) ); -- Create Purchase table 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 data into Customer 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 data into Book 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 data into Purchase (using MySQL date format) 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 operations UPDATE Book SET Price = Price * 1.10 WHERE BookID = 202; UPDATE Customer SET City = 'Mecca' WHERE CustomerID = 102; DELETE FROM Purchase WHERE Quantity = 1; -- Query 1: Customers in Jeddah SELECT * FROM Customer WHERE City = 'Jeddah'; -- Query 2: Books over 100 SAR SELECT * FROM Book WHERE Price > 100; -- Query 3: Join all tables 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; -- Query 4: Total quantity by BookID SELECT BookID, SUM(Quantity) AS TotalQuantity FROM Purchase GROUP BY BookID; -- Query 5: Max and min prices SELECT MAX(Price) AS MaxPrice, MIN(Price) AS MinPrice FROM Book; -- Create view (MySQL syntax) 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; -- Query the view SELECT * FROM View_Customer_Purchases WHERE PurchaseDate > '2025-03-23'; -- Drop the view DROP VIEW IF EXISTS View_Customer_Purchases;