Selecting Rows

    Writing SELECT statements

    Performing Arithmetic Equations

    Handling NULL Values

    Changing Column Headings with Column Aliases

    Putting Columns Together with Concatenation

Limiting and Refining Selected Output

    The ORDER BY Clause

    The WHERE Clause

Using Single-Row Functions

    DECODE function

Go to MS-DOS.

Login to "SQLPLUS" as "iself” password “schooling."

Query the employee names and their salaries from the employee table.

>>           select ename, sal from emp;

For the same query, use the “as” clause for the column heading.

Notice that the column heading is changed.

>>           select ename, sal as salary from emp;

Use the same query without using the “as” clause.  The "AS" clause is a default option. 

>>           select ename, sal salary from emp;

Use the same query to use a double quote for the ename and sal column headings. 

Use a double quote if the column headings contain “case sensitive” or “blank” characters.

Notice that the column headings: (full name and salary)

>>           select ename “Full Name”, sal "Salary" from emp;

Use an arithmetic expression that calculates a 10 percent salary bonus for all employees.

>>           select ename, sal salary, sal * .1 bonus from emp;

Use the dual table as a dummy table to execute the system's function, user's functions or any other arithmetic expressions.

>>           select 100+200 total from dual;

Remember, the result of any arithmetic calculation with a "null" is a "null" value.

>>           select 100+null “Null + 100” from dual;

Query the employee names and their commissions.

Notice that the commission column contains the null value.

>>           select ename, comm commission from emp;

Use the (NVL) the null value function to assign a zero to any null value in the commission column.

>>           select ename, nvl(comm,0) commission from emp;

Use two vertical bars or pipe line to concatenate two or more columns and strings.

>>                select last_name || ', ' || first_name as "full name",

        from customers;

Use the "order by" clause to sort a query.

Query the employee names sorted by ascending order.

>>          select ename from emp

        order by ename asc;

Query the employee names sorted by descending order.

>>          select ename from emp

        order by ename desc;

Query the employee information whose employee number is 7788.

>>           select * from emp

        where empno = 7788;

Query the employee names whose names start with the letter “M.”

>>           select ename from emp

        where ename like 'M%';

Query the employee names whose names end with the letter “R.”

>>           select ename from emp

        where ename like '%R';

Query the employees name whose salaries between 2000 and 3000 dollars.

>>           select * from emp

        where sal between 2000 and 3000;

Query the employee names and their department names using the “decode” function.

Check if  the "department no" is 10 then print "accounting", else if the "department no" is 20 then print "research," or if the "department no" is 30 then print "sales".  Anything else print "others."

>>           select ename, decode (deptno, 10, 'accounting', 20,

        'research', 30, 'sales', 'others') as "Department", deptno from emp;

Now, you should practice this, over and over, until you become a master at it. 

Good Luck!