/* logowanie
** cisco AnyConnect instalowany z vpn.ee.pw.edu.pl
** uruchamiamy go i logujemy siÄ™ do vpn.ee.pw.edu.pl
** a z sieci uczelnianej po prostu:
** SQL Autentication
server: sql.iem.pw.edu.pl
user: B4_NumerAlbumu
hasło: B4_NumerAlbumu
*/
--create database bd_24
/* moja lokalna baza nalokalnym serwerze
*/
/*
skrypt ciÄ…g polecen SQL oddzielonych GO
zmienne istniejÄ… do najbliĹĽszego GO
lub do konca skryptu (w niektorych MSSQL nie wolnu uzywac słowa powyzej
w komentarzach.
Prawidłowy skrypt mozna uruchamiac wiele razy (bez błedu)
- kasujecie wszystkie tabele (jak istnieją) w odpowiedniej kolejnościu
- tworzycie od nowa w odpowiedniej kolejnosci
- tworzenie tabel oddzielacie GO
- potem jeden duzy skrypt z wstawianiem rekordow do tabel
1 cwiczenie to bedzie skrypt
1.a tworzacy 5 tabel: WOJ, MIASTA, OSOBY, FIRMY, ETATY
1.b insertem wstawiamy rekordy do tabel
UWAGA potrzebne bedÄ… zmienne do przechowywania ID_MIAST i ID_OSOBY
klucze tekstowe nadawane przez Panstwa to
KOD_WOJ(WOJ), NAZWA_SKR(FIRMY)
przykłądowo jak prowadzący powie:
dodac 3 wojewĂłdztwa z tego jeno nie ma miast
to oznacza
3 inserty do WOJ
a do miast wtawiacie rekordy bez uzywania jednego z 3 kodow
jesli wymaganie bedzie wstawic 10 osob z czego 2 nigdy nie pracowały
to te 2 nie bedą mieć zadnego rekordu w tabeli ETATY
jesli wymaganie bedzie wstawic 12 osob a 3 AKTUALNIE NIE PRACUJÄ„
to oznacza ze 9 ma rekordy w tabeli ETATY z kolumna DO z wartością NULL
(czyli aktualnie pracujÄ…)
UWAGA !!! ten skrypt uruchamiacie raz
W kolejnych cwiczeniach zakladamy ze dane istniejÄ… i tylko robicie zapytaniua
UWAGA !!!
A 3 osoby majÄ… dowlnÄ… liczbÄ™ ETATY ale WSZYSTKIE w kolumnie DO majÄ… NOT NULL
(jakÄ…Ĺ› date)
Przyklad 3
wstawic 10 miast z tego w 2 nikt nie mieszka
MIESZKA - czyli w tabeli OSOBY sÄ… osoby z ID_MIASTA (tych miast)
wystarczy aby nie było zadnej osoby w tabeli OSOBY która mieszka w jednym
a tych dwu
prosba aby kolumny w tabelach i tabele nazwac tak jak na diagramie w chmurze
bez kolumny IMIE_I_NAZWISKO w tabeli OSOBY (tę kolumnę proszę zignorować)
WYMAGANIA podane w grupie lab przez prowadzÄ…cego to tzw. MINIMUM
Czyli polecenie dodaj 10 osĂłb oznacza MINIMUM 10
*/
/*
use bd_24
*/
go
/* tabele tworzymy w kolejności:
** WOJ
** MIASTA
** OSOBY
** FIRMY
** ETATY
**
** Kasujemy w odwrotnej kolejności
ETATY
FIRMY
OSOBY
MIASTA
WOJ
** Nie można skasować WOJ jak jest tabela MIASTA
**/
IF OBJECT_ID('ETATY') IS NOT NULL
BEGIN
DROP TABLE ETATY
END
GO
IF OBJECT_ID('FIRMY') IS NOT NULL
BEGIN
DROP TABLE FIRMY
END
GO
IF OBJECT_ID('OSOBY') IS NOT NULL
BEGIN
DROP TABLE OSOBY
END
GO
IF OBJECT_ID('MIASTA') IS NOT NULL
BEGIN
DROP TABLE MIASTA
END
GO
IF OBJECT_ID('WOJ') IS NOT NULL
BEGIN
DROP TABLE WOJ
END
GO
/* Polecenie tworzenia tabeli
create table user_bazy.nazwa_tabeli (kol1 typ, null/not null
[,koln typ null/not/ null])
*/
CREATE TABLE dbo.WOJ
( KOD_WOJ NCHAR(10) NOT NULL CONSTRAINT PK_WOJ PRIMARY KEY
, NAZWA NVARCHAR(100) NOT NULL
)
GO
GO
CREATE TABLE dbo.MIASTA
(
ID_MIASTA INT NOT NULL IDENTITY,
KOD_WOJ NCHAR(10) NOT NULL,
NAZWA NVARCHAR(100) NOT NULL,
CONSTRAINT PK_MIASTA PRIMARY KEY (ID_MIASTA),
CONSTRAINT FK_WOJ__MIASTA FOREIGN KEY (KOD_WOJ) REFERENCES WOJ(KOD_WOJ)
);
CREATE TABLE dbo.OSOBY
(
ID_OSOBY INT NOT NULL IDENTITY,
ID_MIASTA INT NOT NULL,
[imiÄ™] NVARCHAR(100) NOT NULL,
nazwisko NVARCHAR(100) NOT NULL,
CONSTRAINT PK_OSOBY PRIMARY KEY (ID_OSOBY),
CONSTRAINT FK_OSOBY__MIASTA FOREIGN KEY (ID_MIASTA) REFERENCES MIASTA(ID_MIASTA)
);
CREATE TABLE dbo.FIRMY
( NAZWA_SKR NCHAR(10) NOT NULL
CONSTRAINT PK_FIR PRIMARY KEY,
ID_MIASTA INT NOT NULL CONSTRAINT FK_FIRMY__MIASTA
FOREIGN KEY REFERENCES MIASTA(ID_MIASTA)
, NAZWA nvarchar(100) NOT NULL
, KOD_POCZTOWY nvarchar(100) NOT NULL
, ULICA nvarchar(100) NOT NULL
)
CREATE TABLE dbo.ETATY
(
ID_ETATU INT NOT NULL IDENTITY
CONSTRAINT PK_ETATY PRIMARY KEY
, ID_OSOBY INT NOT NULL CONSTRAINT FK_ETATY_OSOBY
FOREIGN KEY REFERENCES OSOBY(ID_OSOBY)
, ID_FIRMY NCHAR(10) NOT NULL CONSTRAINT FK_ETATY_FIRMY
FOREIGN KEY REFERENCES FIRMY(NAZWA_SKR)
, STANOWISKO NVARCHAR(100) NOT NULL
, PENSJA INT NOT NULL
, OD DATETIME NOT NULL
, DO DATETIME NOT NULL
)
GO
/*
wstawianie
INSERT INTO NazwaTabeli (lista_kol) VALUES (lista_wa) */
INSERT INTO WOJ (KOD_WOJ, NAZWA) VALUES (N'MAZ',N'MAZOWIECKIE')
INSERT INTO WOJ (KOD_WOJ, NAZWA) VALUES (N'POM',N'POMORSKIE')
INSERT INTO WOJ (KOD_WOJ, NAZWA) VALUES (N'POD',N'PODLASKIE')
INSERT INTO WOJ (KOD_WOJ, NAZWA) VALUES (N'OPO',N'OPOLSKIE')
INSERT INTO WOJ (KOD_WOJ, NAZWA) VALUES (N'WIE',N'WIELKOPOLSKIE')
/* UWAGA !!!
** Zmienne systemowe @@nazwa_zm
** Zmienne lokalne usera @nazwa_zmiennej
** po słowie GO zmienne
** przestają istnieć
** dlatego - najpierw kasujemy tabele
** potem tworzymy na nowo
** potem w jednej sekcji wypełniamy danymi
*/
DECLARE @id_wes int, @id_wa int, @id_rad int, @id_leg int, @id_bia int, @id_sok int, @id_suw int, @id_gda int, @id_gdy int, @id_sop int, @id_op int, @id_str int, @id_pkrz int
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'MAZ', N'WESOĹA')
SET @id_wes = SCOPE_IDENTITY() /* funkcja zwraca jakie IDENTITY było przydzielone */
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'MAZ', N'WARSZAWA')
SET @id_wa = SCOPE_IDENTITY()
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'MAZ', N'RADZYMIN')
SET @id_rad = SCOPE_IDENTITY()
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'MAZ', N'LEGIONOWO')
SET @id_leg = SCOPE_IDENTITY()
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'POD', N'BIALYSTOK')
SET @id_bia = SCOPE_IDENTITY()
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'POD', N'SOKOLKA')
SET @id_sok = SCOPE_IDENTITY()
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'POD', N'SUWALKI')
SET @id_suw = SCOPE_IDENTITY()
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'POM', N'GDANSK')
SET @id_gda = SCOPE_IDENTITY()
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'POM', N'GDYNIA')
SET @id_gdy = SCOPE_IDENTITY()
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'POM', N'SOPOT')
SET @id_sop = SCOPE_IDENTITY()
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'OPO', N'OPOLE')
SET @id_op = SCOPE_IDENTITY()
INSERT INTO MIASTA (KOD_WOJ, NAZWA) VALUES (N'OPO', N'STRZELECZKI')
SET @id_str = SCOPE_IDENTITY()
--SELECT @id_wes AS wesola, @id_wa AS wwwa
DECLARE @id_os1 int, @id_os2 int, @id_os3 int, @id_os4 int, @id_os5 int, @id_os6 int, @id_os7 int, @id_os8 int, @id_os9 int, @id_os10 int, @id_os11 int, @id_os12 int, @id_os13 int, @id_os14 int
INSERT INTO FIRMY (NAZWA_SKR, ID_MIASTA, NAZWA, KOD_POCZTOWY, ULICA)
VALUES (N'BD', @id_str, N'BUDEXPOL', N'15-305', N'SLIWKOWA');
INSERT INTO FIRMY (NAZWA_SKR, ID_MIASTA, NAZWA, KOD_POCZTOWY, ULICA)
VALUES (N'PL', @id_op, N'POLEX', N'15-304', N'GRUSZKOWA');
INSERT INTO FIRMY (NAZWA_SKR, ID_MIASTA, NAZWA, KOD_POCZTOWY, ULICA)
VALUES (N'SM', @id_rad, N'SOMEX', N'15-312', N'CZERESNIOWA');
INSERT INTO FIRMY (NAZWA_SKR, ID_MIASTA, NAZWA, KOD_POCZTOWY, ULICA)
VALUES (N'CH', @id_leg, N'CHROMEX', N'15-354', N'JABLKOWA');
INSERT INTO FIRMY (NAZWA_SKR, ID_MIASTA, NAZWA, KOD_POCZTOWY, ULICA)
VALUES (N'KR', @id_bia, N'KROMEX', N'15-333', N'POMARANCZOWA');
INSERT INTO FIRMY (NAZWA_SKR, ID_MIASTA, NAZWA, KOD_POCZTOWY, ULICA)
VALUES (N'MN', @id_sok, N'MONEX', N'15-344', N'BANANOWA');
INSERT INTO FIRMY (NAZWA_SKR, ID_MIASTA, NAZWA, KOD_POCZTOWY, ULICA)
VALUES (N'SO', @id_suw, N'SOLEX', N'15-355', N'WINOGRONOWA');
INSERT INTO FIRMY (NAZWA_SKR, ID_MIASTA, NAZWA, KOD_POCZTOWY, ULICA)
VALUES (N'QO', @id_gda, N'QOREX', N'15-375', N'CZYSTA');
INSERT INTO FIRMY (NAZWA_SKR, ID_MIASTA, NAZWA, KOD_POCZTOWY, ULICA)
VALUES (N'WO', @id_gdy, N'WOREX', N'15-365', N'DLUGA');
INSERT INTO FIRMY (NAZWA_SKR, ID_MIASTA, NAZWA, KOD_POCZTOWY, ULICA)
VALUES (N'TO', @id_sop, N'TOREX', N'15-332', N'KROTKA');
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_wes, N'Jonasz', N'Fajny')
SET @id_os1 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_wes, N'Maciej', N'Stodolski')
SET @id_os2 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_wa, N'Jacek', N'Korytkowski')
SET @id_os3 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_wa, N'Placek', N'Gacek')
SET @id_os4 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_rad, N'Maciej', N'Wieteska')
SET @id_os5 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_rad, N'Jedrzej', N'Golaszewski')
SET @id_os6 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_leg, N'Jaroslaw', N'Tusk')
SET @id_os7 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_leg, N'Arek', N'Szybki')
SET @id_os8 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_bia, N'Donald', N'Kaczynski')
SET @id_os9 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_sok, N'Stanislaw', N'Sosabowski')
SET @id_os10 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_suw, N'Czeslaw', N'Zgrajewa')
SET @id_os11 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_gda, N'Mis', N'Uszatek')
SET @id_os12 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_gdy, N'Janusz', N'Kowalski')
SET @id_os13 = SCOPE_IDENTITY()
INSERT INTO OSOBY (id_miasta, [imiÄ™], [nazwisko])
VALUES (@id_sop, N'Miroslaw', N'Zelent')
SET @id_os14 = SCOPE_IDENTITY()
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os1, N'BD', N'Kucharz', N'6200', convert(datetime, '20220506',112), convert(datetime, '20221212',112))
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os1, N'BD', N'Programista', N'12000', convert(datetime, '20220605',112), convert(datetime, '20231212',112))
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os1, N'BD', N'Piekarz', N'4500', convert(datetime, '20120404',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os3, N'CH', N'Cukiernik', N'4726', convert(datetime, '20050714',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os3, N'KR', N'Kierowca', N'6500', convert(datetime, '20240101',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os3, N'KR', N'Kierownik działu', N'8700', convert(datetime, '20230303',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os2, N'MN', N'ScrumMaster', N'15000', convert(datetime, '20150505',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os2, N'PL', N'ProductOwner', N'14540', convert(datetime, '20160405',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os7, N'MN', N'Dyrektor', N'25000', convert(datetime, '19890429',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os8, N'PL', N'Naukowiec', N'17000', convert(datetime, '19991212',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os9, N'BD', N'Stazysta', N'1000', convert(datetime, '20001112',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os10, N'QO', N'Dostawca', N'3400', convert(datetime, '20201202',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os11, N'SM', N'Sprzatacz', N'4000', convert(datetime, '20070805',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os3, N'QO', N'Grafik', N'7600', convert(datetime, '20030412',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os3, N'SO', N'Brygadier', N'10000', convert(datetime, '20130304',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os4, N'SM', N'Koordynator', N'9650', convert(datetime, '20061010',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os5, N'CH', N'Slusarz', N'12450', convert(datetime, '20101210',112), CURRENT_TIMESTAMP)
INSERT INTO ETATY (id_osoby, id_firmy, STANOWISKO, PENSJA, OD, DO) VALUES (@id_os6, N'KR', N'Dekarz', N'7650', convert(datetime, '20090909',112), CURRENT_TIMESTAMP)
SELECT woj.* FROM woj
/*
KOD_WOJ NAZWA
---------- ----------------------------------------------------------------------------------------------------
??? NIEZNANE
MAZ MAZOWIECKIE
POM POMORSKIE
(3 row(s) affected)*/
SELECT miasta.* FROM miasta
/*
ID_MIASTA KOD_WOJ NAZWA
----------- ---------- ----------------------------------------------------------------------------------------------------
1 MAZ WESOĹA
2 MAZ WARSZAWA
(2 row(s) affected)
*/
SELECT OSOBY.* FROM OSOBY
/*
ID_OSOBY ID_MIASTA imiÄ™ nazwisko
----------- ----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 1 Maciej Stodolski
2 2 Jacek Korytkowski
(2 row(s) affected)*/
SELECT FIRMY. * FROM FIRMY
SELECT ETATY. * FROM ETATY