Przykłady z wykładów - MySQL

Przykład 1.1

Wyświetl zawartość tablicy PRAC.

SELECT  * FROM  PRAC  ;

Przykład 1.2

Sporządź listę pracowników zatrudnionych w firmie (lista zawiera: numer pracownika , nazwisko pracownika , imię pracownika).

SELECT  NumP , NazwP , ImieP FROM  PRAC  ;

Przykład 1.3

Oblicz przychody pracowników w roku podatkowym (lista zawiera: numer, imię i nazwisko pracownika, jego pobory miesięczne i prowizję roczną oraz przychody roczne).

SELECT  NumP , ImieP , NazwP , Zasad , Prow , 12*Zasad+Prow

FROM  PRAC  ;

Przykład 1.4

Podaj pobory pracowników porządkując listę alfabetycznie wg. nazwisk pracowników (lista zawiera: imię i nazwisko pracownika oraz jego pobory miesięczne).

SELECT  ImieP , NazwP , Zasad FROM  PRAC ORDER BY NazwP  ;

Przykład 1.5

Podaj uporządkowaną listę stanowisk, na których można zatrudnić w firmie pracowników:

a). z powtórzeniami

SELECT Stan FROM  PRAC ORDER BY Stan  ;

b). bez powtórzeń

SELECT DISTINCT  Stan FROM  PRAC ORDER BY Stan  ;

Przykład 1.6

Podaj najniższe, najwyższe i średnie pobory pracowników firmy oraz odpowiedz, jaki procent najwyższych poborów stanowią pobory najniższe:

a). bez przypisania etykiet nagłówkom tabeli wynikowej

SELECT  MIN( Zasad ) , MAX( Zasad ) , AVG( Zasad ) , MIN( Zasad ) / MAX( Zasad ) * 100.00

FROM  PRAC  ;

b). z przypisaniem etykiet nagłówkom tabeli wynikowej

SELECT  MIN( Zasad ) AS Najnizsze , MAX( Zasad ) AS Najwyzsze ,

AVG( Zasad ) AS Srednie , MIN( Zasad ) / MAX( Zasad ) * 100.00 AS MinDoMax

FROM  PRAC  ;

Przykład 2.1

Zdefiniuj tablicę pracowników PRAC.

CREATE TABLE  PRAC

( NumP  INT(4)  KEY NOT NULL , NazwP  CHAR(20)  NOT NULL ,

ImieP  CHAR(15)  NOT NULL , Stan CHAR(10) , Kier  INT(4) ,

DZatrud  DATE  NOT NULL , Zasad  DECIMAL(7,2) ,

Prow  DECIMAL(7,2) , NumDz  INT(2)  )  ;

Przykład 2.2

Zdefiniuj tablicę działów w firmie DZIAL.

CREATE TABLE   DZIAL

( NumDz  INT(2)  KEY NOT NULL  , NazwaDz  CHAR(15)  NOT NULL  , Lok  CHAR(10)  )  ;

Przykład 2.3

Do tablicy DZIAL dodaj kolejną kolumnę, w której będzie zapisany numer telefonu.

ALTER TABLE   DZIAL

ADD ( Tel  CHAR(14)  )  ;

Przykład 3.1

Podaj listę osób zatrudnionych w dziale oznaczonym numerem 10, uporządkuj listę alfabetycznie wg. nazwisk pracowników.

SELECT  NumP , NazwP , ImieP , Zasad , NumDz , Stan

FROM  PRAC WHERE NumDz = 10 ORDER BY NazwP ASC;

Przykład 3.2

Podaj listę zatrudnionych w firmie osób, które zarabiają powyżej 1750 €, uporządkuj wynik począwszy od osób najlepiej zarabiających.

SELECT  NumP , NazwP , ImieP , Zasad FROM  PRAC

WHERE Zasad > 1750 ORDER BY Zasad DESC ;

Przykład 3.3

Podaj listę zatrudnionych w firmie osób, których zarobki mieszczą się w przedziale od 1750 € do 3000 € włącznie (zastosuj warunek wielokrotny). Uporządkuj listę wg. numerów pracowników rosnąco.

SELECT  NumP , ImieP , NazwP , Zasad FROM  PRAC

WHERE Zasad >= 1750 AND Zasad <= 3000 ORDER BY NumP ASC ;

Przykład 3.4

Podaj listę zatrudnionych w firmie osób, których zarobki mieszczą się w przedziale od 1750 € do 3000 € włącznie (zastosuj warunek zakresowy). Uporządkuj listę wg. numerów pracowników rosnąco.

SELECT  NumP , ImieP , NazwP , Zasad FROM  PRAC

WHERE Zasad BETWEEN 1750 AND 3000 ORDER BY NumP ASC ;

Przykład 3.5

Przygotuj listę agentów z proponowanymi poborami zasadniczymi wyższymi o 35% od dotychczas otrzymywanych. Uporządkuj listę alfabetycznie wg. nazwisk.

SELECT  NazwP , ImieP , Zasad AS P_teraz , Zasad*1.35 AS P_nowe , Stan

FROM  PRAC WHERE Stan = ”AGENT” ORDER BY NazwP ASC ;

Przykład 3.6

Przygotuj listę analityków i informatyków z proponowanymi poborami zasadniczymi wyższymi o 35% od dotychczas otrzymywanych (zastosuj warunek złożony - alternatywny). Uporządkuj listę alfabetycznie wg. nazwisk i imion.

SELECT  NazwP , ImieP , Zasad AS P_teraz , Zasad*1.35 AS P_nowe , Stan

FROM  PRAC WHERE Stan = ”INFORMATYK” OR Stan = ”ANALITYK”

ORDER BY NazwP ASC , ImieP DESC ;

Przykład 3.7

Przygotuj listę analityków i informatyków z proponowanymi poborami zasadniczymi wyższymi o 35% od dotychczas otrzymywanych (zastosuj warunek wykorzystujący porównanie ze zbiorem wartości). Uporządkuj listę alfabetycznie wg. nazwisk.

SELECT  NazwP , ImieP , Zasad AS P_teraz , Zasad*1.35 AS P_nowe , Stan

FROM  PRAC WHERE Stan IN ( ”INFORMATYK” , ”ANALITYK” )

ORDER BY NazwP ASC ;

Przykład 3.8

Podaj listę osób, których imiona zaczynają się na literę J. Uporządkuj listę alfabetycznie wg. imion.

SELECT  ImieP , NazwP FROM  PRAC

WHERE ImieP LIKE ”J%” ORDER BY ImieP ASC ;

Przykład 3.9

Podaj listę osób, których druga litera imienia to litera W. Uporządkuj listę alfabetycznie wg. imion.

SELECT  ImieP , NazwP FROM  PRAC WHERE ImieP LIKE ”_W%” ORDER BY ImieP ASC ;

Przykład 3.10

Podaj listę pracowników, którzy nie mają ustalonych przełożonych (nieustalone wartości pola kolumny Kier).

SELECT   NazwP , ImieP , Stan FROM  PRAC WHERE  Kier  IS  NULL  ;

Przykład 3.11

Podaj uporządkowaną malejąco według prowizji listę tych pracowników (w porządku alfabetycznym), którzy wypracowali w bieżącym roku prowizję.

SELECT   NazwP , ImieP , Prow FROM  PRAC

WHERE  Prow  IS  NOT NULL ORDER BY Prow DESC , NazwP ASC ;

Przykład 4.1

Oblicz średnie pobory zasadnicze dla wszystkich stanowisk pracy w firmie wyłączając osoby zatrudnione na stanowisku PREZES. Podaj również liczbę osób zatrudnionych na tych stanowiskach.

SELECT  Stan , AVG( Zasad ) AS ”Śr. pobory” , COUNT( * )  AS ”liczba pracowników”

FROM  PRAC WHERE  Stan  <> ”PREZES”

GROUP BY  Stan ORDER BY Stan ;

Przykład 4.2

Podaj listę tych stanowisk pracy w firmie (wyłączając z listy stanowisko PREZES), dla których średnie pobory zasadnicze są wyższe niż 2.500,00 €.

SELECT  Stan , AVG( Zasad ) AS ”Śr. pobory” , COUNT( * )  AS ”liczba pracowników”

FROM  PRAC WHERE  Stan  <> ”PREZES”

GROUP BY  Stan HAVING  AVG( Zasad )  >  2500

ORDER BY Stan ;

Przykład 5.1

Dołącz do tabeli DZIAL informację o dziale, którego nazwa brzmi SPRZEDAŻ lokalizacja to BYDGOSZCZ, numer telefonu 500-333-444, a nadany numer działu to 50:

a). pełna wersja polecenia (nie wymaga się wiedzy o rzeczywistej kolejności kolumn w tabeli, można również pomijać niektóre pola we wprowadzanym wierszu tabeli)

INSERT  INTO  DZIAL   ( NazwaDz , Lok , Tel , NumDz )

VALUES  ( ”SPRZEDAŻ” , ”BYDGOSZCZ” , ”500333444” , 50 ) ;

b). skrócona wersja polecenia (znamy kolejność kolumn w tabeli, musimy podać wartości dla wszystkich pól wprowadzanego wiersza tabeli, a jeżeli którejś nie znamy a dopuszczalna jest wartość pusta podajemy NULL)

INSERT  INTO  DZIAL  VALUES  ( 50 , ”SPRZEDAŻ” , ”BYDGOSZCZ” , ”500333444” )  ;

Przykład 5.2

Dołącz do tabeli PRAC informację o nowym pracowniku ANDRZEJU PIWNYM zatrudnionym na stanowisku KIEROWNIKA w dniu 18 stycznia 2007 roku w dziale numer 50 z poborami zasadniczymi 2500, któremu przydzielono numer 5965, i którego przełożony ma numer 5516:

a). pełna wersja polecenia (nie wymaga się wiedzy o rzeczywistej kolejności kolumn w tabeli, można również pomijać niektóre pola we wprowadzanym wierszu tabeli)

INSERT  INTO  PRAC  ( ImieP , NazwP , Stan , DZatrud , NumDz , Zasad , NumP , Kier )

VALUES  ( ”ANDRZEJ” , ”PIWNY” , ”KIEROWNIK” , ”18-01-2007” , 50 , 2500 , 5965 , 5516 )  ;

b). skrócona wersja polecenia (znamy kolejność kolumn w tabeli, musimy podać wartości dla wszystkich pól wprowadzanego wiersza tabeli, a jeżeli którejś nie znamy a dopuszczalna jest wartość pusta podajemy NULL)

INSERT  INTO  PRAC   VALUES  (  5965 ,”PIWNY” , ”ANDRZEJ” , ”KIEROWNIK” , 5516  ,
”18-01-2007” , 2500 , NULL , 50  )  ;

Przykład 5.3

W tabeli PRAC zmień pobory zasadnicze i prowizję ZOFII JAWORSKIEJ na wartości: 1520 € i 315 € (pamiętaj, że imię i nazwisko to słabe wyróżniki konkretnej osoby!).

UPDATE  PRAC SET  Zasad  =  1520 , Prow  =  315

WHERE  ImieP  =  ”ZOFIA”  AND  NazwP  =  ”JAWORSKA”  ;

Przykład 5.4

Zwolnij osoby zatrudnione w dziale numer 40 usuwając ich dane z tabeli PRAC.

DELETE FROM  PRAC WHERE  NumDz  =  40  ;

Przykład 6.1

Utwórz listę pracowników, których pobory zasadnicze w firmie są wyższe niż pobory średnie.

SELECT  ImieP , NazwP , Stan , Zasad FROM  PRAC

WHERE  Zasad  > (  SELECT  AVG(Zasad)  FROM  PRAC  )  ;

Przykład 6.2

Utwórz listę pracowników, których pobory zasadnicze w firmie są wyższe niż pobory średnie, ograniczając listę pracowników do tych, którzy nie są zatrudnieni na stanowisku PREZES.

SELECT  ImieP , NazwP , Stan , Zasad FROM  PRAC

WHERE  Zasad  > (  SELECT  AVG(Zasad)  FROM  PRAC  ) 

AND  Stan  !=  `PREZES' ;

Przykład 6.3

Utwórz ograniczoną listę pracowników - bez do tych, którzy są zatrudnieni na stanowisku PREZES - których pobory zasadnicze w firmie są wyższe niż pobory średnie, dbając o to by do średniej nie były liczone pobory zasadnicze tych osób, które są zatrudnione na stanowisku PREZES.

SELECT  ImieP , NazwP , Stan , Zasad FROM  PRAC

WHERE  Zasad  > (  SELECT  AVG(Zasad)  FROM  PRAC  WHERE   Stan  !=  `PREZES' ) 

AND  Stan  !=  `PREZES' ;

Przykład 6.4

Utwórz listę wszystkich pracowników, którzy pracują w działach zlokalizowanych w GDYNI. Uporządkuj wynik rosnąco według numerów działów i alfabetycznie według nazwisk oraz imion pracowników.

SELECT  ImieP , NazwP , NumDz FROM  PRAC

WHERE  NumDz  IN (  SELECT  NumDz  FROM  DZIAL WHERE  Lok = `GDYNIA' ) 

ORDER BY NumDz  ASC , NazwP ASC , ImieP ASC;

Przykład 6.5

Napisz polecenie, które odpowie na pytanie: “Którzy pracownicy z tego samego działu co SAWICZ mają pobory zasadnicze niższe niż pobory średnie? ”

SELECT  NazwP , ImieP , Stan , Zasad , NumDz FROM  PRAC

WHERE  Zasad <  (  SELECT  AVG( Zasad)  FROM  PRAC  )

AND  NumDz  IN  (  SELECT  NumDz   FROM  PRAC WHERE  NazwP  =  `SAWICZ'  )  ;

Przykład 6.6

Napisz polecenie, które odpowie na pytanie: “Czyje pobory zasadnicze są wyższe od poborów LECHA ADAMCZYKA wyłączając z tej listy PREZESÓW?”.

a). gdy wiemy, że jest tylko jeden Lech Adamczyk

SELECT  NazwP , ImieP , Stan , Zasad FROM  PRAC

WHERE  Zasad >   (  SELECT  Zasad  FROM  PRAC

WHERE  NazwP  =  `ADAMCZYK' AND ImieP = `LECH' )

AND  Stan  !=  `PREZES'  ;

b). gdy nie wiemy ile osób o tym samym imieniu i nazwisku pracuje w firmie, a pytanie przekształcimy do formy: “Czyje pobory zasadnicze są wyższe od poborów któregokolwiek LECHA ADAMCZYKA wyłączając z tej listy PREZESÓW?”

SELECT  NazwP , ImieP , Stan , Zasad FROM  PRAC

WHERE  Zasad >  ANY (  SELECT  Zasad  FROM  PRAC

WHERE  NazwP  =  `ADAMCZYK' AND ImieP = `LECH' )

AND  Stan  !=  `PREZES'  ;

c). gdy nie wiemy ile osób o tym samym imieniu i nazwisku pracuje w firmie, a pytanie przekształcimy do formy: “Czyje pobory zasadnicze są wyższe od poborów każdego LECHA ADAMCZYKA wyłączając z tej listy PREZESÓW?”

SELECT  NazwP , ImieP , Stan , Zasad FROM  PRAC

WHERE  Zasad >   (  SELECT  MAX( Zasad )  FROM  PRAC

WHERE  NazwP  =  `ADAMCZYK' AND ImieP = `LECH' )

AND  Stan  !=  `PREZES'  ;

Przykład 6.7

Napisz polecenie, które odpowie na pytanie: “Czyje pobory zasadnicze są wyższe od poborów któregokolwiek ADAMCZYKA wyłączając z tej listy PREZESÓW?”.

SELECT  NazwP , ImieP , Stan , Zasad FROM  PRAC

WHERE  Zasad >  ANY  (  SELECT  Zasad  FROM  PRAC WHERE  NazwP  =  `ADAMCZYK' )

AND  Stan  !=  `PREZES'  ;

Przykład 7.1

Wypisz listę pracowników, których pobory zasadnicze są niższe niż wynoszą średnie pobory zasadnicze wszystkich osób zatrudnionych w firmie na identycznym stanowisku.

SELECT  *  FROM  PRAC  GLOWNE

WHERE  GLOWNE.Zasad  < ( SELECT  AVG( PODZAP.Zasad )  FROM  PRAC  PODZAP

WHERE   PODZAP.Stan  =  GLOWNE.Stan )  ;

Przykład 7.2

Wypisz listę pracowników, którzy mają najwyższą pensję w swoim dziale.

SELECT  NumP , NazwP , NumDz , Stan , Zasad FROM  PRAC  Z

WHERE  Zasad  =  ( SELECT  MAX( Zasad ) FROM  PRAC  W WHERE   W.NumDz  =  Z.NumDz ) ;

Przykład 7.3

Wypisz listę działów, które mają co najmniej pięciu pracowników.

SELECT  NumDz , NazwaDz , Lok FROM  DZIAL  D

WHERE  5  <=  (  SELECT  COUNT( * ) FROM  PRAC  P WHERE  P.NumDz  =  D.NumDz  ) ;

Przykład 7.4

Wypisz listę działów z tabeli DZIAL, w których są zatrudnieni pracownicy otrzymujący prowizję.

SELECT  *  FROM  DZIAL  D

WHERE  EXISTS (  SELECT  *  FROM  PRAC  P WHERE  P.NumDz  =  D.NumDz

AND  Prow  IS  NOT  NULL  ) ;

Przykład 7.5

Sprawdź poprawność wprowadzenia danych do tabel PRAC i DZIAL ( znajdź te wiersze tabeli PRAC, w których wpisana w polu NumDz wartość nie ma odpowiednika w polu NumDz tabeli DZIAL ):

a). chcemy poznać błędne wiersze

SELECT  *  FROM  PRAC  P

WHERE  NOT  EXISTS (  SELECT  *  FROM  DZIAL  D WHERE  D.NumDz  =  P.NumDz  )  ;

wynik (brak błędnych wierszy):

b). interesuje nas tylko liczba błędnych wierszy

SELECT   COUNT(*) AS ”Błędów”  FROM  PRAC  P

WHERE  NOT  EXISTS (  SELECT  *  FROM  DZIAL  D WHERE  D.NumDz  =  P.NumDz  )  ;

wynik (brak błędnych wierszy):

Przykład 8.1

Wypisz listę pracowników, których stanowiska rozpoczynają się na literę A.

SELECT  TP.NazwP , TP.Stan , TD.Lok  FROM  PRAC  TP , DZIAL  TD

WHERE  TP.NumDz = TD.NumDz AND  TP.Stan  LIKE  `A%' ;

Przykład 8.2

Wypisz listę pracowników, których kierownikiem jest BOROWA:

a). użyj konstrukcji podzapytania

SELECT  NazwP , ImieP , Kier FROM  PRAC

WHERE   Kier  =  ( SELECT  NumP  FROM  PRAC WHERE  NazwP  =  `BOROWA'

AND  Stan  =  `KIEROWNIK' )  ;

b). użyj konstrukcji złączenia

SELECT  P.NazwP , P.ImieP , P.Kier FROM  PRAC  P , PRAC  K

WHERE  P.Kier  =  K.NumP AND  K.NazwP  =  `BOROWA' AND  K.Stan  =  `KIEROWNIK'  ;

Przykład 8.3

Wypisz listę działów, których kierownik został przyjęty do pracy później niż przynajmniej jeden z jego podwładnych.

SELECT  NumDz , NazwaDz , Lok  FROM  DZIAL  D

WHERE  EXISTS (  SELECT  K.* FROM  PRAC  K , PRAC  P

WHERE  K.NumP  =  P.Kier

AND  K.NumDz  =  D.NumDz

AND  K.DZatrud  >  P.DZatrud  )  ;

Przykład 8.4

Wypisz bez powtórzeń listę stanowisk, na których pracują osoby zatrudnione w Gdyni:

a). użyj konstrukcji podzapytania

SELECT DISTINCT Stan FROM PRAC

WHERE NumDz IN ( SELECT NumDz FROM DZIAL WHERE Lok = `GDYNIA' ) ;

b). użyj konstrukcji podzapytania skorelowanego z predykatem EXISTS

SELECT DISTINCT Stan FROM PRAC P

WHERE EXISTS ( SELECT * FROM DZIAL D WHERE D.NumDz = P.NumDz

AND Lok = `GDYNIA' ) ;

c). użyj konstrukcji złączenia

SELECT P.Stan , D.Lok FROM PRAC P , DZIAL D

WHERE P.NumDz = D.NumDz AND D.Lok = `GDYNIA' ;

Przykład 8.5

Wypisz listę: ”Po ile osób zatrudnionych w Gdyni pracuje na poszczególnych stanowiskach pracy?”:

a). użyj konstrukcji podzapytania

SELECT Stan , COUNT( * ) AS `Liczba osób' FROM PRAC

WHERE NumDz IN ( SELECT NumDz FROM DZIAL WHERE Lok = `GDYNIA' )

GROUP BY Stan ;

b). użyj konstrukcji złączenia

SELECT P.Stan , COUNT( * ) AS `Liczba osób' FROM PRAC P , DZIAL D

WHERE P.NumDz = D.NumDz AND D. Lok = `GDYNIA'

GROUP BY P.Stan ;

---