Archiwa tagu: sql

Baza Biblioteka 2. Pokaż 10 najczęściej wypożyczających czytelników.

SQL tutorial. Baza Biblioteka ćwiczenia. Środowisko SQL SERVER.

Baza: Biblioteka

Zadanie nr: 2

Treść:  Pokaż 10 najczęściej wypożyczających czytelników.

Polecenia/funkcje w zapytaniu: SELECT TOP, COUNT(), JOIN, GROUP BY, ORDER BY


SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka


Rozwiązanie:

Zapytanie:

select top 10
     C.login
    ,COUNT(*) as [Liczba wypozyczen]

from
    Wypozyczenia W
    join Czytelnicy C on W.id_czytelnik = C.id_czytelnik
    
group by
    C.login    

order by
    COUNT(*) desc   

Wynik:

SQL tutorial.  SQL SERVER. Baza Biblioteka

Pobierz skrypt sql


 SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka


 

Baza Biblioteka 1. Pokaż 10 najczęściej wypożyczanych książek.

SQL tutorial. Baza Biblioteka ćwiczenia. Środowisko SQL SERVER.

Baza: Biblioteka

Zadanie nr: 1

Treść:  Pokaż 10 najczęściej wypożyczanych książek.

Polecenia/funkcje w zapytaniu: SELECT TOP, COUNT(), JOIN, GROUP BY, ORDER BY


SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka


Rozwiązanie:

Zapytanie:

select top 10
     K.tytul
    ,COUNT(*) as [Ilosc wypozyczen]

from
    wypozyczenia W
    join Ksiazki K on W.id_ksiazka = K.id_ksiazka
    
group by
     K.tytul
    ,W.id_ksiazka
    
order by
    COUNT(*) desc

Wynik:

SQL tutorial.  SQL SERVER. Baza Biblioteka

Pobierz skrypt sql


 SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka

SQL REVOKE

W ostatnim wpisie omawialiśmy polecenie GRANT którym możemy nadawać uprawnienia do tabel dla poszczególnych użytkowników. Skoro uprawnienia możemy nadać to i możemy je odebrać i do tego służy polecenie REVOKE.


    


Składnia REVOKE

REVOKE
    uprawnienia_użytkownika ON nazwa_tabeli FROM nazwa_użytkownika

uprawnienia użytkownika – to lista która może zawierać jedno lub więcej (oddzielonych przecinkami) uprawnień które chcemy odebrać użytkownikowi
nazwa tabeli – tutaj wpisujemy nazwę tabeli z które chcemy odebrać uprawnienie(-a)
nazwa_użytkownika – tutaj wpisujemy nazwę użytkownika, któremu chcemy odebrać uprawnienia 


Odbieranie uprawnień w SQL SERVERZE

Krok 1. Uruchamiamy Management Studio

Krok 2. W gałęzi Security -> Logins możemy sprawdzić jakich użytkowników mamy na serwerze. Sprawdzając właściwości poszczególnych tabel możemy dowiedzieć się jakie uprawnienia dany użytkownik ma na danej tabeli. W przykładzie do wpisu gdzie poznawaliśmy polecenie GRANT utworzyliśmy użytkownika USER i nadaliśmy mu uprawnienie SELECT do tabeli WYPOZYCZENIA. To teraz odbierzemy mu to uprawnienie.

Krok 3. W oknie zapytań wybieramy odpowiedni kontekst (odpowiednią bazę danych – Biblioteka) i poleceniem REVOKE odbierzemy uprawnienie SELECT naszemu użytkownikowi USER
REVOKE select ON Wypozyczenia FROM [DESKTOP\USER]

po uruchomieniu komendy REVOKE powinniśmy otrzymać komunikat

Command(s) completed successfully

w przypadku błędnego podania użytkownika np. USER2 otrzymamy komunikat

Cannot find the user 'DESKTOP\USER2", because it does not exist or you do not have permission

Krok 5. Teraz we właściwościach tabeli Wypozyczenia możemy sprawdzić i zobaczyć czy uprawnienia zostały odebrane. W tym celu klikamy PPM na tabeli Wypozyczenia w bazie Biblioteka i wybieramy opcję Properties. W oknie "Table Properties – Wypozyczenia" w sekcji "Select a page" klikamy na "Permissions". Na poniższym zrzucie widzimy że w części "Users or roles" jest pusto i w części "Permissions" jest także pusto. Widzimy więc, że odebraliśmy uprawnienie SELECT użytkownikowi USER do tej tabeli.

sql server revoke odbieranie uprawnień użytkownikom


    

SQL GRANT

Tworząc i administrując bazami danych przychodzi taki moment kiedy potrzebujemy nadać komuś uprawnienia do naszej bazy i niekoniecznie chcemy aby miał on wszystkie uprawnienia do całej naszej bazy np. jakiś analityk potrzebuje robić okresowe zestawienia sprzedaży, jakaś aplikacja korzysta z naszej bazy danych itd. Zwróć uwagę, że jenek użytkownik potrzebuje większe uprawnienia np. odczyt ale również i zapis danych na jakichś tabelach, a drugiemu użytkownikowi wystarczy tylko odczyt na jednej tabeli. Rozwiązaniem tej sytuacji jest stworzenie użytkownika na bazie i nadanie mu odpowiednich uprawnień. Możemy to zrobić dzięki poleceniu GRANT. Jedna uwaga we różnych środowiskach bazodanowych uprawnienia nadajemy w różny sposób tzn. niektóre środowiska jak np. SQL Server wymaga już utworzonego użytkownika i dopiero dla utworzonego użytkownika możemy nadać uprawnienia.


    


Składnia GRANT

GRANT
    uprawnienia_użytkownika ON nazwa_tabeli TO nazwa_użytkownika

uprawnienia użytkownika – to lista która może zawierać jedno lub więcej (oddzielonych przecinkami) uprawnień które chcemy nadać użytkownikowi
nazwa tabeli – tutaj wpisujemy nazwę tabeli na którą chcemy nadać uprawnienie
nazwa_użytkownika – tutaj wpisujemy nazwę użytkownika, któremu chcemy nadać uprawnienia 


Nadawanie uprawnień w SQL SERVERZE

Krok 1. Uruchamiamy Management Studio

Krok 2. Przechodzimy do gałęzi Security -> Logins i sprawdzamy czy nasz użytkownik (któremu chcemy nadać uprawnienia) jest na liście. Jeżeli jest to z poziomu okna zapytań możemy poleceniem GRANT nadać użytkownikowi wymagane uprawnienia. Jeżeli użytkownika nie ma na liście klikamy prawym przyciskiem myszy (dalej PPM) i z menu wybieramy opcję "New Login"

Krok 3. W oknie "Login – New" wprowadzamy nazwę użytkownika (UWAGA wymagana jest pełna nazwa użytkownika razem z domeną: domena\nazwa_użytkownika), możemy określić metodę autektykacji użytkownika a nawet domyślą bazę danych. Po wypełnieniu formatki potwierdzamy nasz wybór przyciskiem OK. W tym momencie nasz użytkownik powiniem znaleźć się na liście Security -> Logins

sql grant sql server

Krok 4. W oknie zapytań wybieramy odpowiedni kontekst (odpowiednią bazę danych) i poleceniem GRANT nadajemy odpowiednia uprawnienia naszemu użytkownikowi np.
GRANT select ON Wypozyczenia TO [DESKTOP\USER]

Krok 5. Teraz we właściwościach tabeli Wypozyczenia możemy sprawdzić i zobaczyć czy uprawnienia zostały nadane. W tym celu klikamy PPM na tabeli Wypozyczenia w bazie Biblioteka i wybieramy opcję Properties. W oknie "Table Properties – Wypozyczenia" w sekcji "Select a page" klikamy na "Permissions". Na poniższym zrzucie widzimy nadane uprawnienie SELECT dla użytkowniak USER.

sql grant sql server


Nadawanie uprawnień w PhpMyAdmin (MySQL)

Krok 1. Łączymy się z PhpMyAdmin

Krok 2. W oknie głównym szukaj opcji "User accounts" i dalej "Add user account".

sql grant phpmyadmin mysql

Krok 3. W kolejnym oknie wypełniamy informacje w sekcji "Login information". Są to informacje dotyczące użytkowniak, a następnie zjeżdżamy stroną w dół i wypełniamy jakie uprawnienia chcemy nadać temu użytkownikowi i potwierdzamy nasz wybór przyciskiem GO na dole strony.

sql grant phpmyadmin mysql

sql grant phpmyadmin mysql

Krok 4. Pojawi nam się nowe okno gdzie zostaniemy poinformowani o pozytywnym nadaniu uprawnień oraz zobaczymy polecenie którym serwer nadał uprawnienia użytkownikowi. Możemy z niego wyczytać, że na początku został utworzony nowy użytkownik o nazwie "user" i nadano mu odpowiednie uprawnienia.
sql grant phpmyadmin mysql

GRANT SELECT ON *.* TO 'user'@'localhost'

widzimy, że użytkownikowi 'user' zostało nadane upoważnienie SELECT na wszytkich tabelach *.*


    

db HOTELS 11. Wyświetl wszystkie rezerwacje (z informacją czy zrealizowane)

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 11

Treść: Wyświetl wszystkie rezerwacje (z informacją czy zrealizowane)

Polecenia/funkcje w zapytaniu: SELECT, CASE, FROM, JOIN, WHERE


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

SELECT
     Klient.client_last_name AS Nazwisko
    ,Klient.client_first_name AS [Imie]
    ,Kraj.country_name AS [Kraj (hotel)]
    ,Miasto.city_name AS [Miasto (hotel)]
    ,reservation_starting_date AS [Data rozpoczecia rezerwacji]
    ,reservation_final_date AS [Data zakończenia rezerwacji]
    ,CASE
        WHEN Rezerwacja.reservation_realized = 1 THEN 'Zrealizowana'
        ELSE 'NIE zrealizowana'
     END AS [Stan realizacji]
FROM
    reservations Rezerwacja
    JOIN clients Klient ON Rezerwacja.reservation_client_id = Klient.client_id
    JOIN rooms Pokoj ON Rezerwacja.reservation_room_id = Pokoj.room_id
    JOIN hotels Hotel ON Pokoj.room_hotel_id = Hotel.hotel_id
    JOIN countries Kraj ON Hotel.hotel_country_id = Kraj.country_id
    JOIN cities Miasto ON Hotel.hotel_city_id = Miasto.city_id
WHERE
    Klient.client_last_name = 'Banko'

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER


 

db HOTELS 10. Oblicz udział procentowy hoteli w danym kraju względem wszystkich hoteli w bazie

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 10

Treść: Oblicz udział procentowy hoteli w danym kraju względem wszystkich hoteli w bazie

Polecenia/funkcje w zapytaniu: DECLARE, SELECT, COUNT, FROM, WHERE, CAST(), JOIN, GROUP BY, ORDER BY


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

DECLARE @ile_hoteli numeric =
    (
        SELECT
            COUNT(*)
        FROM    
            hotels
    )   
SELECT
     C.country_name AS Kraj
    ,CAST(COUNT(*)*100/@ile_hoteli AS NUMERIC(6,2)) AS [%]
FROM
    hotels H
    JOIN countries C ON H.hotel_country_id = C.country_id    
GROUP BY
    C.country_name
ORDER BY
    2 DESC   

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


 

db HOTELS 9. Jakie było obłożenie wszystkich hoteli (%) w dniu 2016-10-10

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 9

Treść: Jakie było obłożenie wszystkich hoteli (%) w dniu 2016-10-10

Polecenia/funkcje w zapytaniu: DECLARE, SELECT, COUNT, FROM, WHERE, BETWEEN, CAST(), ROUND()


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

DECLARE @dataSprawdzenia date = '2016-10-10'
DECLARE @ilePokoi int =
    (
        SELECT
            COUNT(*)
        FROM
            rooms
    )
DECLARE @ilePokoiZajetych int =
    (    
        SELECT
            COUNT(*)    
        FROM    
            reservations R    
        WHERE
            @dataSprawdzenia BETWEEN R.reservation_starting_date AND R.reservation_final_date
    )    
DECLARE @oblozenie REAL = 100*@ilePokoiZajetych/@ilePokoi;
SELECT
     'Wszystkich pokoi: '+CAST(@ilePokoi AS NVARCHAR) AS Pokoi
    ,'Zajętych pokoi: '+CAST(@ilePokoiZajetych AS NVARCHAR) AS Zajętych
    ,'Obłożenie pokoi w dn. 2016-10-10 wynosi: '
        +CAST(CAST(ROUND((100*CAST(@ilePokoiZajetych AS NUMERIC)/CAST(@ilePokoi AS NUMERIC)),2) AS NUMERIC(6,2)) AS NVARCHAR)
        +'%' AS Obłożenie

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


 

db HOTELS 8. W jakim hotelu złożono najwięcej rezerwacji które zostały zrealizowane.

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 8

Treść:  W jakim hotelu złożono najwięcej rezerwacji które zostały zrealizowane.

Polecenia/funkcje w zapytaniu: SELECT TOP, Aliasy, COUNT(), FROM, JOIN, WHERE, GROUP BY, ORDER BY


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

SELECT TOP 1
     CO.country_name AS Kraj
    ,CI.city_name AS Miasto
    ,H.hotel_id AS [Nazwa hotelu]
    ,COUNT(*) AS [Ilość złożonych rezerwacji]
FROM
    reservations RE
    JOIN rooms RO ON RE.reservation_room_id = RO.room_id
    JOIN hotels H ON RO.room_hotel_id = H.hotel_id
    JOIN countries CO ON H.hotel_country_id = CO.country_id
    JOIN cities CI ON H.hotel_city_id = CI.city_id
WHERE
    RE.reservation_realized = 1
GROUP BY
    CO.country_name , CI.city_name , H.hotel_id        
ORDER BY
    COUNT(*) DESC

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


 

db HOTELS 7. W jakim hotelu złożono najwięcej rezerwacji.

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 7

Treść:  W jakim hotelu złożono najwięcej rezerwacji.

Polecenia/funkcje w zapytaniu: SELECT TOP, Aliasy, COUNT(), FROM, JOIN, GROUP BY, ORDER BY


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

SELECT TOP 1
     CO.country_name AS Kraj
    ,CI.city_name AS Miasto
    ,H.hotel_id AS [Nazwa hotelu]
    ,COUNT(*) AS [Ilość złożonych rezerwacji]
FROM
    reservations RE
    JOIN rooms RO ON RE.reservation_room_id = RO.room_id
    JOIN hotels H ON RO.room_hotel_id = H.hotel_id
    JOIN countries CO ON H.hotel_country_id = CO.country_id
    JOIN cities CI ON H.hotel_city_id = CI.city_id
GROUP BY
    H.hotel_id , CO.country_name , CI.city_name
ORDER BY
    COUNT(*) DESC

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


 

db HOTELS 6. Który hotel ma najwięcej miejsc parkingowych

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 6

Treść:  Który hotel ma najwięcej miejsc parkingowych

Polecenia/funkcje w zapytaniu: DECLARE, SELECT TOP, COUNT(), GROUP BY, FROM, ORDER BY, WHERE, HAVING


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

DECLARE @max_parking_places int = (
SELECT TOP 1
    COUNT(P.parking_hotel_id)
FROM
    parking P    
GROUP BY
    P.parking_hotel_id
ORDER BY
    1 DESC
);
SELECT
     H.hotel_name AS [Nazwa hotelu]
    ,COUNT(P.parking_hotel_id) AS [Ilość miejsc parkingowych]
FROM
    parking P    
    JOIN hotels H ON P.parking_hotel_id = H.hotel_id
GROUP BY
    P.parking_hotel_id, H.hotel_name
HAVING    
    COUNT(P.parking_hotel_id) = @max_parking_places   

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER