Sunday, 27 October 2013

About SELECT Command




I would like to discuss about a very simple command, but the most used at the same time. Its the only display statement we have in our Oracle SQL. Hence it gets the most importance. Its the SELECT Query. Its called a Data Query Language Command: a Special set which is used to retrieve and display the rows from a table. We have different types of modifications in the command to suite the different modulations of the output: for example selective rows, differed names etc.,

There can have three types of operations basically a table can have:
  1. Selecting a few Rows from the table.
  2. Selecting a few Columns from the table.
  3. Joining two tables and getting together the output.

There are three types of syntax modifications for the three types of retrievals:




SELECT FROM  

This gets you the suggested columns from the table. 

For example:  We have 

Student(num, name,total,average,section) 

and the query goes: 



SELECT num,name,section FROM Student;

SELECT /* FROM  
WHERE ; 
                           
This gets you the selected rows for a table. For example in our previous Student table, if we want to retrieve the details of a student of number 101 from the table then, We have the following Query: 


SELECT * FROM Student WHERE num=101;

SELECT FROM ,.... WHERE ;
         
This gets you rows from two tables that share a common thing. Now that in our example if we have another table Marks 

Marks(num,sub1,sub2,sub3)

If we want to have the marks of the student with number and name printed along with the marks, then: The Query will be,

SELECT s.num,s.name,m.sub1,m.sub2 from Student s,Marks m where s.num=m.num;

Now that we have seen the ways to have our select query, we can also use some constants while displaying the values: using an operator || . The constant is enclosed under ' '.


Example: SELECT num,||' is '||, name FROM Student;

This statement displays number and name with 'is' in the middle. Another interesting this is the use of keywords in the middle. Oracle uses keywords like 'SYSDATE' , 'USER' etc., for specific things. For Example: 

SELECT USER FROM DUAL; 

displays the current working user, while 

SELECT SYSDATE FROM DUAL; 

displays the current date.

No comments:

Post a Comment