Given the following diagram below, create SQL queries for the following: The fol

Question

Given the following diagram below, create SQL queries for the following:

The following DMLs must be exercised ( these ought to be a consequence of the questions your database is designed to answer).

1.Joins: inner and outer

2. Group By

3. Having

4. Aggregations (Sum, Max)

5. SQL Selects illustrating alias, and computation

Minimum set of DataTypes to be included

Timestamp

Decimal

Integer

Varchar

Minimum Constraints to be illustrated

Binary relation

Ternary relation (e.g Class is held in Room Perl203 on TTH)

Uniqueness, mandatory, and value constraints on all appropriate entities.

Subtype e.g. ( Employee: HourlyEmployee, SalariedEmployee)

Summary

Diagram:

Hospital Hospital ID Room ID RoomType Staff Staff ID fname Iname address phone Doctor Doctor ID fname Iname address phone speciality Medicine Medicine_ID Description Schedule Staff ID Patient_ID Schedule_ID Date Inventory Inventory_ID Room ID InventoryName InventoryAmount Immunization Immunization_ID Staff ID ImmunizationName Patient PatientID fname Iname Prescription Patient_ID Medicine_ID address phone

Explanation / Answer

For the given ER diagram not much information is provided about the data types, so I am assuming data types of the atributes of the given entities. The queries which uses group by, having, aggregate, select are given below. I am providing 3 examples which answer 3 major questions from the database.

1) No. of appointments each doctor have. Two tables are joined and aggregate function COUNT() is used along with group by clause. Date attributes is used which is of type timestamp.

SELECT Doctor_ID, fname, lname, Date, COUNT(s.Schedule_ID)
FROM Doctor d
INNER JOIN Schedule s
ON s.StaffID = d.Doctor_ID
GROUP BY Doctor_ID, fname, lname, Date;

2) Which doctor has the maximum number of appointments in a particular date. Here nesting of aggregate function COUNT and MAX is used along with group by clause.

SELECT Doctor_ID, fname, lname, MAX(COUNT(s.Schedule_ID))
FROM Doctor d
INNER JOIN Schedule s
ON s.StaffID = d.Doctor_ID
WHERE s.Date = '2018-04-25'
GROUP BY Doctor_ID, fname, lname;

3) Which inventory belongs to which hospital along with the amount of inventory. Here alias in select statement is used.
SELECT h.Hospital_ID, i.InventoryName, SUM(i.InventoryAmount) AS 'TotalInventorySum'
FROM Hospital h
INNER JOIN Inventory i
ON i.Room_ID = i.Room_ID
GROUP BY h.Hospital_ID, i.InventoryName
ORDER BY TotalInventorySum;

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.