oracle查询练习

时间：2019-12-05 21:04:57 阅读：75 评论：0 收藏：0 [点我收藏+]

1成绩表score如下,查询出每门课都大于80分的学生姓名

准备数据

-建表-

  
SQL> create table score(
? 2? name varchar(50),
? 3? kecheng varchar(50),
? 4? fenshu number(3)
? 5? );

插入数据

 
SQL> insert into score(name,kecheng,fenshu) values(‘张三‘,‘语文‘,81);
1 row inserted

SQL> insert into score(name,kecheng,fenshu) values(‘张三‘,‘数学‘,78);
1 row inserted

SQL> insert into score(name,kecheng,fenshu) values(‘李四‘,‘语文‘,76);
1 row inserted

SQL> insert into score(name,kecheng,fenshu) values(‘李四‘,‘数学‘,90);
1 row inserted

SQL> insert into score(name,kecheng,fenshu) values(‘王五‘,‘语文‘,81);
1 row inserted

SQL> insert into score(name,kecheng,fenshu) values(‘王五‘,‘数学‘,100);
1 row inserted

SQL> insert into score(name,kecheng,fenshu) values(‘王五‘,‘英语‘,88);
1 row inserted

score表

  
SQL> select * from score;

NAME???????????KECHENG?????????????????FENSHU
----------------- ----------------------- ------
张三?????????? 语文??????????????????????? 81
张三?????????? 数学??????????????????????? 78
李四?????????? 语文??????????????????????? 76
李四?????  ??? 数学???????????????     ????90
王五?????? ??? 语文????????????????????????81
王五?????????? 数学???????              ?? 100
王五?????????? 英语??????????????????????? 88
7 rows selected

方法一

  

SQL> select name from score group by name having min(fenshu)>80;
NAME

--------------------------------------------------
王五

方法二

  

SQL> select distinct name from score where name not in(
? 2? select name from score
? 3? where fenshu<80);

NAME

--------------------------------------------------
王五

删除除了自动编号不同,其他都相同的学生冗余信息

准备数据

  

SQL> create table students1
? 2? (
? 3? 自动编号 number(5),
? 4? 学号 number(10),
? 5? 姓名 varchar(40),
? 6? 课程编号 varchar(60),
? 7? 课程名称 varchar(160),
? 8? 分数 number(3)
? 9? );

Table created

SQL> insert into students1 values(1,2005001,‘张三‘,0001,‘数学‘,69);
1 row inserted

SQL> insert into students1 values(2,2005002,‘李四‘,0001,‘数学‘,69);
1 row inserted

SQL> insert into students1 values(3,2005001,‘张三‘,0001,‘数学‘,69);
1 row inserted



SQL> commit;

Commit complete

students1表

  
SQL> select * from students1;

?自动编号???学号 姓名     课程编号   课程名称  分数

 ---------------------------------------- 

?1??  2005001    张三      1      数学       69

?2????2005002    李四      1      数学       69

?3??? 2005001    张三      1      数学       69

方法一

  
SQL> delete from students1 where 自动编号 not in(
  2  select min(自动编号)
  3  from students1
  4  group by 学号,课程编号);

1 row deleted

方法二

  
SQL> delete from students1 where rowid not in(
? 2? select min(rowid)
? 3? from students1
? 4? group by 学号,课程编号);

1 row deleted

3一个叫team的表,里面只有一个字段name,一共有4条记录,分别是abcd,对应4个球队,现在4个球队进行比赛,用一条sql语句显示所有可能的比赛组合

team表

  
SQL> select * from team;
NAME
------------
a
b
c
d

方法一,考虑主客场

  

SQL> select * from team a,team b
  2  where a.name<>b.name;
NAME      NAME
--------- ---------
a         b
a         c
a         d
b         a
b         c
b         d
c         a
c         b
c         d
d         a
d         b
d         c
12 rows selected

方法二,不考虑主客场

  
SQL> select * from team a,team b
  2  where a.name


4请用sql语句实现,从testdb数据表中查询出所有月份的发生额都比101科目相应月份的发生额
高的科目,

注意: testdb中有很多科目,都有1-12月份的发生额


  testdb表

    
SQL> select * from testdb;

? ACCID OCCMONTH? DEBITOCCUR

------- -------- -----------

??? 101??????? 1????????? 34

??? 101??????? 2????????? 35

??? 101??????? 3????????? 36

??? 101??????? 4????????? 37

??? 102??????? 1????????? 44

??? 102??????? 2????????? 45

??? 102??????? 3????????? 46

??? 102??????? 4????????? 47

??? 103??????? 1????????? 44

??? 103??????? 2????????? 45

??? 103??????? 3????????? 36

??? 103??????? 4????????? 37

12 rows selected
  

  方法一

    
SQL> select distinct accid from testdb where accid not in(

? 2? select b.accid
? 3? from testdb a,testdb b
? 4? where a.accid=‘101‘
? 5? and b.accid<>a.accid
? 6? and a.occmonth=b.occmonth
? 7? and a.debitoccur>=b.debitoccur)
? 8? and accid<>‘101‘;

? ACCID

-------

??? 102
  
行转列,将表a查询为表b

  表a

    
SQL> select * from a;

YEAR MONTH AMOUNT

---- ----- ------

1991???? 1??? 1.1

1991???? 2??? 1.2

1991???? 3??? 1.3

1991???? 4??? 1.4

1992???? 1??? 2.1

1992???? 2??? 2.2

1992???? 3??? 2.3

1992???? 4??? 2.4

8 rows selected
  

  表b

    
????? YEAR???????? M1???????? M2???????? M3???????? M4

---------- ---------- ---------- ---------- ----------

????? 1991??????? 1.1??????? 1.2??????? 1.3??????? 1.4

????? 1992??????? 2.1??????? 2.2??????? 2.3??????? 2.4

  

  方法一

    
SQL> select 1991 year,1.1 m1,1.2 m2,1.3 m3,1.4 m4 from dual

? 2? union all

? 3? select 1992 year,2.1 m1,2.2 m2,2.3 m3,2.4 m4 from dual;

????? YEAR???????? M1???????? M2???????? M3???????? M4

---------- ---------- ---------- ---------- ----------

????? 1991??????? 1.1??????? 1.2??????? 1.3??????? 1.4

????? 1992??????? 2.1??????? 2.2??????? 2.3??????? 2.4
  

  方法二

    
SQL> select year,
? 2? max(case month when 1 then amount end) m1,
? 3? max(case month when 2 then amount end) m2,
? 4? max(case month when 3 then amount end) m3,
? 5? max(case month when 4 then amount end) m4
? 6? from a group by year;

YEAR???????? M1???????? M2???????? M3???????? M4

---- ---------- ---------- ---------- ----------

1991??????? 1.1??????? 1.2??????? 1.3??????? 1.4

1992??????? 2.1??????? 2.2??????? 2.3??????? 2.4

  

  方法三

    
SQL> select * from
? 2? (select year,amount m1 from a where month=1)
? 3? natural join
? 4? (select year,amount m2 from a where month=2)
? 5? natural join
? 6? (select year,amount m3 from a where month=3)
? 7? natural join
? 8? (select year,amount m4 from a where month=4);

YEAR?? M1?? M2?? M3?? M4

---- ---- ---- ---- ----

1991? 1.1? 1.2? 1.3? 1.4

1992? 2.1? 2.2? 2.3? 2.4

  
article_history表存放文章的修改记录有以下字段,查询出所有文章的最后修改人和修改时间

title文章标题

last_time修改时间

username修改人姓名

source文章来源

pin_name作者笔名


  article_history

    

SQL> select * from article_history;
TITLE      LAST_TIME   USERNAME  SOURCE       PIN_NAME
--------------------------------------------------------
1          2019/12/4 1       s         cnblog       s
2          2019/12/4 1       l         cnblog       l
1          2019/12/4 1       s         cnblog       s
2          2019/12/4 1       l         cnblog       l
1          2019/12/5 9       s         cnblog       s
  

  方法一

    
SQL> select title,username,last_time from article_history where 
(title,last_time) in(
? 2? select title,max(last_time)
? 3? from article_history
? 4? group by title);

TITLE???????????????USERNAME????????????????LAST_TIME

---------------------------------------------------------

2?  ?l???? 2019/12/4 1

1   ?s???? 2019/12/5 9

  
有两个表AA和BB,均有key和value两个字段,如果BB的key在AA中也有,就把BB中的value换为AA中对应的value

  准备数据

    
SQL> create table aa
? 2? (
? 3? key varchar(100),
? 4? value varchar(100)
? 5? );

Table created



SQL> 

SQL> create table bb
? 2? (
? 3? key varchar(100),
? 4? value varchar(100)
? 5? );

Table created



SQL> 

SQL> insert into aa values(‘aa‘,‘gond‘);
1 row inserted

SQL> insert into aa values(‘bb‘,‘tend‘);
1 row inserted

SQL> insert into aa values(‘cc‘,‘ailie‘);
1 row inserted

SQL> insert into bb values(‘aa‘,‘geng‘);
1 row inserted

SQL> insert into bb values(‘bb‘,‘dlis‘);
1 row inserted

SQL> insert into bb values(‘dd‘,‘lista‘);
1 row inserted

  

  aa表

    
SQL> select * from aa;

KEY??????????????????VALUE

--------------------------------
aa????????????????????gond
bb????????????????????tend
cc????????????????????ailie
  

  bb表

    
SQL> select * from bb;

KEY?????????????????????? VALUE

----------------------------------
aa???????????????????????geng
bb???????????????????????dlis
dd???????????????????????lista
  

  方法一

    
SQL> update bb b
? 2? set value=(select value from aa a where b.key=a.key)
? 3? where exists(select 1 from aa a where b.key=a.key);

2 rows updated
  

  方法二

    
SQL> merge into bb b
? 2? using aa a
? 3? on(b.key=a.key)
? 4? when matched then
? 5??? update
? 6??? set b.value=a.value;

2 rows merged
  
什么是事务,事务有哪些特性,各个特性是什么意思

一个commit,rollback就是一个事务
原子性,一个事务,要么都执行,要么都回滚,是一个整体
一致性,事务发生之前,所有人查询数据都是一致的,事务发生之后,所有人查询数据都是一致的
隔离型,事务之间相互隔离,比如一个insert和另一个update,相互之间不影响
持久性,事务发生之后,如果没有别的事务,则数据不会再改变

有如下三个表

  students表

    
SQL> select sno 学号,sname 姓名,class 所属班级 from students;

学号?????? 姓名???????????????? 所属班级

---------- -------------------- -----

108??????? 曾华???????????????? 95033

105??????? 匡明???????????????? 95031

107??????? 王丽???????????????? 95033

101??????? 李军???????????????? 95033

109??????? 王芳???????????????? 95031

103??????? 陆君???????????????? 95031

6 rows selected
  

  courses表

    
SQL> select cno 课程编号,cname 课程名称 from courses;

课程编号 课程名称

----- --------------------

3-105 计算机导论

3-245 操作系统

6-166 数据电路

9-888 高等数学
  

  scores表

    
SQL> select sno 学号,cno 课程编号,score 成绩 from scores;

学号? 课程编号?????????????????????? 成绩

----- -------------------- ------------

103?? 3-245??????????????????????? 86.0

105?? 3-245??????????????????????? 75.0

109?? 3-245??????????????????????? 68.0

103?? 3-105??????????????????????? 92.0

105?? 3-105??????????????????????? 88.0

109?? 3-105??????????????????????? 76.0

101?? 3-105??????????????????????? 64.0

107?? 3-105??????????????????????? 91.0

108?? 3-105??????????????????????? 78.0

101?? 6-166??????????????????????? 85.0

107?? 6-106??????????????????????? 79.0

108?? 6-166??????????????????????? 81.0

12 rows selected
  
1使用标准sql嵌套语句查询选修课程名称为‘计算机导论‘的学员学号和姓名

  方法

    
SQL> select sno,sname from students where sno in(
? 2? select sno from scores where cno=(
? 3? select cno from courses where cname=‘计算机导论‘));

SNO??????? SNAME

---------- --------------------

103??????? 陆君

105??????? 匡明

109??????? 王芳

101??????? 李军

107??????? 王丽

108??????? 曾华

6 rows selected
  
2使用标准sql嵌套语句查询选修课程编号为‘3-245‘的学员姓名和班级

  方法

    
SQL> select sname,class from students where sno in(
? 2? select sno from scores where cno=‘3-245‘);

SNAME??????????????? CLASS

-------------------- -----

陆君???????????????? 95031

匡明???????????????? 95031

王芳???????????????? 95031
  
3使用标准sql嵌套语句查询不选修课程编号为‘6-166‘的学员姓名和所属单位

  方法

    
SQL> select sname,class from students where sno not in(
? 2? select sno from scores where cno=‘6-166‘);

SNAME??????????????? CLASS

-------------------- -----

匡明???????????????? 95031

陆君???????????????? 95031

王芳???????????????? 95031

王丽???????????????? 95033
  
4查询选修了课程的学员人数

  方法

    
SQL> select count(distinct sno) from scores;

COUNT(DISTINCTSNO)

------------------

???????????????? 6
  
5查询选修课程没有超过3门的学员学号和所属班级

  方法

    
SQL> select sno,class from students where sno in(
? 2? select sno
? 3? from scores
? 4? group by sno having count(1)<=3);

SNO??????? CLASS

---------- -----

101??????? 95033

105??????? 95031

109??????? 95031

103??????? 95031

108??????? 95033

107??????? 95033

6 rows selected
  
查询表aplus中存在id重复三次以上的记录,写出查询语句

  准备数据

    
SQL> create table aplus(
? 2? id number(6)
? 3? );

Table created



SQL> 

SQL> insert into aplus values(1);
1 row inserted

SQL> insert into aplus values(1);
1 row inserted

SQL> insert into aplus values(2);
1 row inserted

SQL> insert into aplus values(2);
1 row inserted

SQL> insert into aplus values(2);
1 row inserted

SQL> insert into aplus values(3);
1 row inserted

SQL> insert into aplus values(3);
1 row inserted

SQL> insert into aplus values(3);
1 row inserted

SQL> insert into aplus values(3);
1 row inserted
  

  aplus表

    
SQL> select * from aplus;
     ID
-------
      1
      1
      2
      2
      2
      3
      3
      3
      3
9 rows selected
  

  方法一

    
SQL> select id
? 2? from aplus
? 3? group by id
? 4? having count(1)>3;

???? ID

-------

????? 3
  
表sales(pid,sale_date,amount)分别代表产品id,销售日期,销售量. 查询出各个产品每天的销售占当月已销售额的比例

注意当月已销售额是指本月第一天到现在的总和


  准备数据

    
SQL> create table sales(
? 2? pid number(10),
? 3? sale_date date,
? 4? amount number(16)
? 5? );

Table created
SQL> insert into sales 
values(1,to_date(20191201,‘yyyy/mm/dd‘),7789);

1 row inserted

SQL> insert into sales 
values(2,to_date(20101201,‘yyyy/mm/dd‘),2589);

1 row inserted

SQL> insert into sales 
values(1,to_date(20191202,‘yyyy/mm/dd‘),6921);

1 row inserted

SQL> insert into sales 
values(2,to_date(20101202,‘yyyy/mm/dd‘),3501);

1 row inserted

SQL> insert into sales 
values(1,to_date(20191203,‘yyyy/mm/dd‘),7249);

1 row inserted

SQL> insert into sales 
values(2,to_date(20101203,‘yyyy/mm/dd‘),3511);

1 row inserted

  

  sales表

    
SQL> select * from sales;
        PID SALE_DATE              AMOUNT
----------- ----------- -----------------
          1 2019/12/1                7789
          2 2010/12/1                2589
          1 2019/12/2                6921
          2 2010/12/2                3501
          1 2019/12/3                7249
          2 2010/12/3                3511
6 rows selected
  

  方法一

    
SQL> select pid,sale_date,amount,amount/sum_a
? 2? from (select a.pid,a.sale_date,a.amount,
? 3? (select sum(b.amount) from sales b where a.pid=b.pid) sum_a
? 4? from sales a);

??????? PID SALE_DATE????????????? AMOUNT AMOUNT/SUM_A

----------- ----------- ----------------- ------------

????????? 1 2019/12/1??????????????? 7789 0.3547064984

????????? 2 2010/12/1??????????????? 2589 0.2696594104

????????? 1 2019/12/2??????????????? 6921 0.3151782868

????????? 2 2010/12/2??????????????? 3501 0.3646495156

????????? 1 2019/12/3??????????????? 7249 0.3301152147

????????? 2 2010/12/3??????????????? 3511 0.3656910738

6 rows selected
  
oracle查询练习
原文：https://www.cnblogs.com/inmeditation/p/11991727.html


                
                
                    
                        
                            
                                
                                    踩
                                
                                    (0)
                            
                            
                                
                                    赞
                                
                                    (0)
                            
                            
                            
                        
                        
                            
                                    
                                
                            
                        
                    
                    
                        举报
                    
                    
                    
                        
                            评论 一句话评论（0）
                        
                        
                            
                            
                                
                            
                        
                        
                            
                            
                                
                                
                                
                                    
                                
                            
                            
                                登录后才能评论！


        
            
    


            
            

    
        
            分享档案
        
            更多>
        
        
        
    
    
        
                
                    2021年09月23日
                    (328)

            
                
                    2021年09月24日
                    (313)

            
                
                    2021年09月17日
                    (191)

            
                
                    2021年09月15日
                    (369)

            
                
                    2021年09月16日
                    (411)

            
                
                    2021年09月13日
                    (439)

            
                
                    2021年09月11日
                    (398)

            
                
                    2021年09月12日
                    (393)

            
                
                    2021年09月10日
                    (160)

            
                
                    2021年09月08日
                    (222)

            
    


            

    
        最新文章
        
            更多>
        
        
    
    
        
            
                    
                        2021/09/28 scripts
                         2022-05-27
                    
                
                    
                        vue自定义全局指令v-emoji限制input输入表情和特殊字符
                         2022-05-27
                    
                
                    
                        9.26学习总结
                         2022-05-27
                    
                
                    
                        vim操作
                         2022-05-27
                    
                
                    
                        深入理解计算机基础 第三章
                         2022-05-27
                    
                
                    
                        C++ string 作为形参与引用传递（转）
                         2022-05-27
                    
                
                    
                        python 加解密
                         2022-05-27
                    
                
                    
                        JavaScript-对象数组里根据id获取name，对象可能有children属性
                         2022-05-27
                    
                
                    
                        SQL语句——保持现有内容在后面增加内容
                         2022-05-27
                    
                
                    
                        virsh命令文档
                         2022-05-27
                    
                
        
    


            

    
        教程昨日排行
        
            更多>
        
        
    
    
        
            
                    
                        1.list.reverse()
                
                    
                        2.Django Admin 管理工具
                
                    
                        3.AppML 案例模型
                
                    
                        4.HTML 标签列表（功能排序）
                
                    
                        5.HTML 颜色名
                
                    
                        6.HTML 语言代码
                
                    
                        7.jQuery 事件
                
                    
                        8.jEasyUI 创建分割按钮
                
                    
                        9.jEasyUI 创建复杂布局
                
                    
                        10.jEasyUI 创建简单窗口