Tagi: select

Zadanie 10.

Z tabeli Customers (OE) wybierz:

a) klientów urodzonych w czerwcu, pochodzących z Ameryki

b) wszystkie zamężne włoszki (Italy)

c) klientów których nazwisko nie zaczyna się na L, ale zawiera ‚ea’

Schemat: OE, Tabela: Customers, Kolumny: Date_Of_Birth, nls_territory, gender, marital_status, cust_last_name, Wynik: a) 17 rows b) 8 rows c) 3 rows

Rozwiązanie

a) SELECT *
FROM customers
WHERE Extract(month from date_of_birth) = '06' AND nls_territory = 'AMERICA';

b) SELECT *
FROM customers
WHERE nls_territory = 'ITALY' AND gender = 'F' AND marital_status = 'married';

c) SELECT *
FROM customers
WHERE cust_last_name NOT LIKE 'L%' AND cust_last_name LIKE '%ea%';

[collapse]

 

Zadanie 9.

Tabela Employees (HR).

a) Policz wartość wynagrodzenia powiększoną o premię (commission_pct). Weź pod uwagę, że niektórzy nie mają premii (wtedy commission_pct ma wartość NULL).

b) Policz, który pracownik ile dostaje premii (jak wyżej – bierzemy pod uwagę istnienie NULL)

c) Do zestawienia z punktu b) dodaj obliczony staż pracy w latach (by wyniki były jednolite zakładamy, że wszyscy pracowali do 1 sierpnia 2008).

Schemat: HR, Tabela: Employees, Kolumny: First_Name, Last_Name, Salary, Commission_pct, Hire_Date, Wynik: 107 rows (a, b, c)

Rozwiązanie

a) SELECT first_name, last_name, salary * (1 + NVL(COMMISSION_PCT, 0)) as FullSalary
FROM Employees;

b) SELECT first_name, last_name, salary * NVL(COMMISSION_PCT, 0) as Premia
FROM Employees;

c) SELECT first_name, last_name, salary * NVL(COMMISSION_PCT, 0) as Premia,
TRUNC((to_date('2008-08-01', 'YYYY-MM-DD') - Hire_Date) / 365, 1) as Staz_Pracy_Lata
FROM Employees
ORDER BY Premia DESC, Staz_Pracy_Lata DESC;

[collapse]

Zadanie 7.

Korzystając z tabeli Employees (HR), stwórz zestawienie pracowników, tak jak przedstawiono na screenie, dla pracowników zarabiających między 10 a 14 tysięcy; wynik posortuj według zarobków (malejąco).

Schemat: HR, Tabela: Employees, Kolumny: First_Name, Last_name, Hire_Date, Salary, Wynik: 16 rows

07

 

 

Rozwiązanie

SELECT first_name||' '||last_name||' jest z nami od '||to_char(hire_date, 'DD.MM.YYYY')||' i zarabia '||salary as Zestawienie
FROM Employees
WHERE Salary BETWEEN 10000 AND 15000
ORDER BY Salary DESC;

[collapse]

Zadanie 6.

Z tabeli Orders (OE) wybierz zamówienia:

a) złożone w marcu 2007 roku

b) złożone w trybie innym niż direct i o statusie 4 lub 8

c) 10 zamówień o najwyższej wartości

Schemat: OE, Tabela: Orders, Kolumny: Order_Date, Order_Mode, Order-Status, Order_Total, Wynik: a) 7 rows b) 12 rows c) 10 rows

Rozwiązanie

a) SELECT *
FROM Orders
WHERE Order_Date >= To_Date('2007-03-01') AND Order_Date < To_Date('2007-04-01');

b) SELECT *
FROM Orders
WHERE Order_Mode <> 'direct' AND (Order_Status = 4 OR Order_Status = 8);

c) SELECT *
FROM Orders
ORDER BY Order_Total DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

[collapse]

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 30.

Jakie wyniki otrzymamy po uruchomieniu poniższego kodu?

a) SELECT 2 * 2,5

b) SELECT OrderID OrderDate FROM dbo.Orders;

Baza: Northwind

Rozwiązanie


a) 30a

Do określania miejsc dziesiętnych służy kropka, nie przecinek. Powyższy kod inaczej można zapisać jako SELECT (2 * 2), 5. Jeśli chcemy przemnożyć wartości, trzeba zamiast przecinka wstawić kropkę.

b) 30b

Zamiast spodziewanych dwóch kolumn, dostajemy jedną, w dodatku nazwa się nie zgadza. Dzieje się tak dlatego, że nie ma przecinka pomiędzy OrderID a OrderDate, co oznacza nadanie aliasu. Zapis taki jest jest równoważny z "SELECT OrderID as OrderDate".

[collapse]