Oracle Learning Record a basic instruction exercise

The Oracle 11g is used

In fact, it has been a few, but not much study, now began to re-learn, including SQL statements and so on.

Start after installation,

I installed the client sqldeveloper, but I need to connect after entering, I forgot how to do it, and began to view the data.

Click the new connection, pop up a dialog window, the connection name I use the SYS, the user name is also the SYS, the password is to install the database

Write the password, and then in the following "character" this changed to SYSDBA, the following "SID" filled in is ORCL

This will log in as the database administrator.

Because I did not unlock the other users when I finished installing the database, just like Scott, who was practicing, can be unlocked here.

Find other users below, find Scott, right click to select "Edit User", remove the few hooks, then set a new password, then OK,

Then go to create a new connection, log in with Scott, Sid or ORCL this is to be determined at the time of installation.

Here's how to start using Sql*plus to learn SQL commands:

2. SELECT * from EMP; Don't forget the semicolon in the back, which lists all the data in the table EMP

4. Select Job from EMP; This is to remove all data from the job column in the table emp

6. SELECT distinct job from EMP; This takes out the different data in the job column of the table EMP, which means that the same data remains only one

8. SELECT * from emp where Sal > 2000; This is the addition of a condition that only shows people information for Sal greater than 2000

10. Select ename from emp where Sal > 2000; This is the only name that shows Sal greater than 2000.

12. Select ename from emp where Sal > N or (Sal > $ and Sal < 2000); This is more than one condition together

14. SELECT * from emp where job in ' clerk '; This is the information that lists the job is Clerk's personnel

16. SELECT * from emp where job in (' Clerk ', ' ANALYST '); This is the information that lists the job is clerk and analyst

18. SELECT * from emp where hiredate between ' 20月-February -81 ' and ' March-December-81 '; This is the person who lists the date in this range

20. Select DISTINCT job from the EMP where job like '%an% '; This is the job that lists the job name with an

22. Select Sal from emp order by SAL ASC; This is a small-to-large arrangement sal

24. Select Sal, HireDate from emp ORDER by Sal ASC, HireDate DESC; This is by Sal Row, if the same and then press HireDate from the big to the small row

26. Select Ename,job,sal from emp where Sal > N order by sal ASC; Now add the Where condition

Here are the exercises for the function:

AVG (average)
Count (Count)
Max (max)
MIN (minimum value)
SUM (summation)

14. Now ask all Sal's and

Select sum (SAL) from EMP;

15. Ask how many people

Select COUNT (ename) from EMP;

16. Ask for the number of jobs, which will remove duplicate job items

Select COUNT (Distinct job) from EMP;

17. How many people have comm bonuses

Select COUNT (ename) from EMP where comm are NOT null;

18. According to the position to ask Sal and

Select Job,sum (SAL) from the EMP group by job;

19. Set conditions for functions with having, post Sal and more than 5000 posts

Select Job,sum (SAL) from the EMP group by job have sum (SAL) > 5000;

20. Alias, first give sum (SAL) named Sum, the name of the alias in the middle of a space

Select sum (SAL) "sum" from EMP;

21. Give the table an alias

Select T.job "JOBS", Sum (sal) "sum" from the EMP T Group by Job;

Here's the table link.

With EMP and dept These two tables to practice, which I'm a little confused

22. Show the SAL for each region and this shows that the result is less Boston because there is no 40 this deptno in the EMP, this is an internal link

Select T1.loc "Localaddr", Sum (t2.sal) "SALS" from dept T1, EMP T2 where T1.deptno=t2.deptno Group by T1.loc;

Oracle Learning Record a basic instruction exercise