Oracle SQL Basic - Day 1
Writing Simple Queries and Sorting Results
Oracle SQL is a powerful tool used to communicate with Oracle databases. Whether you're a student, analyst, or developer, understanding how to write basic SQL queries is a foundational skill. These SQL can be written in various tools such as TOAD, PL SQL Developer or ORACLE SQL Developer.
Before start make sure Oracle database installed and configured with demo schema. Oracle database can be download from Oracle web site, for practice purpose the Oracle database standard edition can be use. alternate of these without installation ORACLE APEX apex.oracle.com is the best option you just need to sign in then use SQL Work shop to practice online or cloud.
In this lesson, we will cover:
- How to retrieve data from a table using the SELECT
statement
- How to choose specific columns
- How to sort data using the ORDER BY
clause
1. The SELECT Statement
The SELECT statement is used to retrieve data from a source which could be table or view . It's the most commonly used SQL command. It is very simple English based text which select all or specific columns from table or view. Below are some examples
Syntax:
SELECT
column1, column2, ...
FROM
table_name;
Example
Table: employees
select employee_id, first_name, last_name, salary from employees;
The above SQL select data from employees table for some columns, the result would be as below
|
employee_id |
first_name |
last_name |
salary |
|
101 |
Alice |
Johnson |
7000 |
|
102 |
Bob |
Smith |
6000 |
|
103 |
Clara |
Davis |
8000 |
2. Retrieving All Columns
To retrieve everything from a table: To get all data and columns we can use * sign. Remember that SQL is not case sensitive so it can be written in upper/lowr/mix case in single line or multiple lines. It should be end with ; sign that is called terminator.
In tools such as TOAD , PL SQL Developer or SQL Developer we can execute the command through Execute button in tool bar or Press F8 for short cut. If you are using command prompt then Just press ENTER after terminator (;)
SELECT
* FROM employees;
SELECT * FROM DEPARTMENTS;
This returns all rows and all columns. The rows and columns concept is same as Excel sheet, Each table contain data and related columns. All data saved based on columns with different data types such as character, date , number , long character data, binary data etc. It will explain in next lessons.
3. Retrieving Specific Columns
If you only need a few columns, list
them specifically:
SELECT
first_name, last_name FROM employees;
This displays only the first and last names of employees. To know columns names you can use DESC EMPLOYEES command then select specific columns based on that.
4. Concatenating Columns
You can combine column values in
Oracle SQL using the ||
operator:
SELECT first_name || ' ' || last_name AS full_name FROM employees;
In above command we combine two different columns and show as single value with different title/heading "FULL_NAME". we called it COLUMN ALIAS. The column alias is for display purpose only it can not be use in WHERE OR ORDER BY clause.
Output:
|
full_name |
|
Alice Johnson |
|
Bob Smith |
|
Clara Davis |
5.
Sorting Results Using ORDER BY
The ORDER BY clause lets you sort the result of a query by one or more columns. Data can be sort as ascending or descending orders. It will be applicable for DATES, CHARACTER and NUMBERS.
Use ASC or DESC
Syntax:
SELECT column1, column2
FROM
table_name
ORDER
BY column1 [ASC|DESC];
- ASC means
ascending (default)
- DESC means
descending
Examples:
Sort
by salary (ascending):
SELECT
first_name, salary
FROM
employees
ORDER
BY salary;
Sort
by salary (descending):
SELECT
first_name, salary
FROM
employees
ORDER
BY salary DESC;
Sort
by last name:
SELECT
first_name, last_name
FROM
employees
ORDER
BY last_name;
Summary
In this lesson, you’ve learned how
to:
- Use SELECT to retrieve data
- Specify individual columns in a query
- Concatenate columns
- Sort results using ORDER
BY
These skills are essential for
reading and presenting data effectively from an Oracle database.
Next Steps
Once you're comfortable writing
basic queries and sorting data, the next topics to explore would include:
0 Comments