Zadanie cislo 10
SELECT * FROM auto
WHERE typ='o' AND celkova_suma<50000; //vypise podla ktiterii
SELECT typ, spz, celkova_suma FROM auto; //vypise typ, spz, celkova_suma
INSERT INTO auto
VALUES('MT-945DY','o','14.08.2005',20500,'cierna'); //vlozi prvok
Prihlásiť na odber:
Zverejniť komentáre (Atom)
9 komentárov:
INSERT INTO auto
VALUES('MT-541BY','o','27.06.2001',20000); //vlozit prvok do tabulky
Select * from auto; //vypis tabluky auto
select table_name from user_tables; //vypis tabuliek
alter table auto modify farba ; //priradi dalsi stlpec
UPDATE auto SET celova_suma=30000 //zmeni hodnotu prvku
WHERE spz='MT-999ZA';
UPDATE auto SET farba='cervena';
UPDATE auto SET celova_suma=3000
WHERE rok_vyroby>'01.01.2004';
alter table auto rename column celova_suma to celkova_suma; //zmeni nazov stlpca
delete from auto where spz='MT-999ZA'; //vymaze prvok s danou spz
delete from auto where typ='n'; //vymaye vsetky prvky typu 'n'
SELECT * FROM auto
WHERE typ='o' AND celkova_suma<50000; //vypise podla ktiterii
SELECT typ, spz, celkova_suma FROM auto; //vypise typ, spz, celkova_suma
INSERT INTO auto
VALUES('MT-854DY','o','14.08.2005',20500,'cierna'); //vlozi prvok
CREATE VIEW car AS
SELECT typ "car_type", celkova_suma "price", farba "color" FROM auto; //vytvori pohlad pre inych
SELECT * FROM car; //zobrazi pohlad car
INSERT INTO auto
VALUES('MT-999ZA','o','14.01.1986',500,'biela');
drop view car; // vymaze pohlad
ALTER VIEW CAR ADD CHECK('o');
prihlasenie do dabazy:
prihl. meno:TE******
heslo:TE****** //pokial nebolo zmenene
prihl. retazec : orclbo2
SELECT COUNT(*) "pocet" FROM auto; //vypise pocet riadkov v tabulke a da mu alias pocet
SELECT COUNT(*) "pocet" FROM auto
WHERE celkova_suma>7200; // vypise pocet prvkov, ktore zodpovedaju podmienke
SELECT AVG(celkova_suma)"priemerna suma" FROM auto; // vypise priemernu hodnotu podla zadanych parametrov
SELECT SUM(celkova_suma) FROM auto;
SELECT SUM(celkova_suma) FROM auto where typ='o'; //vypise celkovu sumu podla parametrov
SELECT MAX(celkova_suma) FROM auto;
//vypise maximalnu hodnotu podla typu
SELECT typ FROM auto GROUP by typ; //vypise pouzite typy v tabulke !*len raz*!
SELECT typ,COUNT(*) FROM auto GROUP by typ; //vypise pouzite typy a aj ich pocet
SELECT typ, avg(celkova_suma), COUNT(*) FROM auto GROUP by typ; //dalsi variant mozneho zoskupenia
SELECT typ,COUNT(*)"pocet",sum(celkova_suma) FROM auto GROUP by typ having sum(celkova_suma)>122000; // vyuzitie podmienky having, ktora ovplyvnuje celkovu sumu daneho typu a podla podmienky ju vyhodnoti
SELECT typ,COUNT(*)"pocet", sum(celkova_suma) FROM auto where celkova_suma>7000 GROUP by typ having sum(celkova_suma)>50000;
//vypis podla kriterii, najprv prebhne podmienka where a potom having
SELECT typ, farba, COUNT(*) FROM auto GROUP BY GROUPING sets (typ, farba);
//zgrupovanie podla chuti programatora a potreby :-)
SELECT typ, farba, COUNT(*),SUM(celkova_suma) FROM auto GROUP by cube (typ, farba);
//zgrupovanie podla chuti programatora a potreby :-)
SELECT typ, farba, COUNT(*),SUM(celkova_suma) FROM auto GROUP by rollup (typ, farba);
////zgrupovanie podla chuti programatora a potreby :-)
odkazy:
http://hornad.fei.tuke.sk/~bratru/dbs/ -ing Bratru DBS
SELECT object_name FROM user_procedures; //zoznam procedur
CREATE OR REPLACE PROCEDURE upd_auto (ks IN NUMBER, aspz IN VARCHAR)
IS
BEGIN
DECLARE cena NUMBER;
BEGIN
SELECT cena_sluzby INTO cena FROM sluzba WHERE kod_sluzby = ks;
UPDATE auto SET celkova_suma = celkova_suma + cena WHERE spz = aspz;
END;
END upd_auto;
//vytvorenie procedury
SELECT trigger_name FROM user_triggers;
//vypise tigrikov
SHOW ERRORS TRIGGER *nazov triggra*
//vypise tigrove momentalne virusy
CREATE OR REPLACE TRIGGER ins_asz
AFTER INSERT ON asz
FOR EACH ROW
CALL upd_auto(:new.kod_sluzby, :new.spz)
//vytvorenie tigra
SELECT body FROM mnoholnik WHERE cos(uhol)>0;
SELECT * FROM auto Where Substr(spz,1,2)='KE';
//vypise automobily, ktorych SPZ zacina KE
SELECT * FROM auto Where Substr(spz,1,2)='MT';
SELECT * FROM auto WHERE SUBSTR(spz,1,2)='KE';
SELECT * FROM auto WHERE SUBSTR(spz,4,1)='8';
SELECT spz, substr(spz,4,3) ,
sqrt(to_number(substr(spz,4,3))) from auto where sqrt(to_number(Substr(spz,4,3)))>30;
Zverejnenie komentára