Oracle Basics

In this article , I will share some basic command for beginners to understand the Oracle and manage Oracle in their daily work;

Oracle is running under server and client mode, in the server, it is runing a listener service 24 * 7 in the background . the configuration file in the server side is called " listener.ora" specifying the ports and IPs. In the client side, the configuration file  is called "tnsnames.ora". normally , when i manage the database, I don't change these 2 files very ofen. Oracle provides some scripts and "Net manager" for you to easily manage listeners. if these tools fail , then you may need some experience to troubleshoot. Normally it's either the configuration problem or network problem.

Oracle Basics 

Lsnrctl stop/start   // Start / stop listner
Net start oracleserviceorcl  //Start database Service
Conn scotts/123 // connect database
Show user // show current user
Clear screen // clear screen
Spool C:/123.txt // specify the external file
Spool off   // save sql command and result into external file
Edit // show the last sql and edit and run
Set time on // show sql running time
Show error // show the last error message
Host + <dos command> //call system command: eg "host mkdir C:/as/123"
@C:/123.sql // run the sql external runnable script

Basic SQL traps

SQL is a query language for DBA and engineers to use in their daily jobs . Here I won't share the basic grammars , I will share some traps and places you and i may be take notice in your work.

Truncate //delete whole table records but keep table structure，no log，can't rollback
Drop // delete all records and table structures
Desc table1 // check table structure
Alter session set nls_date_format="yyyymmdd" //modify current session's database time
Insert into student2 select * from student; // insert one table into another
Select * from student where name like "S_"; // "_" is a placeholder, it equals 

                                               length(name) = 2 and S%
Select * from student  order 1 desc, 2; // order by first and second column

String Functions

Techniques

SELECT EXTRACT(YEAR FROM SYSDATA) FROM DUAL ; //extract year, month,  day
Select last_day(sysdate) from dual ; // show the last day of a month
Select floor(4.6754) from dual; // only keep the integer removing decimals 
NVL2  NVL DECODE NULLIF  // some null-related functions
To_char, To_date , To_number // some conversion-related functions
Union union all intersect minus // 4 collection-related manipulations
Rename student1 to student2 // rename a table
Alter table  student rename column sno to snumber; // rename a column

Complex SQL traps 

Below are some traps i summarise in my work , if you find anything needed to be notified , please leave msgs.

SQL running order: 1 from 2 where 3 group by 4 having 5 select 6 order by
exists(select * from user) // return true/false, if data is existed , return true, otherwise , false

Select * from student where name in (select name from student group by name having (count(*) > 1)) // select students with duplicated names 

Select  a.name, b.name  from student a, student b where a.no = b.no //inner join ,before you do this ,u'd better list all records and find the columns with same names, then you try to join carefully;

//update result table 
Update result set grade = (select grade from 
       (select sno , 
                   case when socre >= 90 then "excellent",
          When core >=80 then "good",
                        When core >=70 then "fair",
          Else "fail" end grade from result) a where result.sno = a .sno)

// orderly list row_number non-duplicated rows  

Select sno , name row_number() over(order by score desc) from table 1;

//orderly list row number value duplicated，rank is jumping , rows
Select sno , name rank() over(order by score desc) from table 1; 

//orderly list row number value duplicated，rank is continuous , rows
Select sno , name dense_rank() over(order by score desc) from table 1; 
Select sno , name rank() over(order by score desc) from table 1; 

sign() // return 1 -1 0 ,， sign(-99) return -1

// decode with sign together to be used
Select month, decode(sign(sales-sales2), 1,sales, -1, sales2, 0. null)); 

Oracle row to column:

Select names, 
  sum(decode(a.season, '1', a.sales, 0) // 1st sesson, 
        sum(decode(a.season, '2', a.sales, 0) //2ed sesson，
        From table1 a group by a. Names; //use sum+decodeto implement 

rownum // can only apply < / <= , if other signs are used , you need give a alias
Select * from (select a.*, rownum rn from table a) where rn <=5 and rn > 3;

Delete student where sno in (select sno from student group by sno having (count(*) > 1)) and rowid not in (select min(rowid) from student group by sno having (count(*) > 1)) //remove duplicated record

Select ... from .. where sal > 20 group by grouping set ((deptno),(deptno,empno,ename),()); // group and generate statistics reports .