0

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.

2
Contributors
3
Replies
4
Views
10 Years
Discussion Span
Last Post by davidcairns
0

Without answering your whole question I'll give you a start

You need 3 of the tables to find cost per hire -
HireContract
Staff
Branch

Join them to each other and you should then be able to find the contracts for each branch (join fields are StaffID and BranchNo)

Give it a go and post here when your done

Cheers

D

0

Without answering your whole question I'll give you a start

You need 3 of the tables to find cost per hire -
HireContract
Staff
Branch

Join them to each other and you should then be able to find the contracts for each branch (join fields are StaffID and BranchNo)

Give it a go and post here when your done

Cheers

D

hello, cheers for the reply i actually managed to figure it out myself :P after a long and painful process. Really am greatful for the help though. Thanks ..

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.