Napisz powitanie Hello World. Kolumna powinna nazywać się “Powitanie”.
Schemat: – , Tabela: dual, Kolumny: -, Wynik: 1 row
Rozwiązanie
SELECT 'Hello World' as Powitanie
FROM dual;
[collapse]
Zadanie 6.
1. Stwórz tabelę zawierającą pola: Imię, Nazwisko, Pesel, Data, gdzie Pesel będzie kluczem głównym; żadne z pól nie może być puste, z wyjątkiem Daty. Dobierz odpowiednie typy danych.
2. Uzupełnij tabelę danymi (dane w pełni fikcyjne, wymyślone na potrzeby ćwiczenia):
('Anna', 'Braun', '70010112365'),
('Celia', 'Donut', '74011112365'),
('Ellie', 'Fine', '85101512365'),
('Gloria', 'Hint', '79051412369'),
('Ian', 'Johnson', '82021685214'),
('Kelly', 'Long', '64081725874'),
('Merry', 'Nice', '89120425896');
3. Wypełnij kolumnę Data danymi – ma się w niej znaleźć data urodzenia danej osoby, ustalona na podstawie numeru pesel.
Baza: Northwind, Tabela: PeselTab (Twoja tabela), kolumny: Imie, Nazwisko, Pesel, Data, Wynik: 7 rows
Rozwiązanie
1. USE Northwind;
CREATE TABLE PeselTab (
Imie VARCHAR(20) NOT NULL,
Nazwisko VARCHAR(25) NOT NULL,
Pesel VARCHAR(11) PRIMARY KEY,
Data DATE );
2. INSERT INTO PeselTab(Imie, Nazwisko, Pesel) VALUES
('Anna', 'Braun', '70010112365'),
('Celia', 'Donut', '74011112365'),
('Ellie', 'Fine', '85101512365'),
('Gloria', 'Hint', '79051412369'),
('Ian', 'Johnson', '82021685214'),
('Kelly', 'Long', '64081725874'),
('Merry', 'Nice', '89120425896');
3. UPDATE PeselTab
SET Data = CAST(SUBSTRING(Pesel, 1, 6) as DATE);
[collapse]
Zadanie 5.
1. Utwórz tabelę ProductDiscount (ProductID, UnitPrice, Discount). Klucz główny ustaw na ProductID, a pole Discount domyślnie ma wartość 0.
Utwórz również tabelę ArchiwumPD (ProductID, OldDiscount, NewDiscount).
2. Wypełnij tabelę ProductDiscount danymi z tabeli Products (ProductID, UnitPrice).
3. Ustal rabat 9% dla 10 najdroższych produktów, oraz 6% dla kolejnych pięciu. Zarówno nowe jak i stare wartości rabatu wraz z id produktu przenieś do tabeli ArchiwumPD.
4. Dodaj kolumnę NewPrice, która przechowywać będzie cenę produktu po rabacie. Jeśli produkt nie ma przyznanego rabatu, w polu NewPrice widnieć powinno zero.
5. Przywróć wcześniejsze wartości rabatów dla produktów 7-12 (wzgl. ceny).
Baza: Northwind, Tabele: dbo.ProductDiscount, dbo.ArchiwumPD, Kolumny: ProductID, UnitPrice, Discount, NewPrice, OldDiscount, NewDiscount
Rozwiązanie
1. CREATE TABLE dbo.ProductDiscount (
ProductID INT PRIMARY KEY,
UnitPrice INT NOT NULL,
Discount NUMERIC(4,3) DEFAULT 0
);
CREATE TABLE dbo.ArchiwumPD (
ProductID INT PRIMARY KEY,
OldDiscount NUMERIC(4,3),
NewDiscount NUMERIC(4,3)
);
2. INSERT INTO dbo.ProductDiscount (ProductID, UnitPrice)
SELECT ProductID, UnitPrice
FROM dbo.Products;
3. Dla pierwszych 10:
WITH CTE AS (
SELECT ProductID, UnitPrice, Discount
FROM dbo.ProductDiscount
ORDER BY UnitPrice DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY
)
UPDATE CTE
SET Discount = 0.09
OUTPUT
deleted.ProductID,
deleted.Discount,
inserted.Discount
INTO dbo.ArchiwumPD(ProductID, OldDiscount, NewDiscount);
I kolejne 5:
WITH CTE AS (
SELECT ProductID, UnitPrice, Discount
FROM dbo.ProductDiscount
ORDER BY UnitPrice DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
)
UPDATE CTE
SET Discount = 0.06
OUTPUT
deleted.ProductID,
deleted.Discount,
inserted.Discount
INTO dbo.ArchiwumPD(ProductID, OldDiscount, NewDiscount);
Lub z wykorzystaniem tabeli pochodnej (analogicznie będzie przy 10 najdroższych produktach):
UPDATE dbo.ProductDiscount
SET Discount = 0.06
OUTPUT
deleted.ProductID,
deleted.Discount,
inserted.Discount
INTO dbo.ArchiwumPD(ProductID, OldDiscount, NewDiscount)
WHERE ProductID IN (SELECT ProductID
FROM dbo.ProductDiscount
ORDER BY UnitPrice DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY);
4. ALTER TABLE dbo.ProductDiscount
ADD NewPrice NUMERIC(5,2);
UPDATE dbo.ProductDiscount
SET NewPrice = UnitPrice*(1 - Discount);
UPDATE dbo.ProductDiscount
SET NewPrice = 0
WHERE Discount = 0;
5. WITH CTE AS (
SELECT PD.Discount as Disc, APD.OldDiscount as OldDisc
FROM dbo.ProductDiscount as PD LEFT JOIN dbo.ArchiwumPD as APD
on PD.ProductID = APD.ProductID
ORDER BY UnitPrice DESC
OFFSET 6 ROWS FETCH NEXT 6 ROWS ONLY
)
UPDATE CTE
SET Disc = OldDisc;
I aktualizujemy ceny w kolumnie NewPrice:
UPDATE dbo.ProductDiscount
SET NewPrice = UnitPrice*(1 - Discount);
UPDATE dbo.ProductDiscount
SET NewPrice = 0
WHERE Discount = 0;
[collapse]
Zadanie 4.
1. Skopiuj tabelę dbo.Orders za pomocą SELECT INTO (tworząc tabelę dbo.Ord2).
2. Tabeli dbo.Ord2 przypisz klucz główny na kolumnie OrderID
3. Dodaj kolumnę ShipDelay, w której określimy, czy towar został dostarczony na czas.
4. Wypełnij kolumnę ShipDelay danymi (RequiredDate – ShippedDate)
5. Zmodyfikuj tabelę wstawiając 0 (zero) tam, gdzie nie było opóźnień w dostawie.
6. Sprawdź, czy są klienci którzy dostali towar z opóźnieniem, oraz czy złożyli jeszcze jakieś zamówienia (później).
Baza: Northwind, Tabela: dbo.Orders / Ord2, Kolumny: CustomerID, OrderID, RequiredDate, ShippedDate, ShipDelay, Wynik: klienci, którzy złożyli kolejne zamówienia po otrzymaniu dostawy z opóźnieniem: 33, Klienci, którzy po opóźnionej dostawie nie złożyli już zamówień: 4, Klienci, którzy po pierwszym opóźnieniu złożyli kolejne zamówienie, ale po drugim już nie: 1
Rozwiązanie
1.
USE Northwind;
SELECT *
INTO dbo.Ord2
FROM dbo.Orders;
2.
ALTER TABLE dbo.Ord2
ADD CONSTRAINT OrderID_pk PRIMARY KEY (OrderID);
3.
ALTER TABLE dbo.Ord2
ADD ShipDelay INT;
4.
WITH CTE AS (
SELECT OrderID, CustomerID, RequiredDate, ShippedDate, ShipDelay, DATEDIFF(day, RequiredDate, ShippedDate) as DaysDelay
FROM dbo.Ord2
)
UPDATE CTE
SET ShipDelay = DaysDelay;
5.
UPDATE dbo.Ord2
SET ShipDelay = 0
WHERE ShipDelay <= 0;
Albo z użyciem wyrażeń tablicowych:
WITH CTE2 AS (
SELECT ShipDelay,
CASE
WHEN ShipDelay <= 0 THEN 0
ELSE ShipDelay
END AS SD
FROM dbo.Ord2
)
UPDATE CTE2
SET ShipDelay = SD;
6. Klienci, którzy złożyli kolejne zamówienia po otrzymaniu dostawy z opóźnieniem:
SELECT CustomerID, OrderID
FROM dbo.Ord2
WHERE ShipDelay > 0
AND EXISTS (
SELECT *
FROM dbo.Ord2 as O
WHERE Ord2.CustomerID = O.CustomerID
AND O.OrderID > Ord2.OrderID
);
Klienci, którzy po opóźnionej dostawie nie złożyli już zamówień:
SELECT CustomerID, OrderID
FROM dbo.Ord2
WHERE ShipDelay > 0
AND NOT EXISTS (
SELECT *
FROM dbo.Ord2 as O
WHERE Ord2.CustomerID = O.CustomerID
AND O.OrderID > Ord2.OrderID
);
Klienci, którzy po pierwszym opóźnieniu złożyli kolejne zamówienie, ale po drugim już nie:
SELECT CustomerID
FROM dbo.Ord2
WHERE ShipDelay > 0
AND EXISTS (
SELECT *
FROM dbo.Ord2 as O
WHERE Ord2.CustomerID = O.CustomerID
AND O.OrderID > Ord2.OrderID
)
INTERSECT
SELECT CustomerID
FROM dbo.Ord2
WHERE ShipDelay > 0
AND NOT EXISTS (
SELECT *
FROM dbo.Ord2 as O
WHERE Ord2.CustomerID = O.CustomerID
AND O.OrderID > Ord2.OrderID
);
[collapse]
Zadanie 26.
Wybierz wszystkich klientów, którzy zamówili produkty o id 1 i 55. Zadanie rozwiąż na dwa sposoby.
Baza: Northwind, Tabele: dbo.Order Details, dbo.Orders, Kolumny: CustomerID, OrderID, ProductID, Wynik: 16 rows
Rozwiązanie
1. Z użyciem podzapytań i Exists:
SELECT DISTINCT O.CustomerID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE OD.ProductID = 1
AND EXISTS (
SELECT *
FROM dbo.[Order Details] OD2 JOIN dbo.Orders O2
ON OD2.OrderID = O2.OrderID
WHERE O.CustomerID = O2.CustomerID
AND OD2.ProductID = 55
)
ORDER BY O.CustomerID;
2. Z użyciem operatorów zbiorowych:
SELECT O.CustomerID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE OD.ProductID = 1
INTERSECT
SELECT O.CustomerID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE OD.ProductID = 55
ORDER BY O.CustomerID;
[collapse]
Zadanie 3.
1. Utwórz sekwencję (dbo.MySequence) typu INT, o wartości minimalnej 1, z inkrementacją o 1.
2. Utwórz tabelę (dbo.Zabawki) zawierającą kolumny: ID (INT, domyślna wartość to kolejna wartość z sekwencji), KodProduktu (INT, NOT NULL), NazwaProduktu (VARCHAR(20), NOT NULL), Cena (INT, NOT NULL), Kategoria (VARCHAR(15), NOT NULL)
3. Wypełnij tabelę danymi (kod produktu, nazwa, cena, kategoria):
(12064, 'Lego Technic', 565, 'Zabawki'),
(12065, 'Lego City', 129, 'Zabawki'),
(12066, 'Lego City', 155, 'Zabawki'),
(12067, 'Lego Technic', 499, 'Zabawki'),
(12080, 'Need For Speed', 99, 'Gry'),
(12081, 'Tomb Raider', 99, 'Gry'),
(12082, 'The Sims', 159, 'Gry'),
(12083, 'Diablo', 179, 'Gry');
4. Usuń najdroższy produkt z każdej kategorii.
5. Do tabeli dodaj kolumnę Rabat, z wartością domyślną 0.
6. Przyznaj rabat wysokości 15% produktom o kodzie 12082, 12067.
7. Ustaw wartość rabatu na zero wszędzie tam, gdzie ma ma ona wartość NULL.
8. Usuń tabelę dbo.Zabawki oraz sekwencję dbo.MySequence.
Baza: Northwind, Tabela: dbo.Zabawki, Kolumny: ID, KodProduktu, Nazwaproduktu, Cena, Kategoria, Wynik: 6 rows (po modyfikacjach)
Rozwiązanie
1. CREATE SEQUENCE dbo.MySequence AS INT
MINVALUE 1;
–inkrementacja o 1 jest ustawiona domyślnie
2. CREATE TABLE dbo.Zabawki (
ID INT NOT NULL DEFAULT(NEXT VALUE FOR dbo.MySequence),
KodProduktu INT NOT NULL,
NazwaProduktu VARCHAR(20) NOT NULL,
Cena INT NOT NULL,
Kategoria VARCHAR(10) NOT NULL
);
3. INSERT INTO dbo.Zabawki (KodProduktu, NazwaProduktu, Cena, Kategoria)
VALUES
(12064, 'Lego Technic', 565, 'Zabawki'),
(12065, 'Lego City', 129, 'Zabawki'),
(12066, 'Lego City', 155, 'Zabawki'),
(12067, 'Lego Technic', 499, 'Zabawki'),
(12080, 'Need For Speed', 99, 'Gry'),
(12081, 'Tomb Raider', 99, 'Gry'),
(12082, 'The Sims', 159, 'Gry'),
(12083, 'Diablo', 179, 'Gry');
SELECT * FROM dbo.Zabawki;
4. DELETE FROM dbo.Zabawki
WHERE Cena in (
SELECT MAX(Cena)
FROM dbo.Zabawki
GROUP BY Kategoria
);
5. ALTER TABLE dbo.Zabawki
ADD Rabat INT DEFAULT(0);
6. UPDATE dbo.Zabawki
SET Rabat = 15
WHERE KodProduktu in(12082, 12067);
7. UPDATE dbo.Zabawki
SET Rabat = 0
WHERE Rabat IS NULL;
8. DROP TABLE dbo.Zabawki;
DROP SEQUENCE dbo.MySequence;
[collapse]
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)
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.
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
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
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]