server CYRIL

user ---->   SQL*plus ---> SQL   --+
                                   |
                                   V
		system riadenia bazy dat
		
$ oracle_user
$ sqlplus
		username: kruzok1
		password: k582       !!!!

prikazy: 

 help                           - pomoc!
 set pause on/off		- scrollovanie po obrazovkach
 select table_name from all_tables;
 describe user_profile

	typy
	 number(precision,scale)	n.(3,2): 123,45
	 char(pocet_znakov)
	 varchar2
	 date                       	24-JAN-96

 edit 				- command buffer
                                - nezapisuju sa tam sql+ prikazy

 copy from scott/tiger@inst6 to  kruzok1/kp582...

Softwarove noviny 9/95

create
	create table pd
	 ( id number(5) primary key,
	name char(15),
	datum date);

	create table pd_cy as
	select *
	from pd
	where 1=2;    <--- bez tohto riadku copy aj udaje

insert
update
delete	
drop		- odstranit tabulku
	drop table [meno]
describe	- vypis tabulky
copy
alter
	alter table pd  modify (id char(15));


	zmena nazvu stlpca:
				+---nove nazvy stlpcov
	create table [meno1] (a,b,c) as select * from [stareMeno];
	drop table [stareMeno]
	rename [meno1] [stareMeno]



select teamno,name from teams,players
where teams.playerno=players.playerno

select teamno,name from teams t, players p 
where t.playerno=p.playerno;

28.10.1996

edit <file.sql>

 copy from kruzok1/k582@inst6-
 replace players_pd-
 using select * from players
[ctrl-z]

spustenie <file.sql>:     @<file.sql>

PLAYERS           <---> = primary key
playerNo  name  ...
<--||--->
   |+-------------+
   +-------+      |
           |      |
TEAMS      |      |
teamNo  playerNo  |
<-|-->      +-----+
  |         |
  +---------|-------+
MATCHES     |       |
matchNo  playerNo teamNo
<----->

select teams.playerNo
from teams, players
where teams.playerNo = players.playerNo

< > = <> >= <= and or ...

najst cisla hracov, ktori ziju v Stratforde:

select plyerno from players
where town='Stratford';

select ...
from ...
where ...
order by ... desc/asc (asc = default)   <--- vzdy na konci

select ...
from ...
group by ...
having ...

rok narodenia 1962-1967:

select playerno, name, year_of_birth
from players
where year_of-birth>=1962 and year_of_birth<=1967;

to iste inac:

select playerno, name, year_of_birth
from players
where year_of_birth between 1962 and 1967;

operator IN:
~~~~~~~~~~~~
mena hracov, ktori ziju v Inglewoode alebo Plymouthe

select name 
from players
where town in ('Inglewood','Plymouth');

subquery

select playerno
from players
where playerno in
(select playerno
from matches
where teamno=1);

------------------------------------------------------------------------------
11.11.1996

1. statisticke fnc
2. group by ...
3. group by ... having ...


select matchno, '...retazec...', won-lost, won*10 
from matches_pd;

select town 
from players;      	<--- existuju duplicitne riadky

select DISTINCT town 
from players;		<--- bez duplicitnych riadkov

premenna  RowNum  
- novy primarny kluc; novy stlpec: poradove cislo 1 .. n

select rownum, paymentno, amount
from penalties 
where amount>25;

rownum
1   	bla
2   	bla,bla ...
3     	  .
4     	  .
5     	  .


count           - pocet
min             - najmensi prvok
max             - najvacsi prvok
avg             - priemer
stdev		- standardna odchylka
variance        - odchylka od priemeru  (= avg - x_i )

Department:

menoZam | sef
--------+-----
jano	| king
fero	| king
jozo	| king
  . 	|	
  .     |
  .     |
king    | NULL <- specialna hodnota

NULL - ked hodnota nie je definovana

work = pin retaz  znak
	1   prvy    a
	2   druhy   NULL
	3   treti   NULL
	4   treti   b

---------------------------------+
insert into work (pin,retaz)     |
values (5,'stvrty');             |
                                 |
bude to:   5 | stvrty | NULL     |
---------------------------------+

select count(*) from work;       = 4
- pocet * from work  ( aj duplicitne, aj NULL )

select count(pin) from work      = 4
- pocet pin from work

select count(distinct retaz) from work   = 3
- pocet (neduplicitnych!) hodnot retaz

select count(znak) from work  = 2
- nepocita NULL


minimalna hodnota penalty       hracov v Stratforde:
                   (penalties)     (players)
select min(amount)
from penalties_pd
where playerno in
(select playerno 
 from players_pd
 where town='Stratford'
);

#################################

GROUP BY ...   <--- vyuziva sa v spojeni so statistickymi fnc.

select town 
from players_pd;
- aj duplicitne;

select distinct town 
from players_pd;
- neduplicitne
      <=>

select town 
from players_pd 
group by town;

ULOHA: ziskat pocet hracov za kazde mesto

select town,count(*)
from players_pd
group by town;

ULOHA: pre kazdy team, ktoreho kapitan je hrac zijuci v meste Eltham treba
ziskat teamove cislo a pocet zapasov:

select teamno,count(*)
from matches
where teamno in
(select teamno
 from teams,players
 where teams.playerno = players.playerno and town = 'Eltham'
)
group by teamno;

############################

GROUP BY ... HAVING ...

ULOHA: vsetci hraci, ktori mali viac ako 1 penalty:

select playerno
from penalties
group by playerno
having count(*)>1;


25.11.1996 ====================================================================

1. Kombinovanie SELECT prikazov
2. Views (Pohlady)
3. PL/SQL
4. Aktualizacia databazy


4.)  Source  ----->  Work

1.)

set operatory:   UNION, INTERSECT, MINUS

UNION: <zjednotenie>

priklad: ziskajte cisla a rezidencie hracov z Inglewoodu a Plymouthu.
                  ~~~~~   ~~~~~~~~~~

select playerno, town
from players_pd
where town='Inglewood'
union				<--- zjednotenie
select playerno, town
from players_pd
where town='Plymouth';

INTERSECT: <prienik>

priklad: ziskajte cisla a rok nar. hracov zijucich v Stratforde, ktory sa
narodili po roku 1960.

select playerno, year_of_birth
from players_pd
where town='Stratford'
intersect			<--- prienik
select playerno, year_of_birth
from players_pd
where year_of_birth > 1960;

pr.: ziskajte cisla a rok nar. tych hracov, ktori ziju v Stratforde, a ktory
neboli narodeni po roku 1960.

select playerno, year_of_birth
from players_pd
where town='Stratford'
minus
select playerno, year_of_birth
from players_pd
where year_of_birth < 1960

Najdite mena a cisla hracov, ktori su kapitanmi a ktori boli pokutovani aspon
jednou penaltou.

select name, playerno
from players_pd
where playerno in
 (select playerno
  from teams_pd
  intersect
  select playerno
  from penalties_pd);

POZNAMKA: INTERSECT sa da nahradit MINUSom...


2.) Views - virtualna tabulka

chcem mat tabulku, ktora bude obsahovat iba cisla hracov a ich vek:

create view ages(playerno, age) as
select playerno, 1996-year_of_birth
from players_pd;


3.) PL/SQL


9. 12. 1996 ============================================================

NULL operator:

ANALYZA ZADANEHO PRIKLADU:

ULOHA: najdite mena, cisla, a ligove cisla hracov, ktorych ligove cislo
nie je rovne 8467.

RIESENIE 1:
~~~~~~~~~~~
select name, playerno, leagueno 
from players
where not (leagueno = 8467);

riesenie 1 nezahrna tych hracov, ktory maju leagueno rovne NULL!

RIESENIE 2:
~~~~~~~~~~~
select name, playerno, leagueno
from players
where not (leagueno = 8467)
      or leagueno is NULL;

ULOHA: najdite hracov, ktory maju ligve cislo

NULL != NULL  !!!!!!!!

select name, playerno, leagueno
from players
where leagueno = leagueno;  <-- ak je hodnota NULL, tak UNKNOWN

SQL pouziva trojhodnotovu logiku: TRUE, FALSE, UNKNOWN (skoro ako FALSE)


JEDNO Z RIESENI 4. ULOHY Z DU:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select citatel, kniha, count(*)
from pozical_si x
group by citatel, kniha
having count(*) =
   (select max(count(*))
    from pozical_si
    group by kniha, citatel
    having kniha = x.kniha
   );



EXECUTION TIME - cas behu otazky

2 pristupy do db:
	1) sekvencny - postupne vsetky riadky (niekedy zdlhave)
	2) priamy    - priamo len vybrane riadky

vytvorenie indexu:
create index players_ix
on players(playerno);
     !       !
 meno db   meno stlpca

zrusenie indexu:
drop index players_ix;

       //jednoznacny
create unique index town_ix
on players(town);

overenie spravnosti indexu:
validate index town_ix;


EXISTS operator (niekedy namiesto IN):

select name, initials from players
where playerno in
  (select playerno
   from penalties
  );

select name, initials from players
where exists 
  (select * from penalties
   where playerno = players.playerno
  );


JOIN - kombinacia riadkov z roznych tabuliek

pre kazdeho hraca mame najst cislo, meno a vysku penalty

select pl.playerno, name, amount
from players pl, penalties ps
where  pl.playerno=ps.playerno;
                 \\\JOIN columns
union
select tych, ktori nemaju ziadnu penaltu



