Selecting Data - Oracle

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;