Answer To: ALL FIGURES ARE AT THE BOTTOM OF THE PAGE Instructions/Preparation: Step1: Open the SQL Fiddle...
Salony answered on Mar 03 2022
Q3. [ 50 points]Consider the following relations for a database that keeps track of visits of various doctors working for a group of hospitals, to various hospitals under the group:
DOCTORS(DSsn, Name, Start_year, Dept_no, location)
VISIT(DSsn, From_hospital, To_hospital, Departure_date, Return_date, Visit_id)
EXPENSE(Visit_id, Account#, Amount)
(a) [ 25 points]How can you create the table using SQL? Also, show us how you can insert five data values (at least three with the location name New York).
Answer3)
CREATE TABLE DOCTORS (DSsn INT NOT NULL, PRIMARY KEY (DSsn) , Name VARCHAR(50) NOT NULL, Start_year VARCHAR(20) NOT NULL, Dept_no VARCHAR(20), location VARCHAR(20))
INSERT INTO doctors
VALUES (1001,'John',2002,'D-01','New York'),
(1002,'John',2005,'D-01','New York'),
(1004,'Franklin',2009,'D-02','New York'),
(1005,'Smith',2010,'D-03','New York'),
(1006,'Pussy',2012,'D-01','California');
[ 10 points]Write the SQL to list the doctors in New York in descending order by name.
SELECT * FROM `doctors` WHERE location='New York' ORDER BY Name DESC;
[ 15 points] Identify different database keys for this schema, draw the schema diagram, stating any assumptions you make.
Answer
(
Expenses
Visit_id
Account
Amount
) (
VISIT
DSsn
From_hospital
To_hospital
Departure_date
Return_date
Visit_id
) (
Doctors
DSsn
Name
Start Year
Dept No
Location
)2)
4)[ 10 points]Retrieve the names of all male employees in department 5 who earns more than 3000 and works on ProductZ project
i)...