Oracle Assignment
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];
