Autor Kategorii: Agnieszka Włodarczyk

Zadanie 2.

1. Utwórz tabelę (u mnie nazywa się Tab1) zawierającą trzy kolumny: id (będącą identyfikatorem, zaczynającym od wartości 1 z inkrementacją o 1), orderid (typ numeryczny), company (typ tekstowy). Żadna z kolumn nie przyjmuje wartości NULL.

2. Utworzoną tabelę wypełnij danymi, pochodzącymi z tabeli dbo.Orders; niech będzie to 10 najnowszych zamówień (OrderID) oraz odpowiadające im firmy (CustomerID).

3. Dodaj wiersz z numerem zamówienia 11078 i firmą LILAS.

4. Usuń pierwsze trzy wiersze (id od 1 do 3).

5. Zmień nazwę firmy z LILAS na ABCDE.

6. Zwróć przedostatni dodany rekord (bazując na identyfikatorze id).

7. Usuń tabelę stworzoną w punkcie 1.

Baza: Northwind, Tabela: dbo.Orders, Kolumny: OrderID, CustomerID, Wynik: 8 rows (po modyfikacjach)

z2

 

 

 

 

 

 

 

Rozwiązanie

1. CREATE TABLE dbo.Tab1 (
id INT NOT NULL IDENTITY(1,1),
orderid INT NOT NULL,
company VARCHAR(10) NOT NULL
);

 

2. INSERT INTO dbo.Tab1(orderid, company)
SELECT OrderID, CustomerID
FROM dbo.Orders
ORDER BY OrderID DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

 

3. INSERT INTO dbo.Tab1(orderid, company)
VALUES (11078, 'LILAS');

 

4. DELETE FROM dbo.Tab1
WHERE id in (1, 2, 3);

 

5. UPDATE dbo.Tab1
SET company = 'ABCDE'
WHERE company = 'LILAS';

 

6. SELECT *
FROM dbo.Tab1
WHERE id = IDENT_CURRENT('dbo.Tab1') - 1;

 

7. DROP TABLE dbo.Tab1;

[collapse]

Zadanie 1.

1. Całą zawartość tabeli dbo.Employees (baza Northwind) przekopiuj do dbo.Emp1 (za pomocą polecenia SELECT INTO). W dalszej części modyfikujemy tabelę dbo.Emp1.

2. Dla pracownika o ID 3, do imienia dodaj ‘A.’ (np. Jan -> Jan A.)

3. Pracownikowi o ID 8 zmień nazwisko na Smith.

4. Usuń dwóch najstarszych pracowników (wg daty urodzenia).

5. Po wykonaniu ćwiczeń usuń tabelę dbo.Emp1

Baza: Northwind, Tabela: dbo.Employees, Kolumny: EmployeeID, FirstName, LastName, BirthDate

Screen przedstawia zawartość tabeli po wykonaniu punktów 1-4.

z1

 

 

 

 

 

 

 

Rozwiązanie

1. SELECT * INTO dbo.Emp1
FROM dbo.Employees;

2. UPDATE dbo.Emp1
SET FirstName = FirstName + ' A.'
WHERE EmployeeID = 3;

3. UPDATE dbo.Emp1
SET LastName = 'Smith'
WHERE EmployeeID = 8;

4. DELETE FROM dbo.Emp1
WHERE EmployeeID IN (
SELECT TOP(2) EmployeeID
FROM dbo.Emp1
ORDER BY BirthDate);

5. DROP TABLE dbo.Emp1;

[collapse]

Zadanie 25.

Policz, ile w firmie pracuje kobiet, a ilu mężczyzn. Wynik podaj również w procentach.

Baza: AdventureWorks, Tabela: HumanResources.Employee, Kolumny: Gender, Wynik: 2 rows

s25

Rozwiązanie

WITH CTE AS (
SELECT Gender, COUNT(*) as num
FROM HumanResources.Employee
GROUP BY Gender
)

SELECT Gender, num, CAST(100. * num / SUM(num) OVER() as DECIMAL(5,2)) as Pct
FROM CTE;

[collapse]

Zadanie 24.

Wcielmy w życie zasadę Pareto (zasada 80/20). Mówi ona, że tylko ok. 20% klientów firmy odpowiada za ok. 80% jej przychodów. Sprawdźmy, czy podobna relacja zachodzi i tu.

1. Utwórz zapytanie, które zwróci nazwę firmy, łączną wartość zamówień firmy, łączną ogólną wartość zamówień.

2. Zmodyfikuj zapytanie tak, by zwracało również procentowy udział wartości zamówień danej firmy.

3. Powyższe zapytanie zmodyfikuj jeszcze raz, tak, aby pokazywało również zsumowane punkty procentowe (poczynając od najwyższych wartości).

Baza: Northwind, Tabela: Sales Totals by Amount (widok), Kolumny: CompanyName, SaleAmount, Wynik: 31 rows

s24

 

 

 

 

Rozwiązanie

1. SELECT DISTINCT CompanyName, SUM(SaleAmount) OVER(PARTITION BY CompanyName) as TotalByCust, SUM(SaleAmount) OVER() as Total
FROM dbo.[Sales Totals by Amount];

 

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

 

3. WITH CTE AS (
SELECT DISTINCT CompanyName, SUM(SaleAmount) OVER(PARTITION BY CompanyName) as TotalByCust, SUM(SaleAmount) OVER() as Total,
CAST(100. * SUM(SaleAmount) OVER(PARTITION BY CompanyName) / SUM(SaleAmount) OVER() as DECIMAL(5,2)) as Pct
FROM dbo.[Sales Totals by Amount]
)

SELECT CompanyName, TotalByCust, Total, Pct, SUM(Pct) OVER(ORDER BY Pct DESC) as PctSum
FROM CTE
ORDER BY Pct DESC;

[collapse]

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]
« Starsze wpsiy Recent Entries »