Day 6 Practice: Creating Views

Hands-On SQL Assignments

Scenario: You are the DBA for a hospital. You have a main table called PatientRecords with columns: PatientID, Name, DOB, Diagnosis, AssignedDoctor, and BillingAmount. You need to create specific Views so different staff members only see what they are legally allowed to see.

Task 1: Column-Level Security (Front Desk View)

The front desk receptionists need to verify patient names and dates of birth, but they should never see the Diagnosis or BillingAmount.
Write a CREATE VIEW statement named vw_FrontDesk that selects only the appropriate, non-sensitive columns.
Write the SELECT statement to test querying your new view.

Task 2: Row-Level Security (Doctor's View)

Dr. Smith should only have access to their own patients.
Write a CREATE VIEW statement named vw_DrSmithPatients that includes all medical columns (Name, DOB, Diagnosis), but uses a WHERE clause to filter the records where AssignedDoctor = 'Dr. Smith'.

Task 3: Aggregation Security (Accounting View)

The hospital's accountants need to know how much revenue each doctor is bringing in, but they shouldn't see individual patient names or diagnoses.
Write a CREATE VIEW statement named vw_DoctorRevenue.
Use GROUP BY AssignedDoctor and calculate the SUM(BillingAmount) as TotalRevenue.

Task 4: Maintenance (Altering and Dropping)

The front desk now needs to see the AssignedDoctor column to help direct patients. Write an ALTER VIEW vw_FrontDesk statement to update the query logic and include that column.
Write the SQL command to completely delete (DROP) the vw_DrSmithPatients view from the database.