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

Our Random Articles

More Links