for each row get derived data

Hiyas,

    I am having lots of trouble trying to figure out how to do this. I think I need "derived data" for each row returned. Here is instructions:

many booking agents need know the flight number, flight date and number of seats avaialable. create a view for this.

Seats Available is "derived data" :(

well your going to have to know the tables and keys in my db so here are the creates I had.

CREATE TABLE LOCATION(
CITY_CODE NUMBER(3) PRIMARY KEY,
CITY_NAME VARCHAR2(20) NOT NULL,
STATE_CODE VARCHAR2(2) NOT NULL); CREATE TABLE AIRPORT(
AIRPT_CODE VARCHAR2(3) PRIMARY KEY,
AIRPT_NAME VARCHAR2(30) NOT NULL,
CITY_CODE NUMBER(3),
FOREIGN KEY (CITY_CODE) REFERENCES LOCATION); CREATE TABLE CUSTOMER(
CUST_NUM NUMBER(4) PRIMARY KEY,
CUST_NAME VARCHAR2(15) NOT NULL); CREATE TABLE AC_TYPE(
AC_TYPE VARCHAR2(8) PRIMARY KEY,
DESCRIPTION VARCHAR2(25) NOT NULL,
CAPACITY NUMBER(3) NOT NULL,
AC_RANGE NUMBER(4) NOT NULL); CREATE TABLE AIRCRAFT(
SERIAL_NUM VARCHAR2(8) PRIMARY KEY,
AC_TYPE VARCHAR2(8) NOT NULL,
FOREIGN KEY (AC_TYPE) REFERENCES AC_TYPE); CREATE TABLE FLIGHT(
FLT_NUM NUMBER(3) PRIMARY KEY,
TOD NUMBER(4) NOT NULL,
FROM_AIRPT VARCHAR2(3),
TOA NUMBER(4) NOT NULL,
TO_AIRPT VARCHAR2(3),
FOREIGN KEY (FROM_AIRPT) REFERENCES AIRPORT, FOREIGN KEY (TO_AIRPT) REFERENCES AIRPORT); CREATE TABLE SCHEDULED_FLT(
FLT_DATE DATE,
FLT_NUM NUMBER(3),
SERIAL_NUM VARCHAR2(8),

PRIMARY KEY (FLT_DATE, FLT_NUM),
FOREIGN KEY (FLT_NUM) REFERENCES FLIGHT,
FOREIGN KEY (SERIAL_NUM) REFERENCES AIRCRAFT);

CREATE TABLE RESERVATION(
FLT_DATE DATE,
FLT_NUM NUMBER(3),
CUST_NUM NUMBER(4),

PRIMARY KEY (FLT_DATE, FLT_NUM, CUST_NUM),
FOREIGN KEY (FLT_DATE, FLT_NUM) REFERENCES SCHEDULED_FLT,
FOREIGN KEY (CUST_NUM) REFERENCES CUSTOMER);

I started with trying to get the seats available

SELECT COUNT(R.CUST_NUM) - COUNT(T.CAPACITY) AS SEATS_AVAIL  FROM RESERVATION R, SCHEDULED_FLT S, CUSTOMER C,  AC_TYPE T, AIRCRAFT A
 WHERE R.CUST_NUM = C.CUST_NUM AND
  R.FLT_DATE = S.FLT_DATE AND
  R.FLT_NUM = S.FLT_NUM; and then added the other two items needed

SELECT R.FLT_NUM, R.FLT_DATE, COUNT(R.CUST_NUM) - COUNT(T.CAPACITY) AS SEATS_AVAIL
 FROM RESERVATION R, SCHEDULED_FLT S, CUSTOMER C,  AC_TYPE T, AIRCRAFT A
 WHERE R.CUST_NUM = C.CUST_NUM AND
  R.FLT_DATE = S.FLT_DATE AND
  R.FLT_NUM = S.FLT_NUM AND
 S.SERIAL_NUM = A.SERIAL_NUM AND
 A.AC_TYPE = T.AC_TYPE; but it returns an error:
ERROR at line 1:
ORA-00937: not a single-group group function

What do I need to do? This is obviously homework, but note that I made an attempt on my own and put thought into it before asking for help. I can't find much help in my textbook. Thanx,

    Christopher Received on Fri Jul 12 2002 - 13:04:09 CEST