How to use SQL select statement?
In this article, we are going to learn how to use SQL select statement to retrieve data from tables or views. In the database, data stored in tables format and if you want to check that data then you should know how to use Oracle SQL statements.
Important points for SQL select statements
There are three ways to retrieve data from tables using SQL select statements.
- SELECTION: If you retrieve data ROW wise from tables that is called SELECTION.
- PROJECTION: If you retrieve data COLUMN wise from table that is called PROJECTION.
- JOINS: If you select data from multiple tables with single query that method called joins.
How to write SQL statements?
- You can write it in any case, SQL statements are not case sensitive.
- You can write SQL statements in multiple lines.
- Do not abbreviated or split the keywords across the line.
- To better understanding SQL queries, place Clauses in a separate line.
- To execute any query on SQL prompt the semicolon is required, but if you are using the SQL Developer tool then semicolon is optional.
Use below query to select all Columns from any tables.
SQL> select * from ocp;
If you want to select all columns from any table so use * (asterisk) with select command as in the above example, in the above example: [select (command) * (asterisk) from (keyword) OCP (table name)].
How to select any specific columns form any tables?
For selecting any specific columns from any tables, just specify the name of the column with the select command same as following examples, suppose I have one table which is name is OCP and it has 8 columns like (ENAME, JOB, SALARY, DEPTNO, COMM, JDATE, EMPNO, MGR), So I just want to select three columns from this table, using below query.
SQL> select ename, job, salary from ocp;
How to use Arithmetic operators with select command?
Types of Arithmetic operators is:
- + (Add)
- – (Subtract)
- * (Multiply)
- / (Divide)
1. Using “+” (Add)
SQL> select ename, salary + 100 from ocp;
In the above query, select two columns first ename, second salary, and using Add (+) operator with salary column, that means giving 100 rupees incentive to each employee.
2. Using “*” (Multiply)
SQL> select ename, salary * 12 from ocp;
In above example we are using “*” multiply operator which is calculate annual salary of each employees.
How to check current Date of Database server ?
SQL> select sysdate from dual;
In the above query, we are using sysdate this is the predefined functionality of the Oracle Database which always showing the system current Date, and dual also is a predefined object which is we use for temporary calculations.
3. Using “-” (Subtract)
SQL> select ename, sysdate - jdate from ocp;
In the above query, we are using a subtract operator which is used to calculate the number of working days of each employee, from joining date to till date.
4. Using “/” Divide operator
SQL> select ename, (sysdate - jdate)/7 from ocp;
In above query, we are using subtract and divide operator together to calculating number of working weeks of each employees.