Autor Kategorii: Agnieszka Włodarczyk

Zadanie 5.

Korzystając z tabeli Jobs, znajdź:

a) największą płacę minimalną

b) najmniejszą płacę maksymalną

c) dla każdego stanowiska określ różnicę między płacą minimalną a maksymalną

Schemat: HR, Kolumny: Min_Salary, Max_Salary, Job_Title, Wynik: a) 1 row b) 1 row c) 19 rows

Rozwiązanie

a) SELECT max(min_salary)
FROM Jobs;

b) SELECT min(max_salary)
FROM Jobs;

c) SELECT Job_Title, (Max_salary - Min_Salary) as "Max - Min"
FROM Jobs;

[collapse]

Zadanie 4.

Z tabeli Job_History wybierz:

a) pracowników, którzy zostali zatrudnieni między rokiem 2005 a 2006

b) pracowników, którzy odeszli przed 2002 rokiem

c) pracowników o job_id równym AC_ACCOUNT lub ST_CLERK nie należą do departamentu o numerze 110

Schemat: HR, Tabela:  Job-History, Kolumny: Start_Date, End_Date, Job_ID, Department_ID, Wynik: a) 2 rows b) 2 rows c) 3 rows

Rozwiązanie

a) SELECT *
FROM job_history
WHERE START_DATE BETWEEN '05/01/01' AND '06/12/31' ; --RR/MM/DD

b) SELECT *
FROM job_history
WHERE END_DATE <= '01/12/31' ;

c) SELECT *
FROM job_history
WHERE (JOB_ID  = 'AC_ACCOUNT' OR JOB_ID = 'ST_CLERK') AND DEPARTMENT_ID <> 110;

[collapse]

Zadanie 3.

Z tabeli Locations wybierz:

a) Wszystkie adresy w Londynie

b) Wszystkie lokalizacje które są w państwach o id IT, US i CH

c) Lokalizacje, w których nazwa miasta nie zaczyna się na literę S ale ma tą literę w nazwie

d) Lokacje, które nie mają regionu. Pole region powinno zawierać informację ‚<brak regionu>’.

Schemat: HR, Tabela: Locations, Kolumny: City, Country-ID, State_Province  Wynik: a) 1 row b) 8 rows c) 2 rows d) 6 rows

Rozwiązanie

a) SELECT *
FROM LOCATIONS
WHERE City = 'London';

b) SELECT *
FROM LOCATIONS
WHERE COUNTRY_ID IN('IT', 'US', 'CH');

c) SELECT *
FROM LOCATIONS
WHERE City NOT LIKE 'S%' AND City LIKE '_%s%';

d) SELECT STREET_ADDRESS, POSTAL_CODE, CITY, NVL(STATE_PROVINCE, '<brak regionu>'), COUNTRY_ID
FROM LOCATIONS
WHERE STATE_PROVINCE IS NULL;

[collapse]

Zadanie 2.

Z tabeli Employees wybierz:

a) wszystkich o job_id równym SA_REP

b) z wynagrodzeniem pomiędzy 2000 a 5000

c) zatrudnieni przed rokiem 2003

d) tych, którzy nie pracują w departamentach o ID 50 oraz 80

e) tych, których numer telefonu zawiera dwie cyfry 7 (w dowolnym miejscu)

Schemat: HR, Tabela: Employees, Kolumny: Job_ID, Salary, Hire_Date, Department_ID, Phone_Number, Wynik: a) 30 rows, b) 49 rows c) 8 rows d) 27 rows e) 6 rows

Rozwiązanie

a) SELECT *
FROM EMPLOYEES
WHERE job_id = 'SA_REP';

b) SELECT *
FROM EMPLOYEES
WHERE salary BETWEEN 2000 AND 5000;

c) SELECT *
FROM EMPLOYEES
WHERE HIRE_DATE < '03/01/01'; --RR/MM/DD

d) SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID <> 50 AND DEPARTMENT_ID <> 80;

e) SELECT *
FROM EMPLOYEES
WHERE PHONE_NUMBER LIKE '%7%7%';

[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

z6

 

 

 

 

 

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

z5

 

 

 

 

 

 

 

 

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

s26

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