找到所有電影的名稱Title
SELECT Title FROM Movies;

找到所有電影的導(dǎo)演
SELECT Director FROM Movies;

找到所有電影的名稱和導(dǎo)演
SELECT Title,Director FROM Movies;

找到所有電影的名稱和上映年份
SELECT Title,Year FROM Movies;

找到所有電影的所有信息
SELECT * FROM Movies;

找到所有電影的名稱,Id和播放時(shí)長
SELECT Title,Id,Length_minutes FROM Movies;

請列出所有電影的Id,名稱和出版國(即美國)
SELECT Id,Title,“美國” as Country FROM Movies;

note:這里再Country列加入“美國”這個(gè)條件，從而簡化了后續(xù)增加WHERE的語法量

找到Id為6的電影
SELECT * FROM Movies WHERE Id = 6;

找到在2000-2010年間Year上映的電影
SELECT * FROM Movies WHERE Year BETWEEN 2000 AND 2010;

找到不是在2000-2010年間Year上映的電影
SELECT * FROM Movies WHERE Year not BETWEEN 2000 AND 2010;

找到頭5部電影
SELECT * FROM Movies LIMIT 5;

note: 詳見LIMIT方法

找到2010（含）年之后的電影里片長小于兩個(gè)小時(shí)的片子
SELECT * FROM Movies WHERE Year >=2010 AND Length_minutes < 120;

找到99年和09年的電影,只要列出年份和片長看下
SELECT Year,Length_minutes FROM Movies WHERE Year =1999 or Year =2009;

找到所有電影的國內(nèi)Domestic_sales和國際銷售額
SELECT * FROM Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id;

找到所有國際銷售額比國內(nèi)銷售大的電影
SELECT * FROM Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id WHERE demostic_sales < International_sales;

找出所有電影按市場占有率Rating倒序排列
SELECT * FROM Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id ORDER BY Rating ASC;

每部電影按國際銷售額比較,排名最靠前的導(dǎo)演是誰,國際銷量多少
SELECT Director,International_sales FROM Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id ORDER BY International_sales LIMIT 1;

這個(gè)答案不對！

自己寫的：SELECT Director, International_sales FROM Movies INNER JOIN Boxoffice On Movies.Id = Boxoffice.Movie_id GROUP BY Director ORDER BY International_sales DESC LIMIT 1;

要先GROUP BY一下把International_sales加起來然后再排序

找到所有有雇員的辦公室(buildings)名字
SELECT DISTINCT Building FROM Employees WHERE Building is not null;

找到所有辦公室和他們的最大容量
SELECT * FROM buildings;

找到所有辦公室里的所有角色（包含沒有雇員的）,并做唯一輸出(DISTINCT)
SELECT DISTINCT buildings.Building_name,Employees.Role FROM buildings LEFT JOIN Employees on Employees.Building=buildings.Building_name;

自己寫的：SELECT DISTINCT Building_name, Role FROM Buildings LEFT JOIN Employees On Buildings.Building_name = Employees.Building;

找到所有有雇員的辦公室(buildings)和對應(yīng)的容量
SELECT DISTINCT Building,capacity FROM Employees LEFT JOIN buildings on Employees.Building=buildings.Building_name WHERE Employees.Building is not null;

找到雇員里還沒有分配辦公室的(列出名字和角色就可以)
SELECT Name,Role FROM Employees WHERE Building is null;

自己的：SELECT Name, Role FROM Employees WHERE Building is null;

找到還沒有雇員的辦公室
SELECT Building_name FROM Buildings LEFT JOIN Employees on Buildings.Building_name = Employees.Building WHERE Name is null;

自己的：SELECT Building_name FROM Buildings LEFT JOIN Employees On Buildings.Building_name = Employees.Building WHERE Building is null;

找出就職年份最高的雇員(列出雇員名字+年份）
SELECT Name,MAX(Years_employed) FROM Employees;

自己寫的：

SELECT Name, Years_employed FROM Employees ORDER BY Years_employed DESC LIMIT 1;

按角色(Role)統(tǒng)計(jì)一下每個(gè)角色的平均就職年份
SELECT Role, AVG(Years_employed) FROM Employees GROUP BY Role;

按辦公室名字總計(jì)一下就職年份總和
SELECT Building, SUM(Years_employed) FROM Employees GROUP BY Building;

每棟辦公室按人數(shù)排名,不要統(tǒng)計(jì)無辦公室的雇員
SELECT Building, Count(Name) FROM Employees WHERE Building is not NULL GROUP BY Building;

SELECT Building, Count(Name) FROM Employees GROUP BY Building HAVING Building is not NULL;

Note:Count(Name)換成Count(*)也可以

就職1,3,5,7年的人分別占總?cè)藬?shù)的百分比率是多少(給出年份和比率"50%" 記為 50)
SELECT Years_employed, Count() * 100/(select count() FROM Employees) AS Rating FROM Employees WHERE Years_employed in (1,3,5,7) GROUP BY Years_employed;

統(tǒng)計(jì)一下Artist角色的雇員數(shù)量
SELECT Count(*) FROM Employees WHERE Role = ‘Artist’;

按角色統(tǒng)計(jì)一下每個(gè)角色的雇員數(shù)量
SELECT Role, Count(*) FROM Employees GROUP BY Role;

算出Engineer角色的就職年份總計(jì)
SELECT SUM(Years_employed) FROM Employees WHERE Role = ‘Engineer’;

題目要求用分組，但我覺得速度應(yīng)該會變慢

SELECT SUM(Years_employed) FROM Employees GROUP BY Role HAVING Role = ‘Engineer’;

每棟辦公室按人數(shù)排名,不要統(tǒng)計(jì)無辦公室的雇員
SELECT count(*) as count,Role,building is not null as bn FROM employees group by Role,bn;

就職1,3,5,7年的人分別占總?cè)藬?shù)的百分比率是多少(給出年份和比率"50%" 記為 50)
SELECT Role,Years_employed/3 as year_3,count(*) as count FROM employees group by Role,year_3 order by count desc;

統(tǒng)計(jì)出每一個(gè)導(dǎo)演的電影數(shù)量（列出導(dǎo)演名字和數(shù)量）
SELECT Director,Count(*) FROM Movies Group by Director;

統(tǒng)計(jì)一下每個(gè)導(dǎo)演的銷售總額(列出導(dǎo)演名字和銷售總額)
SELECT Director, SUM(Domestic_sales+International_sales) AS ‘銷售總額’ FROM Movies Left Join Boxoffice On Movies.Id = Boxoffice.Movie_id GROUP BY Director;

按導(dǎo)演分組計(jì)算銷售總額,求出平均銷售額冠軍（統(tǒng)計(jì)結(jié)果過濾掉只有單部電影的導(dǎo)演，列出導(dǎo)演名，總銷量，電影數(shù)量，平均銷量)
SELECT director,sum(Domestic_sales + International_sales) AS sum_sales,count(director),sum(Domestic_sales + International_sales)/count(director) AS avg_sales FROM movies LEFT JOIN boxoffice ON movies.id = boxoffice.movie_id group by director having count(director) > 1 ORDER BY avg_sales DESC LIMIT 1

–SELECT Director, SUM(Domestic_sales+International_sales) AS ‘總銷量’, Count() AS ‘電影數(shù)量’, SUM(Domestic_sales+International_sales)/Count() AS ‘平均銷量’ FROM Movies Left Join Boxoffice On Movies.Id = Boxoffice.Movie_id GROUP BY Director HAVING Count() > 1 ORDER BY SUM(Domestic_sales+International_sales)/Count() DESC LIMIT 1;

note:用中文名的話不可以直接用AS的列名來操作

找出每部電影和單部電影銷售冠軍之間的銷售差，列出電影名，銷售額差額
select title ,(select max(international_sales+domestic_sales) from boxoffice)-(international_sales+domestic_sales) AS Margin from movies left join boxoffice on movies.id=boxoffice.movie_id;

SELECT Title, ((SELECT (Domestic_sales + International_sales) FROM Movies Left Join Boxoffice On Movies.Id = Boxoffice.Movie_id ORDER BY (Domestic_sales + International_sales) DESC LIMIT 1 ) - (Domestic_sales + International_sales))AS Rest FROM movies LEFT JOIN boxoffice ON movies.id = boxoffice.movie_id;