hello, ive been busy trying over and over and over again to write a block of SQL that will perform this task for me but have failed ever time. hopefully someone can point me in the right direction or at least gime a hand cheers.
task : to work out what is the average cost of hire contracts for each of the five branches?
i have 5 tables set up
which are as follows
/* creates the Branch table
primary key 'BranchNo' */
CREATE TABLE Branch (
BranchNo NUMBER(3) CONSTRAINT pk_branch PRIMARY KEY ,
BranchName VARCHAR2(28) ,
BranchAddress VARCHAR2(40) ,
City VARCHAR2(18) ,
B_tel_no VARCHAR2(12) );
/* creates the staff table
primary key 'StaffID'
foreign key 'BranchNo' */
CREATE TABLE Staff (
StaffID NUMBER(3) CONSTRAINT pk_staff PRIMARY KEY ,
StaffName VARCHAR2(20) ,
BranchNo NUMBER(3) CONSTRAINT fk_BranchNo references Branch(BranchNo) ,
JobTitle VARCHAR2(20) );
/* creates the Customer table
primary key 'CustomerNo' */
CREATE TABLE Customer (
CustomerNo NUMBER(3) CONSTRAINT pk_customer PRIMARY KEY ,
CustomerName VARCHAR2(20) ,
CustomerAddress VARCHAR2(48) ,
CustomerTelNo VARCHAR2(12) );
/* creates the Car table
primary key 'CarRegNo' */
CREATE TABLE Car (
CarRegNo NUMBER(3) CONSTRAINT pk_car PRIMARY KEY ,
CarTypeNo VARCHAR2(28) ,
CarModel VARCHAR2(48) ,
Colour VARCHAR2(18) ,
Mileage NUMBER(7) );
/* creates the Type table
primary key 'CarTypeNo' */
CREATE TABLE Type (
CarTypeNo NUMBER(3) CONSTRAINT pk_type PRIMARY KEY ,
TypeDescription VARCHAR2(48) ,
DailyCharge NUMBER(5,2) );
/* creates the HireContract table
primary key 'Hire_Contract_No' & 'CarRegNo'
foreign key 'CarRegNo' & 'CustomerNo' */
CREATE TABLE HireContract (
Hire_Contract_No NUMBER(3) ,
CarRegNo NUMBER(3) CONSTRAINT fk_CarRegNo references Car(CarRegNo),
CustomerNo NUMBER(3) CONSTRAINT fk_CustomerNo references Customer(CustomerNo),
StaffID NUMBER(3) ,
CarTypeNo VARCHAR2(3) ,
IssueDate DATE ,
DueReturnDate DATE ,
ActualReturnDate DATE ,
CarCost NUMBER(6,2) ,
CONSTRAINT pk_Hire PRIMARY KEY(Hire_Contract_No, CarRegNo) ,
CONSTRAINT ch_ActualReturnDate CHECK (ActualReturnDate > IssueDate) ,
CONSTRAINT ch_DueReturnDate CHECK (DueReturnDate > IssueDate) );and as the question states i need to work out the average cost of hire contracts for each branch. The tables have been set up this way on purpose but i just cannot seem to find a way to achieve this goal ? any ideas.
