Question as follows: Description: Students will practice how to insert into and
ID: 3585581 • Letter: Q
Question
Question as follows:
Description: Students will practice how to insert into and select from a simple database.
Tasks:
A. Insert: (6 marks)
Hint: Insert valid data, and beware of column data types. Assume primary keys are auto incremented.
1. A new row in the “Patients” table. (2 marks) 2. A new row in the “Doctors” table. (2 marks)
3. A new row in the “Visits” table to record a new patient visit. Use data that exists in the other tables for the columns “Patient ID” and “Doctor ID”, and assume any other valid data in the remaining columns. (2 marks)
B. Select: (4 marks)
1. Doctors who work in the “Cardiology” department. (2 marks)
2. Patients who were previously treated by Dr. John Steven (Doctor_ID 5).
Questions (4 marks)
Hint: only brief answers are required, but you are free to explain as much as you need.
C. What is the relationship type between “Patients” and “Doctors” tables? Briefly explain your answer. (2 marks) D. What is the purpose of the columns “Patient ID” in the “Patients” table and “Doctor ID” in the “Doctors”
table? And what is their purpose in the “Visits” table? (2 marks)
Bonus Question (2 marks)
In task B-2 above, assume that we do not know the ID of Dr. John Steven in the “Doctors” table, and rewrite the select statement accordingly.
(2 marks)
Your deliverable for this Question:
For Tasks and Bonus parts, submit your SQL code that you have run correctly and produced results, label each answer of yours by the task letter and number (e.g. ‘A-2’). TA will run the same code to get results. If you couldn’t run the code correctly and received errors and therefore you have no results, submit your code and explain what you did. You will still get a half mark for each task for which you wrote the SQL code partially correct.
For the Questions part, submit your answers labeled by the question letter (e.g. ‘C’ or ‘D’).
This needs to be done in MySQL. And step by step process needs to be shown.
Explanation / Answer
A. Insert:
Hint: Insert valid data, and beware of column data types. Assume primary keys are auto incremented.
1. A new row in the “Patients” table.
Sol: valid data means follow the format of table column type and length. Assume, if name column can contain 500 characters.
Query to insert data :
insert into patients values (null, "Cheggster", null, '1990-08-23', "+19023334444", "1670 Qunipool St, Halifax, NS");
Explanation(Column-wise):
ID: you can put null since it is autoincrement primary key.
Name: you can put name of below or equal to 500 char.
Family_physician : you can put name of below or equal to 200 char. We have not given any name. So this column would be blank.
Date_of_birth: Here date format specified i.e. YYYY-MM-DD. Put date in this format only.
Main_phone: you can put phone number not more than 15 characters.
Home_address: Give address below or equal to 500 characters.
2. A new row in the “Doctors” table.
Sol: Here doctors table is named as physician table.
Query:
insert into physicians values (null, "Arun Grover", "Physiotherapy", "9129333333", "M.D");
Explanation(Column-wise):
ID: you can put null since it is autoincrement primary key.
Name: you can put name of below or equal to 500 char.
Department: you can put department name of below or equal to 50 char.
Office_phone: you can put phone number not more than 15 characters.
seniority: Give address below or equal to 50 characters.
3. A new row in the “Visits” table to record a new patient visit. Use data that exists in the other tables for the columns “Patient ID” and “Doctor ID”, and assume any other valid data in the remaining columns.
Sol:
insert into visits values (null, 3, 4, '2017-08-10', "Normal fever", "C17");
Explaination:
ID: put id as null since it is auto increment primary key.
patient_ID: give existing patient ID. Currently five patients are there so I have chose 3 as patient id.
physician_ID : give existing patient ID. Currently five physicians are there so I have chose 4 as phyician id.
visit_date: give date in specific format.
primary_diagnoses: Give this column below or equal to 1500 characters.
room_number: It is of 5 characters. Give any value below or equal to 5 characters.
B. Select:
1. Doctors who work in the “Cardiology” department.
Sol:
Query:
select physicians.name
from physicians
where physicinas.department = ‘Cardiology’;
Explanation: Here we need to select those doctors name who belong to cardiology department. To write a select query we need to follow a specific order of events. For example, here first we need to mention the column name which we want to fetch then from which table and then put where clause where you write the condition.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.