目錄

Day 9 在查詢中使用表達式

Day 10 在查詢中進行統計 聚合函數

Day 11 在查詢中進行統計 HAVING關鍵字

Day12 查詢執行順序

---

Day 9 在查詢中使用表達式

·

SELECT id , Title , (International_sales+Domestic_sales)/1000000 AS International_sales FROM moviesLEFT JOIN boxoffice WHERE movies.Id = boxoffice.Movie_id;SELECT id , Title , Rating * 10 AS RatingFROM moviesLEFT JOIN boxoffice WHERE movies.Id = boxoffice.Movie_id;-- SELECT id , Title , Year AS Rating
-- 	        FROM movies
--      	LEFT JOIN boxoffice 
--          WHERE movies.Id = boxoffice.Movie_id AND YEAR%2==0;SELECT Title  ,(International_sales+Domestic_sales)/ Length_minutes AS value FROM moviesINNER JOIN boxoffice ON movies.Id = boxoffice.Movie_id WHERE director = "John Lasseter"ORDER BY (International_sales+Domestic_sales)/ Length_minutes DESCLIMIT 3 OFFSET 0;

Day 10 在查詢中進行統計 聚合函數

SELECT Name , MAX(Years_employed) FROM employees ;SELECT ROLE ,  AVG(Years_employed) FROM employeesGROUP BY ROLE;SELECT Building , SUM(Years_employed)FROM employeesGROUP BY Building;SELECT BuildingFROM employees WHERE Building IS NOT NULLGROUP BY BuildingORDER BY SUM(name);

Day 11 在查詢中進行統計 HAVING關鍵字

 SELECT COUNT(*)FROM employeesWHERE Role="Artist";SELECT ROLE ,COUNT(*) FROM employeesGROUP BY Role;SELECT SUM(Years_employed)FROM employeesWHERE Role = "Engineer"SELECT role,count(years_employed) AS count_num,CASE WHEN building is null THEN 0 ELSE 1 end AS c_bFROM employeesGROUP BY role,c_b

Day12 查詢執行順序

SELECT Director,count(*)from movies group by directorSELECT Director,sum( Domestic_sales+International_sales )
from
movies left join Boxoffice
where id=Movie_id
group by director;SELECT Director,
sum( Domestic_sales+International_sales ),
count(),avg(Domestic_sales+International_sales) as va
from
movies left join Boxoffice
on id=movie_id
group by director having count(director)>1
order by va desc
limit 1select title,maxx-(Domestic_sales+International_sales) as cha from movies left join Boxoffice
on id = movie_id join(
select max(Domestic_sales+International_sales)as maxx from Boxoffice )