PUTIN SECRET RECIPE FOR VODKA

https://www.postgresql.org/docs/9.1/functions-formatting.html

cv. 5
2.2

SELECT
    invoicedate,
    concat(
        "billingaddress",
        ' ',
        "billingcity",
        ', ',
        "billingpostalcode"
        ) as address
FROM
    invoice
WHERE billingcountry = 'United Kingdom'
AND DATE_PART('month', invoicedate) = '5'
AND DATE_PART('year', invoicedate) = '2013';

2.3

SELECT
    "firstname",
    "lastname",
    to_char("birthdate", 'DD.MM.YY') as birthdate,
    ROUND(EXTRACT('days' FROM ('2021-02-10' - "birthdate" ) ) / 365) as age
FROM employee;

--

SELECT
    COUNT(*) as albums_count
FROM album
    WHERE artistid = (
        SELECT a.artistid FROM artist as a WHERE a.name = 'U2' LIMIT 1
    )
;

--

SELECT
    COUNT(*) as count,
    a.name
FROM album al
INNER JOIN artist a on a.artistid = al.artistid
GROUP BY (al.artistid), a.name
;

-----------------------------------------------------
-----------------------------------------------------
-----------------------------------------------------


6

1.1

SELECT *
FROM track
WHERE mediatypeid = (
    SELECT mediatypeid
    FROM mediatype
    WHERE name LIKE '%video%' LIMIT 1
);

1.2

SELECT
    firstname,
    lastname,
    phone
FROM customer
WHERE supportrepid = (SELECT employeeid FROM employee WHERE firstname = 'Margaret' AND lastname = 'Park')
ORDER BY lastname, firstname
;

3.1

SELECT
   name
FROM artist
LEFT OUTER JOIN album a on artist.artistid = a.artistid
GROUP BY albumid, artist.name
HAVING
    COUNT(albumid) = 0;


3.2

SELECT
    concat(
        e.lastname, ' ', e.firstname
        ) as zamestnanec,
        case
            when e.reportsto is null then concat( e.lastname, ' ', e.firstname )
            else concat( em.lastname, ' ', em.firstname )
        end
        as nadriadeny
FROM employee as e
LEFT JOIN employee as em on em.employeeid = e.reportsto
;