Zad. 1
SELECT
orderid
FROM
Orders
where
freight
between
20
and
70
order
by
freight
zad. 2
SELECT
ProductName
as
'Nazwa produktu'
,
UnitPrice
as
'Cena w EUR'
,
UnitPrice
*
1.37
as
'Cena w USD'
FROM
Products
zad. 3
SELECT
*
FROM
Employees
WHERE
LastName
not
like
'K%'
1.
SELECT
CustomerID
,
City
,
Fax
FROM
Customers
WHERE
(
City
like
'London'
or
City
like
'Paris'
)and
Fax
is
not
null
2.
SELECT
CustomerID
+
' '
+
City
+
' '
+
Address
+
' '
+
PostalCode
FROM
Customers
ORDER
BY
City
3.
SELECT
*
FROM
Customers
WHERE
Country
in
(
'Italy'
,
'Germany'
,
'France'
,
'Spain'
)
4.
SELECT
*
FROM
Customers
WHERE
ContactTitle
like
'sales representative'
and
City
like
'London'
or
ContactTitle
like
'Sales agent'
and
City
like
'Buenos Aires'
5.
SELECT
*
FROM
Customers
WHERE
(
City
like
'London'
or
City
like
'Buenos Aires'
)and
(
ContactTitle
like
'sales representative'
or
ContactTitle
like
'Sales agent'
)
6. Wyświetl wszystkie zamówienia, które zostały zrealizowane na czas
SELECT
*
FROM
Orders
WHERE
ShippedDate
<=
RequiredDate
7. Wyświetl wszystkie spóźnione zamówienia oraz liczbę dni, o jakie były spóźnione
w kolejności od najbardziej spóźnionych
SELECT
*,
CAST
(
ShippedDate
-
RequiredDate
AS
INT
)
’opoznienie’
FROM
Orders
WHERE
ShippedDate
>
RequiredDate
ORDER
BY
opoznienie
desc
LUB
SELECT
*,
DATEDIFF
(
DD
,
RequiredDate
,
ShippedDate
)
'opoznienie'
FROM
Orders
WHERE
ShippedDate
>
RequiredDate
ORDER
BY
opoznienie
desc
8.
Wyświetl zamówienia złożone od 1 sierpnia 1996 do 31 października.
SELECT
*
FROM
Orders
WHERE
OrderDate
between
'1996-08-01'
and
'1996-10-31'
Lub
SELECT
*
FROM
Orders
WHERE
OrderDate
>=
'1996-08-01'
and
OrderDate
<=
'1996-11-01'
9.
SELECT
*
FROM
Orders
WHERE
YEAR
(
ShippedDate
)=
1998
and
(
ShipCountry
like
'Mexico'
or
ShipCountry
like
'Venezuela'
)
10.
DOM
11.
Wyświetl jak długo (w latach) pracują w firmie poszczególni pracownicy
SELECT
*,
GETDATE
(),
DATEDIFF
(
yy
,
HireDate
,
getdate
())
'ilosc lat'
FROM
Employees
12.
SELECT
LastName
,
HireDate
,
DATEADD
(
yy
,
10
,
hiredate
)
'pierwszy awans'
FROM
Employees
13.
SELECT
Title
+
' '
+
CAST
(
HireDate
AS
NCHAR
(
11
))
FROM
Employees
(11) liczba znaków
SELECT
Title
+
' '
+
CAST
(
HireDate
AS
NvarCHAR
(
11
))
FROM
Employees
W DOMU FUNKCJA CONVERT SPRAWDZIĆ !!!