Question: For this project, you will be using the ScrubsHospital database to write the SQL queries for the following scenarios. You need to submit the SQL query for each question and a snapshot of the output (edited screenshot of the Result window in SSMS). Please refer to the Database Diagram “ERDiagramScrubs” in the database. I saw these questions were answeredFor this project, you will be using the ScrubsHospital database to write the SQL queries for the following scenarios. You need to submit the SQL query for each question and a snapshot of the output (edited screenshot of the Result window in SSMS). Please refer to the Database Diagram “ERDiagramScrubs” in the database.
I saw these questions were answered previously, but the queries are very confusing how they were written. For each individual question I need to know what exactly to put into SQL so I can generate it myself and learn from that. This is supposed to be very basic SQL format. Thank you!
Please show each individual query for each question so I understand the entire process. The data needed to be retrieved to form the tables and answer the questions are all in the “ScrubsDatabase.”
List the details of all nurses. In your output, generate an additional column called “Comment” and populate it with the text “Send registration reminder” if the nurse is not registered. [5pt]
List all the departments and their head’s names along with the position of the head. [5pt]
List the names of all patients and the number of appointments they have made where the examination room was “A”. [5pt]
List all the unique patient names who got an appointment in room “B”. [5pt]
List the names of all physicians who are trained in a particular medical procedure along with the name of the procedure. Also list their certification expiration date. You should have the following columns in your output- Physician name, Med Procedure Name, Certificate expiration. [10pt]
List all the physicians with department who are not affiliated. Your output should contain columns with the names – Physician name, Department Name, Physician Position. [10pt]
List the names of patients and their PCP only if their PCP is trained in a medical procedure. [10pt]
List the names of patients and the number of physicians they have taken appointments with only if the number of physicians is greater than 1. [10pt]
List all the patients along with their physician names, nurse names and room numbers when the patient has an appointment anytime between 4/21/2019 and 4/24/2019. [10pt]
List all the patient names and their medications for patients who did not make an appointment. [10pt]
List the count of number of rooms that are unavailable on each block on each floor. Make sure to order the list by floor and block number. Your output should have the columns – “Floor”, “Block” and “# of unavailable rooms” [10pt]
List the floor where there are minimum number of rooms unavailable. Your output should have the floor number, max number of rooms possible on the floor and the number of rooms unavailable. [10pt]
***************************************** CREATING TABLES FROM SCHEMA: SQL to build database schema is as below: CREATE TABLE Employee(emp_id int primary key, name varchar(100), address varchar(100), type varchar(15)); CREATE TABLE Doctor(doctor_emp_…View the full answer
