Kategoria: T-SQL

Zadanie 23.

Korzystając z widoku Sales Totals by Amount, utwórz zapytanie, które zwróci nazwę firmy, numer zamówienia, wartość zamówienia, procentowy udział zamówienia oraz łączną wartość zamówień klienta.

Baza: Northwind, Tabela: dbo.Sales Totals by Amount (widok), Kolumny: CompanyName, OrderID, SaleAmount, Wynik: 66 rows

s23

 

 

 

 

Rozwiązanie

SELECT CompanyName, OrderID, SaleAmount,
CAST(100. * SaleAmount / SUM(SaleAmount) OVER(PARTITION BY CompanyName) as DECIMAL(5,2)) as 'Percent',
SUM(SaleAmount) OVER(PARTITION BY CompanyName) as TotalByCust
FROM dbo.[Sales Totals by Amount]
ORDER BY CompanyName;

[collapse]

Zadanie 22.

Utwórz kwerendę, która policzy, ile jest niepowtarzających się imion. Zadanie rozwiąż na co najmniej dwa sposoby.

Baza: AdventureWorks2012, Tabela: Person.Person, Kolumna: FirstName, Wynik: 1018 rows

s22

 

 

 

 

 

 

 

Rozwiązanie

1. Z użyciem funkcji row_number i grupowaniem:

SELECT FirstName, ROW_NUMBER() OVER(ORDER BY FirstName) as nr
FROM Person.Person
GROUP BY FirstName
ORDER BY FirstName;

2. Z użyciem funkcji dense_rank i distinct:

SELECT DISTINCT FirstName, DENSE_RANK() OVER(ORDER BY FirstName) as nr
FROM Person.Person
ORDER BY FirstName;

3. Z użyciem tabeli pochodnej i row_number:

SELECT FirstName, ROW_NUMBER() OVER(ORDER BY FirstName) as nr
FROM ( SELECT DISTINCT FirstName FROM Person.Person ) as Tab
ORDER BY FirstName;

[collapse]

Zadanie 21.

Utwórz tabelę, gdzie w jednej kolumnie będą liczby od 1 do 10, a w drugiej od 10 do 1. Nie używaj pętli.

Baza: -, Tabela: -, Kolumny: -, Wynik: 10 rows

s21

 

 

 

 

 

 

 

 

Rozwiązanie

WITH CTE AS
(
SELECT 1 as x
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 10
)

SELECT x, ROW_NUMBER() OVER(ORDER BY x DESC) as x2
FROM CTE
ORDER BY x;

[collapse]

Zadanie 20.

1. Utwórz wspólne wyrażenie tablicowe (CTE), które zwróci nazwy miast i państw, w których są zarówno klienci jak i dostawcy.

2. W ostatecznym wyniku zapytania chcemy zobaczyć klientów i dostawców (CompanyName, SupplierID / CustomerID) z miast określonych w wyrażeniu tablicowym.

Baza: Northwind, Tabele: dbo.Suppliers, dbo.Customers, Kolumny: City, Country, CompanyName, SupplierID, CustomerID, Wynik: 14 rows

s20

 

 

 

 

 

Rozwiązanie

WITH CTE AS (

SELECT Country, City
FROM dbo.Suppliers

INTERSECT

SELECT Country, City
FROM dbo.Customers

)

SELECT s.SupplierID, s.CompanyName, s.City, c.CustomerID, c.CompanyName
FROM dbo.Suppliers as s JOIN dbo.Customers as c on s.City = c.City
WHERE s.City in (SELECT City FROM CTE);

[collapse]

Zadanie 19.

Wskaż, w których państwach i miastach (Country, City):

a) mają siedziby zarówno klienci, jak i dostawcy

b) mają siedziby dostawcy i klienci, ale nie pracownicy

c) są zarówno klienci i pracownicy, ale nie ma dostawców

Baza: Northwind, Tabele: dbo.Customers, dbo.Suppliers, dbo.Employees, Kolumny: Country, City, Wynik: a) 5 rows, b) 90 rows, c) 2 rows

Rozwiązanie

a) SELECT Country, City
FROM dbo.Customers

INTERSECT

SELECT Country, City
FROM dbo.Suppliers

 

b) SELECT Country, City
FROM dbo.Customers

UNION

SELECT Country, City
FROM dbo.Suppliers

EXCEPT

SELECT Country, City
FROM dbo.Employees

 

c) (SELECT Country, City
FROM dbo.Customers

EXCEPT

SELECT Country, City
FROM dbo.Suppliers)

INTERSECT

SELECT Country, City
FROM dbo.Employees

lub

SELECT Country, City
FROM dbo.Customers

INTERSECT

SELECT Country, City
FROM dbo.Employees

EXCEPT

SELECT Country, City
FROM dbo.Suppliers

[collapse]

Zadanie 18.

1. Stwórz kwerendę, która zwróci nazwę firmy, kraj oraz miasto klientów oraz dostawców.

2. Zmodyfikuj kwerendę tak, by pokazywała również kto jest dostawcą, a kto klientem. Wynik posortuj według typu (klienci najpierw), kraju i miasta.

Baza: Northwind, Tabele: dbo.Customers, dbo.Suppliers, Kolumny: CompanyName, Country, City, Wynik: 120 rows

s18

 

 

 

Rozwiązanie

1. SELECT CompanyName, Country, City
FROM dbo.Customers

UNION ALL

SELECT CompanyName, Country, City
FROM dbo.Suppliers

 

2. SELECT 'Customer' as 'Type', CompanyName, Country, City
FROM dbo.Customers

UNION ALL

SELECT 'Supplier' as 'Type', CompanyName, Country, City
FROM dbo.Suppliers

ORDER BY Type, Country, City

[collapse]

Zadanie 17.

1. Utwórz wspólne wyrażenie tablicowe (CTE), nazwane SalesSummary, które wyświetli nam szereg informacji o zamówieniach (OrderID, SaleAmount, CompanyName, ProductID, ProductName, UnitPrice, Quantity, Discount, ExtendedPrice, ShippedDate). Użyj w tym celu widoków Sales Totals by Amount oraz Order Details Extended.

2. W kwerendzie zewnętrznej wybierz wszystkie kolumny tabeli SalesSummary, oraz dołóż kolumnę zawierającą ID klienta (CustomerID, z tabeli Customers).

Baza: Northwind, Tabele: dbo.Sales Totals by Amount (widok), dbo.Order Details Extended (widok), dbo.Customers, Kolumny: OrderID, SaleAmount, CompanyName, ProductID, ProductName, UnitPrice, Quantity, Discount, ExtendedPrice, ShippedDate, CustomerID, Wynik: 233 rows

s17

 

 

Rozwiązanie

1. WITH SalesSummary AS
(
SELECT s.OrderID, s.SaleAmount, s.CompanyName, o.ProductID, o.ProductName, o.UnitPrice, o.Quantity, o.Discount, o.ExtendedPrice,  s.ShippedDate
FROM dbo.[Sales Totals by Amount] as s
JOIN dbo.[Order Details Extended] as o on s.OrderID = o.OrderID
)

2. SELECT *, (SELECT c.CustomerID FROM dbo.Customers as c WHERE SalesSummary.CompanyName = c.CompanyName) as CustomerID
FROM SalesSummary
ORDER BY OrderID;

[collapse]

Zadanie 16.

Wykorzystamy tabelę wynikową uzyskaną w zadaniu 15.

1. Powyższą tabelę przekształć do postaci wspólnego wyrażenia tablicowego (CTE), nazwanego Tab.

2. W kwerendzie zewnętrznej wybierz “20% najcenniejszych klientów”: nazwę firmy, łączną wartość zamówień złożonych przez daną firmę oraz łączną wartość wszystkich zamówień złożonych przez wszystkich klientów. Interesuje nas tylko 20% firm z najwyższymi łącznymi wartościami zamówień.

Baza: Northwind, Tabele: CTE Tab, dbo.order Subtotals, Kolumny: CompanyName, Subtotal, Wynik: 18 rows

s16

 

 

 

 

Rozwiązanie

WITH Tab AS (
SELECT o.OrderID, c.CompanyName, os.Subtotal
FROM dbo.Orders as o JOIN dbo.Customers as c on o.CustomerID = c.CustomerID
JOIN dbo.[Order Subtotals] as os on o.OrderID = os.OrderID
)

SELECT TOP (20) PERCENT CompanyName, SUM(Subtotal) as 'CustSum', ( SELECT SUM(Subtotal) FROM dbo.[Order Subtotals] ) as TotalSum
FROM Tab
GROUP BY CompanyName
ORDER BY CustSum DESC;

[collapse]

Zadanie 15.

Utwórz zapytanie, które pokaże nr zamówienia, nazwę firmy zamawiającej oraz wartość zamówienia. Zadanie rozwiąż na dwa różne sposoby.

Baza: Northwind, Tabele: dbo.Orders, dbo.Customers, dbo.Order Subtotals (widok), Kolumny: OrderID, CustomerID, CompanyName, Subtotal, Wynik: 830 rows

s15

 

 

 

Rozwiązanie

1. Z użyciem złączenia tabel:

SELECT o.OrderID, c.CompanyName, os.Subtotal
FROM dbo.Orders as o JOIN dbo.Customers as c on o.CustomerID = c.CustomerID
JOIN dbo.[Order Subtotals] as os on o.OrderID = os.OrderID;

2. Z wykorzystaniem podzapytań skorelowanych:

SELECT o.OrderID,
( SELECT c.CompanyName FROM dbo.Customers as c WHERE o.CustomerID = c.CustomerID ) as 'CompanyName',
( SELECT os.Subtotal FROM dbo.[Order Subtotals] as os WHERE o.OrderID = os.OrderID ) as 'Subtotal'
FROM dbo.Orders as o;

[collapse]

Zadanie 14.

1. Utwórz wspólne wyrażenie tablicowe (CTE) nazwane ShortOrd, które zwróci ID zamówienia, ID klienta, ID pracownika, datę zamówienia oraz ID przewoźnika (z tabeli Orders).

2. Utwórz kwerendę zewnętrzną, opartą na powyższym wyrażeniu, która zwróci ID zamówienia, ID klienta, wartość zamówienia oraz nazwę przewoźnika, dotyczące zamówień złożonych w 1996 roku. (Uwaga: potrzebne będzie złączenie wyrażenia tablicowego z tabelą Shippers oraz widokiem Order Subtotals).

Baza: Northwind, Tabele: dbo.Orders, dbo.Shippers, dbo.Order Subtotals (widok), Kolumny: OrderID, CustomerID, EmployeeID, OrderDate, ShipVia, Subtotal, CompanyName, Wynik: 152 rows

s14

 

 

 

Rozwiązanie

WITH ShortOrd AS
(
SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipVia
FROM dbo.Orders
)

SELECT so.OrderID, so.CustomerID, os.Subtotal as Sum, s.CompanyName as Shipper
FROM ShortOrd as so JOIN dbo.Shippers as s on so.ShipVia = s.ShipperID
JOIN dbo.[Order Subtotals] as os on so.OrderID = os.OrderID
WHERE YEAR(so.OrderDate) = '1996'
ORDER BY so.CustomerID;

[collapse]
« Starsze wpsiy Recent Entries »