SQL语句练习自学SQL网在查询中使用表达式统计-CSDN博客

本文链接：https://blog.csdn.net/qq_30500575/article/details/147668931

Day 9 在查询中使用表达式

SELECT id , Title , 
		(International_sales+Domestic_sales)/1000000 
        AS International_sales 
        FROM movies
		LEFT JOIN boxoffice 
        WHERE movies.Id = boxoffice.Movie_id;
        
SELECT id , Title , Rating * 10 AS Rating
		FROM movies
 		LEFT 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 movies
		 INNER JOIN boxoffice 
         ON movies.Id = boxoffice.Movie_id 
         WHERE director = "John Lasseter"
         ORDER BY (International_sales+Domestic_sales)/ Length_minutes DESC
         LIMIT 3 OFFSET 0;

Day 10 在查询中进行统计聚合函数

SELECT Name , MAX(Years_employed) FROM employees ;

SELECT ROLE ,  AVG(Years_employed) 
 	  	FROM employees
        GROUP BY ROLE;

SELECT Building , SUM(Years_employed)
		FROM employees
		GROUP BY Building;

SELECT Building
		FROM employees 
		WHERE Building IS NOT NULL
		GROUP BY Building
        ORDER BY SUM(name);

Day 11 在查询中进行统计 HAVING关键字

 SELECT COUNT(*)
    FROM employees
    WHERE Role="Artist";

SELECT ROLE ,COUNT(*) 
    FROM employees
    GROUP BY Role;
    
SELECT SUM(Years_employed)
    FROM employees
    WHERE Role = "Engineer"

SELECT role,count(years_employed) AS count_num,
        CASE WHEN building is null THEN 0 ELSE 1 end AS c_b
		FROM employees
		GROUP BY role,c_b

Day12 查询执行顺序

SELECT Director,count(*)from movies group by director

SELECT 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 1

select 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 )