Oracle Learning record six more learning materials exercises

Learned the simplest kinds of statements, but also installed a few times the database, was looking at that high-level programming, but there is a primer did not see, and then read the first to say,

More practice, familiar with the most basic operation.

1. I used to log in before using Conn carriage return, and then enter the password, this document is the first one to introduce another method of login.

Conn Scott/xx

This will be directly logged in,/before the user name,/after the password

Conn Sys/xx as Sysdba

This is the login with the administrator

2. Then save the SQL you just entered as a file with the. sql suffix

SELECT * from EMP;

Save/home/oracle/sql/1.sql;

This will save this file to the path, open 1.sql can see the above command.

If you want to re-save a different statement, you want to replace it in this file.

Save/home/oracle/sql/1.sql Replace;

If you want to run a statement in a file, you can

@/home/oracle/sql/1.sql;

This will run.

3. Then there are some actions under the user

Show Current User

Show user;

Show all tables under this user

SELECT * from tab;

Show the structure of one of the tables

Describe EMP;

Or

DESC EMP;

Under this user to view the other user's table, the front to add that user's name separated by Dot

SELECT * from Oe.orders;

Of course, you have to have permission to look at other people's tables, generally use SYS to see anything else, and if you use Scott, you can't see it.

4. Some basic exercises, based on the table scott.emp

Bonuses and wages for each employee and

Select Ename, Sal+comm from EMP;

One problem here is that the bonus is null and cannot be obtained. Need to be solved.

Here the function of NVL () is used

Select Ename, NVL (Sal+comm, Sal) from EMP;

This means that if Sal+comm is null, the SAL is returned, if not NULL, the Sal+comm is returned, and NULL is returned if NULL

If you want to practise more, look for something, and now find a way to do it.

Select Ename, SAL+NVL (comm,0) from EMP; This is what I saw when I practiced the next chapter and came back to add.

Identify employees in department 10 who are neither ' MANAGER ' nor ' clerk ' and pay more than 2000

Select ename from emp

where deptno=10 and job not in (' MANAGER ', ' Clerk ') and sal>2000;

Find out what job there is a bonus

SELECT DISTINCT job from emp

Where Comm is not null and comm>0;

Here's the beginning of the function exercise

Character function of one-line function

Here to use the dual this virtual table, the relevant introduction to see this

Http://www.cnblogs.com/qiangqiang/archive/2010/10/15/1852229.html

Select Upper (' AAAA ') from dual;

Turn AAAA all into uppercase

Like what:

SELECT * from emp where Ename=upper (' Smith ');

Equivalent

SELECT * from emp where ename= ' SMITH ';

And, of course, all of them are lowercase lower.

Select lower (' FFF ') from dual;

and a capital letter, like when you get a name.

Select Initcap (ename) from EMP;

The next one's connected. Concat and | |

Select Concat (ename, Job) from EMP;

Select (Ename | | "| | Job) from EMP;

Calculating string Lengths

Select ename, Length (ename) from EMP;

Output sub-string

Select Ename, substr (ename, 2, 2) from EMP;

Replace character

Select ename, replace (ename, ' S ', ' SS ') from EMP;

Find substring Location

Select InStr (' Hello World ', ' or ') from EMP;

The result is where the or is located index

You can also set where to start looking, and the first few times you find

Select InStr (' Hello World OR and ', ' or ', 6, 2) from EMP;

This is the position value that starts with the 6th character and encounters the second time

Lpad and Rpad are restricted character lengths and are insufficient to be filled with specific symbols

Select Lpad (ename, 8, ' * ') from EMP;

Select Rpad (ename, 8, ' * ') from EMP;

Remove the edge of the top and tail of the space trim LTrim RTrim

Select Trim (' DDD ') from dual;

Select LTrim (' DDD ') from dual;

Select RTrim (' DDD ') from dual;

Rounding function Round

Select Round (454,-2) from dual;

Select Round (454.3456, 2) from dual;

The second parameter is negative, which is a positive number rounded from the first

The second argument is positive, which is to keep a few decimals, rounded back

Find more MoD

Select mod (3,4) from dual;

Select mod (4,3) from dual;

Truncation function trunc and round similar, but not rounded

Select Trunc (455666,-2) from dual;

Select Trunc (45.6666, 2) from dual;

Http://database.51cto.com/art/201004/197703.htm

Calculates the number of months between two dates Months_between

Select Months_between (sysdate, HireDate) from EMP;

You can use the round on it.

Select Round (Months_between (sysdate,hiredate), 0) from EMP;

Http://blog.sina.com.cn/s/blog_63c5e4a80100o7pl.html

Add a number of months to a date to get a new date

Select Add_months (sysdate, 2) from dual;

Select Add_months (Sysdate,-2) from dual;

Http://blog.sina.com.cn/s/blog_4cbadf5a0100fafh.html

The next specified date for a given date

Select Next_day (sysdate, ' Sunday ') from dual;

Next Sunday of the current date

http://blog.csdn.net/chenjinlin1/article/details/6582761

But the question in this article, he says, is that what's going to return is the cycle of the week, that's because it's coming back, not about going back to the next week.

Get the last day of the month Last_day

Select Last_day (sysdate) from dual;

Http://www.360sdn.com/oracle/2013/0615/401.html

Oracle Learning record six more learning materials exercises