Oracle Assignment
CREATE TABLE Patients (
PatientId INT PRIMARY KEY,
PatientName VARCHAR(255),
Phone VARCHAR(20)
);
INSERT INTO Patients VALUES (101, 'Hassan Ali Omar', '0617290012');
INSERT INTO Patients VALUES (102, 'Jamila Ahmed Abdi', '0612936543');
INSERT INTO Patients VALUES (103, 'Yusuf Faysal Iman', '0611228731');
INSERT INTO Patients VALUES (104, 'Bashi Ibrahim Osman', '0614101921');
INSERT INTO Patients VALUES(105, 'Asli Mohamed Farah', '0615593212');
CREATE TABLE Drugs (
DrugId INT PRIMARY KEY,
DrugName VARCHAR(255),
DrugType VARCHAR(50),
Quantity INT,
Price DECIMAL(10, 2),
ExpireDate DATE
);
INSERT INTO Drugs VALUES (1, 'Paractemol', 'Tablet', 34, 0.5, '24-dec-21');
INSERT INTO Drugs VALUES (2, 'Coldtab', 'Tablet', 20, 0.75, '24-may-01');
INSERT INTO Drugs VALUES (3, 'Ceftrixione', 'Injection', 12, 1,'24-march-01');
INSERT INTO Drugs VALUES (4, 'Gentamycin', 'Injection', 5, 0.75, '24-sep-01');
INSERT INTO Drugs VALUES (5, 'MultiVIT', 'Syrup', 10, 1.5, '24-Dec-01');
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
PatientId INT,
DrugId INT,
Qty INT,
OrderDate DATE,
FOREIGN KEY (PatientId) REFERENCES Patients(PatientId),
FOREIGN KEY (DrugId) REFERENCES Drugs(DrugId)
);
INSERT INTO Orders VALUES (1001, 101, 3, 2, '23-Dec-13');
INSERT INTO Orders VALUES (1002, 104, 2, 1, '23-Dec-13');
INSERT INTO Orders VALUES (1003, 102, 1, 5, '23-Dec-14');
INSERT INTO Orders VALUES (1004, 101, 5, 2, '23-Dec-14');
INSERT INTO Orders VALUES(1005, 105, 5, 1, '23-Dec-14');
INSERT INTO Orders VALUES(1006, 105, 5, 1, '23-Dec-14');
Having these tables and their data write the following queries
1) Using Orders Table, write a query that tells the highest QTY that is ordered.
SELECT MAX(Qty) AS HighestQty FROM Orders;
2) Using Drugs Table, write a query that displays the lowest Price.
SELECT MIN(Price) AS LowestPrice FROM Drugs;
3) Using Drugs and Orders, write a query that creates an INNER JOIN of these two tables.
SELECT [Link], [Link], [Link], [Link], [Link] FROM Orders o
INNER JOIN Drugs d ON [Link] = [Link];
4) Using Patients and Orders, write a query that creates an INNER JOIN of these two
Table.
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Orders o
INNER JOIN Patients p ON [Link] = [Link];
5) Using Orders, write a query that COUNTS the number of Orders that is made at (2023-
12-14)
SELECT COUNT([Link]) AS NumberOfOrders
FROM Orders o
WHERE [Link] = TO_DATE('23-Dec-14', 'DD-Mon-YY');
6) Using Orders, write a query that displays Orderdate and the SUM of the Total Qty that
is Made but use group by OrderDate
SELECT Orderdate, SUM(Qty) AS TotalQty
FROM Orders
GROUP BY Orderdate;
7) Using Drugs Table, write a query that displays DrugType and the SUM of the Total
Quantity but use group by DrugType
SELECT DrugType, SUM(Quantity) AS TotalQuantity
FROM Drugs
GROUP BY DrugType;
8) Using Patients,Drugs and Orders, write a query that joins triple tables and the output is
like this:
SELECT [Link], [Link], [Link], [Link], [Link], [Link],
[Link],
([Link] * [Link]) AS Total, [Link]
FROM Orders o
INNER JOIN Patients p ON [Link] = [Link]
INNER JOIN Drugs d ON [Link] = [Link];
9) Write a query that subtracts the ordered drug quantity and drug store quantity to display
the remaining drug quantity in the store.
SELECT [Link], [Link], [Link] AS InitialQuantity,
([Link] - COALESCE(SUM([Link]), 0)) AS RemainingQuantity
FROM Drugs d
LEFT JOIN Orders o ON [Link] = [Link]
GROUP BY [Link], [Link], [Link];
