SQL

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

Go to MS-DOS and login to "SQLPLUS" as "oracle" password "learning"

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;

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"

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

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;

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�

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

SQL> SELECT ename, comm commission FROM emp;

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

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"

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.

ORDER BY ename ASC;

Query the employee names sorted by descending order.

ORDER BY ename DESC;

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

Query the employee information whose employee number is 7788.

WHERE empno = 7788;

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

WHERE ename LIKE 'M%';

Query the employee names whose names end with the letter �R.�

WHERE ename LIKE '%R';

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.

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

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.

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',

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.