1-qism select* from invoice where invoiceDate like '2013%' 2-qism

MA’LUMOTLAR TAHLILCHISI Bo‘ling

1-laboratoriya
1-qism
SELECT*
FROM Invoice
WHERE InvoiceDate like '2013%'

2-qism
SELECT FirstName, LastName, Country
FROM Customer
Where
((Country='Brazil')OR(Country='Canada')OR(Country='India')OR(Country='Sweden'))

3-qism
SELECT t.Name, t.Composer, a.Title
FROM Album as a
JOIN Track as t
ON t.AlbumId=a.AlbumId
WHERE t.Name LIKE "A%" AND t.Composer IS NOT NULL;

4-qism
SELECT c.FirstName, c.LastName, i.Total
FROM Customer c
JOIN Invoice i
ON c.customerid=i.customerid
ORDER BY i.Total DESC LIMIT 10

2-labaratoriya

1-qism
SELECT BillingCountry AS billingCountry, COUNT(*) AS Invoices FROM Invoice GROUP BY BillingCountry ORDER BY Invoices DESC;

2-qism
SELECT BillingCity , SUM(Total) AS sum_invoices FROM Invoice GROUP BY 1 ORDER BY 2 desc LIMIT 1

3-qism
SELECT c.CustomerId ,c.FirstName, c.LastName, SUM(i.Total) Invoices FROM Invoice i JOIN Customer c ON c.CustomerId = i.CustomerId GROUP BY 1,2,3 ORDER BY 4 DESC limit 1

4-qism
SELECT distinct(c.Email), c.FirstName , c.LastName, g.Name as Genre FROM Customer c JOIN Invoice i ON c.CustomerId = i.CustomerId JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId JOIN Track t ON il.TrackId = t.TrackId JOIN Genre g ON t.GenreId = g.GenreId WHERE g.GenreId = ( SELECT GenreId FROM Genre WHERE NAME = "Rock") ORDER BY 1

5-qism
WITH t1 AS (SELECT c.Country As Country, c.FirstName As FirstName, c.LastName AS LastName, c.CustomerId As CustomerId, SUM(i.Total) AS TotalSpent FROM Customer c JOIN invoice i ON c.CustomerId = i.CustomerId GROUP BY 1,2,3), t2 AS (SELECT t1.Country, MAX(t1.TotalSpent) AS max_spent FROM t1 GROUP BY 1 ORDER BY 1, 2 DESC) SELECT
t1.CustomerId, t1.FirstName, t1.LastName, t1.Country, t1.TotalSpent FROM t1 JOIN t2 ON t1.country = t2.country WHERE t1.TotalSpent = t2.max_spent