"Ali Baba AB"
select meno,priezvisko,substr(meno,1,1)||substr(priezvisko,1,1) ziak from sk.student
 order by ziak


"A. Baba"
select substr(meno,1,1)||'. '||priezvisko ziak from sk.student
 order by ziak


"Ali B."
select meno||' '||substr(priezvisko,1,1)||'.' korhel from sk.student
 order by korhel


"Jan Janovic Hrasko"
select meno||' '||meno||'ovic'||' '||priezvisko korhel from sk.student
 order by korhel


"Mgr. Jan Janovic Hraskov"
select 'Mgr. '||meno||' '||meno||'ovic'||' '||priezvisko||'v' korhel from sk.student
 order by korhel


"Ali |Baba"
select * from sk.student where substr(priezvisko,1,1)='B'
 order by 1


"ktori sa koncia na a:"
select * from sk.student where substr(priezvisko,length(priezvisko),1)='a'
 order by 1


"ktorych priezvisko obsahuje o"
select * from sk.student where priezvisko like '%o%'
 order by 1


create table XXX.student
(
meno varchar (10),
priezvisko varchar (20)
)   ... stlacit tlacitdlo SQL


select * from xxx.student

insert into xxx.student
values ('Jan','Hrasko'),
('Jan','Hrasko'),
('Janco','Hrasko'),
('Peter','Ivanco'),
('Pika','Cu'),


update xxx.student
set priezvisko='Caprio'
 where priezvisko='Ivanco'


delete from xxx.student 
where priezvisko='Caprio'

select * from xxx.student

drop table xxx.student ...vymaze tabulku


create table xxx.student
(
id integer,
meno varchar (10),
priezvisko varchar (20),
datumnar DATE,
mesto varchar (15) 
) 



insert into xxx.student
 values
 (1,'Jan','Hrasko','12.12.1988','Kosice'),
 (2,'Ruzena','Sipova','1.1.1111','Bardejov'),
 (3,'Ali','Baba','5.5.1971','Bagdad'),
 (4,'Jan','Polienko','7.7.1981','Kosice'),
 (5,'Juro','Trulo','8.8.1888','KOSICE'),
 (6,'Juro','Trulo','4.4.1444','Trebisov')


select count (*) from xxx.student where mesto='KOSICE'


----------
create table xxx.student
(
id integer,
meno varchar (10),
priezvisko varchar (20),
datumnar DATE,
mesto integer
);

create table xxx.mesto
(
id integer,
nazov varchar (10),
) 
 

15.10.2001

select * from sk.student,sk.mesto ...skombinuje tabulky kazdy s kazdym

select * from pg.student,pg.mesto 
 where pg.student.id_mesto = pg.mesto.id... spoji dve tab. podla id


select pg.student.priez,pg.student.meno
 from pg.student, mb.mesto 
 where mb.mesto.id= pg.student.id_mesto

select pg.student.priez,pg.student.meno,mb.mesto.nazov
 from pg.student, mb.mesto 
 where mb.mesto.id= pg.student.id_mesto and
 mb.mesto.nazov='Kosice'

select count(*)from pg.student, mb.mesto
 where mb.mesto.id= pg.student.id_mesto and
 mb.mesto.nazov='Kosice' ..vypise kolko je kosicanov

select mb.mesto.nazov , count(*)from pg.student, mb.mesto
 where mb.mesto.id= pg.student.id_mesto 
 group by mb.mesto.nazov..vypise 2 stlpce, kde v jednom bude mesto, a v druhom, kolko ma obyvatelov.

select mb.mesto.nazov , count(*)from pg.student, mb.mesto
 where mb.mesto.id= pg.student.id_mesto 
 group by mb.mesto.nazov having count (*)>1


POZOR: select mb.mesto.nazov , count(*)AS pocet 
 from pg.student, mb.mesto
 where mb.mesto.id= pg.student.id_mesto 
 group by mb.mesto.nazov having count (pocet)>1 ..NEFUNGUJE TU ALIAS !!!


select * from pg.student as d
 where datnar < date('2010-10-10')

select * from pg.student as d
 where datnar between date('1010-10-10') and date('2010-10-10') 

select priez,year(datnar)from pg.student ...2001-10-10 zobrazi ako 2001

-----5.cvicenie-----2001-10-22

ROBIME NOVE TABULKY POUZITIM PRIMARY KEY

create table qq.student
(
id integer NOT NULL,
meno varchar (10),
priezvisko varchar (20),
datumnar DATE,
mesto integer NOT NULL,
PRIMARY KEY (id)
);          
  {...... aby sme nemali dvoch studentov 2x danych, tak toto. Nemoze
  byt nulova hodnota v Primary key, preto pouzijeme pri id Not Null.}


create table qq.mesto
(
id integer NOT NULL,
nazov varchar (10),
PRIMARY KEY(id)
) 


insert into qqq.student
 values
 (1,'Jan','Hrasko','12.12.1988',1),
 (2,'Ruzena','Sipova','1.1.1111',2),
 (3,'Ali','Baba','5.5.1971',3),
 (4,'Jan','Polienko','7.7.1981',1),
 (5,'Juro','Trulo','8.8.1888',1),
 (6,'Juro','Trulo','4.4.1444',4)

insert into qqq.MESTO 
 values
 (1,'KOSICE'),
 (2,'BARDEJOV'),
 (3,'BAGDAD'),
 (4,'TREBISOV')
 

alter table qqq.student 
 add foreign key sm(mesto) references qqq.mesto

select * from qqq.mesto
select * from qqq.student


alter table qqq.student add column izba char(5) ...doplni do tabulky stpec 'izba' .. (5 znakov)
update qqq.student set izba='220B' where id=3

select * from qqq.student where izba is not null
select * from qqq.student where not izba is not null
select * from qqq.student where izba='220B'

{select izba from qqq.student as P;
 izba from qqq.student as I
where P.izba=I.izba}

ALTER TABLE kk.student DROP CONSTRAINT sm   sm- veyba-toto zrusi vezbu

ALTER TABLE kk.student ADD FOREIGN KEY sm(mesto_id)
 REFERENCES kk.mesto ON DELETE CASCADE  -pridaj vez, ak sa odstranuje, potom odstran aj vezby

DELETE FROM kk.mesto 
 WHERE nazov='Kosice'



SELECT * FROM kk.student as s1, kk.student as s2 -priradi aliasy
 WHERE s1.izba=s2.izba -ludia na tej istej izbe
 AND s1.id<>s2.id  -okrem hraska (hrasko nie je spolub. sam seba)
 AND s1.priezvisko='Hrasko';      -urci ze s1=hrasko (- vyp susedov hraska)

--------------------------------------------------

uloha:

mesto n----------1 student 1----------------n skupina
                 1
                 -
                 -
                 -
                 -
                 n
                 izba 1------------n typ izby


 DROP TABLE pg.mesto;
 DROP TABLE pg.skupina;
 DROP TABLE pg.student;
 DROP TABLE pg.typ_izby;
 DROP TABLE pg.izba;

 create table pg.mesto
  ( id integer not null,
    nazov varchar(20) not null,
    primary key (id)
  );

 create table pg.skupina
  ( id integer not null,
    nazov varchar(20) not null,
    kod varchar(5) not null,
    primary key (id)
   );

 create table pg.typ_izby
  ( id integer not null,
    kod varchar(5) not null, 
    popis varchar(20) not null,
    kapacita integer not null,
    poplatok decimal (5,2),
    primary key (id)
   );

 create table pg.izba
  ( id integer not null,
    cislo varchar(5) not null, 
    id_typ integer not null,
    primary key (id)
   );

 create table pg.student
  ( id integer not null,
    meno varchar(20) not null,
    priezvisko varchar(20) not null,
    dat_nar date not null,
    pohlavie varchar(1) not null,
    rocnik integer,
    id_mesto integer not null,     
    id_izba integer,
    id_skupina integer not null,
    primary key (id)
   );

 ALTER TABLE pg.student ADD FOREIGN KEY sm(id_mesto) REFERENCES pg.mesto;  
    -- prida vazbu (s nazvom sm) polozky id_mesto s tabulkou pg.mesto 
 ALTER TABLE pg.student ADD FOREIGN KEY ss(id_skupina) REFERENCES pg.skupina;
 ALTER TABLE pg.student ADD FOREIGN KEY si(id_izba) REFERENCES pg.izba;
 ALTER TABLE pg.izba ADD FOREIGN KEY it(id_typ) REFERENCES pg.typ_izby;



/////////////////////////////kosta///////////////////////////////////

 DROP TABLE kk.mesto;
 DROP TABLE kk.skupina;
 DROP TABLE kk.student;
 DROP TABLE kk.typ_izby;
 DROP TABLE kk.izba;

 create table kk.mesto
  ( id integer not null,
    nazov varchar(20) not null,
    primary key (id)
  );

 create table kk.skupina
  ( id integer not null,
    nazov varchar(20) not null,
    kod varchar(5) not null,
    primary key (id)
   );

 create table kk.typ_izby
  ( id integer not null,
    kod varchar(5) not null, 
    popis varchar(20) not null,
    kapacita integer not null,
    poplatok decimal (5,2),
    primary key (id)
   );

 create table kk.izba
  ( id integer not null,
    cislo varchar(5) not null, 
    id_typ integer not null,
    primary key (id)
   );

 create table kk.student
  ( id integer not null,
    meno varchar(20) not null,
    priezvisko varchar(20) not null,
    dat_nar date not null,
    pohlavie char(1) not null,
    rocnik integer,
    id_mesto integer not null,     
    id_izba integer,
    id_skupina integer not null,
    primary key (id)
   );

 ALTER TABLE kk.student ADD FOREIGN KEY sm(id_mesto) REFERENCES kk.mesto;
 ALTER TABLE kk.student ADD FOREIGN KEY ss(id_skupina) REFERENCES kk.skupina;
 ALTER TABLE kk.student ADD FOREIGN KEY si(id_izba) REFERENCES kk.izba;
 ALTER TABLE kk.izba ADD FOREIGN KEY it(id_typ) REFERENCES kk.typ_izby;


///////////////////////////////////////////////////////////////////////////////////////////////




napln tabulky....


 DELETE FROM pg.mesto;

 INSERT INTO pg.mesto 
 VALUES
 (1,'Kosice'),
 (2,'Bardejov'),
 (3,'Bagdad'),
 (4,'Trebisov'),
 (5,'Presov');

 INSERT INTO pg.skupina 
 VALUES
 (1,'Matematika','M'), 
 (3,'Matematika-Fyzika','MF'), 
 (2,'Informatika','I');

 INSERT INTO pg.typ_izby
 VALUES
 (1,'M6A','2 os. bez TV',2,500.00),
 (2,'M6B','3 bez pristelku',3,450.00); 
