In this Section we are going to try out all possible simple SELECT queries. First lets create a sample table called emp which should have rows like the following.

emp_no	dept_no	first_name	last_name	salary
7000	114	Read	Vitamin	62,000
4000	113	John	Row	55,000
5000	114	Robert	Howard	65,000
2000	112	Larry	King	60,000
1000	112	Philip	Josheph	65,000
6000	115	Tony	Peter	72,000
3000	111	Micheal	Reed	46,000
8000	113	Philip	Josheph	65,000
9000	115	Tony	Peter	72,000
1100	114	Micheal	Reed	46,000

Follow this command to create a temporary table:

CREATE SET VOLATILE TABLE emp, NO FALLBACK,
CHECKSUM = DEFAULT,
LOG
(
“emp_no” INTEGER,
“dept_no” VARCHAR (10),
“first_name” CHAR (20) CHARACTER SET LATIN NOT CASESPECIFIC,
“last_name” CHAR (20) CHARACTER SET LATIN NOT CASESPECIFIC,
“salary” INTEGER
)
PRIMARY INDEX ( “emp_no” )
ON COMMIT PRESERVE ROWS;

Insert values into this temp table using the following insert commands.

INSERT INTO emp VALUES (1000,112,’Philip’,'Josheph’,65000);
INSERT INTO emp VALUES (2000,112,’Larry’,'King’,60000);
INSERT INTO emp VALUES (3000,111,’Micheal’,'Reed’,46000);
INSERT INTO emp VALUES (4000,113,’John’,'Row’,55000);
INSERT INTO emp VALUES (5000,114,’Robert’,'Howard’,65000);
INSERT INTO emp VALUES (6000,115,’Tony’,'Peter’,72000);
INSERT INTO emp VALUES (7000,114,’Read’,'Vitamin’,62000);
INSERT INTO emp VALUES (8000,113,’Philip’,'Josheph’,65000);
INSERT INTO emp VALUES (9000,115,’Tony’,'Peter’,72000);
INSERT INTO emp VALUES (1100,114,’Micheal’,'Reed’,46000);

1. Selecting all columns and all rows

SELECT *FROM emp;

Result:-

emp_no	dept_no	first_name	last_name	salary
7,000	114	Read	Vitamin	62,000
4,000	113	John	Row	55,000
5,000	114	Robert	Howard	65,000
2,000	112	Larry	King	60,000
1,000	112	Philip	Josheph	65,000
6,000	115	Tony	Peter	72,000
9,000	115	Tony	Peter	72,000
8,000	113	Philip	Josheph	65,000
3,000	111	Micheal	Reed	46,000
1,100	114	Micheal	Reed	46,000

2. Selecting with DISTINCT clause.

SELECT DISTINCT *FROM emp;

Result:-

emp_no	dept_no	first_name	last_name	salary
1,000	112	Philip	Josheph	65,000
1,100	114	Micheal	Reed	46,000
2,000	112	Larry	King	60,000
3,000	111	Micheal	Reed	46,000
4,000	113	John	Row	55,000
5,000	114	Robert	Howard	65,000
6,000	115	Tony	Peter	72,000
7,000	114	Read	Vitamin	62,000
8,000	113	Philip	Josheph	65,000
9,000	115	Tony	Peter	72,000

Note: - When you use DISTINCT * rows would be ordered

3. Selecting with WHERE Clause

SELECT DISTINCT *
FROM emp
WHERE dept_no = 114 ;

Result :-

emp_no	dept_no	first_name	last_name	salary
1,100	114	Micheal	Reed	46,000
5,000	114	Robert	Howard	65,000
7,000	114	Read	Vitamin	62,000

4. Selecting with GROUP BY Clause

SELECT emp_no, dept_no, first_name, last_name, salary
FROM emp
WHERE dept_no = 114
GROUP BY emp_no, dept_no, first_name, last_name, salary

Result:-

emp_no	dept_no	first_name	last_name	salary
7,000	114	Read	Vitamin	62,000
5,000	114	Robert	Howard	65,000
1,100	114	Micheal	Reed	46,000

Note :- When you use GROUP BY, You have to mention column names in the SELECT query and while GROUPING you have the option of either mentioning column names or positional value of the columns as follows

SELECT emp_no, dept_no, first_name, last_name, salary
FROM emp
WHERE dept_no = 114
GROUP BY 1,2,3,4,5

5. Selecting with GROUP BY and ORDER BY Clause

SELECT emp_no, dept_no, first_name, last_name, salary
FROM emp
WHERE dept_no = 114
GROUP BY 1,2,3,4,5
ORDER BY emp_no, dept_no, first_name, last_name, salary

Result:-

emp_no	dept_no	first_name	last_name	salary
1,100	114	Micheal	Reed	46,000
5,000	114	Robert	Howard	65,000
7,000	114	Read	Vitamin	62,000

 Note :- As seen above with the GROUP BY clause while using ORDER BY clause you have the option of either mentioning column names or positional value of the columns as follows:

SELECT emp_no, dept_no, first_name, last_name, salary
FROM emp
WHERE dept_no = 114
GROUP BY 1,2,3,4,5
ORDER BY 1,2,3,4,5

Result:-

emp_no	dept_no	first_name	last_name	salary
1,100	114	Micheal	Reed	46,000
5,000	114	Robert	Howard	65,000
7,000	114	Read	Vitamin	62,000

Popularity: 76% [?]

Our Random Articles

• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns
• How to Post on a Separate/New Page in WordPress
• Usage of Macro in Teradata

More Links