#1.
/*SELECT c.id,c.name FROM cities as c
WHERE country_code IN ("BG")
ORDER BY c.name ASC*/

#2.
/*SELECT
CASE
WHEN middle_name IS NULL THEN CONCAT(first_name, " ", last_name)
ELSE CONCAT(first_name, " ", middle_name, " " , last_name)
END as 'Full Name'
, YEAR(birth_date) BirthYear FROM accounts as acc
WHERE YEAR(acc.birth_date) > '1991'
ORDER BY YEAR(acc.birth_date) DESC, first_name ASC*/

#3.
/*SELECT city.name as City, count(hotels.id) as Hotels FROM cities as city
left join hotels ON city.id = hotels.city_id
group by city.name
ORDER BY count(hotels.id) DESC, city.name ASC*/


#4.
/*SELECT room.id as Id, room.price as Price, hotels.name as Hotel, cities.name as City
from rooms as room
left join hotels on room.hotel_id = hotels.id
left join cities on cities.id = hotels.city_id
where room.type = 'First-Class'
ORDER BY room.price DESC, room.id ASC*/

#2.5.
/*SELECT c.id,c.name FROM client
ORDER BY c.id ASC*/

#7
/*SELECT acc.id,acc.email,cit.name, count(*) as trips_count FROM accounts as acc
JOIN cities as cit on acc.city_id = cit.id
JOIN accounts_trips as AAA ON acc.id = AAA.account_id
JOIN trips as t ON AAA.trip_id = t.id
JOIN rooms as r ON t.room_id = r.id
JOIN hotels as h ON r.hotel_id = h.id
WHERE h.city_id = cit.id
GROUP BY acc.id
ORDER BY trips_count DESC, acc.id ASC;*/

#2.6
/*SELECT emp.id, concat(emp.first_name," ",emp.last_name) as full_name, emp.salary,emp.started_on FROM employees as emp
WHERE started_on > '2018-01-01'
AND salary >= 100000
ORDER BY salary ASC*/

#2.7
/*SELECT cards.id as id, CONCAT(cards.card_number," : ", client.full_name) as card_token FROM cards
inner join bank_accounts on cards.bank_account_id = bank_accounds.id
inner join client on bank_accounts.client_id = client_id
order by id desc*/


#8
/*SELECT bacc.account_number as acc_num, CONCAT('$', bacc.balance) as 'name', client.full_name
FROM cards
INNER JOIN bank_accounts as bacc ON cards.bank_account_id = bacc.id
INNER JOIN client ON bacc.client_id = client.id
WHERE bacc.id NOT IN (SELECT bank_account_id FROM cards WHERE card_status = 'Active')
GROUP BY bacc.id
ORDER BY bacc.id DESC;*/

#9???


#10
SELECT client.id, client.full_name, COUNT(cards.bank_account_id) as cards from cards
inner join bank_accounts as bacc on cards.bank_account_id = bacc.id
inner join client on bacc.client_id = bacc.id
group by (client.full_name)
order by cards desc, clients.id asc limit 1;