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)

;