Täältä löydät kuusi lyhyttä näytettä osaamisestani, joissa painottuvat nopeat haut tietokannasta ja muutokset tietokantaan. Tietokantana toimii The Sakila Database. ERD löytyy täältä.

1. Vuokrausmäärät vuosilta 2005 ja 2006 kuukausittain.

SELECT EXTRACT(YEAR FROM payment_date) AS year,
TO_CHAR(payment_date, 'Month') AS month,
COUNT(*) AS rental_count
FROM payment
GROUP BY year, month, TO_CHAR(payment_date, 'MM')
HAVING EXTRACT(YEAR FROM payment_date) IN (2005, 2006)
ORDER BY year DESC, TO_CHAR(payment_date, 'MM') ASC;

2. TOP 5 henkilöiden sähköpostiosoitteet, jotka ovat ostaneet rahallisesti eniten vuonna 2005.

SELECT payment.customer_id, email, SUM(amount) AS total FROM payment
LEFT OUTER JOIN customer
ON payment.customer_id = customer.customer_id
WHERE EXTRACT(YEAR FROM payment_date) = 2005
GROUP BY payment.customer_id, email
ORDER BY total DESC
LIMIT 5;

3. Sen työntekijän nimi ja osoite, joka on vuokrannut elokuvia eniten kappalemäärällisesti.

SELECT staff.first_name, staff.last_name, address.address, city.city, COUNT(*) AS rented_products
FROM rental
INNER JOIN staff ON rental.staff_id = staff.staff_id
INNER JOIN address ON staff.address_id = address.address_id
INNER JOIN city ON address.city_id = city.city_id
GROUP BY staff.first_name, staff.last_name, address.address, city.city
ORDER BY rented_products DESC
LIMIT 1;

4. Elokuviin, joissa MPA (Motion Picture Association) on NC-17 tai R halutaan 10 % hinnan korotus korvaushintaan (replacement_cost). Tiedot tulee päivittää tietokantaan siten, että sekä uusi että vanha korvaushinta näkyy.

ALTER TABLE film
ADD COLUMN replacement_cost_new NUMERIC(5,2); -- add new column

UPDATE film
SET replacement_cost_new = replacement_cost; -- set initial values for all film

UPDATE film
SET replacement_cost_new = 1.10 * replacement_cost_new
WHERE rating IN ('NC-17', 'R'); -- set new values if MPA is NC-17 or R

ALTER TABLE film
ALTER COLUMN replacement_cost_new SET NOT NULL; -- set the NOT NULL constraint

***************************************

-- check next
-- total rows in film table: 1000
-- Total rows if ratings are NC-17 or R: 405

SELECT * FROM film
WHERE rating IN ('NC-17', 'R') AND replacement_cost != replacement_cost_new; -- result, total rows: 405

-- Total rows if ratings are NOT NC-17 or R: 595

SELECT * FROM film
WHERE rating NOT IN ('NC-17', 'R') AND replacement_cost = replacement_cost_new; -- result, total rows: 595

***************************************

-- short samples

SELECT title, rating, replacement_cost, replacement_cost_new FROM film
WHERE rating IN ('NC-17', 'R');

SELECT title, rating, replacement_cost, replacement_cost_new FROM film
WHERE rating NOT IN ('NC-17', 'R');

5. Tietokannasta halutaan usein tietoja asiakkaiden osoitteista. Tällä kertaa halutaan tietää TOP 3 maata, joissa asiakkaat asuvat. Tulevia kyselyitä varten muodostetaan objekti (a database object, VIEW) tiedon hakemisen tehostamiseksi.

-- define a view

CREATE VIEW customer_address AS
SELECT first_name, last_name, address, city, country FROM customer
INNER JOIN address
ON customer.address_id = address.address_id
INNER JOIN city
ON address.city_id = city.city_id
INNER JOIN country
ON city.country_id = country.country_id;

-- top 3 countries where customers are located

SELECT country, COUNT(*) AS total FROM customer_address
GROUP BY country
ORDER BY total DESC
LIMIT 3;

6. On suunnitteilla asiakkuus-ohjelma. Kultatasolle pääsee asiakkaat, jotka ovat vuokranneet tai ostaneet vähintään kahdella sadalla. Hopeataso edellyttää 150,00 – 199,99 ja pronssitaso 100,00 – 144,99 ostoja. Halutaan tietää, kuinka moni asiakas yltää kyseisille tasoille tällä hetkellä.

SELECT
     SUM(CASE
              WHEN amount_total >= 200 THEN 1 ELSE 0
              END) AS gold,
     SUM(CASE
              WHEN amount_total >= 150 AND amount_total < 200 THEN 1 ELSE 0
              END) AS silver,
     SUM(CASE
              WHEN amount_total >= 100 AND amount_total < 150 THEN 1 ELSE 0
              END) AS bronze,
     SUM(CASE
              WHEN amount_total < 100 THEN 1 ELSE 0
              END) AS normal
FROM (SELECT customer_id, SUM(amount) AS amount_total FROM payment
              GROUP BY customer_id) AS subquery