1
Uczelnia
Jakub Stefko edited this page 2017-11-16 01:27:21 +00:00
--drop DATABASE Projekty
--GO
--CREATE DATABASE Projekty
--GO
USE dbad_s426254
GO
SET LANGUAGE polski
GO
-------- USUŃ TABELE
IF OBJECT_ID('Realizacje', 'U') IS NOT NULL
DROP TABLE Realizacje;
IF OBJECT_ID('Projekty', 'U') IS NOT NULL
DROP TABLE Projekty;
IF OBJECT_ID('Pracownicy', 'U') IS NOT NULL
DROP TABLE Pracownicy;
IF OBJECT_ID('Stanowiska', 'U') IS NOT NULL
DROP TABLE Stanowiska;
--------- CREATE - UTWÓRZ TABELE I POWIĄZANIA
CREATE TABLE Stanowiska(
nazwa VARCHAR(10) PRIMARY KEY,
placa_min MONEY,
placa_max MONEY,
CHECK (placa_min < placa_max)
);
CREATE TABLE Pracownicy(
id INT NOT NULL PRIMARY KEY,
nazwisko VARCHAR(20) NOT NULL,
szef INT REFERENCES Pracownicy(id),
placa MONEY,
dod_funkc MONEY,
stanowisko VARCHAR(10) REFERENCES Stanowiska(nazwa),
zatrudniony DATETIME
);
CREATE TABLE Projekty(
id INT IDENTITY(10,10) NOT NULL PRIMARY KEY,
nazwa VARCHAR(20) NOT NULL UNIQUE,
dataRozp DATETIME NOT NULL,
dataZakonczPlan DATETIME NOT NULL,
dataZakonczFakt DATETIME NULL,
kierownik INT REFERENCES Pracownicy(id),
stawka MONEY
);
CREATE TABLE Realizacje(
idProj INT REFERENCES Projekty(id),
idPrac INT REFERENCES Pracownicy(id),
godzin REAL DEFAULT 8
);
GO
---------- INSERT - WSTAW DANE
INSERT INTO Stanowiska VALUES ('profesor', 3000, 5000);
INSERT INTO Stanowiska VALUES ('adiunkt', 2000, 3000);
INSERT INTO Stanowiska VALUES ('doktorant', 900, 1300);
INSERT INTO Stanowiska VALUES ('sekretarka', 1500, 2500);
INSERT INTO Stanowiska VALUES ('techniczny', 1500, 2500);
INSERT INTO Stanowiska VALUES ('dziekan', 2700, 4800);
INSERT INTO Pracownicy VALUES (1, 'Wachowiak', NULL, 4500, 900, 'profesor', '01-09-1980');
INSERT INTO Pracownicy VALUES (2, 'Jankowski', 1, 2500, NULL, 'adiunkt', '01-09-1990');
INSERT INTO Pracownicy VALUES (3, 'Fiołkowska', 1, 2550, NULL, 'adiunkt', '01-01-1985');
INSERT INTO Pracownicy VALUES (4, 'Mielcarz', 1, 4000, 400, 'profesor', '01-12-1980');
INSERT INTO Pracownicy VALUES (5, 'Różycka', 4, 2800, 200, 'profesor', '01-09-2001');
INSERT INTO Pracownicy VALUES (6, 'Mikołajski', 4, 1000, NULL, 'doktorant', '01-10-2002');
INSERT INTO Pracownicy VALUES (7, 'Wójcicki', 5, 1350, NULL, 'doktorant', '01-10-2003');
INSERT INTO Pracownicy VALUES (8, 'Listkiewicz', 1, 2200, NULL, 'sekretarka', '01-09-1980');
INSERT INTO Pracownicy VALUES (9, 'Wróbel', 1, 1900, 300, 'techniczny', '01-01-1999');
INSERT INTO Pracownicy VALUES (10, 'Andrzejewicz', 5, 2900, NULL, 'adiunkt', '01-01-2002');
INSERT INTO Projekty VALUES ('e-learning', '01-01-2015', '31-05-2016', NULL, 5, 100);
INSERT INTO Projekty VALUES ('web service', '10-11-2009', '31-12-2010', '20-04-2011', 4, 90);
INSERT INTO Projekty VALUES ('semantic web', '01-09-2017', '01-09-2019', NULL, 4, 85);
INSERT INTO Projekty VALUES ('neural network', '01-01-2008', '30-06-2010', '30-06-2010', 1, 120);
INSERT INTO Realizacje VALUES (10, 5, 8);
INSERT INTO Realizacje VALUES (10, 10, 6);
INSERT INTO Realizacje VALUES (10, 9, 2);
INSERT INTO Realizacje VALUES (20, 4, 8);
INSERT INTO Realizacje VALUES (20, 6, 8);
INSERT INTO Realizacje VALUES (20, 9, 2);
INSERT INTO Realizacje VALUES (30, 4, 8);
INSERT INTO Realizacje VALUES (30, 6, 6);
INSERT INTO Realizacje VALUES (30, 10, 6);
INSERT INTO Realizacje VALUES (30, 9, 2);
INSERT INTO Realizacje VALUES (40, 1, 8);
INSERT INTO Realizacje VALUES (40, 2, 4);
INSERT INTO Realizacje VALUES (40, 3, 4);
INSERT INTO Realizacje VALUES (40, 9, 2);
------------ SELECT
SELECT * FROM Stanowiska;
SELECT * FROM Pracownicy;
SELECT * FROM Projekty;
SELECT * FROM Realizacje;
--1.1 Dla każdego pracownika podaj jego nazwisko i płacę roczną, tzn. (placa + dodatki) * 12
SELECT nazwisko, (placa+ISNULL(dod_funkc,0))*12 'placa roczna' FROM Pracownicy;
--1.2 Znajdź najnowszy projekt.
SELECT * FROM Projekty WHERE dataRozp =
(SELECT MAX(dataRozp) FROM Projekty);
--1.3 Dla każdego projektu podaj jego nazwę i czas trwania (w miesiącach - wykorzystaj DATEDIFF; jeżeli projekt nadal trwa, to należy porównać z dzisiejszą datą)
SELECT nazwa, (DATEDIFF(month,dataRozp,ISNULL(dataZakonczFakt,GETDATE()))) 'sthing' FROM Projekty;
--1.4 Podaj informację o pracownikach zatrudnionych na stanowisku adiunkt lub doktorant i zarabiających powyżej 1500 zł
SELECT * FROM Pracownicy WHERE stanowisko='adiunkt' AND placa > 1500;
--1.5 Podaj nazwiska pracowników którzy nie mają szefa
SELECT nazwisko FROM Pracownicy WHERE szef IS NULL;
--1.6 Znajdź projekty zawierające web w nazwie
SELECT * FROM Projekty WHERE nazwa Like '%web%';
--2.1 Dla każdego pracownika podaj jego nazwisko, płacę, nazwę stanowiska oraz widełki płacowe na jego stanowisku
SELECT A.nazwisko, A.stanowisko, A.placa, B.placa_min, B.placa_max FROM Pracownicy A
INNER JOIN Stanowiska B ON A.stanowisko=B.nazwa;
--2.2 Znajdź doktorantów, których płaca nie mieści się w widełkach płacowych dla doktoranta
SELECT * FROM Pracownicy A
INNER JOIN Stanowiska B ON A.stanowisko=B.nazwa
WHERE (placa < placa_min OR placa > placa_max)
AND stanowisko = 'doktorant';
--2.3 Dla każdego pracownika podaj nazwy projektów w których pracuje; posortuj alfabetycznie po nazwisku
SELECT A.nazwisko, C.nazwa FROM Pracownicy A
INNER JOIN Realizacje B ON A.id=B.idPrac
INNER JOIN Projekty C ON C.id=B.idProj
ORDER BY nazwisko;
--2.4 Dla każdego pracownika podaj jego nazwisko oraz nazwisko jego szefa
SELECT A.nazwisko, B.nazwisko FROM Pracownicy A
JOIN Pracownicy B ON A.szef=B.id;
--2.5 Zmodyfikuj zad 2.4. aby na liście pracowników uwzględnić również Wachowiaka
SELECT A.nazwisko, B.nazwisko FROM Pracownicy A
LEFT OUTER JOIN Pracownicy B ON A.szef=B.id;
--2.6 Podaj nazwiska pracowników, którzy nie kierują żadnym projektem
SELECT A.nazwisko FROM Pracownicy A
LEFT OUTER JOIN Projekty B ON A.id=B.kierownik
WHERE kierownik IS NULL;
--2.7 Podaj nazwiska pracowników, którzy nie są zatrudnieni w projekcie nr 10
SELECT nazwisko FROM Pracownicy A
WHERE id NOT IN (SELECT idPrac FROM Realizacje WHERE
idProj=10);
--2.8 Sprawdź, czy w bazie znajdują się pracownicy o tym samym nazwisku
SELECT DISTINCT A.nazwisko, B.nazwisko FROM Pracownicy A
JOIN Pracownicy B ON A.nazwisko = B.nazwisko
WHERE A.id != B.id;
--2.9 Zweryfikuj, czy każdy pracownik kierujący projektem jest również pracownikiem tego projektu (wpisanym do tabeli Realizacje). Zapytanie ma zwracać pracowników nie spełniających tego warunku
SELECT * FROM Pracownicy A
INNER JOIN Realizacje B ON A.id=B.idPrac
INNER JOIN Projekty C ON A.id=C.kierownik
WHERE id
--WHERE (SELECT D.id FROM Pracownicy D WHERE D.id=A.id) IN
(SELECT E.id FROM Pracownicy E JOIN
Realizacje F ON E.id=F.idPrac
WHERE F.idProj = B.idProj);
SELECT * FROM Stanowiska;
SELECT * FROM Pracownicy;
SELECT * FROM Projekty;
SELECT * FROM Realizacje;
---4.10 ???
SELECT nazwisko FROM Pracownicy
EXCEPT
SELECT nazwisko FROM Pracownicy P
JOIN Projekty R ON P.id = R.kierownik;
---
--- 4.12 ???
SELECT p.nazwisko, (SELECT
SUM(R.godzin * Pr.stawka * DATEDIFF(week, dataRozp, ISNULL(dataZakonczFakt, GETDATE())))
FROM Realizacje R, Projekty Pr
WHERE R.idPrac = P.id AND R.idProj = Pr.id) as 'Zarobki'
FROM Pracownicy P
WHERE (SELECT
SUM(R.godzin * Pr.stawka * DATEDIFF(week, dataRozp, ISNULL(dataZakonczFakt, GETDATE())))
FROM Realizacje R, Projekty Pr
WHERE R.idPrac = P.id AND R.idProj = Pr.id) IS NOT NULL
ORDER BY 'Zarobki' DESC;
---
CREATE VIEW AktualneProjekty2(nazwa, kierownik, licz_pracownikow)
AS
(
SELECT nazwa,
(SELECT nazwisko FROM Pracownicy P WHERE Pr.kierownik = P.id),
(SELECT COUNT (*) FROM Realizacje R WHERE Pr.id = R.idProj)
FROM Projekty Pr
WHERE dataZakonczFakt IS NULL
);
GO
SELECT * FROM AktualneProjekty2;
SELECT nazwa, licz_pracownikow
FROM AktualneProjekty2
GROUP BY nazwa, licz_pracownikow
HAVING licz_pracownikow >= ALL
(SELECT licz_pracownikow FROM AktualneProjekty2
GROUP BY licz_pracownikow);