Laboratorium 21.10.2013
Dużo zadań! Możemy je rozdzielić na zajęcia 21 i 28 października. Jak macie Państwo ochotę.
1. Zarządzanie prawami dostępu i bezpieczeństwem c.d. Tematyka nie jest na razie poruszana na wykładzie. Zadania te mają za cel wyrobienie w studentach nawyku samodzielnej pracy i
nabranie biegłości w posługiwaniu się systemem pomocy. Przykład skryptu definiującego
uprawnienia użytkownika:
create login andrzejs1 with password='abc'
create user andrzejs1 for login andrzejs1
use Northwind
--zwracamy uwagę, że nie ma możliwości wskazania docelowej bazy
--należy posłużyć się poleceniem USE
grant select to andrzejs1
--andrzejs1 może jedynie wykonywać polecenia select w bazie northwind
use CJDate
grant control to andrzejs1
Ustalić do czego służy klauzula GRANT OPTION występująca opcjonalnie w poleceniu GRANT.
Wzorując się na przykładzie przygotować skrypty złożone z poleceń SQL tworzące użytkowników dla baz dostępnych na serwerze (Northwind, Pubs, Adventure Works i CJDate) o różnych
poziomach uprawnień (prawa do modyfikowania danych, prawo do przeglądania danych, prawo
tworzenia/usuwania obiektów). W jaki sposób mechanizmy związane z rolami (ang. roles) umożliwiają efektywne administrowanie nadawaniem praw? Podać przykłady zastosowania tych
mechanizmów. (Dodatkowo) Rozważyć projekt aplikacji klienckiej służącej do administrowania
użytkownikami i ich rolami. W tym celu należy przeanalizować dane dostępne w tzw. widokach
systemowych ( System Views) dla danej bazy. Przykładowe zapytanie:
select * from sys.sysusers Wyświetla użytkowników bieżącej bazy.
2. Złączenie (join) – zapytania korzystające z więcej niż jednej tabeli, ew. z wielu egzemplarzy tej samej tabeli. Baza Northwind:
A. Pozycje zamówień wraz z datą dostawy (ShippedDate)
B. Pozycje zamówień zamówione (OrderDate) w roku 1997 – zapoznać się z funkcją YEAR
C. Dane o zamówieniach wraz nazwami firm klientów (Customers – CompanyName)
D. Pozycje zamówień wraz nazwami firm klientów (złączenie 3 tabel)
E. Dla wybranych zapytań podać alternatywne postacie – operator JOIN lub warunek
złączenia w klauzuli WHERE
F. Pozycje zamówień wraz nazwą i kategorią produktu z pozycji
G. Klienci którzy zamówili produkty z kategorii ‘Beverages’
H. Pracownicy sprzedający ‘Beverages’
I. Klienci składający zamówienia w roku 1998
J. Produkty dostarczone do Niemiec (Country=‘Germany’)
A. Części dostarczane do projektów rezydujących w tych samych miastach, w których te
części są składowane.
B. Miasta projektów do których dostarczono jednorazowo co najmniej 500 sztuk części
jakiegoś typu. (jednorazowo czyli istnieje wiersz w tabeli SPJ o wartości QTY>=500)
C. Miasta projektów do których dostarczono jednorazowo co najmniej 500 sztuk części P1.
D. Miasta projektów do których dostarczono jednorazowo co najmniej 500 sztuk części P1
lub P5.
E. Miasta projektów do których dostarczono jednorazowo co najmniej 500 sztuk części
różnej od P1.
4. Baza Northwind. Agregacja i złączenia (wstęp) Zwróćmy uwagę, że warunki z zadań 3B-3E
mogłyby dotyczyć sumarycznych dostaw (suma QTY ze wszystkich odpowiednich wierszy
tabeli SPJ). W takim wypadku należy się posłużyć złączeniem i agregacją.
A. Daty zamówień wraz z liczbą pozycji
B. Klienci wraz liczbą zamówień
C. Klienci (CompanyName) wraz z wartością zamówień
D. Klienci (CompanyName) o wartości zamówień większej od średniej
E. Klienci wraz z wartością zamówień z roku 1997
5. Operatory LIKE i BETWEEN
Zapoznać się z powyższymi operatorami. Pokazać , że BETWEEN jest w zasadzie zbędny. LIKE
jest potrzebny.
A. Zamówienia między majem a wrześniem (włącznie) z 1998
B. Zamówienia między majem a wrześniem ze wszystkich lat
C. Zapytania A oraz B w wersji bez operatora BETWEEN, wskazać regułę zastępowania
D. Nazwy firm zaczynające się na ‘X’
E. Nazwy firm zawierające ‘X’
F. Nazwy firm dłuższe niż 20 znaków – jak to zrobić za pomocą LIKE? Oczywiście jest też
funkcja len)
G. Nazwy firm zawierające znak podkreślenia ‘_’
H. Nazwy firm zawierające niemieckie znaki tj. (üöäß)
I. Nazwy nie zawierające tych znaków.
J. Wypisać produkty których cena zaczyna się cyfrą 3. (jest w helpie w opisie
CONVERT/CAST – będzie za tydzień)
6. Baza Northwind cd. (Agregacja i złączenie)
A. Obroty (podsumowanie Quantity*UnitPrice) uzyskane w poszczególnych kategoriach
produktów, zgrupować wg. CategoryID wypisać nazwy – zapisać zapytanie z
wykorzystaniem obydwu technik. i.e. Sztuczna agregacja oraz dodatkowe pola w
GroupBy
B. Obroty uzyskiwane w krajach
C. Obroty uzyskiwane w latach
D. Klient, który uzyskał najwyższe obroty w 1997
E. Znaleźć pracownika, który w 1997 uzyskał największe obroty w kategorii ‘Beverages’
7. Dla chętnych : dzielenie relacyjne.
A. Którzy klienci kupują produkty we wszystkich kategoriach
B. Którzy klienci kupują wszystko co sprzedaje Steven Buchanan (EmployeeID=5)
C. Zadania A i B rozwiązać korzystając z obydwu technik i.e. podwójnej negacji oraz zliczania D. Ponieważ nie ma takiego klienta który spełnia warunki w B poluźnić kryteria zamieniając produkty na kategorie