# Relational Algebra and SQL Queries

4 Pages935 Words496 Views
|
|
|
QUESTION 1: RELATIONAL ALGEBRA1.ΠInstructorName, Specialty(INSTRUCTOR)2.ΠClassName, DescriptionσSuitableFor>60(CLASS)3.ΠInstructorNameσClassName = “Aqua Deep”˅ClassName=”Aqua Fit”(SESSION x INSTRUCTOR)4.ΠMemberName, ClassNameσVenueName = “Studio 5”˄DayAndTime=”19/9/2018”(MEMBER∩ PARTICIPANT ∩ SESSION)5.ΠMemberName(MEMBER∩ PARTICIPANT ∩(σCapacity>30 (SESSION x VENUE)))6.ΠMemberName, SESSION.*σDayAndTime=”19/9/2018”(MEMBER∩ PARTICIPANT ∩ SESSION)7.ΠInstructorNameσVenueName=”Studio 1”˄ VenueName=”Studio 2”(SESSION)8.ΠMemberName(MEMBER∩ PARTICIPANT ∩ (σSuitableFor>60 ˅ VenueName=”Warm Water Pool”(SESSION x CLASS)))9.ΠMemberName(MEMBER∩ PARTICIPANT –(σClassName=”Zumba”(SESSION)))10.ΠMemberName(MEMBER∩ PARTICIPANT ∩ SESSION)QUESTION 2: SQL-SELECT QUERIES1.SELECT FamilyName,SuburbFROM PATIENTWHERE State = "SA";2.SELECT FamilyName,SuburbFROM PATIENTWHERE State = "SA" OR State = "NSW"ORDER BY FamilyName;3.SELECT p.GivenName,p.SuburbFROM PATIENT p INNER JOIN ACCOUNT a ON p.PatientID=a.PatientID INNER JOIN DOCTOR d ON a.ProviderNo=d.ProviderNoWHERE p.State = "WA" AND (d.Name="Dr Brian" OR d.Name="Dr Barbara");4.SELECT p.GivenName,p.SuburbFROM PATIENT p INNER JOIN ACCOUNT a ON p.PatientID=a.PatientID INNER JOIN DOCTOR d ON a.ProviderNo=d.ProviderNoWHERE d.Name="Dr Brian" AND d.Name <> "Dr Ima";5.SELECT COUNT(p.Suburb)FROM PATIENT p INNER JOIN ACCOUNT a ON p.PatientID=a.PatientID INNER JOIN DOCTOR d ON a.ProviderNo=d.ProviderNoGROUP BY a.ProviderNo;
6.SELECT i.Description,a.TreatmentDateFROM ITEM i INNER JOIN ACCOUNTLINE al ON i.ItemNo=al.ItemNo INNER JOIN ACCOUNT a ON al.AccountNo=a.AccountNoWHERE a.PatientID=(SELECT PatientID FROM PATIENT WHERE FamilyName="Eggert" AND GivenName="Betty");7.SELECT d.Name,SUM(i.Fee)FROM DOCTOR d INNER JOIN ACCOUNT a ON d.ProviderNo=a.ProviderNo INNER JOIN ACCOUNTLINE al ON a.AccountNo=al.AccountNo INNER JOIN ITEM i ON al.ItemNo=i.ItemNoGROUP BY d.NameORDER BY d.Name,a.TreatmentDate;8.SELECT d.NameFROM DOCTOR d INNER JOIN ACCOUNT a ON d.ProviderNo=a.ProviderNo INNER JOIN ACCOUNTLINE al ON a.AccountNo=al.AccountNo INNER JOIN ITEM i ON al.ItemNo=i.ItemNoWHERE i.Fee>(SELECT AVG(FEE) FROM ITEM)GROUP BY d.Name;9.SELECT SUM(i.Fee)FROM ITEM i INNER JOIN ACCOUNTLINE al ON i.ItemNo=al.ItemNo INNER JOIN ACCOUNT a ON al.AccountNo=a.AccountNo INNER JOIN PATIENT p ON a.PatientID=p.PatientIDGROUP BY i.Description,p.StateORDER BY p.State;10.SELECT p.PatientID,p.FamilyNameFROM PATIENT p INNER JOIN ACCOUNT a ON p.PatientID=a.PatientID;QUESTION 3: FURTHER SQL1.CREATE TABLE INSTRUCTOR(InstructorName VARCHAR(15),InstructorEmail VARCHAR(20),Biography VARCHAR(30),Specialty VARCHAR(15),CONSTRAINT PK_Instructor PRIMARY KEY (InstructorName)

## End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents

### Support

#### +1 306 205-2269

Chat with our experts. we are online and ready to help.