SQL Distinct, SQL Select
- 22.43 - 0 komentar
Basic Select Statement
SQL statement is not case sensitive and can be entered on one or many lines. Clauses are placed on separate lines for readability and easy of editing. Oracle will recognize SQL statements even if it entered in mix format (lowercase and uppercase), but if we entered all the SQL statement in a standard form, we can construct a valid statements that easy to read and easy to edit.
A Select statement is used to retrieves Data from a table in the Database.Syntax :SELECT [DISTINCT] (*, column_name [alias],...)
FROM table_name;
The SELECT keyword choose which columns in a table that is returned by a query and FROM keyword identifies from which table the data should be picked up.
Selecting All Columns
we gonna get started with selecting all column in the SCOTT.dept table. to display all column in a table you must use an asterisk (*) character right after the SELECT keyword.
SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONYou can also mentioned all the column name separated by commas, after the SELECT keyword.
SELECT deptno, dname, loc
FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSelecting Specific Columns
You can select specific column that you want to display in the query result by specifies the column name separated by commas, like the previous example
SELECT deptno, dname
FROM dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONSChanging Column Title in the Query Output
You can change a column title in the query output using an alias for a column name that less descriptive or if you are using calculation in the query that produce a long column name.
Without Using Column Alias :
SELECT Ename, sal, comm, sal+comm
FROM emp;
ENAME SAL COMM SAL+COMM
---------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
. . . . . . . . . . . .
. . . . . . . . . . . .
14 rows selected.Using Column Alias :
SELECT Ename, sal, comm, sal+comm AS Total
FROM emp;
ENAME SAL COMM Total
---------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
. . . . . . . . . . . .
. . . . . . . . . . . .
14 rows selected.If the alias column has more than one word that separated by space, you must quote the alias column. Example :
SELECT Ename, sal, comm, sal+comm AS "Paid Salary"
FROM emp;
ENAME SAL COMM Paid Salary
---------- ---------- ---------- -----------
SMITH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
. . . . . . . . . . . .
. . . . . . . . . . . .
14 rows selected.Displaying Unique Values with SQL Distinct Keywords
Distinct keyword will return the unique values from specified column and remove the duplicate values from the query result. First we are going to display all rows in the EMP table :
SELECT empno,ename,deptno
FROM emp;
EMPNO ENAME DEPTNO
------ ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
14 rows selected.Now we are going to display how many unique department in the EMP table using SQL Distinct keyword :
SELECT distinct(Deptno) as "Unique Dept"
FROM emp;
Unique Dept
-----------
10
20
30Combining Column Using Concatenation Operator
Concatenation Operator is used to combine two or more column into a single column in the query output. Concatenation in oracle signed by two "||" (pipe) character. We can see how this operator work using a simple example bellow.
SELECT ename||' Work as a/an '||job
as "Employees Information"
FROM emp;
Employees Information
---------------------------------
SMITH Work as a/an CLERK
ALLEN Work as a/an SALESMAN
WARD Work as a/an SALESMAN
. . . .
. . . .
14 rows selected.More Example :
SELECT ename||' Work as a/an '||job||
' with $'||sal||' monthly salary'
as "Employees Information"
FROM emp;
Employees Information
-----------------------------------------------------
SMITH Work as a/an CLERK with $800 monthly salary
ALLEN Work as a/an SALESMAN with $1600 monthly salary
WARD Work as a/an SALESMAN with $1250 monthly salary
. . . .
. . . .
14 rows selected.
