For the PHYSICIAN tables below I must design and develop at least 6 queries in y

Question

For the PHYSICIAN tables below I must design and develop at least 6 queries in your database application, among them. (MYSQL)

1) at least 2 queries are multitable queries;

2) at least 2 queries use SQL aggregate functions;

3) at least 1 query uses subquery;

4) NULL search condition should be used at least once;

5) GROUP BY and HAVING clause should be used as least once.

create table office
   (building       varchar(15),
   room_number       varchar(7),
   capacity       numeric(4,0),
   primary key (building, room_number)
   );

create table department
   (dept_name       varchar(20),
   building       varchar(15),
   budget                numeric(12,2) check (budget > 0),
   primary key (dept_name)
   );

create table specialization
   (special_id       varchar(8),
   title           varchar(50),
   dept_name       varchar(20),
   base_cost       numeric(3,0) check (credits > 0),
   primary key (special_id),
   foreign key (dept_name) references department
       on delete set null
   );

create table physician
   (ID           varchar(5),
   name           varchar(20) not null,
   dept_name       varchar(20),
   salary           numeric(8,2) check (salary > 75000),
   primary key (ID),
   foreign key (dept_name) references department
       on delete set null
   );

create table section
   (visit_id       varchar(8),
         sec_id           varchar(8),
   fiscal_quarter       varchar(6)
       check (semester in ('Quarter1', 'Quarter2', 'Quarter3', 'Quarter4')),
   year           numeric(4,0) check (year > 1701 and year < 2100),
   building       varchar(15),
   room_number       varchar(7),
   time_slot_id       varchar(4),
   primary key (visit_id, sec_id, fiscal_quarter, year),
   foreign key (visit_id) references visit
       on delete cascade,
   foreign key (building, room_number) references office
       on delete set null
   );

create table attends
   (ID           varchar(5),
   visit_id       varchar(8),
   sec_id           varchar(8),
   fiscal_quarter   varchar(6),
   year           numeric(4,0),
   primary key (ID, visit_id, sec_id, fiscal_quarter, year),
   foreign key (visit_id,sec_id, fiscal_quarter, year) references section
       on delete cascade,
   foreign key (ID) references instructor
       on delete cascade
   );

create table patient
   (ID           varchar(5),
   name           varchar(20) not null,
   dept_name       varchar(20),
   tot_costs       numeric(3,0) check (tot_cred >= 0),
   primary key (ID),
   foreign key (dept_name) references department
       on delete set null
   );

create table visit
   (ID           varchar(5),
   visit_id       varchar(8),
   sec_id           varchar(8),
   fiscal_quarter   varchar(6),
   year           numeric(4,0),
   health_grd     varchar(2),
   primary key (ID, visit_id, sec_id, fiscal_quarter, year),
   foreign key (visit_id,sec_id, fiscal_quarter, year) references section
       on delete cascade,
   foreign key (ID) references patient
       on delete cascade
   );

create table administrator
   (s_ID           varchar(5),
   i_ID           varchar(5),
   primary key (s_ID),
   foreign key (i_ID) references physician (ID)
       on delete set null,
   foreign key (s_ID) references patient (ID)
       on delete cascade
   );

create table time_slot
   (time_slot_id       varchar(4),
   day           varchar(1),
   start_hr       numeric(2) check (start_hr >= 0 and start_hr < 24),
   start_min       numeric(2) check (start_min >= 0 and start_min < 60),
   end_hr           numeric(2) check (end_hr >= 0 and end_hr < 24),
   end_min       numeric(2) check (end_min >= 0 and end_min < 60),
   primary key (time_slot_id, day, start_hr, start_min)
   );

create table office (building varchar (15) room number varchar (7), capacity numeric (4,0), primary key building room number create table department (dept name varchar (20), building varchar (15), numeric (12, 2) check budget 0), budget primary key dept name create table specialization (special id varchar (8), title varchar (50), dept name varchar (20), base cost numeric (3,0) check credits 0) primary key (special id), foreign key dept name references department on delete set null create table physician (ID varchar (5), varchar (20) not null, name varchar (20), dept name numeric (8, 2) check salary 75000), salary primary key (ID) foreign key dept name references department on delete set null

Explanation / Answer

1)-Multitable queries

i)-select s.title,d.building from department d join specialization s

where d. dept_name =s. dept_name

Query will display the title from specialization and building from department where both the table contaion same dept_name .

ii)-

Select p.name,p.id from patient p join administrator a

where p. ID= a. s_ID

Query will display patient name along with his/her ID from patient whose administrator is same as patient ID.

2)-SQL aggregate functions

i)-Find the sum of budget from department and display it as ‘Total_Budget’.

Select sum (budget) as Total_Budget’ from department

ii)-find the maximum salary of physician group by dept_name.

select max(salary) from physician group by ‘dept_name’ having max (salary)>85000

3)- Subquery

i)-find the name of the department whose budget is greater than average budget.

Select dept_name where budget> (select AVG (budget) from department)

ii)-find the name of all patients those visited after year 2000.

Select p.name from patient where ID=(select ID from visit where year>2000)

4)- NULL search condition

Select * from section where fiscal_quarter IS NULL

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---