Zadanie nr 6, mysql
Dostęp do bazy danych następuje poprzez program Putty na konto shellowe
213.184.8.199 przez protokół ssh, kodowanie znaków (zakładka translation w putty) UTF-8. Login to pierwsza litera imienia i nazwisko, bez polskich liter, na przykład jroza dla Jana Róży. Domyślne hasło to numer indeksu. Logowanie do wiersza poleceń konsoli mysql za pomocą polecenia mysql –uLOGIN –pHASŁO. Należy pracować na własnej bazie danych o nazwie takiej jak login.
Do wykonania:
Proszę przerobić skrypt tworzące tabele, tak aby dało się bez przeszkód uruchomić skrypt dodający dane do tabel, a następnie wypełnić tabele wspomnianymi danymi:
skrypt tworzący tabele (do zmodyfikowania):
drop table if exists Pracownik;
drop table if exists Student;
drop table if exists Przedmioty;
drop table if exists Oceny;
CREATE TABLE Pracownik(pesel char(11) not null primary key, imie varchar(20) not null, nazwisko varchar(30) not null,
data_ur date, telefon varchar(15), zarobki float(7,2), stanowisko enum('asystent', 'adiunkt'), ulica varchar(30), kod_pocztowy char(6), miasto varchar(20)
) engine = MyISAM;
CREATE TABLE Student(nrindeksu smallint unsigned primary key auto_increment, imie varchar(20) not null, nazwisko varchar(20) not null, adres varchar(50), data_ur date, typ_studiow enum('lic','mgr','inz')) engine = MyISAM;
CREATE TABLE Przedmioty(idprzedmiotu smallint primary key auto_increment, nazwa varchar(20), prowadzacy char(11), rodzaj enum ('lab','cw','wyk'), FOREIGN KEY(prowadzacy) REFERENCES Pracownik(pesel)) engine = MyISAM;
CREATE TABLE Oceny(student smallint unsigned not null, idprzedmiotu smallint not null, data date,
ocena float(2,1), PRIMARY KEY(student,idprzedmiotu), FOREIGN KEY(student) REFERENCES Student(nrindeksu),
FOREIGN KEY(idprzedmiotu) REFERENCES Przedmioty(idprzedmiotu)) engine = MyISAM;
skrypt wypełniający tabele:
INSERT INTO
Pracownik(pesel,imie,nazwisko,data_ur,telefon,zarobki,stanowisko,ulica,kod_pocztowy,miasto,zar obki_dodatkowe,id_pracownika) VALUES
('72031204135', 'Stefan', 'Zeromski', '1972-03-12', '613-432-321',3300,'adiunkt','Kajki 4/5','10- 871','Olsztyn',1210,1),
('74060206641', 'Monika', 'Janicka', '1974-06-02', '512-112-331',3100,'adiunkt','Olsztynska 23','87-100','Torun',1000,2),
('62122211192', 'Jacek', 'Kaczynski', '1962-12-22', '509-278-178',4300,'profesor','Olsztynska 33/44','01-431','Warszawa',0,3),
('32071503445', 'Matylda', 'Bosak', '1932-07-15', '507-499-300',6300,'profesor','Wiazowa 12/5','87-101','Torun',0,4),
('78093004386', 'Julita', 'Kopaczel', '1978-09-30', '606-123-931',1800,'asystent','Wiazowa 66/6','09-520','Ostroda',2130,5),
('58121014665', 'Janina', 'Tanik', '1958-12-10', '601-492-000',4150,'adiunkt','Wiazowa 5/4','10- 564','Olsztyn',0,6),
('72092510277', 'Tadeusz', 'Fraczek', '1972-09-25', '503-726-025',3300,'adiunkt','Matejki 7/62','10-124','Olsztyn',0,7),
('81122710555', 'Gerwazy', 'Flak', '1981-12-27', '609-000-453',1720,'asystent','Mickiewicza 34/2','09-304','Ostroda',100,8),
('34082810135', 'Cezary', 'Dobry', '1934-08-28', '781-411-899',1500,'techniczny','Matejki 1/123','00-100','Warszawa',153,9),
('52041212312', 'Boguslaw', 'Zly', '1952-04-12', '606-333-222',4920,'profesor','Mickiewicza 8/7','87-101','Torun',5000,10),
('59071610171', 'Bronislaw', 'Gierdys', '1959-07-16', '514-675-333',4600,'profesor','Matejki 43','10-129','Olsztyn',3000,11),
('74050303225', 'Karolina', 'Manka', '1974-05-03', '664-432-321',1900,'asystent','Teczowa 2/3','80-508','Gdansk',201,12),
('83090708865', 'Ilona', 'Wegorz', '1983-09-07', '775-929-341',2010,'asystent','Kajki 12','10- 430','Olsztyn',3000,13),
('38070902746', 'Miroslawa', 'Pila', '1938-07-09', '501-565-033',5300,'profesor','Promienista 4/5','00-950','Warszawa',0,14),
('44041410596', 'Pawel', 'Czerwinski', '1944-04-14', '606-897-564',1400,'techniczny','Bolka 5','10- 543','Olsztyn',1000,15),
('58121711134', 'Tomasz', 'Karski', '1958-12-17', '608-965-755',4600,'profesor','Klosowa 43','87- 100','Torun',0,16),
('76032212478', 'Jaroslaw', 'Barski', '1976-03-22', '507-820-198',3100,'adiunkt','Olsztynska 34/2','11-200','Ilawa',304,17),
('49093004149', 'Agnieszka', 'Latecka', '1949-09-30', '608-127-389',4200,'adiunkt','Rolna 123','10-940','Olsztyn',2091,18),
('56022218391', 'Stanislaw', 'Malek', '1956-02-22', '510-445-100',3700,'adiunkt','Kajki 7','11- 930','Ilawa',101,19),
('77092009182', 'Maja', 'Wesola', '1977-09-20', '511-111-000',2700,'asystent','Wiazowa 12','11- 900','Ilawa',1010,20);
INSERT INTO Student(nrindeksu,imie,nazwisko,adres,data_ur,typ_studiow,rok ) VALUES (1, 'Jacek', 'Placek', 'Koswa 1 Olsztyn', '1987-09-09', 'lic', 1),
(2, 'Jan', 'Malarz', 'Ksiezycowa 1 Olsztyn', '1934-01-01', 'lic', 1),
(3, 'Michalina', 'Madej', 'Dworcowa 20 Olsztyn', '1982-02-20', NULL, 4), (4, 'Michal', 'Bosak', 'Targowa 20 Torun', '1980-11-30', 'mgr', 2),
(5, 'Kazimierz', 'Galgan', 'Wiazowa 5/6 Torun', '1986-12-30', 'inz', 1), (6, 'Janina', 'Dabrowska', 'Studniowa 4 Warszawa', '1966-11-10', 'inz', 2), (7, 'Tadeusz', 'Janicki', 'Myszki Miki 15/6 Torun', '1974-10-03', 'lic', 2), (8, 'Wiktoria', 'Kowalska', 'Olsztynska 1/6 Klewki', '1937-02-03', 'mgr', 4), (9, 'Cezary', 'Kowal', 'Ostrodzka 6 Ilawa', '1989-10-10', 'mgr', 5),
(10, 'Anna', 'Grabowska', 'Warszawska 52/16 Olsztyn', '1986-02-20', 'lic', 3), (11, 'Bronislaw', 'Gierdys', 'Owocowa 23/4 Poznan', '1981-02-11', 'mgr', 5),
(12, 'Karolina', 'Michalska', 'Raczki 2/3 Ostroda', '1979-07-03', NULL, 1), (13, 'Iga', 'Ostalowska', 'Jagiellonczyka 5/61 Ilawa', '1966-08-09', 'lic', 2), (14, 'Miroslaw', 'Domian', 'Hallera 5/6 Gdansk', '1971-05-13', NULL, 3), (15, 'Paulina', 'Czerwinska', 'Wiazowa 11/6 Gdynia', '1977-01-30', 'mgr', 5), (16, 'Tomasz', 'Koterski', 'Poznanska 32 Torun', '1986-03-21', NULL, 1),
(17, 'Boguslaw', 'Wisniewski', 'Ogrodowa 1/6 Warszawa', '1975-04-30', 'inz', 1), (18, 'Agnieszka', 'Borowska', 'Promienista 56 Warszawa', '1989-11-16', 'mgr', 2), (19, 'Stanislaw', 'Borkowski', 'Pomologiczna 5/6 Olsztyn', '1956-03-02', 'mgr', 5), (20, 'Maja', 'Drozda', 'Torunska 523 Wroclaw', '1987-08-18', NULL, 3);
INSERT INTO Przedmioty(idprzedmiotu,nazwa,id_pracownika) VALUES (1, 'Algebra liniowa', 1),(12, 'Filofofia', 8),(33, 'Fizyka', 4),
(2, 'Metody probabilistyczne', 3),(13, 'AiSD', 13),(32, 'Podstawy elektroniki', 4), (3, 'Analiza matematyczna', 2),(14, 'Bazy danych', 5),(31, 'Metody symulacji', 3), (4, 'W-F', 12),(15, 'Jezyk angielski', 8),(30, 'Jezyki programowania', 16),
(5, 'Matematyka dyskretna', 13),(16, 'Metody numeryczne', 20),(29, 'Programowanie stron WWW', 19),
(6, 'Statystyka', 19),(17, 'Inzynieria oprogramowania', 16),(28, 'Bezpieczenstwo sieci', 11), (7, 'Systemy mikroprocesorowe', 3),(18, 'Programowanie obiektowe', 10),(27, 'Programowanie strukturalne', 10),
(8, 'Sztuczna inteligencja', 2),(19, 'Administracja sieciami', 7),(26, 'Systemy rozproszone', 18), (9, 'Seminarium', 1),(20, 'Wyklad monograficzny', 6),(25, 'Matematyka finansowa', 17), (10, 'Logika matematyczna', 12),(21, 'Lingwistyka matematyczna', 16),(24, 'PTO', 15), (11, 'Psychologia', 6),(22, 'Prawo', 6),(23, 'Algebra I', 17);
INSERT INTO Oceny(student,idprzedmiotu,data,ocena) VALUES
(1,1,'2008-01-15',3),(1,2,'2008-02-14',4),(1,4,'2008-02-23',4),(1,5,'2008-01-23',5), (1,3,'2008-01-16',2),(2,1,'2008-02-13',3),(2,3,'2008-02-23',3.5),(2,5,'2008-02-24',2), (2,8,'2008-01-17',5),(2,10,'2008-02-12',3.5),(2,21,'2008-02-23',5),(2,23,'2008-01-25',2), (2,25,'2008-02-18',4.5),(2,27,'2008-02-11',5),(3,2,'2008-02-23',2),(3,3,'2008-01-26',3), (3,5,'2008-03-19',2),(3,6,'2008-02-10',3.5),(3,8,'2008-02-23',3.5),(3,9,'2008-02-27',2), (3,15,'2008-03-20',3),(3,20,'2008-02-09',2),(3,21,'2008-02-23',2),(4,4,'2008-02-28',5), (4,5,'2008-02-21',4.5),(4,25,'2008-01-08',5),(4,33,'2008-02-23',4),(5,10,'2008-02-29',4.5), (6,12,'2008-01-22',3.5),(6,17,'2008-01-07',3),(6,25,'2008-02-23',2),(6,31,'2008-03-28',4.5), (6,32,'2008-02-23',2),(6,33,'2008-01-08',2),(7,21,'2008-02-23',3.5),(7,22,'2008-01-19',3.5), (7,23,'2008-01-24',3),(7,24,'2008-01-09',3),(9,25,'2008-02-23',2),(9,26,'2008-01-18',5), (9,27,'2008-02-25',3),(9,28,'2008-01-10',2),(9,29,'2008-02-23',2),(9,30,'2008-02-17',4), (9,31,'2008-02-26',5),(9,10,'2008-01-11',4),(9,11,'2008-02-23',3.5),(10,1,'2008-01-16',2), (10,2,'2008-01-27',3),(10,4,'2008-01-12',3.5),(10,5,'2008-02-23',2),(10,7,'2008-02-15',5), (10,15,'2008-01-28',2),(11,2,'2008-03-13',3.5),(11,3,'2008-02-23',4.5),(11,4,'2008-03-14',3.5), (11,8,'2008-01-22',2),(11,9,'2008-03-31',2),(13,11,'2008-02-23',2),(13,12,'2008-03-13',3), (13,13,'2008-02-21',2),(13,14,'2008-03-14',4),(13,15,'2008-02-23',3),(14,22,'2008-03-12',2), (14,23,'2008-01-07',3.5),(14,24,'2008-03-15',4.5),(14,26,'2008-02-23',3.5),(15,1,'2008-03-11',2), (15,2,'2008-01-05',5),(15,4,'2008-03-16',4.5),(15,6,'2008-02-23',4),(15,8,'2008-02-10',3.5), (15,10,'2008-03-10',3),(15,11,'2008-03-17',2),(15,13,'2008-02-23',2),(15,17,'2008-01-09',2), (16,13,'2008-03-18',3),(17,17,'2008-01-18',2),(19,11,'2008-02-23',3),(19,1,'2008-02-08',3.5), (19,3,'2008-03-12',3.5),(19,5,'2008-01-19',2),(19,31,'2008-02-23',3),(19,32,'2008-01-07',3), (20,33,'2008-01-12',2),(20,32,'2008-01-20',3.5),(20,21,'2008-02-23',4.5),(20,31,'2008-02-23',5), (20,30,'2008-02-03',4),(20,28,'2008-01-21',3.5),(20,27,'2008-02-23',3.5),(20,2,'2008-01-22',4.5);
Proszę stworzyć perspektywy:
1. Wyświetlić pracowników ze stanowiskiem ’adiunkt’
2. Wyświetlić numery indeksu i oceny z przedmiotów o indeksie 1,3,5 (zastosować IN) 3. Wyświetlić pracowników, którzy nie są z Olsztyna
4. Wyświetlić dane pracowników zarabiających (biorąc pod uwagę zarobki dodatkowe) co najmniej 5000 zł.
5. Wyświetlić Imię i Nazwisko studentów od 3 do 5 roku których imię kończy się na ’slaw’
6. Wyświetli pracowników, którzy urodzili się przed wojną (na podstawie Pesel)
7. Wyświetlić wszystkie studentki, które mają zdefiniowany typ studiów, od najstarszej do najmłodszej
8. Wyświetlić 10 najstarszych pracowników od najstarszego do najmłodszego 9. Wyświetlić nazwy przedmiotów zawierających w nazwie ’an’, w kolejności
alfabetycznej
Przydatne funkcje i polecenia:
ISNULL(), SUBSTR(źr,poz,dł), LIMIT
