select * from albums;
--- komentar
select customerid, firstname, country from customers
where Country = 'USA'

select Country, count(customerid) as pocet_zakazniku_dane_zeme from customers
where Country = 'USA'
group by Country

select distinct Country from customers

select * from employees
---where Title = 'Sales Manager'
order by Title desc

%like 'Sales M'

select distinct Title FROM Employees



---funkce where: vybira podminku zobrazeni, zde podminka "Sales Manager" pozor na typ uvozovekalbums"" ceske, '' anglicek, nkekdz funguji obojialbums
select distinct Title from employees;
---nasli jsme jednoho sales managera, Nancz Edwardsalbums
select a.firstname, a.lastname, c.invoiceid from employees as a
left join customers b
on a.employeeid = b.supportrepid
left join invoices c
on b.customerid = c.customerid
---where a.Title = 'Support Manager'
---vytvorili jsme seznam vsech faktur nasich zakazniku, join pres tri tabulky dle diagramu, diagram ulozim tez 
select d.name, e.invoiceLineId from tracks d ---v dokumentaci se jmenuje atribut jinak nez v db:D
inner join  invoice_items e
on d.trackid = e.trackid

select d.name, e.invoiceLineId, e.invoiceid from tracks d ---v dokumentaci se jmenuje atribut jinak nez v db:D
left join  invoice_items e
on d.trackid = e.trackid
---order by d.name asc
---pismena za nazvem tabulky oznaci tabulku po cely select v pripade stejneho nazvu atributu v obou tabulkach jasne rikaji odkud ma kod vybiratalbums
---inner join je prusecik obou tabulek
---done
select a.employeeid,a.LastName,sum(d.unitprice*d.quantity) ---pocita obrat a  diky nize zapsane agregacni funkci group by pocita sumu toho nasobeni pres radky
as obrat_prodejce from employees as a
left join customers b
on a.employeeid = b.supportrepid
left join invoices c
on b.customerid = c.Customerid
left join invoice_items d
on d.invoiceid  = c.invoiceid
---
select a.employeeid,a.LastName,unitprice ---pocita obrat a  diky nize zapsane agregacni funkci group by pocita sumu toho nasobeni pres radky
as obrat_prodejce from employees as a
left join customers b
on a.employeeid = b.supportrepid
left join invoices c
on b.customerid = c.Customerid
left join invoice_items d
on d.invoiceid  = c.invoiceid
where a.Title = 'Sales Support Agent'

drop table x

create table x as 
select 
	a.employeeid
	, a.LastName
    ,sum(d.unitprice) as obrat_prodejce
    ,a.title
from employees as a
left join customers b
on a.employeeid = b.supportrepid
left join invoices c
on b.customerid = c.Customerid
left join invoice_items d
on d.invoiceid  = c.invoiceid
----where a.Title = 'Sales Manager'  ---neni zde potreba, vybira sales managery ze zamestnancu
where a.Title = 'Sales Support Agent'
group by a.EmployeeId ---scita pres radky dostupne zamestnance

create  table y as
select count(title) as pocet_zam_ve_funkcich, title from employees
group by title;

select * from y
order by pocet_zam_ve_funkcich

select * from x
order by obrat_prodejce desc

---serazeno vzestupne,  prvni hodnota je maximum, seradit tabuliu> prikaz order by
---A to je pro dnesek vse, pratele, dekuji za pozornost!
---vyexportujete takhle>