DML Exercises – Pharmacy Database
The main objective of these exercises is to help students practice and master Oracle SQL Data
Manipulation Language (DML) commands. The exercises focus on data retrieval, filtering,
sorting, aggregation functions, joins, and views using a Pharmacy Management Database.
The database contains the following tables:
● branches(branch_id, branch_name, location, phone_number, manager_name)
● pharmacists(pharmacist_id, full_name, qualification, phone_number, branch_id)
● cashiers(cashier_id, full_name, phone_number, hire_date, branch_id)
● medicines(medicine_id, medicine_name, category, unit_price, expiry_date)
● customers(customer_id, full_name, phone_number, address, gender)
● sales(sale_id, sale_date, quantity, customer_id, medicine_id)
For each exercise, write the appropriate Oracle SQL command to obtain the required result.
1. Write an SQL command to display all medicines whose unit price is greater than 1,000.
Show the medicine ID, medicine name, category, and unit price. Sort the results by unit
price in descending order.
2. Write an SQL command to display the most expensive medicine and the cheapest
medicine available in the pharmacy database.
3. Write an SQL command to display:
○ The total quantity of medicines sold.
○ The total number of sales transactions.
○ The average quantity sold per transaction.
4. Write an SQL command to display the number of pharmacists working in each branch.
Show the branch ID and the number of pharmacists assigned to that branch.
5. Write an SQL command to display all branches together with their pharmacists. Include:
○ Branch ID
○ Branch Name
○ Pharmacist ID
○ Pharmacist Name
6. Ensure that branches without pharmacists are also displayed.
7. Write an SQL command to display all customers whose gender is 'Female'.
8. Write an SQL command to display all medicines belonging to the category 'Painkiller'.
9. Write an SQL command to display all sales where the quantity sold is greater than 2.
10.Write an SQL command to display all medicines ordered by medicine name in ascending
order.
1
11.Write an SQL command to display all customers ordered by full name in descending
order.
12.Write an SQL command to display the maximum quantity sold in a single sale
transaction.
13.Write an SQL command to display the minimum quantity sold in a single sale
transaction.
14.Write an SQL command to display the total value of all medicine prices.
15.Write an SQL command to display the total number of medicines available in the
database.
16.Write an SQL command to display the number of cashiers working in each branch.
17.Write an SQL command to display the total quantity sold for each medicine.
18.Write an SQL command to display the number of customers by gender.
19.Write an SQL command to display the average quantity sold for each medicine.
20.Write an SQL command to display all branches together with their cashiers, including
branches that have no cashiers assigned.
21.Write an SQL command to display all branches together with their pharmacists, including
branches that have no pharmacists assigned.
22.Write an SQL command to display all pharmacists and the branches where they work,
including pharmacists that are not assigned to any branch.
23.Write an SQL command to display all cashiers and the branches where they work,
including cashiers that are not assigned to any branch.
24.Write an SQL command to display all sales together with the customer information.
25.Write an SQL command to display all sales together with the medicine information.
26.Write an SQL command to display the customer name, medicine name, sale date, and
quantity sold for every sale transaction.
27.Write an SQL command to create a view called V_MEDICINES that displays the
medicine ID, medicine name, category, and unit price.
28.Write an SQL command to create a view called V_CUSTOMERS that displays the
customer ID, full name, phone number, and address.
29.Write an SQL command to create a view called V_PHARMACISTS that displays the
pharmacist ID, pharmacist name, qualification, and branch ID.
30.Write an SQL command to display all records from the V_MEDICINES view.
31.Write an SQL command to display all records from the V_CUSTOMERS view ordered
by full name.