Exerciții SQL

Exerciţii de rezolvat

Fie baza de date Editura, care are în componenţă următoarele tabele:

Carte (isbn, titlu, tip)

Scriitori (cnp, nume)

Autori (cnp,isbn)

Tipul, semnificaţia şi constrângerile impuse asupra fiecărui câmp sunt următoarele:

- isbn – caracter(20)- reprezinta ISBN-ul fiecărei cărţi;

- titlu – caracter (50) – titlul cărţii

- tip – caracter(9)- tipul cărţii. Acest câmp poate lua numai una din valorile:

o „didactic”

o „tehnic”

o „fictiune”

- cnp – caracter(13) codul numeric personal al autorilor

- nume – caracter (20) – numele autorului

1. Creaţi baza de date Editură, după care, utilizând instrucţiunile SQL, creaţi cele trei tabele.

CREATE DATABASE

MODIFY DATABASE

create table Carte ( isbn C(20) PRIMARY KEY, titlu C(50) , tip C(9) CHECK tip="didactic" OR tip="tehnic" OR tip="fictiune")

create table scriitori ( cnp C(13) PRIMARY KEY , NUME C(20) )

create table AUTORI ( cnp C(13) REFERENCE SCRIITORI , ISBN C(20) REFERENCE CARTE)

2. Folosind instrucţiunea INSERT SQL, populaţi tabelele cu datele din fişierul SG1.doc.

INSERT INTO CARTE VALUES ("1","CARTE1","didactic")

INSERT INTO CARTE VALUES ("2","CARTE2","tehnic")

INSERT INTO CARTE VALUES ("3","CARTE3","fictiune")

INSERT INTO SCRIITORI VALUES ("100","AUTOR1")

INSERT INTO SCRIITORI VALUES ("101","AUTOR2")

INSERT INTO SCRIITORI VALUES ("103","AUTOR3")

INSERT INTO AUTORI VALUES ("101","2")

INSERT INTO AUTORI VALUES ("103","2")

INSERT INTO AUTORI VALUES ("102","1")

INSERT INTO AUTORI VALUES ("103","3")

3. Formulaţi în SQL următoarele interogări:

a. Care este cnp-ul scriitorilor care au scris orice carte care nu este de tipul „ficţiune”.

SELECT CNP FROM AUTORI,CARTE WHERE AUTORI.ISBN=CARTE.ISBN AND TIP<>"fictiune"

b. Care sunt ISBN-urile cărţilor care au mai mult de un autor?

SELECT ISBN DISTINCT FROM AUTORI A1 WHERE ISBN IN (SELECT ISBN FROM AUTORI A2 WHERE A1.CNP<>A2.CNP)

c. Care este numele autorilor editaţi şi numărul cărţilor scrise de aceştia?

Select scriitori.nume, count(autori.isbn) as nr_carti_editate from scriitori inner join autori on scriitori.cnp=autori.cnp

SELECT NUME,COUNT(AUTORI.CNP) AS NR FROM AUTORI,SCRIITORI GROUP BY AUTORI.CNP WHERE AUTORI.CNP=SCRIITORI.CNP

4. Scrieţi două interogări SQL care să difere semnificativ şi care să întoarcă cnp-ul autorilor care nu au scris nici o carte de tipul „fictiune”.

Select cnp from autori where isbn in (select isbn from carte where tip<>”fictiune”)

Select cnp from autori inner join carte on carte.isbn=autori.isbn where carte.tip<>”fictiune”

SELECT CNP FROM AUTORI WHERE CNP NOT IN (SELECT CNP FROM AUTORI,CARTE WHERE CARTE.ISBN=AUTORI.ISBN AND CARTE.TIP="fictiune")

5. Creaţi o vedere care să afişeze toate cărţile şi autorii acestora.

CREATE SQL VIEW VEDERE AS SELECT TITLU,NUME,TIP FROM CARTE,AUTORI,SCRIITORI WHERE CARTE.ISBN=AUTORI.ISBN AND AUTORI.CNP=SCRIITORI.CNP

6. Creaţi o vedere parametrizată care să afişeze toate cărţile scrise de un autor al cărui cnp este dat ca parametru.

CREATE SQL VIEW VEDERE2 AS SELECT TITLU,TIP FROM CARTE,AUTORI WHERE CARTE.ISBN=AUTORI.ISBN AND AUTORI.CNP="102