Bazy Danych 2 - Laboratorium
Laboratorium nr.4	Data: 03.04.2012 Godz. 09:50-11:20	Skład zespołu: Grupa: 211B

-- Projekt: „Układ gwiazdy”

DROP TABLE g1_zaliczenie ;

DROP TABLE g1_student ;

DROP TABLE g1_grupa ;

DROP TABLE g1_wykladowca ;

DROP TABLE g1_przedmiot ;

CREATE TABLE g1_przedmiot (

id_przedmiot NUMBER(5,0) CONSTRAINT g1_id_przedmiot_pk PRIMARY KEY ,

nazwa VARCHAR2(25) ,

typ VARCHAR2(15)

) ;

CREATE TABLE g1_wykladowca (

id_wykladowca NUMBER(3,0) CONSTRAINT g1_id_wykladowca_pk PRIMARY KEY ,

imie VARCHAR2(15) ,

nazwisko VARCHAR2(15) ,

stopien VARCHAR2(15)

) ;

CREATE TABLE g1_grupa (

nr_grupy NUMBER(3,0) CONSTRAINT g1_nr_grupy_pk PRIMARY KEY ,

nazwa VARCHAR2(15) ,

liczba_st NUMBER(2,0)

) ;

CREATE TABLE g1_student (

nr_albumu NUMBER(3,0) CONSTRAINT g1_nr_albumu_pk PRIMARY KEY ,

imie VARCHAR2(15) ,

nazwisko VARCHAR2(15) ,

wiek NUMBER(2,0) ,

nr_grupy NUMBER(3,0) CONSTRAINT g1_nr_grupy_fk REFERENCES g1_grupa (nr_grupy)

) ;

create table g1_zaliczenie (

id_zaliczenie NUMBER(6,0) CONSTRAINT g1_z_id_zaliczenie_pk PRIMARY KEY ,

id_przedmiot NUMBER(3,0) CONSTRAINT g1_z_id_przedmiotu_fk REFERENCES g1_przedmiot ( id_przedmiot ) ,

nr_albumu NUMBER(3,0) CONSTRAINT g1_z_nr_albumu_fk REFERENCES g1_student ( nr_albumu ) ,

nr_grupy NUMBER(3,0) CONSTRAINT g1_z_nr_grupy_fk REFERENCES g1_grupa ( nr_grupy ) ,

id_wykladowca NUMBER(3,0) CONSTRAINT g1_z_id_wykladowca_fk REFERENCES g1_wykladowca ( id_wykladowca ) ,

termin VARCHAR2(15) ,

ocena number(1)

) ;

-- Zapytania i wyniki.

--ZAD1 A)

SELECT nr_albumu, id_przedmiot, AVG(ocena) AS "Srednia ocen" FROM g1_zaliczenie

GROUP BY rollup(nr_albumu, id_przedmiot, ocena);

.

.

.

SELECT nr_albumu, id_przedmiot, AVG(ocena) AS "srednia ocen" FROM g1_zaliczenie

GROUP BY cube(nr_albumu,id_przedmiot,ocena);

.

.

.

SELECT id_przedmiot, nr_albumu, AVG(ocena) as "srednia ocen" FROM g1_zaliczenie

GROUP BY rollup(id_przedmiot, nr_albumu, ocena);

.

.

.

SELECT id_przedmiot, termin, AVG(ocena) as "srednia ocen" FROM g1_zaliczenie

GROUP BY cube(id_przedmiot, termin, ocena);

.

.

.

--ZAD1 B)

SELECT id_przedmiot, nr_albumu, AVG(ocena) as "srednia ocen" FROM g1_zaliczenie

GROUP BY rollup(id_przedmiot,nr_albumu,ocena);

.

.

.

SELECT id_przedmiot, nr_albumu, AVG(ocena) AS "srednia ocen" FROM g1_zaliczenie

GROUP BY cube(id_przedmiot,nr_albumu,ocena);

.

.

.

SELECT id_przedmiot, termin, AVG(ocena) as "srednia ocen" FROM g1_zaliczenie

GROUP BY rollup(id_przedmiot,termin,ocena);

.

.

.

SELECT id_przedmiot, termin, AVG(ocena) AS "srednia ocen" FROM g1_zaliczenie

GROUP BY cube(id_przedmiot,termin,ocena);

.

.

.

SELECT id_przedmiot, AVG(ocena) AS "srednia ocen" FROM g1_zaliczenie

GROUP BY rollup(id_przedmiot,ocena);

.

.

.

SELECT id_przedmiot, AVG(ocena) AS "srednia ocen" FROM g1_zaliczenie

GROUP BY cube(id_przedmiot);

--ZAD2 A)

SELECT id_przedmiot, termin, ocena,

Round(100*ocena/(SUM(ocena) over(PARTITION BY id_przedmiot)))

"procent %" FROM g1_zaliczenie order by id_przedmiot, termin;

.

.

.

--ZAD2 B)

SELECT id_przedmiot, ocena,

Round(100*ocena/(SUM(ocena) over(PARTITION BY id_przedmiot)))

"procent %" FROM g1_zaliczenie order by id_przedmiot, termin;

.

.

.

--ZAD3 A)

SELECT id_przedmiot, nr_albumu, termin, (AVG(ocena) over

(partition by id_przedmiot order by nr_albumu DESC

RANGE between 3 preceding and current row)) as srednia_ocen

FROM g1_zaliczenie order by termin;

.

.

.

--ZAD3 B)

SELECT id_przedmiot, nr_albumu, (AVG(ocena)

over (partition by id_przedmiot order by nr_albumu DESC RANGE between 3

preceding and current row)) as srednia_ocen

FROM g1_zaliczenie order by termin;

.

.

.

--ZAD4 A)

SELECT nr_albumu, id_przedmiot, AVG(ocena) AS "srednia",

Rank() over (PARTITION BY null ORDER BY nr_albumu DESC) AS Rank

FROM g1_zaliczenie GROUP BY nr_albumu, id_przedmiot;

.

.

.

--ZAD4 B)

SELECT nr_albumu, AVG(ocena) AS "srednia",

RANK() over (PARTITION BY null ORDER BY nr_albumu DESC) AS Rank

FROM g1_zaliczenie GROUP BY nr_albumu;

---