iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 02

"Glory is fleeting, but obscurity is forever." - Napoleon Bonaparte (1769-1821)

 

Read first then play the video:

   SQL-VIDEO-Basic Important SQL Notes

   SQL-VIDEO-Selecting Data from Oracle

  

Selecting Data (SQL)

In this section, you will learn how to query an Oracle Table.

 

Go to MS-DOS and login to "SQLPLUS" as "iself” password “schooling."

SQL> CONNECT iself/schooling@school

 

SELECT statements

The SELECT statement is a DML (Data Manipulation Language) statement. DML statements are SQL commands that allows you to retrieve and manipulate a data in the database. SQL commands are divided to three parts:

 

DML - Data Manipulation Language are SQL commands that allows you to query and manipulate data such as UPDATE, SELECT, INSERT, and DELETE.

 

DDL - Data Definition Language are SQL commands that allows you to create, alter, and/or remove an object in the database such as CREATE TABLE, DROP TABLE, ALTER TABLE, etc.

 

DCL - Data Command Language are SQL commands that allows you to execute a command in the SQLPLUS such as CONNECT, etc.

 

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

SQL> SELECT ename, sal FROM emp;

 

Column Heading

As you notice from the above query statement, the columns heading were the columns name as a default. These column names most of the time are meaningless. You use the column heading to make them more meaningful.

For the same above query, now you use the “as” clause for the column heading. Notice that the column heading is changed.

SQL> SELECT ename, sal AS salary FROM emp;

 

Use the same above query without using the “as” clause. The ‘as’ clause is a default option.

SQL> SELECT ename, sal salary FROM emp;

 

Use the same above query to use a double quote for the ename and sal as “Full Name” and “Salary” column headings. You use a double quote if the column headings contain “case sensitive” or “blank” characters.

SQL> SELECT ename “Full Name”, sal "Salary"

              FROM emp;

 

Arithmetic Expression

You can use an arithmetic expression in the SELECT statement.

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

SQL> 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.

SQL> SELECT 100+200 total FROM dual;

 

NULL value

A null value means I don’t know. Note that any number that is added, multiplied, divided, or subtracted from NULL will be NULL.

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

SQL> SELECT 100+null “Null + 100”

              FROM dual;

 

Query the employee names and their commissions. Notice that the commission column contains the null value.

SQL> SELECT ename, comm commission FROM emp;

 

NVL function

You can use the NVL function to substitute any not NULL value with a NULL column. This way, you will get more control on how to manipulate that column in a WHERE clause.

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

SQL> SELECT ename, NVL(comm,0) commission

           FROM emp;

 

Concatenation

You will use two vertical bars (||) to concatenate two or more strings or columns together.

Use two vertical bars or pipe line to concatenate a last and first name with the following format (example: Smith, Joe).

SQL> SELECT last_name || ', ' || first_name AS "full name"

              FROM customers;

 

ORDER BY clause

You use the ORDER BY clause to sort one or more columns in a query.

Use the "order by" clause to sort a query. Query the employee names sorted by ascending order.

SQL> SELECT ename

              FROM emp

              ORDER BY ename ASC;

 

Query the employee names sorted by descending order.

SQL> SELECT ename

              FROM emp

              ORDER BY ename DESC;

 

WHERE clause

You use the WHERE clause to query one or more records selectively.

 

Query the employee information whose employee number is 7788.

SQL> SELECT *

              FROM emp

              WHERE empno = 7788;

 

LIKE condition

The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second. The pattern can contain the special pattern-matching characters:

· % matches any string of any length (including length 0)

· _ matches any single character.

To search for the characters % and _, precede them by the escape character. For example, if the escape character is @, then you can use @% to search for %, and @_ to search for _.

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

SQL> SELECT ename

              FROM emp

              WHERE ename LIKE 'M%';

 

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

SQL> SELECT ename

              FROM emp

              WHERE ename LIKE '%R';

 

BETWEEN condition

The BETWEEN conditions specify a test to query all the records between the selection values inclusively.

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

SQL> SELECT ename

              FROM emp

              WHERE sal BETWEEN 2000 AND 3000;

 

Query the employees’ name that’s their names start with a letter ‘C’ and end with a letter ‘F’.

SQL> SELECT ename

              FROM emp

              WHERE upper (ename) BETWEEN ‘C’ AND ‘EZZZ’;

 

You used the UPPER function in the above query to convert all names to upper case in the case if the employee’s names are case sensitive.

 

DECODE function

The DECODE function is like an IF-THEN-ELSE statement. It compares the content of a column to each of the comparisons. If there is a match, then it replaces the value. If there is no match, then the else action will be performed.

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."

SQL> SELECT ename, DECODE (deptno, 10, 'Accounting',

                                                                       20, 'Research',

                                                                       30, 'Sales',

                                                                       'Others') AS "Department"

               FROM emp;

 

 

“I know of only one bird - the parrot - that talks; and it can't fly very high.” Wilbur Wright (1867 - 1912), declining to make a speech in 1908

 

Questions:

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

Q: Do the above query and use an “as” clause for the “salary” column aliases or column headings.

Q: Repeat the previous query and have “Full Name” for the ename’s column heading and “Salary” for the “sal” column heading.

Q: What is the result of 100 + NULL?

Q: Query the employee names with their commissions.

Q: Use the (NVL) the null value function to assign zero to any null value in the commission column for the previous query.

Q: Concatenate the customers’ last name and first name separated by comma.

Q: Query the employees name sorted by ascending order.

Q: Query the employees name sorted by descending order.

Q: Query the employee information whose employee number is 7788.

Q: Query the employees name whose names start with the letter “M.”

Q: Query the employees name whose names end with the letter “R.”

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

Q: Query the employees name and their department name using the “DECODE” function. If the department number is 10 then print "accounting.” If the department number is 20 then print "research," or if the department number is 30 then print "sales." Anything else prints others.