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