CREATE TABLE SupaRecept(
CisloReceptu INTEGER NOT NULL,
Nazov CHAR(15) NOT NULL,
PocetPorcii INTEGER CHECK (PocetPorcii BETWEEN 0 AND 100 ),
PracPostup CHAR (100),
ZaradenieChuti CHAR(1) CHECK (ZaradenieChuti IN ('C','S','H')),
Primary Key (CisloReceptu)

INSERT INTO SupaRecept VALUES
(3,'gulas',7,'Poprazit maso na cibulke, zaliat vodou a varit','S');

INSERT INTO SupaRecept(CisloReceptu, Nazov) 
VALUES(1,'prazenica'); 

INSERT INTO SupaRecept(CisloReceptu, Nazov,PocetPorcii) 
VALUES(2,'gulas',10);  
Vkladanie viacerch riadkov naraz

INSERT INTO SupaRecept(CisloReceptu, Nazov,PocetPorcii) VALUES
(7,'svieckova',4),
(5,'torta',12),
(6,'pecene kura',4);

INSERT INTO SupaRecept VALUES
(8,'caj',13,'Uvarit vodu','H'),
(9,'polievka',12,'Uvarit vodu','H'),
(10,'vianocka',1,'Urobit vlacne cesto','C'); 

UPDATE SupaRecept SET 
nazov='hovadzi gulas',
PracPostup=NULL 
WHERE CisloReceptu = 3;

UPDATE SupaRecept SET 
PracPostup=NULL 
WHERE CisloReceptu IN (8,9);  

Vber riadkov tabuky 

  UPDATE menotabulky SET
  nazov='...',
  popis='...'
  WHERE CisloReceptu=5

  WHERE CisloReceptu IN (8,9)


  mazanie riadkov
  DELETE FROM menotabulky
  WHERE

  zrusenie tabulky
  DROP TABLE menotabulky


CREATE TABLE SupaSurovina(
CisloSuroviny INTEGER NOT NULL,
Nazov CHAR(15) NOT NULL,
Jednotka CHAR(7) NOT NULL,
Primary Key (CisloSuroviny)
); 

CREATE TABLE SupaDavka(
CisloReceptu INTEGER NOT NULL,
CisloSuroviny INTEGER NOT NULL,
Davka INTEGER NOT NULL
Primary Key (CisloReceptu, CisloSuroviny)
); 

SELECT Nazov FROM SupaRecept WHERE Pocetporcii>1

INSERT INTO SupaSurovina(CisloSuroviny, Nazov, Jednotka) VALUES
(1,'vajcia','ks'),
(2,'muka','kg'),
(3,'olej','ml'); 

INSERT INTO SupaDavka(CisloReceptu, CisloSuroviny, Davka) VALUES
(1, 1, 4),
(1, 8, 5),
(6, 1, 4);

__________________________________________________________________________________________
23. 10. 2001

ORDER BY  sluzi na usporiadanie 

Vber pecifikovanch stpcov, poradie stpcov sa uvdza tak, 
ako maj by uveden na vstupe
SELECT nazov, pocetPorcii 
FROM ZVRecept;

DSC  zostupne

Pri projekcii meme elovo meni poradie riadkov a to pouitm 
ORDER SELECT nazov, pocetPorcii 
FROM ZVRecept
ORDER BY pocetPorcii; 

 

V selecte je mon miesto nzvov stpcov pouva vrazy. 
SELECT nazov, pocetPorcii*casPripravy 
FROM ZVRecept
ORDER BY casPripravy DESC; 

SELECT nazov, casPripravy<30 
FROM ZVRecept
ORDER BY nazov; 

 

Vber riadkov SELECT nazov 
FROM ZVRecept
WHERE pocetPorcii>7 AND caspripravy<30 ORDER BY casPripravy DESC; 

SELECT nazov, casPripravy 
FROM ZVRecept
WHERE caspripravy between 10 and 30 
ORDER BY nazov; 

SELECT nazov, casPripravy 
FROM ZVRecept
WHERE nazov='gulas' 
ORDER BY nazov;  

Vyhadvanie textovch reazcov pomocou kovho slova LIKE - znamen to, 
e nezle na presnej zhode textu. Pri vyhadvan sa vyuvaj dva pecilne znaky 
_ - nahrdza jeden znak
% - nahrdza ubovon poet znakov SELECT nazov 
FROM ZVRecept
WHERE nazov LIKE 'gulas'
ORDER BY casPripravy DESC; 

FROM ZVRecept
WHERE nazov LIKE 'gul__'
ORDER BY casPripravy DESC; 

SELECT nazov, casPripravy 
FROM ZVRecept
WHERE nazov LIKE '%zavin'
ORDER BY nazov; 

SELECT nazov, casPripravy 
FROM ZVRecept
WHERE nazov LIKE '%ul%'
ORDER BY nazov; 
 

v 2 tab. rovnaky nazov stlpca:
Recept R, Surovina S skratky su symbolicke nazvy

SELECT nazov nazov
FROM SupaRecept R, SupaSurovina S
WHERE R.nazov = 'gulas'
(WHERE S.CisloSuroviny = 1)

pripustne je aj Recept.nazov, Surovina.nazov

SELECT SupaRecept.nazov SupaSurovnina.nazov
FROM SupaRecept R, SupaSurovina S
WHERE R.nazov = 'gulas'

____________________________________________________________________________________________

30. 10. 2001

Vytvorte tabuku ZAMESTNANEC s atribtmi: 
osobn slo (primrny k), 
meno (povinn atribt), 
priezvisko (povinn atribt), 
bydlisko (povinn atribt), 
tarifn plat (povinn atribt), 
osobn ohodnotenie (nepovinn atribt).


CREATE TABLE SupaZamestnanec(
OsobneCislo INTEGER NOT NULL,
Meno CHAR(15) NOT NULL,
Priezvisko CHAR(20) NOT NULL,
Bydlisko CHAR(30) NOT NULL,
TarifnyPlat INTEGER NOT NULL,
Osobne Ohodnotenie INTEGER),
Primary Key (OsobneCislo)
);

INSERT INTO SupaZamestnanec











