Cvičná databáze OSDL: Porovnání verzí

Z GeoWikiCZ
Skočit na navigaci Skočit na vyhledávání
(schema tabulek)
(wiki preformatted (mezera prvni znak) nezobrazi '' ...nahrazeno <pre>...</pre>)
Řádek 38: Řádek 38:
 
== Skript ==
 
== Skript ==
 
Skript pro založení cvičné databáze OSDL a naplnění tabulek je
 
Skript pro založení cvičné databáze OSDL a naplnění tabulek je
 +
<pre>
 +
DROP TABLE Pracoviste CASCADE;
 +
DROP TABLE Zamestnanci CASCADE;
 +
DROP TABLE Mzdy CASCADE;
  
DROP TABLE Pracoviste CASCADE;
+
CREATE TABLE Pracoviste(
DROP TABLE Zamestnanci CASCADE;
+
  kod char(2) PRIMARY KEY,
DROP TABLE Mzdy CASCADE;
+
  popis varchar(20) UNIQUE NOT NULL CHECK (popis <> '')
+
);
CREATE TABLE Pracoviste(
+
 
  kod char(2) PRIMARY KEY,
+
CREATE TABLE Zamestnanci(
  popis varchar(20) UNIQUE NOT NULL CHECK (popis <> '')
+
  id SERIAL PRIMARY KEY,
);
+
  jmeno varchar(20) NOT NULL CHECK (jmeno <> ''),
+
  prijmeni varchar(20) NOT NULL CHECK (prijmeni <> ''),
CREATE TABLE Zamestnanci(
+
  pracoviste_kod char(2) NOT NULL REFERENCES pracoviste(kod),
  id SERIAL PRIMARY KEY,
+
  vek integer NOT NULL CHECK(vek > 0)
  jmeno varchar(20) NOT NULL CHECK (jmeno <> ''),
+
);
  prijmeni varchar(20) NOT NULL CHECK (prijmeni <> ''),
+
 
  pracoviste_kod char(2) NOT NULL REFERENCES pracoviste(kod),
+
CREATE TABLE Mzdy(
  vek integer NOT NULL CHECK(vek > 0)
+
  id SERIAL PRIMARY KEY,
);
+
  vlozeno date NOT NULL DEFAULT(CURRENT_DATE),
+
  zamestnanec_id integer NOT NULL REFERENCES Zamestnanci(id),
CREATE TABLE Mzdy(
+
  castka NUMERIC(8,2) NOT NULL CHECK (castka > 0.0)
  id SERIAL PRIMARY KEY,
+
);
  vlozeno date NOT NULL DEFAULT(CURRENT_DATE),
+
 
  zamestnanec_id integer NOT NULL REFERENCES Zamestnanci(id),
+
INSERT INTO Pracoviste VALUES('kc','Konstrukce');
  castka NUMERIC(8,2) NOT NULL CHECK (castka > 0.0)
+
INSERT INTO Pracoviste VALUES('pr','Provoz');
);
+
INSERT INTO Pracoviste VALUES('sk','Sekretariat');
+
INSERT INTO Pracoviste VALUES('vy','Vyroba');
INSERT INTO Pracoviste VALUES('kc','Konstrukce');
+
INSERT INTO Pracoviste VALUES('it','Informatika');
INSERT INTO Pracoviste VALUES('pr','Provoz');
+
 
INSERT INTO Pracoviste VALUES('sk','Sekretariat');
+
SELECT * FROM Pracoviste;
INSERT INTO Pracoviste VALUES('vy','Vyroba');
+
 
INSERT INTO Pracoviste VALUES('it','Informatika');
+
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Pavel', 'Stehule', 'it',33);
+
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Radek', 'Hirjak', 'kc', 32);
SELECT * FROM Pracoviste;
+
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Jan', 'Pytel', 'it', 31);
+
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Zdenek', 'Stehule', 'vy', 28);
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Pavel', 'Stehule', 'it',33);
+
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Lucie', 'Kubikova', 'sk', 25);
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Radek', 'Hirjak', 'kc', 32);
+
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Tomas', 'Zezula', 'sk', 45);
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Jan', 'Pytel', 'it', 31);
+
 
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Zdenek', 'Stehule', 'vy', 28);
+
SELECT * FROM Zamestnanci;
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Lucie', 'Kubikova', 'sk', 25);
+
 
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Tomas', 'Zezula', 'sk', 45);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 1, 30000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 2, 25000);
SELECT * FROM Zamestnanci;
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 3, 28000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 4, 20000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 1, 30000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 5, 18000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 2, 25000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 6, 50000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 3, 28000);
+
 
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 4, 20000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 1, 30000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 5, 18000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 2, 25000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 6, 50000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 3, 30000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 4, 20000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 1, 30000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 5, 18000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 2, 25000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 6, 50000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 3, 30000);
+
 
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 4, 20000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 1, 31000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 5, 18000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 2, 26000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 6, 50000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 3, 31000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 4, 20000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 1, 31000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 5, 19000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 2, 26000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 6, 60000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 3, 31000);
+
 
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 4, 20000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 1, 30500);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 5, 19000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 2, 25000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 6, 60000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 3, 28000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 4, 20000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 1, 30500);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 5, 18000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 2, 25000);
+
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 6, 51000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 3, 28000);
+
 
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 4, 20000);
+
SELECT * FROM Mzdy;
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 5, 18000);
+
</pre>
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 6, 51000);
 
 
SELECT * FROM Mzdy;
 

Verze z 21. 3. 2007, 17:40

Tabulky

Cvičná databáze OSDL obsahuje tři tabulky

Pracoviste
kod
popis
Zamestnanci
id
jmeno
prijmeni
pracoviste_kod
vek
Mzdy
id
vlozeno
zamestnanec_id
castka

Příklady

Skript

Skript pro založení cvičné databáze OSDL a naplnění tabulek je

DROP TABLE Pracoviste CASCADE;
DROP TABLE Zamestnanci CASCADE;
DROP TABLE Mzdy CASCADE;

CREATE TABLE Pracoviste(
  kod char(2) PRIMARY KEY,
  popis varchar(20) UNIQUE NOT NULL CHECK (popis <> '')
);

CREATE TABLE Zamestnanci(
  id SERIAL PRIMARY KEY,
  jmeno varchar(20) NOT NULL CHECK (jmeno <> ''),
  prijmeni varchar(20) NOT NULL CHECK (prijmeni <> ''),
  pracoviste_kod char(2) NOT NULL REFERENCES pracoviste(kod),
  vek integer NOT NULL CHECK(vek > 0)
);

CREATE TABLE Mzdy(
  id SERIAL PRIMARY KEY,
  vlozeno date NOT NULL DEFAULT(CURRENT_DATE),
  zamestnanec_id integer NOT NULL REFERENCES Zamestnanci(id),
  castka NUMERIC(8,2) NOT NULL CHECK (castka > 0.0)
);

INSERT INTO Pracoviste VALUES('kc','Konstrukce');
INSERT INTO Pracoviste VALUES('pr','Provoz');
INSERT INTO Pracoviste VALUES('sk','Sekretariat');
INSERT INTO Pracoviste VALUES('vy','Vyroba');
INSERT INTO Pracoviste VALUES('it','Informatika');

SELECT * FROM Pracoviste;

INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Pavel', 'Stehule', 'it',33);
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Radek', 'Hirjak', 'kc', 32);
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Jan', 'Pytel', 'it', 31);
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Zdenek', 'Stehule', 'vy', 28);
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Lucie', 'Kubikova', 'sk', 25);
INSERT INTO Zamestnanci(jmeno, prijmeni, pracoviste_kod, vek) VALUES('Tomas', 'Zezula', 'sk', 45);

SELECT * FROM Zamestnanci;

INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 1, 30000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 2, 25000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 3, 28000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 4, 20000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 5, 18000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-01-01', 6, 50000);

INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 1, 30000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 2, 25000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 3, 30000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 4, 20000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 5, 18000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-02-01', 6, 50000);

INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 1, 31000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 2, 26000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 3, 31000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 4, 20000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 5, 19000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-03-01', 6, 60000);

INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 1, 30500);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 2, 25000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 3, 28000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 4, 20000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 5, 18000);
INSERT INTO Mzdy VALUES(DEFAULT, '2007-04-01', 6, 51000);

SELECT * FROM Mzdy;