DB2 SQL - DML (Data Manipulation Language) - Part II





Different Types of SELECTS

SELECT *
SELECT COLUMN(s)
SELECT DISTINCT
SELECT .. WHERE
SELECT ... WHERE... AND / OR
SELECT ... WHERE... LIKE
SELECT ... WHERE... IN
BETWEEN Operator


Let us assume we have following tables defined on our database.

SORT * FROM

Select all records from DEPARTMENT table

SQL Query : SELECT * FROM DEPARTMENT

Result :

SORT COLUMN NAME FROM

Get all Department names from the table.

SQL Query : SELECT DEPTNAME FROM DEPARTMENT

SELECT DISTINCT

Get distinct departments from EMPLOYEE table.

SQL Query : SELECT DISTINCT DEPT FROM EMPLOYEE

Result :
DEPT
A00
D01

SELECT ... WHERE ...

Get the all the employees working in department number A00

SQL Query : SELECT * FROM EMPLOYEE WHERE DEPT = 'A00'

Result :

Following row wont get displayed since this employee belong to DEPT D01

1006 MIKE DWAYER 1001 D01 15000 2002-07-19

Following Relational Operators we can use in WHERE clause

The WHERE clause is used to specify that only certain rows of the table are displayed, based on
the criteria described in that WHERE clause.

SELECT ... WHERE ... Complex Comditions

The AND operator joins two or more conditions, and displays a row only if that row's data satisfies ALL conditions listed For example, to display all staff making over $15,000, and belong to department no A00 use:

SQL Query :

SELECT EMPNAME
FROM EMPLOYEE
WHERE SALARY > 15000 AND DEPT = 'A00'

Result :
The OR operator joins two or more conditions, but returns a row if ANY of the conditions listed hold true.

SELECT ... WHERE ... LIKE - NOT LIKE

Get all employee details whoes names starts with M.

SQL Query : SELECT * FROM DEPT FROM EMPLOYEE WHERE EMPNAME LIKE 'M%'
Result :

The percent sign (%) is used to represent any possible character (number, letter, or punctuation) or set of characters that might appear after the "M". To find those people with Name's ending in "M", use '%M', or if you wanted the "M" in the middle of the word, try '%M%'. The '%' can be used for any characters in the same position relative to the given characters. NOT LIKE displays rows not fitting the given description.

NOTE : The wild-card characters for LIKE are '%' and '_'.
Underscore matches any single character. Percent matches zero or more characters.

SELECT ... WHERE ... IN

When want to compare list of values compare against the table field you can use IN.

Get all employee details for employee numbers 1001, 1003 and 1005

SQL Query :

SELECT EMPNAME FROM EMPLOYEE
WHERE EMPNO IN ( 1001, 1003 and 1005 )

Result :

SELECT ... WHERE ... BETWEEN

You can use BETWEEN Operator , to get the range of records between two values.

SYNTAX : Field [NOT] BETWEEN Value-1 AND Value-2

Get all employee details who is getting salary between 15000 and 40000 ( including these amounts)

SQL Query :

SELECT EMPNO, EMPNAME FROM EMPOYEE
WHERE SALARY BETWEEN 15000 AND 40000

Result :

SELECT ... WHERE ... IS [NOT] NULL

We need to use this operator when we want to get the record which contains NULLS.

Get all Records who does not have managers assinged.

SQL Query :

SELECT EMPNO, EMPNAME FROM EMPLOYEE
WHERE MGRNO IS NULL

Result :





SQL / DB2 UNIVERSAL DATABASE / DB2 DATABASE / SQL / DB2 / DB2 DATABASE / SQL / DB2 / DB2 DATABASE