
 ####   ##                   #                 ##                        
######  ##                  ##                 ##                        
##      #####  ####  ##### #### #####  ###     ##                        
##      ## ##  # ##  ## ##  ##  ##  ## ##      ##                        
##  ##  ## ## #####  ## ##  ##  ###### ##      ##                        
######  ## ## ## ##  ## ##  ##  ## ### ##      ##  ##                    
 #####  ## ## #####  #####  ###  ####  ##      ##  ##                    
                     ##                                                  
2.10.2001            ##   

SQL - structured query language
data su ulozene v tabulkach - pristup pomocou mena tabulky - nezaujimave poradie
tabulky organizovane v stlpcoch - bez poradia
search - vpravo hore - vyhladavanie tabuliek
pozn> vhodne davat pred nazov tabulky prefix

buttony>
select - SELECT
sql - insert,update prikazy
clear

prikaz SELECT

SELECT co FROM meno_tabulky

pr: SELECT * FROM mn_student
do velkeho txt pola!!!
vysledok>
Jan    |Hrasko 
Ruzena |Sipova 
Ali    |Baba 
Jan    |Polienko 
Juraj  |Trulo  

utriedenie podla priezviska - ORDER BY
SELECT * FROM mn_student ORDER BY priezvisko
Ali    |Baba 
Jan    |Hrasko 
Jan    |Polienko 
Ruzena |Sipova 
Juraj  |Trulo  

SELECT * FROM mn_student ORDER BY priezvisko ASC - ascending - vzostupne
SELECT * FROM mn_student ORDER BY priezvisko DESC - descending - zostupne
Ali    |Baba 
Jan    |Hrasko 
Jan    |Polienko 
Ruzena |Sipova 
Juraj  |Trulo  

ORDER BY 2 - utriedenie podla 2. polozky (priezvisko)

utriedenie len priezvisk podla abecedy:
SELECT priezvisko FROM mn_student ORDER BY priezvisko


SELECT priezvisko,meno FROM mn_student ORDER BY meno,priezvisko
SELECT priezvisko,meno FROM mn_student ORDER BY 1,2

Konkatenacia || (niekde +)
zotriedenie mena a priezviska ako jednej polozky - su to znaky
SELECT meno||' '||priezvisko FROM mn_student

Jan Hrasko 
Ruzena Sipova 
Ali Baba 
Jan Polienko 
Juraj Trulo  

pozn.konkatenovat mozno len rovnake typy (nie cislo a string)
SELECT meno||' '||priezvisko FROM mn_student ORDER BY 1 (uz nefunguje ORDER BY 2)
teda
SELECT meno||' '||priezvisko FROM mn_student ORDER BY priezvisko,meno
Ali Baba 
Jan Hrasko 
Jan Polienko 
Ruzena Sipova 
Juraj Trulo  

vyberme priezvisko,cele meno,usporiadajme podla 1.stlpca
SELECT priezvisko,meno||' '||priezvisko FROM mn_student ORDER BY 1
Baba     |Ali Baba 
Hrasko   |Jan Hrasko 
Polienko |Jan Polienko 
Sipova   |Ruzena Sipova 
Trulo    |Juraj Trulo  

--
vezmime z mena prvy znak - funkcia SUBSTR (pole,pociatoc_index,pocet_znakov)
SELECT priezvisko,SUBSTR(meno,1,1)||'. '||priezvisko FROM mn_student
Hrasko   |J. Hrasko 
Sipova   |R. Sipova 
Baba     |A. Baba 
Polienko |J. Polienko 
Trulo    |J. Trulo  

cv:Hrasko,J.H., usporiadat podla priezviska
SELECT priezvisko || ', ' || SUBSTR(meno,1,1)||'. '||SUBSTR(priezvisko,1,1)||'.' 
FROM mn_student ORDER BY 1

Baba, A. B. 
Hrasko, J. H. 
Polienko, J. P. 
Sipova, R. S. 
Trulo, J. T.  

--
klauzula WHERE (filter zaznamov)
SELECT meno,priezvisko FROM mn_student WHERE meno='Jan'
Jan |Hrasko 
Jan |Polienko  


vyberie ostatnych:
SELECT meno,priezvisko FROM mn_student WHERE meno<>'Jan'
Ruzena |Sipova 
Ali    |Baba 
Juraj  |Trulo  

vsetci studenti, ktorych priezvisko zacina na 'B' - LIKE
SELECT meno,priezvisko FROM mn_student WHERE priezvisko LIKE 'B%'
Ali |Baba  

% nahradza skupinu (0+) znakov
priezvisko ma koncit na 'a'
SELECT meno,priezvisko FROM mn_student WHERE priezvisko LIKE '%a'

_ nahradza prave jeden znak (niekde ?)
SELECT meno,priezvisko FROM mn_student WHERE priezvisko LIKE '_a%'
alt. WHERE SUBSTR(priezvisko,2,1)='a' - 2.znak ma byt 'a'
Ali |Baba  
--
spajanie obmedzeni - AND
SELECT meno,priezvisko FROM mn_student WHERE priezvisko LIKE 'H%' AND meno='Jan'
Jan |Hrasko  

spojka OR:
SELECT meno,priezvisko FROM mn_student WHERE priezvisko LIKE 'H%' OR meno='Jan'
Jan |Hrasko 
Jan |Polienko  

pozn: mozno aj spravne uzatvorkovat

--
prikaz COUNT - zistovanie poctu
SELECT COUNT(*) FROM mn_student
5

SELECT COUNT(*) FROM mn_student WHERE meno='Jan'
2

--
kolko jednoznacnych mien je v databaze? - prikaz DISTINCT
SELECT DISTINCT meno FROM mn_student
Ali 
Jan 
Juraj 
Ruzena  

pocet roznych krstnych mien
SELECT COUNT(DISTINCT meno) FROM mn_student
4

--
repetitorium
SELECT, ||, COUNT, DISTINCT, FROM, WHERE, AND/OR, LIKE, ORDER BY

----------------------------------------------
vytvorenie tabulky - CREATE
CREATE TABLE meno_tabulky (atribut typ_atributu...)

CREATE TABLE mn_student(
  meno VARCHAR(10), 
  priezvisko VARCHAR(20)
);
; oddeluje viacero prikazov

typy udajov> vid HELP
==============================================================
odporuca sa zaroven pisat prikazy na zrusenie! (upratovanie - cleanup)
zmazanie tabulky - DROP
DROB TABLE meno_tabulky

klikat na tlacidlo SQL nie na SELECT
okno ukaze spravu - uspech/chyba
====================

vkladanie dat do tabuliek - prikaz INSERT


INSERT INTO rn_student VALUES('Jan','Hrasko'),('Juraj','Janosik');

SELECT * FROM rn_student
Jan   |Hrasko
Juraj |Janosik 

editovanie zaznamov - prikaz UPDATE
zmenme priezvisko Jana Hraska na Nemcek
UPDATE rn_student SET priezvisko='Nemcek' WHERE priezvisko='Hrasko'

SELECT * FROM rn_student
Jan   |Nemcek 
Juraj |Janosik  

===
vymazanie dat - prikaz DELETE

vymazme vsetkych Nemcekov z tabulky
DELETE FROM rn_student WHERE priezvisko='Nemcek'

=========================
repetitorium II
SELECT, ||, COUNT, DISTINCT, FROM, WHERE, AND/OR, LIKE, ORDER BY
--
CREATE,DROP
INSERT,UPDATE,DELETE

Here endeth the lesson 1.