In Oracle, the SELECT statement is used to get the data from the tables, views, etc. The following is the syntax of the SQL SELECT statement:
Oracle SELECT Statement Syntax
[ with_clause ] SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list FROM { table_reference | join_clause | ( join_clause ) | inline_analytic_view } [ , { table_reference | join_clause | (join_clause) | inline_analytic_view} ] ... [ where_clause ] [ hierarchical_query_clause ] [ group_by_clause ] [ model_clause ]
Below is the simple version of the SELECT statement syntax:
SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1 WHERE COLUMN1 = 123 ORDER BY COLUMN1, COLUMN2;
In this SQL query:
- First, we specify the name of the table from which we want to retrieve data. In this case, we're interested in a table called "TABLE1." Think of it as telling the database, "Hey, go look in 'TABLE1' for the information we need."
- Next, we indicate which specific columns we want to see data from. It's like telling the database, "We're interested in the values stored in three columns: COLUMN1, COLUMN2, and COLUMN3." This part is essential because it tells the database exactly what pieces of information we want to retrieve.
So, in a nutshell, this SQL query fetches specific data (COLUMN1, COLUMN2, COLUMN3) from "TABLE1," but it only retrieves data where COLUMN1 has the value 123. Finally, it arranges the results in ascending order based on the values in COLUMN1 and COLUMN2. This makes it easier for us to work with and present the data.
SELECT Statement Examples
Example-1:
Suppose you want to fetch all columns and all rows of EMP table, then you would simply execute the following Oracle SELECT statement:
SELECT * FROM EMP;
In this SQL query:
- We use the asterisk symbol (*) after the SELECT statement. Instead of listing specific column names like COLUMN1, COLUMN2, and so on, the asterisk is a shorthand way of saying, "Give me all the columns." It's like telling the database, "I want to see all the information available in each column of the table."
- Then, we specify the table from which we want to retrieve data. In this case, it's a table named "EMP." This informs the database that we want to fetch data from the "EMP" table.
So, to sum it up, this SQL query retrieves all the data from every column in the "EMP" table. It's a way to quickly see all the information stored in that table without specifying each column individually. This can be useful when you want a complete view of the data in the table.
Example-2:
And if you want to fetch only a few specific columns then your SQL query would be as follows:
SELECT EMPNO, ENAME, SAL, HIREDATE FROM EMP;
In this SQL query:
- We start with the SELECT statement, followed by a list of specific column names: EMPNO, ENAME, SAL, and HIREDATE. This tells the database, "I'm interested in seeing the values stored in these four columns."
- After specifying the columns we want, we use the FROM clause to indicate the source of the data. In this case, we're fetching data from a table called "EMP." Think of it as instructing the database to go to the "EMP" table to get the information we've requested.
So, in simple terms, this SQL query retrieves specific data from the "EMP" table. It fetches the values found in the EMPNO, ENAME, SAL (salary), and HIREDATE columns. This can be useful when you only need to see certain pieces of information from the table rather than all the columns.
Example-3:
The following is an example of fetching the data from the EMP table for department number 10 only.
SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10;
In simple terms, this SQL query fetches data from the "EMP" table but only for employees who belong to Department 10. It specifically retrieves the values in the EMPNO (employee number), ENAME (employee name), and SAL (salary) columns for these employees. This way, we can focus on the information we need for employees in that particular department.
Example-4:
To sort the result order by employee name:
SELECT EMPNO, ENAME, SAL, COMM FROM EMP WHERE DEPTNO = 10 ORDER BY ENAME;
So, in simple terms, this SQL query fetches data from the "EMP" table, but only for employees in Department 10. It retrieves values from the EMPNO (employee number), ENAME (employee name), SAL (salary), and COMM (commission) columns. The retrieved data is then organized and displayed in alphabetical order by employee name. This makes it easier to view and analyze the information for employees in that specific department.
In this tutorial, we've illustrated SQL query examples using a clear and visually informative format. Each example demonstrates the fundamental components of an SQL query, including SELECT, FROM, WHERE, and ORDER BY clauses. This approach aims to make SQL query comprehension more accessible, whether you're new to SQL or seeking a quick reference for query construction and analysis."
Reference: Oracle SELECT Statement - Oracle Docs
Leave a comment
You must login or register to add a new comment.