Baza de date lab 10

        Seminar 10

CREATE TABLE studenti
(
    nr_matricol NUMBER(5) PRIMARY KEY,
    nume VARCHAR2(50),
    data_nastere DATE,
    email VARCHAR2(50) UNIQUE
)

CREATE TABLE note
(
    nr_matricol NUMBER(5) REFERENCES studenti(nr_matricol),
    nota NUMBER(2) CHECK (nota BETWEEN 1 AND 10),
    disciplina VARCHAR2(20)
    data DATE,
    PRIMARY KEY(nr_matricol, nota, disciplina, data)
)

ALTER TABLE note
    ADD CHECK(nota) (nota BETWEEN 1 AND 10)

DESCRIBE studenti - vizualizare studenti

-consultarea(interogarea) dictionarului datelor (DICT)
    *pt tabele distincte
        SELECT *
        FROM USER_TABLES;
    *pt vizualizarea constrangerilor unui TABLE
        SELECT *
        FROM USER_CONSTRAINTS
        WHERE TABLE_NAME = 'STUDENTI';


////////////////////////////////////

CREATE TABLE emp_copy[(cod,nume,.....)]
AS SELECT * FROM EMP[WHERE 1=0]


ALTER TABLE studenti ADD nr_tel VARCHAR2(10)
DROP TABLE studenti;

///DROP sterge intregul tabel, DELETE elimina o inregistrare

CREATE TABLE angajati
(
Cnp NUMBER(13) PRIMARY KEY,
Nume VARCHAR(10) NOT NULL,
Cod_drpt NUMBER(2),
Cnp_sef NUMBER (13),
Data_nastere DATE,
Data_angajare DATE,
Salar NUMBER (7,2) CHECK(Salar BETWEEN 200 AND 2000),
Comission NUMBER (7,2),
Loc_nastere VARCHAR(10)
),

CREATE TABLE departamente
(
Cod_dept NUMBER(2) PRIMARY KEY,
Nume_dept VARCHAR(10) UNIQUE,
Localitate VARCHAR(10)
);



INSERT INTO DEPARTAMENTE(cod_dept, nume_dept)
VALUES(10, 'IT');

INSERT INTO DEPARTAMENTE
SELECT * FROM dept;

UPDATE DEPARTAMENTE
SET cod_dept = 50,
localitate = 'TIMISOARA'
WHERE nume_dept = 'IT';

SAVEPOINT B;

DELETE FROM DEPARTAMENTE
WHERE cod_dept = 50;

//COMMIT/ROLLBACK( goes TO SAVEPOINT )

/ALTER TABLE student
//MODIFY