Kolokwium – Bazy danych, SQL

Nazwisko:

WZÓR

WZÓR

WZÓR

WZÓR

WZÓR

WZÓR

Imię:

indeks:

Dane są następujące tabele Artykul i Dziennikarz:

[Artykul]
id | tytuł | nr | kod_dz | kB
----+---------------------+-----+--------+---------
1 | Wielkie Żarcie | 14 | BEKSA | 18.50
2 | Boso, ale z muchą | 14 | REDD |
3 | Władcy much | 17 | REDD | 24.02
4 | Myślenie owiec | 15 | BEKSA | 17.40
5 | Prawda bez barier | 15 | BEKSA | 16.20

[Dziennikarz]
id | imie | nazwisko | płeć
-------+--------+-----------+------
NOWAK | Jan | Nowak | M
BEKSA | Karol | Beksiński | M
KOWAL | Jakub | Kowalski | M
REDD | Carol | Redd | K

'id' jest kluczem głównym w tabeli Artykul, jest nadawany automatycznie jako kolejna liczba
naturalna, 'kod_dz' jest kluczem obcym w tabeli Artykul wskazującym na klucz główny 'id' w tabeli
Dziennikarz.

Zad. 1. Zapisz w SQL definicje tych tabel (tylko definicje, nie konkretn tre

).

ą

ść

Zad. 2. Wstaw do ka dej z tabel drugi od góry wiersz z odpowiedniej tabeli powy ej.

ż

ż

create table Dziennikarz(

id varchar(5) primary key,

nazwisko varchar(22),

imie varchar(22),

plec char(1)

);

create table Artykul(

id serial,

tytul varchar(22),

nr integer,

kod_dz varchar(5) references dziennikarz(id),

kB numeric (7,2) -- decimal(7,2)

);

insert into dziennikarz (id,imie,nazwisko,plec)

values('BEKSA','Karol','Beksiński','M');

insert into artykul (tytul,nr,kod_dz,kB)

values('Boso, ale z muchą',14,'REDD',NULL);

Odwołujemy się do treści tabel podanej w zad.1

Zad. 3. Jak b dzie wygl da wynik zapytania

ę

ą ł

SELECT kod_dz, COUNT(*) AS liczba_artykułów FROM Artykul GROUP BY
kod_dz HAVING COUNT(*)>1;

Zad. 4. Jak b dzie wygl da wynik zapytania

ę

ą ł

SELECT tytul,nr from Artykul

WHERE kod_dz IN (SELECT id FROM Dziennikarz
WHERE plec='M');

kod_dz | liczba_artykułów

--------+------------------

REDD | 2

BEKSA | 3

(2 rows)

tytul | nr

-------------------+----

Wielkie Żarcie | 14

Myślenie owiec | 15

Prawda bez barier | 15

(3 rows)

Zad. 5. Zmie definicj tabel z zad. 1 wstawiaj c dodatkowy atrybut dla daty publikacji

ń

ę

ą

w tabeli artyku ów. W kolumnie ustaw dat dzisiejsz .

ł

ę

ą

Zad. 6. Usu z systemu tabel dziennikarzy.

ń

ę

alter table artykul add column data_wyd date default

date('2008-05-24')

;

drop table dziennikarz cascade;

Posługując się strukturą tabel z zad. 1 skonstruuj zapytania SQL wykonujące polecenia opisane
poniżej. Zapytania powinny zwracać poprawne wyniki bez względu na to jakie dane znajdują się w
tabelach (tzn. nie mogą odwoływać się bezpośrednio do konkretnych danych).

Zad. 7. Zmie d ugo

artyku ów Beksi skiego stosuj c mno nik 0,75.

ń ł

ść

ł

ń

ą

ż

Zad. 8. Wypisz numery, w których ukaza y sie artyku y Beksi skiego w kolejno ci malej cej.

ł

ł

ń

ś

ą

Zad. 9. Wypisz nazwiska dziennikarzy, którzy nie napisali adnego artyku u.

ż

ł

Zad. 10. Usu z bazy dane dziennikarzy, którzy nie napisali adnego artyku u.

ń

ż

ł

Zad. 11. Podaj d ugo

najd u szego artyku u napisanego przez kobiet .

ł

ść

ł ż

ł

ę

Zad. 12. Podaj tytu najd u szego artyku u napisanego przez kobiet .

ł

ł ż

ł

ę

Zad. 13. Usu dane dziennikarzy m

czyzn, pod warunkiem jednak, e nie s autorami

ń

ęż

ż

ą

adnego artyku u.

ż

ł

zad. 7 UPDATE Artykul set kB=kB*1.05 WHERE kod_dz IN (SELECT id FROM

Dziennikarz WHERE nazwisko like 'Beksiński')

;

zad. 8 SELECT distinct nr FROM Artykul WHERE kod_dz IN (SELECT id

FROM Dziennikarz WHERE nazwisko like 'Beksiński') order by nr desc

;

zad. 9 SELECT nazwisko FROM Dziennikarz WHERE id not in (SELECT

kod_dz FROM Artykul)

;

select nazwisko from Dziennikarz left join Artykul on

kod_dz=Dziennikarz.id where kod_dz is null;

select nazwisko from Dziennikarz where not exists (select 1

from Artykul where kod_dz=Dziennikarz.id);

zad. 10 delete FROM Dziennikarz WHERE id not in (SELECT kod_dz FROM

Artykul)

;

zad. 11 SELECT max(kB) FROM Artykul A inner join

Dziennikarz D on A.kod_dz=D.id WHERE plec='K'

;

zad. 12 SELECT tytul FROM Artykul A inner join

Dziennikarz D on A.kod_dz=D.id WHERE plec='K' and kB=(SELECT max(kB)

FROM Artykul A inner join

Dziennikarz D on A.kod_dz=D.id WHERE plec='K')

;

zad. 13 delete FROM Dziennikarz WHERE plec='M' and id not in (SELECT

kod_dz FROM Artykul)

;