09 -Plus- SQL附加练习

09 /Plus/ SQL附加练习

数据文件

数据文件在

数据字典

• 第一张表：url_info
  表示url的基本信息，主键id，表示编号，name为url的名称，比如google，url为网址地址，比如www.google.com，country为该网址主营公司所属国家。
• 第二张表：ad_info
  主要是需要打广告的公司信息，主键id，表示公司编号，name为需要打广告的公司名称。
• 第三张表：access_log。
  表示网站登录情况统计，主键id，登录情况的编号，url_id，表示对应的网站的id编号（url_info主键），count表示访问次数，date表示访问日期。
• 第四张表：ad_log
  表示各网站广告访问的记录，url_id是说对应网站的id编号（url_info主键），ad_id是打广告公司公司编号（ad_info主键）,count是广告点击次数，price是点击一次的单价。

练习题（请做完了再看后面答案）

• 问题一：从url_info选取所有国家（country）为usa的网站。
• 问题二：从url_info中选取国家（country）为cn并且id编号小于等于6的网站。
• 问题三：从url_info选取所有国家（country）为cn的网站，并且按照id的降序排列。
• 问题四：从url_info选取网站名称name开头为b的网站。
• 问题五：从access_log中选取count计数在10至30（不包含10，包含30）的记录
• 问题六：从url_info选取网站名称name开头为b或者为t的网站。
• 问题七：从url_info选取网站名称name开头不为b或者为t的其余网站。
• 问题八：统计所有网站的访问量，包含没有访问量的网站，需要显示网站名称和总访问量，并以访问量降序排列
• 问题九：显示出前五个点击赚钱多的网站，需要显示出网站名称和所赚金额总和（单价乘以点击数），金额降序排列
• 问题十：显示出总访问量大于200的网站，需要网站名称和访问量，访问量升序排列
• 问题十一：显示出广告公司名为coco的，投放广告到了哪些网站，在各网站上支出了多少钱，按金额降序排列

答案

// 问题一

从url_info选取所有国家（country）为usa的网站。

SELECT *
FROM url_info
WHERE country in ('usa');

// 问题二

从url_info中选取国家（country）为cn并且id编号小于等于6的网站。

SELECT *
FROM url_info
WHERE country in ('cn') AND id <= 6
ORDER BY id DESC;

// 问题三

从url_info选取所有国家（country）为cn的网站，并且按照id的降序排列。

SELECT *
FROM url_info
WHERE country in ('cn')
ORDER BY id DESC;

// 问题四

从url_info选取网站名称name开头为b的网站。

SELECT *
FROM url_info
WHERE name LIKE ('b%')
ORDER BY id DESC;

// 问题五

从access_log中选取count计数在10至30（不包含10，包含30）的记录

SELECT *
FROM access_log
WHERE count <=40 AND count> 30
ORDER BY id DESC;

// 问题六

从url_info选取网站名称name开头为b或者为t的网站。

SELECT *
FROM url_info
WHERE name LIKE ('b%') OR name LIKE ('t%')
ORDER BY id DESC;

// 问题七

从url_info选取网站名称name开头不为b或者为t的其余网站。

SELECT *
FROM url_info
WHERE name NOT LIKE ('b%') AND name NOT LIKE ('t%')
ORDER BY id DESC;

// 问题八

统计所有网站的访问量，包含没有访问量的网站，需要显示网站名称和总访问量，并以访问量降序排列

SELECT u.name, SUM(a.count) s
FROM url_info u
LEFT JOIN access_log a
ON u.id = a.url_id
GROUP BY u.name
ORDER BY s DESC;

// 问题九

显示出前五个点击赚钱多的网站，需要显示出网站名称和所赚金额总和（单价乘以点击数），金额降序排列

SELECT u.name, SUM(a.count*a.price) total
FROM url_info u
LEFT JOIN ad_log a
ON u.id = a.url_id
GROUP BY u.name
ORDER BY total DESC
LIMIT 5;

// 问题十

显示出总广告点击量大于200的网站，需要网站名称和访问量，访问量升序排列。（题目有调整）

SELECT u.name, SUM(a.count) c
FROM url_info u
LEFT JOIN ad_log a
ON u.id = a.url_id
GROUP BY u.name
ORDER BY c;

// 问题十一

显示出广告公司名为coco的，投放广告到了哪些网站，在各网站上支出了多少钱，按金额降序排列。

SELECT u.name, SUM(a.count*a.price) total
FROM url_info u
JOIN ad_log a
ON u.id = a.url_id
JOIN ad_info i
ON a.ad_id = i.id
WHERE i.name = ('coco')
GROUP BY u.name
ORDER BY total DESC;