-- Yazan Mousa Alrehailie / 4304033
CREATE TABLE Customer (
CustomerID NUMBER(5) PRIMARY KEY,
CustomerName VARCHAR2(30) NOT NULL,
City VARCHAR2(20),
Phone VARCHAR2(12) UNIQUE
);
CREATE TABLE Book (
BookID NUMBER(5) PRIMARY KEY,
BookTitle VARCHAR2(40) NOT NULL,
Author VARCHAR2(30),
Price NUMBER(6,2) CHECK (Price >= 0)
);
CREATE TABLE Purchase (
PurchaseID NUMBER(5) PRIMARY KEY,
CustomerID NUMBER(5),
BookID NUMBER(5),
Quantity NUMBER(3) DEFAULT 1,
PurchaseDate DATE DEFAULT SYSDATE,
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, TO_DATE('20-03-2025', 'DD-MM-YYYY'));
INSERT INTO Purchase VALUES (302, 102, 202, 2, TO_DATE('23-03-2025', 'DD-MM-YYYY'));
INSERT INTO Purchase VALUES (303, 103, 203, 1, TO_DATE('25-03-2025', 'DD-MM-YYYY'));
INSERT INTO Purchase VALUES (304, 101, 202, 3, TO_DATE('01-04-2025', 'DD-MM-YYYY'));
INSERT INTO Purchase VALUES (305, 103, 201, 2, TO_DATE('05-04-2025', 'DD-MM-YYYY'));
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;
BEGIN
EXECUTE IMMEDIATE 'DROP VIEW View_Customer_Purchases';
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
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 > TO_DATE('23-03-2025', 'DD-MM-YYYY');
DROP VIEW View_Customer_Purchases;
Telegram: DB developers channel