SQL练习9月10
来源:互联网 发布:四川广电网络套餐介绍 编辑:程序博客网 时间:2024/04/29 00:14
-- 给出诺贝尔奖获奖总人数
SELECT COUNT(*) FROM nobel;
-- 给出诺贝尔物理奖获奖次数
SELECT COUNT(*) FROM nobel WHERE SUBJECT='Physics';
-- 显示每个奖项获奖总次数
SELECT n.SUBJECT,COUNT(SUBJECT) FROM nobel n GROUP BY n.SUBJECT;
-- 显示每个奖项第一次获得该奖的年份
SELECT n.SUBJECT,MIN(yr) FROM nobel n GROUP BY n.SUBJECT ;
-- 显示每个奖项在2000年获奖的人数
SELECT n.SUBJECT, COUNT(winner) FROM nobel n WHERE yr=2000 GROUP BY n.SUBJECT;
-- 显示每个奖项不同获奖者人数
SELECT * FROM nobel;
SELECT n.SUBJECT , COUNT(DISTINCT n.winner) FROM nobel n GROUP BY n.SUBJECT ;
-- 显示每个奖项有多少年获奖
SELECT SUBJECT,COUNT(DISTINCT yr) FROM nobel GROUP BY SUBJECT;
-- 显示当年有三个物理奖的年份
SELECT yr,COUNT(winner) FROM nobel WHERE SUBJECT='Physics'
GROUP BY yr HAVING COUNT(winner)=3;
-- 显示得奖超过一次的获奖者
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(SUBJECT)>1
-- 显示得到多个奖项的获奖者
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT SUBJECT)>1
-- 显示2000年及以后,有三个人获得同一奖项的年份及奖项
SELECT yr,SUBJECT FROM nobel WHERE yr>=2000 GROUP BY yr,SUBJECT HAVING COUNT(winner)=3;
SELECT COUNT(*) FROM nobel;
-- 给出诺贝尔物理奖获奖次数
SELECT COUNT(*) FROM nobel WHERE SUBJECT='Physics';
-- 显示每个奖项获奖总次数
SELECT n.SUBJECT,COUNT(SUBJECT) FROM nobel n GROUP BY n.SUBJECT;
-- 显示每个奖项第一次获得该奖的年份
SELECT n.SUBJECT,MIN(yr) FROM nobel n GROUP BY n.SUBJECT ;
-- 显示每个奖项在2000年获奖的人数
SELECT n.SUBJECT, COUNT(winner) FROM nobel n WHERE yr=2000 GROUP BY n.SUBJECT;
-- 显示每个奖项不同获奖者人数
SELECT * FROM nobel;
SELECT n.SUBJECT , COUNT(DISTINCT n.winner) FROM nobel n GROUP BY n.SUBJECT ;
-- 显示每个奖项有多少年获奖
SELECT SUBJECT,COUNT(DISTINCT yr) FROM nobel GROUP BY SUBJECT;
-- 显示当年有三个物理奖的年份
SELECT yr,COUNT(winner) FROM nobel WHERE SUBJECT='Physics'
GROUP BY yr HAVING COUNT(winner)=3;
-- 显示得奖超过一次的获奖者
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(SUBJECT)>1
-- 显示得到多个奖项的获奖者
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT SUBJECT)>1
-- 显示2000年及以后,有三个人获得同一奖项的年份及奖项
SELECT yr,SUBJECT FROM nobel WHERE yr>=2000 GROUP BY yr,SUBJECT HAVING COUNT(winner)=3;
0 0
- SQL练习9月10
- SQL练习9月10
- 10月练习
- 10月11日练习
- sql练习
- SQL练习
- sql练习
- sql练习
- sql练习
- sql 练习
- SQL练习
- sql练习
- sql练习
- sql 练习
- sql练习
- SQL 练习
- sql练习
- sql练习
- 计算某天在本年中为第几天
- Qt Creator:连接数据库和初步了解QSqlTableModel
- NOIP2010 关押罪犯
- 2015 ICPC 长春网赛 解题报告
- Google《Android性能优化》学习笔记链接
- SQL练习9月10
- cassandra clustering key 的查询原理
- 1.嵌入式开发环境搭建--虚拟机安装(unbutu)系统
- shell脚本 关联数组的使用
- 近期刷题题解
- SQL练习9月10
- 网易游戏2016在线笔试
- 编译器实践的重要性-专家评说
- 黑马程序员——学习日记10