bazy danych zadania

Bazy danych, ćwiczenia 6
.
Język SQL, zadania
.
Zadania będą dotyczyć bazy danych Northwind.

Zadanie 1.
Proszę wypisać wszystkich klientów z Polski i z Niemiec.

select * from customers where country = 'Poland' or country = 'germany'

Zadanie 2.
Proszę wypisać wszystkich klientów, których nazwa rozpoczyna się od litery N oraz tych,
których nazwa
rozpoczyna się od litery C.

select * from customers where CompanyName like 'c%' or companyname like 'n%'



Zadanie 3.
Proszę wypisać dane wszystkich towarów, które są z kategorii o nazwach rozpoczynających
się na literę C.

select * from products p
where p.CategoryID in (select c.CategoryID from Categories c where c.CategoryName like 'C%')


Zadanie 4.
Proszę wypisać następujące dane z zamówień: OrderID, OrderDate, ProductID,
ProductName, UnitPrice(z tabeli [Order Details])
, Quantity, CategoryName, CustomerID,
CompanyName. Ile wierszy ma taki zestaw danych?

select o.OrderID, o.OrderDate, od.ProductID, p.ProductName, p.UnitPrice, od.Quantity, cat.CategoryName, c.CustomerID,
c.CompanyName
from orders o
join [Order Details] od on od.OrderID = o.OrderID
join customers c on o.CustomerID = c.CustomerID
join products p on p.ProductID = od.ProductID
join Categories cat on cat.CategoryID = p.CategoryID




Zadanie 5.
Proszę wypisać wszystkich klientów, którzy nie mają wpisane nic do kolumny Region.

select * from customers where region is null
Zadanie 6.
Proszę wypisać wszystkie kraje, z których mamy klientów.
select distinct country from customers

Zadanie 7.
Proszę wypisać wszystkie produkty kategorii o nazwie ‘Beverages’ (napoje) o cenie
jednostkowej większej bądź równej niż 20 i mniejszej bądź równej 30.

select * from products p
join Categories c on c.CategoryID = p.CategoryID and c.CategoryName = 'Beverages'
where p.UnitPrice >=20 and p.UnitPrice <= 30

Zadanie 8.
Proszę wypisać wszystkich klientów, którzy jeszcze nic nie zamówili. Wskazówka: należy połączyć tabelę Customers
z tabelą Orders przez złączenie zewnętrzne (np.LEFT JOIN) i dodać odpowiednią klauzulę WHERE.
select * from customers c
left join orders o on o.CustomerID = c.CustomerID
where o.CustomerID is null

Zadanie 9.
Proszę wypisać wszystkich klientów, którzy jeszcze nic nie zamówili. Należy wykorzystać
złożenie operatorów NOT oraz IN.
se
select * from customers c
where c.CustomerID not in (select distinct o.CustomerID from orders o)
Zadanie 10.
Proszę wypisać wszystkich
klientów, którzy jeszcze nic nie zamówili. Należy wykorzystać
złożenie operatorów NOT oraz EXISTS.

select * from customers c where not exists (select o.orderid from orders o where o.CustomerID = c.CustomerID)

Zadanie 11.
Proszę wypisać ilu mamy klientów z Polski i z Niemiec (zestaw ma mieć dwie kolumny: kraj i
liczbę klientów).

select country, count(*) from customers
where country = 'poland' or country = 'germany'
group by country

Zadanie 12.
Proszę wypisać z ilu krajów mamy klientów (zdanie ma wypisać jedną liczbę).
select count(distinct country) from customers

Zadanie 13.
Proszę wypisać dla każdego dnia ile przyjęto w ciągu tego dnia zamówień.
select o.OrderDate, count(*) as "Ile" from orders o
group by o.OrderDate

Zadanie 14.
Proszę wypisać, ile złożono zamówień na każdą kategorię towarów.

select p.categoryid, count(o.orderid) as "ile na kategorie"
from products p
right join [Order Details] od on od.ProductID = p. ProductID
right join Orders o on o.OrderID = od.OrderID
group by p.CategoryID

select * from orders
select * from [order details]

Zadanie 15.
Proszę wypisać dla każdego towaru ilu klientów złożyło zamówienie na ten towar.
select p.ProductName, count (o.CustomerID) "ilu zamowilo" from orders o
join [Order Details] od on od.OrderID = o.OrderID
right join products p on p.ProductID = od.ProductID
group by p.ProductName

Zadanie 16.
Proszę wypisać najwyższe i najniższe ceny towarów w każdej kategorii, przy czym chodzi o
towary o cenach wyższych od 30.

select p.CategoryID, max( p.unitprice) "max", min(p.unitprice) "min" from products p
where  p.unitprice >30
group by p.CategoryID


Zadanie 17.
Proszę wypisać dla każdego dnia jaka jest suma kwot na wszystkich zamówieniach w danym dniu.

select o.orderdate, sum (sub1.sumaWzam ) "suma w dniu"
from
    (select  o.orderid, sum(od.unitprice) "sumaWzam"
    from orders o
    join [Order Details] od on od.OrderID = o.OrderID
    group by o.OrderID) sub1
join orders o on o.orderid = sub1.orderid
group by o.OrderDate



Zadanie 18.
Proszę zmodyfikować rozwiązanie poprzedniego zadania tak, by w każdym dniu pokazana
była suma kwot uzyskanych za zamówienia poszczególnych produktów.
select o.orderdate,sub1.productid, sum (sub1.sumaWzam ) "suma w dniu"
from
    (select  o.orderid, od.productid , sum(od.unitprice) "sumaWzam"
    from orders o
    join [Order Details] od on od.OrderID = o.OrderID
    group by od.productid, o.OrderID) sub1
join orders o on o.orderid = sub1.orderid
group by o.OrderDate, sub1.productid



Zadanie 19.
Proszę wypisać identyfikatory klientów (może być jeden taki klient lub więcej), którzy
zamówili złożyli zamówienia w sumie na największą kwotę.

select o.CustomerID
    from [Order Details] od
    join orders o on od.OrderID = o.OrderID
    group by o.CustomerID
    having sum(od.unitprice) =
        (select  max(WartosciZamowien.sumWZam)
        from
            (select o.CustomerID,  sum(od.unitprice) "sumWZam"
            from [Order Details] od
            join orders o on od.OrderID = o.OrderID
            group by o.CustomerID ) "WartosciZamowien" )


Zadanie 20.
Proszę wypisać wszystkie dane klientów (może być jeden taki klient lub więcej), którzy
zamówili złożyli zamówienia w sumie na największą kwotę.
select c.* from customers c
join
(select o.CustomerID
    from [Order Details] od
    join orders o on od.OrderID = o.OrderID
    group by o.CustomerID
    having sum(od.unitprice) =
        (select  max(WartosciZamowien.sumWZam)
        from
            (select o.CustomerID,  sum(od.unitprice) "sumWZam"
            from [Order Details] od
            join orders o on od.OrderID = o.OrderID
            group by o.CustomerID ) "WartosciZamowien" ) )"maksKlienci"
on maksKlienci.CustomerID = c.CustomerID


Zadanie 21.
Proszę wypisać najwyższe i najniższe ceny towarów w każdej kategorii.
Przy każdej cenie należy również podać identyfikator i nazwę towaru o tej cenie (może być kilka towarów o
jednakowej cenie, należy je wszystkie uwzględnić).

select cat1.CategoryID, p1.productid, p1.productname, p1.UnitPrice "najwyzsza", '' "odstep",p2.productid, p2.productname, p2.UnitPrice "najnizsza" from categories cat1
join products p1 on p1.CategoryID = cat1.CategoryID
and p1.UnitPrice =
    (select max (p.unitprice) "maks" from products p
    join categories cat on cat.CategoryID = p.CategoryID
    group by cat.CategoryID
    having cat.CategoryID = cat1.categoryID)

join products p2 on p2.CategoryID = cat1.CategoryID
and p2.UnitPrice =
    (select min (p.unitprice) "mini" from products p
    join categories cat on cat.CategoryID = p.CategoryID
    group by cat.CategoryID
    having cat.CategoryID = cat1.categoryID)




Zadanie 22.
Proszę wypisać trzy najwyższe ceny w tabeli Products. Proszę wykorzystać konstrukcję TOP 3.
Czym różni się TOP 3 od TOP 3 WITH TIES?

WITH TIES spowoduje dołączenie do wynikowego zbioru danych wszystkich rekordów które mają takie same wartości jak
 3 pierwsze po order by. czyli nie będzie 3 wierszy w top 3 with ties tylko będzie tyle wierszy ile wierszy ma 3 topowe wartości

select top 3 p.unitprice from products p
order by p.UnitPrice desc

select top 3 with ties p.unitprice from products p
order by p.UnitPrice desc