Selecting Rows and Columns SQL Projection

Selecting Columns: SQL Projection The projection is a fundamental relational database operation that produces the table from selected columnsnot all columns of one or more existing tables. The order in which you want has to be specified. For example, if you enter:

SQL > SELECT EMPNO ENAME FROM EMP;

employee number and employee name alone are displayed.

When you use asterisk to select all the columns of a table, the columns are displayed in the order in which they were defined when the table was created. If you wish to display the columns in a different order that as defined, then enter each column name instead of using an asterisk.

By including the DISTINCT clause in the SELECT command we can eliminate duplicate rows in the result. For example, enter

SQL SELECT DISTINCT JOB

2 FROM EMP;

This will list all the distinct jobs in EMP.
<b>Selecting Rows: SQL Selection</b>

Selection, another important relational database operation, selects and displays rows of one or more tables that satisfy some criteria. For example, if you want to list the employee number, name and job of all the employees *in department 2, enter:
SQL > SELECT EMPNO, ENAME, JOB, DEPTNO

2 FROM EMP

3 WHERE DEPTNO = 2;

To eliminate duplicate rows in the result, we include the DISTINCT clause in the SELECT command. To list all the distinct jobs in ElP, enter.

SQL > SELECT DISTINCT JOB

2 FROM EMP:

<b> Combining Selection and Projection</b>

To select specific rows from a table, then we include a WHERE clause in the SELECT statement. The general format of this statement is as:

SELECT < colunm name(s) > FROM < table name > WHERE < condition >

To view the employee number, name and job of all the employee in department 3 enter.

SQL > SELECT EMPNO, ENAME, JOB

2 FROM EMP

3 WHERE DEPTNO = 3;

Like logical expression above, we can also have compounded logical expression using AND and OR logical operators. For example, if you want to find the employee numbers, names and joining dates who joined the organization on of after 1 st JUNE 1996, then enter:

SQL > SELECT EMPNO, ENAME, JOIN DT

2 FROM EMP

3 WHERE JOB = "MANAGER"

4 AND JOIN DT > ='O 1 jun 96';

All operators in SQL*Plus are arranged in a hierarchy, as in high school Algebra. This hierarchy determines
their precedence during query execution. Where operators are of equal precedence in an expression, then
they are performed from left to right.
To select the rows that do not have certain values in a column, use the comparison operator! = ("not equal to"). For example, if you want to find all the managers who are not in department 1, then enter:

SQL > SELECT ENAME, JOB, DEPTNO FROM EMP

2 WHERE JOB ='MANAGER'

3 AND DEPTNO! = 1;
<b>Selecting Rows Within a Range</b>

If you wish to select rows in a table in which a column contains a value within a specified lower and upper limit, we use the BETWEEN operator. For example, if you wanted to know the names, jobs and joining dates of employees who jointed the organization between 1st January '89 and 1st January'94, then the query will be as follows:

SQL > SELECT ENAME, JOB, JOIN DT FROM EMP

2 WHERE JOIN_DT BETW EEN'01 JAN 89'AND'Ol JAN 94';

You can also negate BETWEEN like other operators, with NOT. The clause NOT BETWEEN means that only rows outside the range will be selected.

The IN operator, similar to BETWEEN is a shorthand solution to certain SQL problems. With IN, you can select rows that match one of the values in a list. For example, to find the employees who are analysis, engineers and salesmen, we enter:

SQL > SELECT ENAME, JOB, DEPTNO F ROM EMP

2 WHERE JOB IN('ANALYST','ENGINEER, 'SALESMAN');

SQL *Plus provides you with a marvellous pattern matching operator called LIKE. LIKE is able to search through the rows of a database column for values that look like a pattern you describe. It uses special characters to denote which kind of matching you wish to do: a percent slgn,(%o) called wild card, and an underline(un(ierscore) character (_) called a position market. The wild card is used for representing any sequence of zero or more characters and the position marker is used for representing any sequence of zero or more characters. To look for the names, jobs and employee numbers of all employees whose names begin with K, the query will be as follows:

SQL > SELECT EMPNO, ENAME, JOB FROM EMP

2 WHERE ENAME LIKE K%

<b>The Order by Clause</b>

With the ORDER BY clause you can order rows in ascending or descending order, by multiple columns or with null values.

Consider the following example:

SQL > SELECT EMPNO, ENAME, JOB FROM EMP

2 WHERE DEPTNO = I

3 ORDER BY EMPNO;

As seen above, the ORDER BY clause must be placed last in the SELECT command statement.

Domain Name Search