Seminárka

3555878');
INSERT INTO Offices VALUES ('2', 118, '30000', '50000','Horní 98','Pardubice','CZ','50300','455955878');
INSERT INTO Offices VALUES ('3', 145, '50000', '79900','Stodolní 10','Ostrava','CZ','70100','555955878');
INSERT INTO Salesman VALUES (101, '1', 'pokladník', 'Karel Novák','2000000','287968658','Na Kačerově 201','Praha','CZ','11000');
INSERT INTO Salesman VALUES (102, '1', 'obsluha', 'Jaromír Halabala','3000000','287585576','Jiráskova 609','Praha','CZ','11000');
INSERT INTO Salesman VALUES (103, '1', 'obsluha', 'Jindřich Koutný','3000000','288403768','Na bělidle 761','Praha','CZ','11000');
INSERT INTO Salesman VALUES (104, '1', 'servis', 'Josef Struh','2000000','223454358','Smetanova 1201','Praha','CZ','11000');
INSERT INTO Salesman VALUES (105, '1', 'vedoucí pobočky', 'Jarmila Štěková','5000000','222587668','Jaroňkova 3401','Praha','CZ','11100');
INSERT INTO Salesman VALUES (201, '2', 'pokladník', 'Hana Nosná','3000000','487968658','Janečka 341','Pardubice','CZ','50300');
INSERT INTO Salesman VALUES (202, '2', 'obsluha', 'Veronika Semradová','3000000','487356658','Kudlákova 1341','Pardubice','CZ','50300');
INSERT INTO Salesman VALUES (203, '2', 'servis', 'Tomáš Janík','2500000','487876554','Hradecká 987','Pardubice','CZ','50300');
INSERT INTO Salesman VALUES (204, '2', 'vedoucí pobočky', 'Jan Šmíd','3500000','43645258','Smolná 476','Pardubice','CZ','50300');
INSERT INTO Salesman VALUES (301, '3', 'pokladník', 'Jiří Rys','2000000','555968658','Na Machulce 583','Ostrava','CZ','70300');
INSERT INTO Salesman VALUES (302, '3', 'obsluha', 'Lukáš Nejedlý','3000000','555900576','Soukenická 729','Ostrava','CZ','70300');
INSERT INTO Salesman VALUES (303, '3', 'obsluha', 'Soňa Heláková','3000000','555340768','Svoďáka 615','Ostrava','CZ','70300');
INSERT INTO Salesman VALUES (304, '3', 'servis', 'Petr Pet','2000000','555678876','Slaninova 1253','Ostrava','CZ','70300');
INSERT INTO Salesman VALUES (305, '3', 'vedoucí pobočky', 'Svatoslava Jaková','4000000','555870668','Jiříkovského 401','Ostrava','CZ','70300');
INSERT INTO Customer VALUES ('10001', 'Stroje, s.r.o.', 'Karel Plyš','Stáň 78','Kladno','CZ','12345','233765785','1', 0, 0, 0);
INSERT INTO Customer VALUES ('10002', 'Sefra, s.r.o.', 'Jan Směch','Sovákova 564','Příbram','CZ','16455','337653345','1',0, 0, 0);
INSERT INTO Customer VALUES ('20001', 'Miste, s.r.o.', 'Kamil Slabý','U řeky 134','Náchod','CZ','54544','454565785','2',0, 0, 0);
INSERT INTO Customer VALUES ('20002', 'SKG, s.r.o.', 'Vojtěch Jindruch','Dlouhá 554','Choceň','CZ','65534','445656015','2', 0, 0, 0);
INSERT INTO Customer VALUES ('30001', 'URT,a.s.', 'Luboš Farný','Stoklasova 238','Orlová','CZ','73744','558393939','3', 0, 0, 0);
INSERT INTO Customer VALUES ('30002', 'FTT,a.s.', 'Slavomír Hlinka','Kluzká 743','Havířov','CZ','74574','558789658','3', 0, 0, 0);
INSERT INTO Parts VALUES ('T9999', 'nazev', 500, 130, 15000, 10000, 1400, 210000);
INSERT INTO Parts VALUES ('T9998', 'nazev2', 5000, 1300, 150, 100, 11400, 17100);
INSERT INTO Parts VALUES ('T9997', 'nazev3', 1500, 130, 8000, 5000, 2000, 160000);
INSERT INTO Parts VALUES ('T9996', 'nazev4', 800, 260, 1000, 500, 2290, 22900);
INSERT INTO Parts VALUES ('T9995', 'nazev5', 200, 360, 1000, 500, 2290, 22905);
INSERT INTO Parts VALUES ('T9994', 'nazev6', 800, 560, 1000, 500, 2290, 22900);
INSERT INTO Parts VALUES ('T9993', 'nazev7', 500, 130, 15000, 10000, 1400, 210000);
INSERT INTO Parts VALUES ('T9992', 'nazev8', 4000, 1300, 150, 200, 11400, 17100);
INSERT INTO Parts VALUES ('T9991', 'nazev9', 1500, 130, 8000, 5000, 2000, 160000);
INSERT INTO Parts VALUES ('T9990', 'nazev10', 800, 260, 1000, 300, 2290, 22900);
INSERT INTO Parts VALUES ('T9989', 'nazev11‘, 200, 160, 1000, 500, 2290, 22905);
INSERT INTO Parts VALUES ('T9987', 'nazev12', 800, 560, 1000, 500, 2290, 22900);
INSERT INTO Invoices VALUES (9999, '10001', '3.3.2006',350000,'105');
INSERT INTO Invoices VALUES (9998, '10002', '3.1.2006',50000,'104');
INSERT INTO Invoices VALUES (9997, '20001', '13.3.2006',540000,'203');
INSERT INTO Invoices VALUES (9996, '20002', '14.2.2006',890000,'204');
INSERT INTO Invoices VALUES (9995, '30001', '23.1.2006',290000,'305');
INSERT INTO Invoices VALUES (9994, '30002', '23.2.2006',120000,'301');
INSERT INTO Detail VALUES (9999,1234, 100,'T9999',1000,100000);
INSERT INTO Detail VALUES (9999,1235, 50,'T9999',8000,400000);
INSERT INTO Detail VALUES (9999,1236, 10,'T9999',15000,150000);
II. ZADÁNÍ – TVORBA JEDNODUCHÝCH DOTAZŮ
Vypište všechny faktury, vystavené v určitém období (např. březen 2006), tabulka invoices
USE _RIHA_216
GO
SELECT [ino], [cno], [idate], [itotal], [salesman] from invoices where [idate]>='2006-01-03' and [idate]30000
begin
print 'Dneska jste se snazili chlapecci'
end
else
begin
print 'Dneska to stalo za prd vazeni'
end
execute trzby
create procedure faktura
as
declare @faktura integer
select @faktura = ino FROM invoices WHERE itotal = (SELECT MAX( itotal ) FROM invoices )
if @faktura=9999
begin
print 'Faktura 9999 je vystavena na nejvyssi castku'
end
else
begin
print 'Faktura 9999 neni vystavena na nejvyssi castku'
end
execute faktura