Bazy danych – Kolokwium nr 1 A
1. Pokaż nr zamówienia (order id), datę zamówienia (orderdate), datę wysyłki
(shippeddate) oraz id pracownika (employeeid) z tabeli zamówienia (orders) (1 pkt)
select
orderid
,
orderdate
,
shippeddate
,
employeeid
from
orders
2. Pokaż nazwę produktu (productname), stan w magazynie (unitsinstock), ubytek
(wszędzie wstaw 5%) oraz stan w magazynie po uwzględnieniu ubytku (czyli -5%) z
tabeli produkty (products). Dla nowej kolumny nadaj nazwę. (2 pkt)
select
productname
,
UnitsInStock
,
'5%'
as
'ubytek'
,
UnitsInStock
*
0.95
as
'nowy stan w magazynie'
from
Products
3. Pokaż nazwę produktu (productname) oraz cenę (unitprice) dla produktów z
kategorii 1 (categoryid) z tabeli produkty (products). Wyniki uporządkuj wg ceny
rosnąco. (3 pkt)
select
productname
,
UnitPrice
from
Products
where
CategoryID
=
1
order
by
UnitPrice
4. Pokaż ilu jest klientów z Polski (3 pkt)
select
COUNT
(*)
from
Customers
where
Country
=
'Poland'
5. Pokaż średnią cenę dla produktów z poszczególnych kategorii (3 pkt)
select
categoryid
,
AVG
(
unitprice
)
from
Products
group
by
CategoryID
6. Pokaż nazwę produktu (productname), zapas (unitsinstock) i wartość zapasu z tabeli
produkty (products) gdzie: nazwa zawiera jako trzeci znak „o”, „a” lub „i” oraz
wartość zapasu jest mniejsza niż 500. (4 pkt)
select
productname
,
UnitsInStock
,
UnitPrice
*
UnitsInStock
from
products
where
ProductName
like
'__[o,a,i]%'
and
UnitPrice
*
UnitsInStock
<
500
7. Wyszukaj w bazie Northwind id zamówienia (orderid), datę złożenia zamówienia
(orderdate) i czas jego realizacji w dniach dla zamówień rozpoczętych w I i III
kwartale 1996 roku. Wyniki uporządkuj według czasu trwania malejąco. (4 pkt)
select
orderid
,
OrderDate
,
CAST
(
shippeddate
-
OrderDate
as
int
)
from
orders
where
(
OrderDate
between
'1996-01-01'
and
'1996-03-30'
)
or
(
OrderDate
between
'1996-07-01'
and
'1996-09-30'
)
order
by
3
desc
8. Dana jest tabela: (2 pkt)
Ksiazki
Id_ksiazki Tytul
Autor
Rok_wydania Ilosc_stron Cena
1
Potop
Sienkiewicz
2000
600
25,99
2
Zemsta
Fredro
2007
200
15,99
3
Pan
Tadeusz
Mickiewicz
1999
553
30,99
4
Śluby
panieńskie
Fredro
2009
250
11,99
5
Krzyżacy
Sienkiewicz
2004
1000
40,99
Co zwrócą poniższe zapytania:
a) SELECT Tytuł FROM Ksiazki WHERE Nazwisko=”Fredro” AND
Ilosc_stron>200
Zapytanie zwróci błąd, brak w tabeli kolumny „Nazwisko”
b) SELECT MIN(Cena) FROM Ksiazki
Zapytanie zwróci wartość 11,99 – najniższa cena książki
Bazy danych – Kolokwium nr 1 B
1. Pokaż nazwę (companyname), adres (address), nr telefonu (phone) oraz osobę
reprezentującą (contact name) z tabeli klienci (customers) (1 pkt)
select
companyname
,
Address
,
Phone
,
ContactName
from
customers
2. Pokaż nazwę produktu (productname), cenę netto (unitprice), marżę (wszędzie wstaw
15%) oraz cenę z uwzględnieniem marży z tabeli produkty (products). Dla nowej
kolumny nadaj nazwę. (2 pkt)
select
productname
,
UnitPrice
,
'15%'
as
'mar¿a'
,
UnitPrice
*
1.15
as
'nowa cena'
from
Products
3. Pokaż zamówienia (order id) oraz datę zamówienia (orderdate) złożone przez klienta
VINET (customerID) z tabeli zamówienia (orders). Wyniki uporządkuj wg daty
malejąco. (3 pkt)
Select
orderid
,
orderdate
from
orders
where
CustomerID
=
'VINET'
order
by
OrderDate
desc
4. Pokaż średnią cenę, dla produktów z kategorii 6 (3 pkt)
select
AVG
(
unitprice
)
from
Products
where
CategoryID
=
6
5. Pokaż ilu jest klientów z poszczególnych krajów (3 pkt)
select
country
,
COUNT
(*)
from
Customers
group
by
country
6. Pokaż nazwę produktu (productname), cenę netto (unitprice) i cenę brutto (stawka
VAT 22%) z tabeli produkty (products) gdzie: nazwa zawiera znak „t”, „o” lub „w”
oraz cena jest z przedziału [20,500]. (4 pkt)
select
productname
,
UnitPrice
,
UnitPrice
*
1.22
from
products
where
ProductName
like
'%[t,o,w]%'
and
UnitPrice
between
20
and
500
7. Wyszukaj w bazie Northwind id zamówienia (orderid), datę wysłania zamówienia
(shippeddate) i czas jego realizacji w dniach dla zamówień rozpoczętych w II i IV
kwartale 1997 roku. Wyniki uporządkuj według czasu trwania. (4 pkt)
select
orderid
,
ShippedDate
,
CAST
(
shippeddate
-
OrderDate
as
int
)
from
orders
where
(
OrderDate
between
'1997-04-01'
and
'1997-06-30'
)
or
(
OrderDate
between
'1997-10-01'
and
'1997-12-31'
)
order
by
3
8. Dana jest tabela: (2 pkt)
Ksiazki
Id_ksiazki Tytul
Autor
Rok_wydania Ilosc_stron Cena
1
Potop
Sienkiewicz
2000
600
25,99
2
Zemsta
Fredro
2007
200
15,99
3
Pan
Tadeusz
Mickiewicz
1999
553
30,99
4
Śluby
panieńskie
Fredro
2009
250
11,99
5
Krzyżacy
Sienkiewicz
2004
1000
40,99
Co zwrócą poniższe zapytania:
a) SELECT Tytuł FROM Ksiazki WHERE (Rok_wydania BETWEEN 2000 AND
2005) AND Ilosc_stron<1000
Zapytanie zwróci tytuł “Potop”
b) SELECT MAX(Tytul) FROM Ksiazki
Zapytanie zwróci tytuł “Zemsta”
Bazy danych – Kolokwium nr 1 C
1. Pokaż nr zamówienia (order id), datę zamówienia (orderdate), wymaganą datę wysyłki
(requireddate) oraz id klienta (customerid) z tabeli zamówienia (orders) (1 pkt)
select
orderid
,
OrderDate
,
RequiredDate
,
CustomerID
from
orders
2. Pokaż nazwę produktu (productname) oraz przyszłą ilość produktów w magazynie z
tabeli produkty (products) wiedząc, że obecny stan magazynu to unitsinstocks a
zamówiony towar to unitsonorder. Dla nowej kolumny nadaj nazwę. (2 pkt)
select
productname
,
UnitsInStock
+
UnitsOnOrder
as
'przysz³y stan'
from
products
3. Pokaż nazwę produktu (productname) oraz cenę (unitprice) dla produktów od
dostawcy 3 (supplierid) z tabeli produkty (products). Wyniki uporządkuj wg nazwy
malejąco. (3 pkt)
select
productname
,
unitprice
from
products
where
SupplierID
=
3
order
by
1
desc
4. Pokaż wartość zamówienia o numerze 10291 (3 pkt)
select
SUM
(
unitprice
*
quantity
)
from
[Order Details]
where
OrderID
=
10291
5. Pokaż ile zamówień przyjął każdy pracownik w roku 1997 (3 pkt)
select
employeeid
,
COUNT
(*)
from
orders
where
YEAR
(
OrderDate
)=
1997
group
by
EmployeeID
6. Pokaż nazwę produktu (productname), cenę netto (unitprice) i cenę brutto (stawka
VAT 22%) z tabeli produkty (products) gdzie: nazwa zawiera znaki b-n oraz cena jest
spoza przedziału [20,500]. (4 pkt)
select
productname
,
UnitPrice
,
UnitPrice
*
1.22
from
products
where
ProductName
like
'%[b-n]%'
and
UnitPrice
not
between
20
and
500
7. Wyszukaj w bazie Northwind id zamówienia (orderid), datę wysłania zamówienia
(shippeddate) i czas jego realizacji w dniach dla zamówień rozpoczętych w I i III
kwartale 1996 roku. Wyniki uporządkuj według czasu trwania. (4 pkt)
select
orderid
,
ShippedDate
,
CAST
(
shippeddate
-
OrderDate
as
int
)
from
orders
where
(
OrderDate
between
'1996-01-01'
and
'1996-03-30'
)
or
(
OrderDate
between
'1996-07-01'
and
'1996-09-30'
)
order
by
3
8. Dana jest tabela: (2 pkt)
Ksiazki
Id_ksiazki Tytul
Autor
Rok_wydania Ilosc_stron Cena
1
Potop
Sienkiewicz
2000
600
25,99
2
Zemsta
Fredro
2007
200
15,99
3
Pan
Tadeusz
Mickiewicz
1999
553
30,99
4
Śluby
panieńskie
Fredro
2009
250
11,99
5
Krzyżacy
Sienkiewicz
2004
1000
40,99
Co zwrócą poniższe zapytania:
a) SELECT Tytuł FROM Ksiazki WHERE Nazwisko=”Sienkiewicz” AND Cena>30
Zapytanie zwróci tytuł „Krzyżacy”
b) SELECT MAX(Rok_wydania) FROM Ksiazki
Zapytanie zwróci rok 2009
Bazy danych – Kolokwium nr 1 D
1. Pokaż nazwę (companyname), adres (address), region (region) oraz tytuł osoby do
kontaktu (contact title) z tabeli dostawcy (suppliers) (1 pkt)
select
companyname
,
Address
,
Region
,
ContactTitle
from
Suppliers
2. Pokaż nazwę produktu (productname), cenę netto (unitprice), procent podwyżki
(wszędzie wstaw 20%) oraz nową cenę po podwyżce z tabeli produkty (products). Dla
nowej kolumny nadaj nazwę. (2 pkt)
select
ProductName
,
UnitPrice
,
'20%'
as
'podwy¿ka'
,
UnitPrice
*
1.20
as
'nowa cena'
from
Products
3. Pokaż zamówień (order id) oraz datę zamówienia (orderdate) przyjętych przez
pracownika 5 (employeeID) z tabeli zamówienia (orders). Wyniki uporządkuj wg daty
rosnąco. (3 pkt)
select
OrderID
,
OrderDate
from
orders
where
EmployeeID
=
5
order
by
2
4. Pokaż ile zamówień przyjął pracownik o id 5 w II kwartale 1997 roku (3 pkt)
select
COUNT
(*)
from
Orders
where
EmployeeID
=
5
and
OrderDate
between
'1997-04-01'
and
'1997-06-30'
5. Pokaż wartość produktów w magazynie dla każdego dostawcy (3 pkt)
select
supplierid
,
SUM
(
unitprice
*
unitsinstock
)
from
Products
group
by
SupplierID
6. Pokaż nazwę produktu (productname), zapas (unitsinstock) i wartość zapasu z tabeli
produkty (products) gdzie: nazwa zawiera jako drugi znak a-g oraz wartość zapasu jest
większa niż 100. (4 pkt)
select
productname
,
UnitsInStock
,
UnitPrice
*
UnitsInStock
from
products
where
ProductName
like
'_[a-g]%'
and
UnitPrice
*
UnitsInStock
>
100
7. Wyszukaj w bazie Northwind id zamówienia (orderid), datę złożenia zamówienia
(orderdate) i czas jego realizacji w dniach dla zamówień zrealizowanych w II i IV
kwartale 1997 roku. Wyniki uporządkuj według czasu trwania malejąco. (4 pkt)
select
orderid
,
OrderDate
,
CAST
(
shippeddate
-
OrderDate
as
int
)
from
orders
where
(
OrderDate
between
'1997-04-01'
and
'1997-06-30'
)
or
(
OrderDate
between
'1997-10-01'
and
'1997-12-31'
)
order
by
3
desc
8. Dana jest tabela: (2 pkt)
Ksiazki
Id_ksiazki Tytul
Autor
Rok_wydania Ilosc_stron Cena
1
Potop
Sienkiewicz
2000
600
25,99
2
Zemsta
Fredro
2007
200
15,99
3
Pan
Tadeusz
Mickiewicz
1999
553
30,99
4
Śluby
panieńskie
Fredro
2009
250
11,99
5
Krzyżacy
Sienkiewicz
2004
1000
40,99
Co zwrócą poniższe zapytania:
a) SELECT Tytuł FROM Ksiazki WHERE (Rok_wydania >2000) AND Cena
BETWEEN 10 AND 20)
Zapytanie zwróci tytuły “Zemsta” i “Śluby Panieńskie”
b) SELECT MIN(Ilosc_stron) FROM Ksiazki
Zapytanie zwróci 200