MATCH HISTORYMatchID TeacherID StudentID StartDate EndDate M

MATCH HISTORY(MatchID, TeacherID, StudentID, StartDate, EndDate)

MatchID

TeacherrID

StudentID

StartID

EndDate

1

100

3000

1/10/2008

2

101

3001

1/15/2008

5/15/2008

3

102

3002

2/10/2008

3/01/2008

4

106

3003

5/28/2008

5

103

3004

6/01/2008

6/15/2008

6

104

3005

6/01/2008

6/28/2008

7

104

3006

6/01/2008

What is the average length of time ,the student stayed in adult litracy program.the table of adult litracy

MatchID	TeacherrID	StudentID	StartID	EndDate
1	100	3000	1/10/2008
2	101	3001	1/15/2008	5/15/2008
3	102	3002	2/10/2008	3/01/2008
4	106	3003	5/28/2008
5	103	3004	6/01/2008	6/15/2008
6	104	3005	6/01/2008	6/28/2008
7	104	3006	6/01/2008

Solution

CREATE TABLE MATCH(MatchID NUMBER, TeacherrID NUMBER, StudentID NUMBER, StartID DATE, EndDate DATE);

INSERT INTO MATCH VALUES(1,100,3000,’1/10/2008’,NULL);

INSERT INTO MATCH VALUES(2,101,3001,’1/15/2008’,’5/15/2008’);

INSERT INTO MATCH VALUES(3,102,3002,’2/10/2008’,’3/1/2008’);

INSERT INTO MATCH VALUES(4,106,3003,’5/28/2008’,NULL);

INSERT INTO MATCH VALUES(5,103,3004,’6/01/2008’,’6/15/2008’);

INSERT INTO MATCH VALUES(6,104,3005,’06/01/2008’,’6/28/2008’);

INSERT INTO MATCH VALUES(7,104,3006,’06/01/2008’,NULL);

select avg(nvl(EndDate,StartID)-StartID)) AS AVERAGE from MATCH;

AVERAGE

26

CREATE TABLE MATCH(MatchID NUMBER, TeacherrID NUMBER, StudentID NUMBER, StartID DATE, EndDate DATE);

INSERT INTO MATCH VALUES(1,100,3000,’1/10/2008’,NULL);

INSERT INTO MATCH VALUES(2,101,3001,’1/15/2008’,’5/15/2008’);

INSERT INTO MATCH VALUES(3,102,3002,’2/10/2008’,’3/1/2008’);

INSERT INTO MATCH VALUES(4,106,3003,’5/28/2008’,NULL);

INSERT INTO MATCH VALUES(5,103,3004,’6/01/2008’,’6/15/2008’);

INSERT INTO MATCH VALUES(6,104,3005,’06/01/2008’,’6/28/2008’);

INSERT INTO MATCH VALUES(7,104,3006,’06/01/2008’,NULL);

select avg(nvl(EndDate,StartID)-StartID)) AS AVERAGE from MATCH;

AVERAGE

26