In [3]:
%load_ext sql
%sql sqlite:///chinook.db
Out[3]:
In [4]:
%%sql
SELECT t.name
FROM tracks t
INNER JOIN genres g
ON t.genreid = g.genreid
INNER JOIN media_types m
ON m.mediatypeid = t.mediatypeid
ORDER BY t.bytes desc
limit 10
Out[4]:
In [51]:
%%sql
SELECT distinct ar.name, t.name, a.title
FROM tracks t
INNER JOIN albums a
ON a.albumid = t.albumid
INNER JOIN artists ar
ON a.artistid = ar.artistid
INNER JOIN invoice_items i
ON i.trackid = t.trackid
INNER JOIN invoices ii
on ii.invoiceid = i.invoiceid
INNER JOIN customers c
ON ii.customerid = c.customerid
INNER JOIN genres g
ON t.genreid = t.genreid
WHERE c.company like '%Microsoft%'
AND g.name = 'Rock'
Out[51]:
In [12]:
%%sql
WITH A
AS
(
select g.genreid, g.name, m.mediatypeid, m.name as m_name, i.unitprice
from tracks t
inner join media_types m
on t.mediatypeid = m.mediatypeid
inner join genres g
on t.genreid = t.genreid
inner join invoice_items i
on i.trackid = t.trackid
)
select name, m_name, avg(unitprice) as avg_unitprice
from A
where not exists
(
select *
from A Inn
where A.genreid = Inn.genreid
and A.mediatypeid = Inn.mediatypeid
and unitprice <= 1.5)
group by genreid, mediatypeid, name, m_name
Out[12]:
In [17]:
%%sql
WITH A
AS
(
select c.company, count(i.invoiceid) as cnt
from customers c
inner join invoices i
on i.customerid = c.customerid
where company is not null
group by company
)
SELECT *
FROM A
where cnt in (select min(cnt) from A union select max(cnt) from A )
Out[17]:
In [21]:
%%sql
select c.company, count(*) as cnt
from tracks t
inner join genres g
on t.genreid = g.genreid
inner join invoice_items ii
on t.trackid = ii.trackid
inner join invoices i
on i.invoiceid = ii.invoiceid
inner join customers c
on c.customerid = i.customerid
where g.name = 'Pop'
and c.company is not null
group by c.company
Out[21]:
In [29]:
%%sql
Select avg(sb) as avg_sb
from
(
select al.title, sum(bytes) as sb
from albums al
inner join tracks t
on al.albumid = t.albumid
group by al.title
)
Out[29]:
In [ ]: