课程第7/8次：2016-09-09星期五，2016-09-12星期一

botang

课程第7次2016-09-09星期五：
网络介绍完了。
关系型数据库基本概念和SQL五类语句介绍完了。
【1Z0-051】：共12章（0 1 2 3 4 5 6 7 8 9 10 11）
【1Z0-052】：共19章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18）
【1Z0-053】：共21章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20）

共享服务器几个查询：

1. select  * from v$circuit;
   
2. 
   
3. select  * from v$dispatcher;
   
4. 
   
5. select  * from v$session where saddr='00000001EC928508';

复制代码

课程第8次2016-09-12星期一：
WHERE和ORDER BY以及空值
【1Z0-051】：共12章（0 1 2 3 4 5 6 7 8 9 10 11）
【1Z0-052】：共19章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18）
【1Z0-053】：共21章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20）

2016-09-12.sql：

1. select   * from employees where employee_id=100;
   
2. 
   
3. select  employee_id, rowid ,rownum  from employees
   
4. where rownum=2;
   
5. 
   
6. select   * from employees e where e.department_id=80;
   
7. 
   
8. SELECT last_name||' xxxx '||first_name   "Last Name" , 12*salary*(1+nvl(commission_pct,0))  "Anaualsal"
   
9. FROM   employees;
   
10. 
    
11. select * from employees
    
12. where manager_id is  null;
    
13. 
    
14. 
    
15. SELECT department_name || q'# *  #'
    
16.        || manager_id
    
17.        AS "Department and Manager"
    
18. FROM departments;
    
19. 
    
20. SELECT DISTINCT department_id
    
21. FROM   employees;
    
22. 
    
23. select  * from employees e
    
24. where e.employee_id=178;
    
25. 
    
26. 
    
27. 
    
28. create table t05101_distinct ( a   varchar2(10)  , b varchar2(10))  ;
    
29. 
    
30. insert into t05101_distinct  values ( 'A'   ,'B' ) ;
    
31. select   distinct a, b from  t05101_distinct ;
    
32.   insert into t05101_distinct  values ( 'A'   ,'B1' ) ;
    
33.   
    
34.    select   distinct a from  t05101_distinct ;
    
35.    
    
36.    
    
37.    select   *
    
38.      from user_tab_cols tc
    
39.      where tc.TABLE_NAME='LOCATIONS';
    
40.      
    
41. select  * from employees
    
42. where rownum=1;
    
43. 
    
44. alter session set nls_date_format='YYYY-MM-DD';
    
45. 
    
46. SELECT last_name
    
47. FROM   employees
    
48. WHERE  hire_date ='21-JUN-07';
    
49. 
    
50. 
    
51. 
    
52. 
    
53. 
    
54. SELECT last_name
    
55. FROM   employees
    
56. WHERE  hire_date = to_date('21-JUN-07','DD-MON-RR') ;
    
57. ---
    
58. 
    
59. select  *
    
60. from employees e
    
61. where e.employee_id   not in
    
62. (select manager_id from employees  
    
63. where manager_id is not null);
    
64. 
    
65. 
    
66. xx and yy and  zz and null
    
67. 
    
68. create table t05102_a ( a varchar2(10)) ;
    
69. 
    
70. insert into t05102_a  values ('A' ) ;
    
71. 
    
72. insert into t05102_a  values ('A1' ) ;
    
73. 
    
74. insert into t05102_a  values ('%' ) ;
    
75. 
    
76. insert into t05102_a  values ('_' ) ;
    
77. 
    
78. insert into t05102_a  values ('_1' ) ;
    
79. 
    
80. 
    
81. select  * from   t05102_a;
    
82. 
    
83. select  * from t05102_a where a like 'A%';
    
84. select  * from t05102_a where a like 'A_';
    
85. select  * from t05102_a where a like '%%';
    
86. 
    
87. select  * from t05102_a where a like '\%%'  escape '\';
    
88. select  * from t05102_a where a like '\_%'  escape '\';
    
89. 
    
90. insert into t05102_a values ('''');
    
91. 
    
92. select  * from t05102_a;
    
93. 
    
94. insert into t05102_a values (chr(39)||1);
    
95. 
    
96. ---
    
97. 
    
98. create table t05102_b ( a  number , b number ) ;
    
99. 
    
100. insert into t05102_b values ( 1  , 999) ;
     
101. insert into t05102_b values ( 1  , 0) ;
     
102. 
     
103. insert into t05102_b values ( 2  ,999);
     
104. 
     
105. insert into t05102_b values (2,0);
     
106. 
     
107. 
     
108. select  * from  t05102_b  
     
109. order by a desc  ,b desc  ;
     
110. 
     
111. 
     
112. select   employees.employee_id
     
113.   from employees e ;
     
114.   
     
115. 
     
116. 
     
117. 
     
118. 
     
119. 
     
120. select   a  "X" , b  "Y" from  t05102_b
     
121. order by 1  desc,2  ;
     
122. 
     
123. 
     
124. select  employee_id, salary
     
125.   from employees
     
126.    where employee_id=&S_1;
     
127.    
     
128.   select  &S_1;
     

复制代码