BD9
rem
rem $Header: PLdemobld.sql.0,v 00001 91/09/21 11:15:45
rem
rem set termout on
rem host write sys$output "Konstrukcja relacji demonstracyjnych. Prosze czekac"
rem host echo "Konstrukcja relacji demonstracyjnych. Prosze czekac"
set feedback on
DROP TABLE PRACOWNICY cascade constraints;
DROP TABLE ZESPOLY cascade constraints;
DROP TABLE ETATY cascade constraints;
DROP TABLE PROJEKTY cascade constraints;
DROP TABLE PROJEKTY_KOPIA cascade constraints;
CREATE TABLE ZESPOLY
(ID_ZESP NUMBER(4) CONSTRAINT PK_ZESP PRIMARY KEY,
NAZWA VARCHAR2(20),
ADRES VARCHAR2(20) );
CREATE TABLE ETATY
( NAZWA VARCHAR2(10) CONSTRAINT PK_ETAT PRIMARY KEY,
PLACA_MIN NUMBER(6,2),
PLACA_MAX NUMBER(6,2));
CREATE TABLE PRACOWNICY
(ID_PRAC NUMBER(6) CONSTRAINT PK_PRAC PRIMARY KEY,
NAZWISKO VARCHAR2(15),
ETAT VARCHAR2(10) CONSTRAINT FK_ETAT REFERENCES ETATY(NAZWA),
ID_SZEFA NUMBER(6) CONSTRAINT FK_ID_SZEFA REFERENCES PRACOWNICY(ID_PRAC),
ZATRUDNIONY DATE,
PLACA_POD NUMBER(6,2) CONSTRAINT MIN_PLACA_POD CHECK(PLACA_POD>100),
PLACA_DOD NUMBER(6,2),
ID_ZESP NUMBER(4) CONSTRAINT FK_ID_ZESP REFERENCES ZESPOLY(ID_ZESP));
INSERT INTO ZESPOLY VALUES (10,'ADMINISTRACJA', 'PIOTROWO 3A');
INSERT INTO ZESPOLY VALUES (20,'SYSTEMY ROZPROSZONE','PIOTROWO 3A');
INSERT INTO ZESPOLY VALUES (30,'SYSTEMY EKSPERCKIE', 'STRZELECKA 14');
INSERT INTO ZESPOLY VALUES (40,'ALGORYTMY', 'WLODKOWICA 16');
INSERT INTO ZESPOLY VALUES (50,'BADANIA OPERACYJNE', 'MIELZYNSKIEGO 30');
INSERT INTO ETATY VALUES ('PROFESOR' ,800.00,1500.00);
INSERT INTO ETATY VALUES ('ADIUNKT' ,510.00, 750.00);
INSERT INTO ETATY VALUES ('ASYSTENT' ,300.00, 500.00);
INSERT INTO ETATY VALUES ('STAZYSTA' ,150.00, 250.00);
INSERT INTO ETATY VALUES ('SEKRETARKA',270.00, 450.00);
INSERT INTO ETATY VALUES ('DYREKTOR' ,1280.00,2100.00);
INSERT INTO PRACOWNICY VALUES (100,'WEGLARZ' ,'DYREKTOR' ,NULL,to_date('01-01-1968','DD-MM-YYYY'),1730.00,420.50,10);
INSERT INTO PRACOWNICY VALUES (110,'BLAZEWICZ' ,'PROFESOR' ,100 ,to_date('01-05-1973','DD-MM-YYYY'),1350.00,210.00,40);
INSERT INTO PRACOWNICY VALUES (120,'SLOWINSKI' ,'PROFESOR' ,100 ,to_date('01-09-1977','DD-MM-YYYY'),1070.00, NULL,30);
INSERT INTO PRACOWNICY VALUES (130,'BRZEZINSKI' ,'PROFESOR' ,100 ,to_date('01-07-1968','DD-MM-YYYY'), 960.00, NULL,20);
INSERT INTO PRACOWNICY VALUES (140,'MORZY' ,'PROFESOR' ,130 ,to_date('15-09-1975','DD-MM-YYYY'), 830.00,105.00,20);
INSERT INTO PRACOWNICY VALUES (150,'KROLIKOWSKI','ADIUNKT' ,130 ,to_date('01-09-1977','DD-MM-YYYY'), 645.50, NULL,20);
INSERT INTO PRACOWNICY VALUES (160,'KOSZLAJDA' ,'ADIUNKT' ,130 ,to_date('01-03-1985','DD-MM-YYYY'), 590.00, NULL,20);
INSERT INTO PRACOWNICY VALUES (170,'JEZIERSKI' ,'ASYSTENT' ,130 ,to_date('01-10-1992','DD-MM-YYYY'), 439.70, 80.50,20);
INSERT INTO PRACOWNICY VALUES (190,'MATYSIAK' ,'ASYSTENT' ,140 ,to_date('01-09-1993','DD-MM-YYYY'), 371.00, NULL,20);
INSERT INTO PRACOWNICY VALUES (180,'MAREK' ,'SEKRETARKA',100 ,to_date('20-02-1985','DD-MM-YYYY'), 410.20, NULL,10);
INSERT INTO PRACOWNICY VALUES (200,'ZAKRZEWICZ' ,'STAZYSTA' ,140 ,to_date('15-07-1994','DD-MM-YYYY'), 208.00, NULL,30);
INSERT INTO PRACOWNICY VALUES (210,'BIALY' ,'STAZYSTA' ,130 ,to_date('15-10-1993','DD-MM-YYYY'), 250.00,170.60,30);
INSERT INTO PRACOWNICY VALUES (220,'KONOPKA' ,'ASYSTENT' ,110 ,to_date('01-10-1993','DD-MM-YYYY'), 480.00, NULL,20);
INSERT INTO PRACOWNICY VALUES (230,'HAPKE' ,'ASYSTENT' ,120 ,to_date('01-09-1992','DD-MM-YYYY'), 480.00, 90.00,30);
COMMIT;
set termout on
set feedback on
SELECT '' AS ZADANIE_1 FROM dual;
CREATE TABLE PROJEKTY (
ID_PROJEKTU NUMBER(4) GENERATED ALWAYS AS IDENTITY,
OPIS_PROJEKTU VARCHAR(20),
DATA_ROZPOCZECIA DATE DEFAULT CURRENT_DATE,
DATA_ZAKONCZENIA DATE,
FUNDUSZ NUMBER(7,2)
);
SELECT '' AS ZADANIE_2 FROM dual;
INSERT INTO PROJEKTY (OPIS_PROJEKTU, DATA_ROZPOCZECIA, DATA_ZAKONCZENIA, FUNDUSZ) VALUES ('Indeksy bitmapowe', TO_DATE('02-04-1999', 'DD-MM-YYYY'), TO_DATE('31-08-2001', 'DD-MM-YYYY'), 25000);
INSERT INTO PROJEKTY (OPIS_PROJEKTU, FUNDUSZ) VALUES ('Sieci kręgosłupowe', 19000);
SELECT '' AS ZADANIE_3 FROM dual;
SELECT ID_PROJEKTU, OPIS_PROJEKTU FROM PROJEKTY;
SELECT '' AS ZADANIE_4 FROM dual;
INSERT INTO PROJEKTY (OPIS_PROJEKTU, DATA_ROZPOCZECIA, DATA_ZAKONCZENIA, FUNDUSZ) VALUES ('Indeksy drzewiaste', TO_DATE('24-12-2013', 'DD-MM-YYYY'), TO_DATE('01-01-2014', 'DD-MM-YYYY'), 1200);
SELECT ID_PROJEKTU, OPIS_PROJEKTU FROM PROJEKTY;
SELECT '' AS ZADANIE_5 FROM dual;
UPDATE PROJEKTY SET ID_PROJEKTU = 10 WHERE OPIS_PROJEKTU = 'Indeksy drzewiaste';
-- operacja nie powiodła sie.
SELECT '' AS ZADANIE_6 FROM dual;
CREATE TABLE PROJEKTY_KOPIA AS SELECT * FROM PROJEKTY;
SELECT * FROM PROJEKTY_KOPIA;
SELECT '' AS ZADANIE_7 FROM dual;
INSERT INTO PROJEKTY_KOPIA (ID_PROJEKTU, OPIS_PROJEKTU,DATA_ROZPOCZECIA, DATA_ZAKONCZENIA, FUNDUSZ) VALUES
(10, 'Sieci lokalne', CURRENT_DATE, CURRENT_DATE+INTERVAL '1' YEAR, 24500);
SELECT * FROM PROJEKTY_KOPIA;
SELECT '' AS ZADANIE_8 FROM dual;
DELETE FROM PROJEKTY WHERE OPIS_PROJEKTU = 'Indeksy drzewiaste';
SELECT * FROM PROJEKTY;
-- nie zostanie usunięta automatycznie tez z kopii.
SELECT * FROM PROJEKTY_KOPIA;
SELECT '' AS ZADANIE_9 FROM dual;
SELECT table_name from user_Tables ORDER BY table_name;
--------------------------------------------------------------------------------
SELECT '' AS Zadanie_1 FROM DUAL;
ALTER TABLE PROJEKTY ADD CONSTRAINT PK_PROJEKTY PRIMARY KEY (ID_PROJEKTU);
ALTER TABLE PROJEKTY ADD CONSTRAINT UK_PROJEKTY UNIQUE (OPIS_PROJEKTU);
ALTER TABLE PROJEKTY MODIFY OPIS_PROJEKTU NOT NULL;
ALTER TABLE PROJEKTY ADD CONSTRAINT CHK_DATA_ZAKONCZENIA
CHECK (DATA_ZAKONCZENIA > DATA_ROZPOCZECIA);
ALTER TABLE PROJEKTY ADD CONSTRAINT CHK_FUNDUSZ CHECK (FUNDUSZ > 0);
SELECT CONSTRAINT_NAME,
DECODE(CONSTRAINT_TYPE,
'P', 'PRIMARY KEY',
'U', 'UNIQUE',
'C', 'CHECK',
'R', 'FOREIGN KEY') AS C_TYPE,
SEARCH_CONDITION,
COLUMN_NAME
FROM USER_CONSTRAINTS
NATURAL JOIN USER_CONS_COLUMNS
WHERE TABLE_NAME = 'PROJEKTY';
SELECT '' AS Zadanie_2 FROM DUAL;
INSERT INTO PROJEKTY (OPIS_PROJEKTU, DATA_ROZPOCZECIA, DATA_ZAKONCZENIA, FUNDUSZ)
VALUES ('Indeksy bitmapowe',
TO_DATE('2015-04-12', 'YYYY-MM-DD'),
TO_DATE('2016-09-30', 'YYYY-MM-DD'),
40000);
-- naruszenie ograniczenia unique UK_PROJEKTY
SELECT '' AS Zadanie_3 FROM DUAL;
CREATE TABLE PRZYDZIALY (
ID_PROJEKTU NUMBER(4) NOT NULL,
NR_PRACOWNIKA NUMBER(6) NOT NULL,
PRZYDZIAL_OD DATE DEFAULT SYSDATE,
PRZYDZIAL_DO DATE,
STAWKA NUMBER(7,2),
ROLA VARCHAR2(20),
CONSTRAINT PK_PRZYDZIALY PRIMARY KEY (ID_PROJEKTU, NR_PRACOWNIKA),
CONSTRAINT FK_PRZYDZIALY_01 FOREIGN KEY (ID_PROJEKTU)
REFERENCES PROJEKTY(ID_PROJEKTU),
CONSTRAINT FK_PRZYDZIALY_02 FOREIGN KEY (NR_PRACOWNIKA)
REFERENCES PRACOWNICY(ID_PRAC),
CONSTRAINT CHK_PRZYDZIALY_DATY
CHECK (PRZYDZIAL_DO > PRZYDZIAL_OD),
CONSTRAINT CHK_PRZYDZIALY_STAWKA
CHECK (STAWKA > 0),
CONSTRAINT CHK_PRZYDZIALY_ROLA
CHECK (ROLA IN ('KIERUJĄCY', 'ANALITYK', 'PROGRAMISTA'))
);
-- Zadanie 4
SELECT '' AS Zadanie_4 FROM DUAL;
INSERT INTO PRZYDZIALY
SELECT (SELECT ID_PROJEKTU FROM PROJEKTY WHERE OPIS_PROJEKTU = 'Indeksy bitmapowe'),
170,
TO_DATE('1999-04-10', 'YYYY-MM-DD'),
TO_DATE('1999-05-10', 'YYYY-MM-DD'),
1000,
'KIERUJĄCY'
FROM DUAL;
INSERT INTO PRZYDZIALY
SELECT (SELECT ID_PROJEKTU FROM PROJEKTY WHERE OPIS_PROJEKTU = 'Indeksy bitmapowe'),
140,
TO_DATE('2000-12-01', 'YYYY-MM-DD'),
NULL,
1500,
'ANALITYK'
FROM DUAL;
INSERT INTO PRZYDZIALY
SELECT (SELECT ID_PROJEKTU FROM PROJEKTY WHERE OPIS_PROJEKTU = 'Sieci kręgosłupowe'),
140,
TO_DATE('2015-09-14', 'YYYY-MM-DD'),
NULL,
2500,
'KIERUJĄCY'
FROM DUAL;
SELECT * FROM PRZYDZIALY;
SELECT '' AS Zadanie_5 FROM DUAL;
ALTER TABLE PRZYDZIALY ADD GODZINY NUMBER(4) NOT NULL;
-- tabela już zawiera rekordy i nie można dodać kolumny NOT NULL bez wartości domyślnej
SELECT '' AS Zadanie_6 FROM DUAL;
ALTER TABLE PRZYDZIALY ADD GODZINY NUMBER(4);
UPDATE PRZYDZIALY SET GODZINY = 30;
ALTER TABLE PRZYDZIALY MODIFY GODZINY NOT NULL;
SELECT '' AS Zadanie_7 FROM DUAL;
ALTER TABLE PROJEKTY DISABLE CONSTRAINT UK_PROJEKTY;
SELECT CONSTRAINT_NAME, STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'PROJEKTY'
AND CONSTRAINT_NAME = 'UK_PROJEKTY';
SELECT '' AS Zadanie_8 FROM DUAL;
INSERT INTO PROJEKTY (OPIS_PROJEKTU, DATA_ROZPOCZECIA, DATA_ZAKONCZENIA, FUNDUSZ)
VALUES ('Indeksy bitmapowe',
TO_DATE('2015-04-12', 'YYYY-MM-DD'),
TO_DATE('2016-09-30', 'YYYY-MM-DD'),
40000);
SELECT * FROM PROJEKTY;
SELECT '' AS Zadanie_9 FROM DUAL;
ALTER TABLE PROJEKTY ENABLE CONSTRAINT UK_PROJEKTY;
-- zduplikowane wartości w kolumnie OPIS_PROJEKTU
SELECT '' AS Zadanie_10 FROM DUAL;
UPDATE PROJEKTY
SET OPIS_PROJEKTU = 'Inne indeksy'
WHERE ID_PROJEKTU = (
SELECT MAX(ID_PROJEKTU)
FROM PROJEKTY
WHERE OPIS_PROJEKTU = 'Indeksy bitmapowe'
);
ALTER TABLE PROJEKTY ENABLE CONSTRAINT UK_PROJEKTY;
SELECT '' AS Zadanie_11 FROM DUAL;
ALTER TABLE PROJEKTY MODIFY OPIS_PROJEKTU VARCHAR2(10);
-- istnieją dane dłuższe niż 10 znaków
SELECT '' AS Zadanie_12 FROM DUAL;
DELETE FROM PROJEKTY WHERE OPIS_PROJEKTU = 'Sieci kręgosłupowe';
-- naruszenie integralności referencyjnej
SELECT '' AS Zadanie_13 FROM DUAL;
ALTER TABLE PRZYDZIALY DROP CONSTRAINT FK_PRZYDZIALY_01;
ALTER TABLE PRZYDZIALY ADD CONSTRAINT FK_PRZYDZIALY_01
FOREIGN KEY (ID_PROJEKTU)
REFERENCES PROJEKTY(ID_PROJEKTU)
ON DELETE CASCADE;
DELETE FROM PROJEKTY WHERE OPIS_PROJEKTU = 'Sieci kręgosłupowe';
SELECT * FROM PROJEKTY;
SELECT * FROM PRZYDZIALY;
SELECT '' AS Zadanie_14 FROM DUAL;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'PRZYDZIALY';
SELECT '' AS Zadanie_15 FROM DUAL;
DROP TABLE PROJEKTY CASCADE CONSTRAINTS;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'PRZYDZIALY';
SELECT '' AS Zadanie_16 FROM DUAL;
DROP TABLE PRZYDZIALY;
DROP TABLE PROJEKTY_KOPIA;
SELECT TABLE_NAME FROM USER_TABLES;