Using the tables above, write one sql query to answer each of these questions. 1

Question

Using the tables above, write one sql query to answer each of these questions.

1. List the name of every staff member who receives the salary higher than the average salary of the company.

1(a). For every clinic, list clinic number, city, and state along with the pen number and pen status for each clinic.

1(b). List the name and type of pet that has been treated more than 2 days and spent more than $100 for treatment.

1(c). Find the busiest registration month for pet in the past. We assume that the more pets are registered, the busier the month is.

1(d). Find the average cost of treatment for each type of pet.

1(e). List the name of every drug and its current stock that need to be re-ordered. A drug needs to be re-ordered when the quantity in stock is less than the quantity of re-order level. Sort the result by the quantity in stock.

1(f). List the city (e.g., for pet owners) and the total amount of cost spent for pet treatment in each city.

Attribute Type Constraint clinicNg, char05) street 20400 not null. varchar2(15) not null not null state zipcode varchar2(9) not null, unique varchar (20) not null, unique varchar (20) not null, unique Staff Examination Attribute Type examNo char examDate date examTime number not null examResulta varchar2(40) not null char ft Pet stan char 4 Staff Penal Attribute Type char pencapacity number default 2, l and -4. penStatus charadefault. A', either 'A' or FN clinicNg, char(5) fl Clinic, not null Pharmacy Attribute Type drugName varchar not null drugDesc varchar2(40) not null dosage varchar (20) :not null methodadmin varshag20) not null drug cost, pumber 4,2) not null PharmClinicStock Attribute Type Constraint drug Ng, chart 3) pkl, fs Pharmacy) clinicNo char05) pk2, f Clinic) instock number(3) not null reorderLewel pumber 3) not null reorderotye number 3) not null Attribute Type Constraint Attribute Type stan char ownerNo char(5) SEName varchar2(30) not null ofName varchar2(30) not null NAme varchar2(30) not null Name varchar2(30) not null sStreet varchar2(40) not null oStreet varchar2(40) not null varchar2(15) not null varchar2(15) not null not null not null szipCRde varchar not null 209) oziplCode, varchar 209) STAAR varchar (20) not null oTeNg, varchar2(20) not null clinicNo char 5) fl Clinic, not mull not null gender 012) not null, unique Pet position varchar2(20) not null number Attribute Type clinicNo char 5) fl Clinic, notnull char petName, varchar2(30) Treatment petDeas varchar2(40) not rull Attribute Type petDOR date treatNo char not null description 2(40) not null petStatus char not null ownerNo char(5) clinicNg, char(5) f Clinic) Attribute Type Constraint Attribute Tpe Constraint char 4 pkl, fly Pen char pk2, fly Pet) 6) pkl, fs examination) pk3, not null treatSo char 4) pk2, fstreatment) dateOut date startDate, date not null comments varchar2040) endDate date not null quantity number 4,1) not null petCommantsvarchar2040) Item Attribute Type Constraint lteauClinic Stock itemNo char Attribute Type itemName varchar (20) not null item.Desc varchar2(40) not null itemNo char itemcast number 4,2) not null, >0 clinicNe char(5) pk2, f Clinic) instock pumb 6) 60 not null Invoice reorderQbt number(6) not null Atribute Type Constraint Appointment inRaniceDate date not null Attribute Type Constraint datePaid date paymentMethod varchar2015Mnot null char appDate date appTime number not null examNo char fl examination),not null petNo char pet), not null

Explanation / Answer

SELECT Name FROM employees Having Salary>(avg(Salary)); SELECT Clinic.ClinicNo,Clinic.City,Clinic.State,Pen.PenNo,Pen.PenStatus FROM Clinic LEFT JOIN Pen ON Clinic.ClinicNo = Pen.PenNo; SELECT pet.petName,pet.petType FROM pet LeftJoin PetPen On pet.petNo=PetPen.PetPenNo where treatment = 2 days AND cost = 100$; select * from ( select extract(month from dateRegistered) as mnth, count(*) as cnt, rank() over (order by count(*) desc) as rnk from pet group by extract(month from dateRegistered) ) dt where rnk = 1;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.