2 tabele:

student | nota admitere | id | bursa | id_facultate
facultate_id | nume

a) Acordati burse studentilor de la facultatea citita de la tastura si
a studentilor care nu au facultate, care au nota mai
mare decat 7, iar bursa = nota * 100

b) Acordati burse studentilor de la celelalte facultati care au nota
mai mare decat media facultatii, bursele sunt proportionale
cu nota iar suma tuturor burselor, inclusiv cele de la A sa fie 1000 lei.

c) ceva cu raport

drop table studenti;
drop table facultati;

create table studenti (
    nr_matricol number,
    nume varchar(20),
    grupa varchar(10),
    medie_admitere float,
    bursa float,
    id_facultate number
);

create table facultati (
    id_facultate number,
    nume varchar(15)
);

insert into studenti values (1, 'Derek Shepherd', '123Med', 8.5, 0, 1);
insert into studenti values (2, 'Meredith Grey', '123Med', 8.5, 0, 1);

insert into studenti values (3, 'Christina Yang', '321Surg', 4.5, 0, 2);
insert into studenti values (4, 'Miranda Bailey', '321Surg', 8.9, 0, 2);
insert into studenti values (5, 'Owen Hunt', '321Surg', 7.7, 0, 2);

insert into studenti values (11, 'Nick Stokes', '331CA', 8.9, 0, 0);

insert into studenti values (12, 'D.B. Russell', '123Crim', 5.9, 0, 5);
insert into studenti values (13, 'Sara Sidle', '123Crim', 7.5, 0, 5);
insert into studenti values (14, 'Catherine Willows', '123Crim', 6, 0, 5);
insert into studenti values (15, 'David Hodges', '123Crim', 8.7, 0, 5);

select * from studenti;

insert into facultati values (1, 'SeatleGrace');
insert into facultati values (2, 'GreySloanMem');
insert into facultati values (3, 'FacultVietii');
-- insert into facultati values (4, 'Acs');
insert into facultati values (5, 'CrimeLab');

select * from facultati;

accept facult_cerut prompt 'Facultatea ceruta de la tastatura: '

update studenti
    set bursa =  medie_admitere * 100
    where ((id_facultate=&facult_cerut or id_facultate=0) and
medie_admitere > 7);

select * from studenti;

-- b) Acordati burse studentilor de la celelalte facultati care au
nota mai mare decat media facultatii,
-- bursele sunt proportionale
-- cu nota iar suma tuturor burselor, inclusiv cele de la A sa fie 1000 lei.

--select sum(bursa) from studenti where id_facultate != &facult_cerut;

DEFINE fond = '(select sum(bursa) from studenti where id_facultate !=
&facult_cerut)';

update studenti a
    set a.bursa = (10000 - &fond) /
    (select sum(medie_admitere) from studenti b where b.id_facultate =
a.id_facultate and b.id_facultate != &facult_cerut)
        where (
                a.medie_admitere >= (select avg(medie_admitere) from
studenti where id_facultate = a.id_facultate)
                and a.id_facultate != &facult_cerut and a.id_facultate != 0
        );

--select sum(bursa) from studenti;
--select * from studenti;

DEFINE Z="to_char(sysdate, 'dd/mm/yyyy')";
repheader left 'data: 10-04-2013 ' center underline 'CATALOG' right
'pag: ' sql.pno
repfooter right 'director, '

set echo off
set linesize 90
set pagesize 25

col A format a35 heading 'nume'
col B format 9999.99 heading 'nota_adm'
col C format 9999.99 heading 'bursa'
col D format a35 heading 'facultate'

break on D skip 4
compute sum of C on D
compute avg of B on D

select a.nume A, a.medie_admitere B, a.bursa C, b.nume D
    from studenti a, facultati b
    where a.id_facultate = b.id_facultate order by D;


clear columns;
-- @"C:\Documents and Settings\Student\Desktop\lab7.sql"

--
Cu stimă,
Mihai Bărbulescu
