QUESTION 1 : RELATIONAL ALGEBRA.

Added on - 22 Sep 2019

  • 4

    Pages

  • 935

    Words

  • 101

    Views

  • 0

    Downloads

Trusted by +2 million users,
1000+ happy students everyday
Showing pages 1 to 2 of 4 pages
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 dON 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 dON 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 dON 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 aON al.AccountNo=a.AccountNoWHERE a.PatientID=(SELECT PatientID FROM PATIENT WHERE FamilyName="Eggert" ANDGivenName="Betty");7.SELECT d.Name,SUM(i.Fee)FROM DOCTOR d INNER JOIN ACCOUNT a ON d.ProviderNo=a.ProviderNo INNER JOINACCOUNTLINE 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 JOINACCOUNTLINE 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 aON 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)
desklib-logo
You’re reading a preview
Preview Documents

To View Complete Document

Click the button to download
Subscribe to our plans

Download This Document