example.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

SQL> select s1.name from student s1,student s2 where s1.id = s2.lid;

NAME
--------------------
jacky
jacky
好
好
lucy

SQL> select distinct s1.name from student s1,student s2 where s1.id = s2.lid;

NAME
--------------------
jacky
lucy
好

SQL> select s1.name from student s1,student s2 ;

NAME
--------------------
andy
andy
andy
andy
andy
jacky
jacky
jacky
jacky
jacky
lucy
lucy
lucy
lucy
lucy
好
好
好
好
好

NAME
--------------------
lucy
lucy
lucy
lucy
lucy

25 rows selected

SQL> select distinct s1.name from student s1,(select * from student) s2 where s1.id = s2.lid;

NAME
--------------------
jacky
lucy
好

SQL> select distinct s1.name from student s1,(select lid from student) s2 where s1.id = s2.lid;

NAME
--------------------
jacky
lucy
好

SQL> select distinct s1.name from student s1,(select lid lingdao from student) s2 where s1.id = s2.lid;

select distinct s1.name from student s1,(select lid lingdao from student) s2 where s1.id = s2.lid

ORA-00904: "S2"."LID": 标识符无效

SQL> select distinct s1.name from student s1,(select lid lingdao from student) s2 where s1.id = s2.lingdao;

NAME
--------------------
jacky
lucy
好

SQL> select distinct s1.name from student s1,(select lid as lingdao from student) s2 where s1.id = s2.lingdao;

NAME
--------------------
jacky
lucy
好

SQL> select distinct s1.name from student s1,(select lid as lingdao from student) s2(ld) where s1.id = s2.lingdao;

select distinct s1.name from student s1,(select lid as lingdao from student) s2(ld) where s1.id = s2.lingdao

ORA-00933: SQL 命令未正确结束

SQL> select distinct s1.name from student s1,(select lid as lingdao from student) s2(ld) where s1.id = s2.ld;

select distinct s1.name from student s1,(select lid as lingdao from student) s2(ld) where s1.id = s2.ld

ORA-00933: SQL 命令未正确结束

SQL> select distinct s1.name from student s1,(select lid  from student) s2(ld) where s1.id = s2.ld;

select distinct s1.name from student s1,(select lid  from student) s2(ld) where s1.id = s2.ld

ORA-00933: SQL 命令未正确结束

SQL> select distinct s1.name from student s1,(select lid as ld  from student) s2 where s1.id = s2.ld;

NAME
--------------------
jacky
lucy
好

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE   
EMP                            TABLE   
BONUS                          TABLE   
SALGRADE                       TABLE   
STUDENT                        TABLE   

SQL> select name ,age a from student where age > 20 order by age;

NAME                                                       A
-------------------- ---------------------------------------
lucy                                                      23
好                                                        23
lucy                                                      23
jacky                                                     40

SQL> select * from student;

                                     ID NAME                 SEX                                     AGE                                     LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
                                      1 andy                 男                                       18                                       2
                                      2 jacky                女                                       40                                       2
                                      3 lucy                 女                                       23                                       4
                                      4 好                   男                                       23                                       4
                                      5 lucy                 女                                       23                                       3

SQL> select * from student order by age;

                                     ID NAME                 SEX                                     AGE                                     LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
                                      1 andy                 男                                       18                                       2
                                      3 lucy                 女                                       23                                       4
                                      4 好                   男                                       23                                       4
                                      5 lucy                 女                                       23                                       3
                                      2 jacky                女                                       40                                       2

SQL> select '该班的学生名字为：'|| name from student;

'该班的学生名字为：'||NAME
--------------------------------------
该班的学生名字为：andy
该班的学生名字为：jacky
该班的学生名字为：lucy
该班的学生名字为：好
该班的学生名字为：lucy

SQL> select * from student group by sex;

select * from student group by sex

ORA-00979: 不是 GROUP BY 表达式

SQL> select sex from student group by sex;

SEX
---
男
女

SQL> select sex '性别' , count(*) from student group by sex; 

select sex '性别' , count(*) from student group by sex

ORA-00923: 未找到要求的 FROM 关键字

SQL> select sex '性别' , count(*) '人数' from student group by sex;

select sex '性别' , count(*) '人数' from student group by sex

ORA-00923: 未找到要求的 FROM 关键字

SQL> select sex , count(*) '人数' from student group by sex;

select sex , count(*) '人数' from student group by sex

ORA-00923: 未找到要求的 FROM 关键字

SQL> select sex ,count(id) from student group by sex;

SEX  COUNT(ID)
--- ----------
男           2
女           3

SQL> select sex ,count(id),id from student group by sex;

select sex ,count(id),id from student group by sex

ORA-00979: 不是 GROUP BY 表达式

SQL> select sex ,count(id) 人数 from student group by sex;

SEX       人数
--- ----------
男           2
女           3

SQL> select * from student;

                                     ID NAME                 SEX                                     AGE                                     LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
                                      1 andy                 男                                       18                                       2
                                      2 jacky                女                                       40                                       2
                                      3 lucy                 女                                       23                                       4
                                      4 好                   男                                       23                                       4
                                      5 lucy                 女                                       23                                       3

SQL> select sex ,count(id) 人数 from student where age > 25 group by sex;

SEX       人数
--- ----------
女           1

SQL> select sex ,count(id) 人数 from student where age > 25 group by sex having sex = '女';

SEX       人数
--- ----------
女           1

SQL> select sex ,count(id) 人数 from student where age > 10 group by sex having sex = '女';

SEX       人数
--- ----------
女           3

SQL> select sex ,count(id) 人数 from student where age > 10 group by sex having sex in( '女','男') order by sex ;

SEX       人数
--- ----------
男           2
女           3

SQL> select count(*) from emp,dept;

  COUNT(*)
----------
        56

SQL> select count(*) from emp,dept where emp.deptno = dept.deptno;

  COUNT(*)
----------
        14

SQL> select count(*) from emp,dept,emp where emp.deptno = dept.deptno;

select count(*) from emp,dept,emp where emp.deptno = dept.deptno

ORA-00918: 未明确定义列

SQL> select count(*) from emp e1,dept,emp e2 where e1.deptno = dept.deptno;

  COUNT(*)
----------
       196

SQL> select count(*) from emp e1,dept d1,dept d2 where e1.deptno = d1.deptno;

  COUNT(*)
----------