Oracle Common Language exercises

First, log on to oraclesqlplussysbjsxtassysdba as a super administrator and then remove the alteruserscot lock on scott users.

First, log on to oracle sqlplus sys/bjsxt as sysdba as a super administrator and then remove the alter user scot lock on scott users.

First, log on to Oracle as a super Administrator
Sqlplus sys/bjsxt as sysdba

Then, unlock the scott user.
Alter user scott account unlock;
Then the user name can be used.
(Default Global Database Name orcl)

1. select ename, sal * 12 from emp; // calculate the annual salary
2. select 2*3 from dual; // calculate a pure data table using dual
3. select sysdate from dual; // view the current system time
4. select ename, sal * 12 anuual_sal from emp; // change the name of the search field (the double quotation mark keepFormat alias has special characters and must be enclosed in double quotation marks ).
5. Any mathematical expression containing null values, the final calculation result is null.
6. select ename | sal from emp; // (converts the query result of sal to a string and connects it with the ename, which is equivalent to a string connection in Java)
7. select ename | 'afasjkj 'from emp; // string connection
8. select distinct deptno from emp; // clear the repeated values of the deptno Field
9. select distinct deptno, job from emp; // remove the values that are repeated with the two fields.
10. select * from emp where deptno = 10; // (conditional filter query)
11. select * from emp where empno> 10; // greater than the filtering Value
12. select * from emp where empno <> 10 // filter criteria not equal
13. select * from emp where ename> 'CBA'; // string comparison. In fact, the AscII value of each character is compared, which is the same as the string in Java.
14. select ename, sal from emp where sal between 800 and 1500; // (between and filter, including 800 1500)
15. select ename, sal, comm from emp where comm is null; // (select data whose comm field is null)
16. select ename, sal, comm from emp where comm is not null; // (select data whose comm field is not null)
17. select ename, sal, comm from emp where sal in (800,150); // (in table range)
18. select ename, sal, hiredate from emp where hiredate> '02-January 1, February-1981 '; // (only in the specified format)
19. select ename, sal from emp where deptno = 10 or sal> 1000;
20, select ename, sal from emp where deptno = 10 and sal> 1000;
21. select ename, sal, comm from emp where sal not in (800,150,); // (you can reverse the conditions specified by in)
22. select ename from emp where ename like '% ALL %'; // (fuzzy query)
23. select ename from emp where ename like '_ A %'; // (take all fields of A with the second letter)
24. select ename from emp where ename like '%/%'; // (Use escape characters/query fields with % fields)
25. select ename from emp where ename like '% $ % 'escape' $ '; // (Use escape characters/query fields with % fields)
26. select * from dept order by deptno desc; (use the order by desc field to sort data in descending order. asc is selected by default );
27. select * from dept where deptno <> 10 order by deptno asc; // (we can sort the filtered data)
28. select ename, sal, deptno from emp order by deptno asc, ename desc; // (sort by multiple fields in ascending order of deptno. When detpno is the same, sort by ename in descending order)
29. select lower (ename) from emp; // (the function lower () converts all names searched by ename to lowercase );
30. select ename from emp where lower (ename) like '_ a %'; // (first, convert the field to lowercase, and then judge whether the second letter is)
31. select substr (ename, 2, 3) from emp; // (use the substr () function to cut the searched ename field from the second letter, 3 Characters in total)
32. select chr (65) from dual; // (function chr () converts a number to a character in AscII)
33. select ascii ('A') from dual; // (function ascii () and chr () in 32 () the function converts the corresponding characters to the corresponding Ascii code ))
34. select round (23.232) from dual; // (function round () Rounding)
35. select round (23.232, 2) from dual; // (the number of decimal places reserved after rounding is 0-10 digits)
36. select to_char (sal, '$99,999.9999') from emp; // (add the $ symbol to the thousands separator and retain the four decimal places without zeros)
37. select to_char (sal, 'l99, 999.9999 ') from emp; // (L converts a currency to a local currency. Here $ RMB is displayed)
38. select to_char (sal, 'l00, 000.0000 ') from emp; // (the number of digits to be filled is different. You can check it in the database)
39. select to_char (hiredate, 'yyyy-MM-DD HH: MI: ss') from emp; // (change the default display format of the date)
40. select to_char (sysdate, 'yyyy-MM-DD HH: MI: ss') from dual; // (displays the current system time in 12-hour format)
41. select to_char (sysdate, 'yyyy-MM-DD HH24: MI: ss') from dual; // (displays the current system time in 24-hour format)
42. select ename, hiredate from emp where hiredate> to_date ('2017-2-20 12:24:45 ', 'yyyy-MM-DD HH24: MI: ss '); // (function to-date queries the employees who have joined the company after the given time)
43. select sal from emp where sal> to_number ('$1,250.00', '$9,999.99'); // (function to_number () calculates the salary with special symbols)
44. select ename, sal * 12 + nvl (comm, 0) from emp; // (function nvl () calculates the employee's "annual salary + Commission (or bonus ")
45. select max (sal) from emp; // (function max () returns the maximum value of the sal field in the emp table)
46. select min (sal) from emp; // (function max () calculates the minimum value of the sal field in the emp table)
47. select avg (sal) from emp; // (avg () calculates the average salary );
48. select to_char (avg (sal), '192. 99') from emp; // (keep the average salary calculated only two decimal places)
49. select round (avg (sal), 2) from emp; // (round the average salary to the second decimal place)
50. select sum (sal) from emp; // (calculate the total salary to be paid each month)

//// // Group function (5 Functions in total ): combine multiple conditions to produce only one data // min () max () avg () sum () count () /////////////////////////////
51. select count (*) from emp; // obtain the total number of records in the table.
52. select count (*) from emp where deptno = 10; // when you need to specify the total number of records, you can keep up with the following conditions.
53. select count (distinct deptno) from emp; // The number of the Statistics Department must be removed.
/// // Group () //////////////////////////////////////
54. select deptno, avg (sal) from emp group by deptno; // view the average salary of each department by deptno.
55. select max (sal) from emp group by deptno, job; // when grouping, you can also group multiple fields.
56. select ename from emp where sal = (select max (sal) from emp); // obtain
57. select deptno, max (sal) from emp group by deptno; // the highest salary in this department
//////////////////////////////////////// ///// // The having function cannot filter the group by function /////////////////// ///////////////////
58. select deptno, avg (sal) from emp group by deptno having avg (sal)> 2000; (order by) // obtain the average value of each department, and it must be greater than 2000
59. select avg (sal) from emp where sal> 1200 group by deptno having avg (sal)> 1500 order by avg (sal) desc; // obtain the average values of sal> 1200 grouped by deptno. The average value must be greater than 1500 and finally sorted by sal in descending order.
60. select ename, sal from emp where sal> (select avg (sal) from emp); // calculate the average salary of those people.
61. select ename, sal from emp join (select max (sal) max_sal, deptno from emp group by deptno) t on (emp. sal = t. max_sal and emp. deptno = t. deptno); // query the person with the highest salary in each department
/// // Equivalent connection /////// ///////////////////////////////
62. select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; // use a table as two tables.
63. select ename, dname from emp, dept; // The Cartesian product of the 92-year syntax.
64. select ename, dname from emp cross join dept; // use cross join to connect two tables in the 99-year syntax.
65. select ename, dname from emp, dept where emp. deptno = dept. deptno; // 92-year syntax table join + condition join
66. select ename, dname from emp join dept on (emp. deptno = dept. deptno); // new syntax
67. select ename, dname from emp join dept using (deptno); // The statement is the same as that for question 66, but using is not recommended. Suppose there are too many conditions.
/// // Equivalent connection ////////////////////////////////////// /////
68. select ename, grade from emp e join salgrade s on (e. sal between s. losal and s. hisal); // The join of the two tables is clearer than where.
69. select ename, dname, grade from emp e
Join dept d on (e. deptno = d. deptno)
Join salgrade s on (e. sal between s. losal and s. hisal)
Where ename not like '_ A %'; // connection of three tables
70. select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno); // The second method of self-join, which is the same as 62
71. select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno); // The left Outer join retrieves data that does not meet the conditions on the left.
72. select ename, dname from emp e right join dept d on (e. deptno = d. deptno); // right Outer join
73. select deptno, avg_sal, grade from (select deptno, avg (sal) avg_sal from emp group by deptno) t join salgrade s on (t. avg_sal between s. losal and s. hisal); // calculate the average salary level of each department
74. select ename from emp where empno in (select mgr from emp); // you can search for managers in the table.
75. select sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal <e2.sal); // questions do not need a group function max () maximum salary
76. select deptno, max_sal from
(Select avg (sal) max_sal, deptno from emp group by deptno)
Where max_sal =
(Select max (max_sal) from
(Select avg (sal) max_sal, deptno from emp group by deptno)
); // Calculate the name and number of the department with the highest average salary.
77. select t1.deptno, grade, avg_sal from
(Select deptno, grade, avg_sal from
(Select deptno, avg (sal) avg_sal from emp group by deptno) t
Join salgrade s on (t. avg_sal between s. losal and s. hisal)
) T1
Join dept on (t1.deptno = dept. deptno)
Where t1.grade =
(
Select min (grade) from
(Select deptno, grade, avg_sal from
(Select deptno, avg (sal) avg_sal from emp group by deptno) t
Join salgrade s on (t. avg_sal between s. losal and s. hisal)
)
) // Calculate the name of the Department with the lowest average salary.
78. create view v $ _ dept_avg_sal_info
Select deptno, grade, avg_sal from
(Select deptno, avg (sal) avg_sal from emp group by deptno) t
Join salgrade s on (t. avg_sal between s. losal and s. hisal );
// View creation, generally starting with v $, but not fixed