Home arrow Oracle arrow Page 4 - Oracle Database Fundamentals

Selecting Data - Oracle

In this article we will mainly focus on basic database development using Oracle. We will learn how to create new tables, alter them, insert data into the database, update data, retrieve data, delete data and drop tables. We have lots to do, so let's get started.

TABLE OF CONTENTS:
  1. Oracle Database Fundamentals
  2. Creating Database Tables
  3. Inserting Data
  4. Selecting Data
  5. Updating Data
By: Mamun Zaman
Rating: starstarstarstarstar / 103
May 01, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

The syntax for selecting data from an Oracle table is

select columnname, columnname... from tablename;

If we want to select data from our department table we will use

select dept, empid, deptid from department;

This will select all data from a department table. There is a shortcut version of selecting all data from a table, and that is *.

select * from department;

The above two SQL statements are the same. To get the count of records (rows) in a table use the count() function.

select count(*) from department;

To get the maximum value of a column we can use the max() function. Use the min() function to get the minimum.

select max(salary) from employee;

select min(salary) from employee;

If we want to get the information about a particular employee whose first empid is 5 from our employee table, the SQL will be

select * from employee where empid=5;

In SQL we can add a where clause such as where empid=5 to get data that match the condition. There can be more than one where condition combined by 'and' and 'or'.

We want to get the full name of the employees whose deptid is 1 and who earn more than 5000.

select "First Name"||' '||'Last Name' from employee where deptid=1 and salary>5000;

  • In Oracle, || is used for string concatenation as well as concat() function.

If you see the resulting column name then you can see it is the same as the expression we used in our SQL. We can give an alias for this expression using AS. For example:

select "First Name"||' '||'Last Name' as 'Full Name' from employee where deptid=1 and salary>5000;

or (without AS, it is the same)

select "First Name"||' '||'Last Name' 'Full Name' from employee where deptid=1 and salary>5000;

We can also give an alias to a table name in this way. We can sort the output of a SQL select using order by.

select "First Name", 'Last Name', salary from employee where salary>5000 order by salary;

The above SQL will select the first name, last name and salary from the employee table whose salary is more than 5000, and sort the result using salary in ascending order. The default order is ascending (asc).

select "First Name", 'Last Name', salary from employee where salary>5000 order by salary asc;

To put the list in descending order use desc.

select "First Name", 'Last Name', salary from employee where salary>5000 order by salary desc;



 
 
>>> More Oracle Articles          >>> More By Mamun Zaman
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: