A derived column is a result of a calculation (most of the time mathematical) that happens during the SQL query processing and generates a new column which is not part of the original table definition. For example, in our table we have salary column present but when we want to extract a percentage of the salary or portion of the salary, we generally create a column on the fly with the mathematical calculation from the existing salary column.

Suppose in a well managed successful organization, the HR head decided to reduce the salary by 20% of all the employees just to show that he is something. To know the future salary of the employees working in the dept 114, we have to use a derived column as mentioned in the following query.

SELECT emp_no, dept_no, salary “Actual Salary”,
(Salary * 80/100) “Proposed Salary” <== This is a derived Column
FROM emp
WHERE dept_no = 114
GROUP BY 1,2,3,4
ORDER BY 1, 2

Result:-

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.

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:-

2. Selecting with DISTINCT clause.

SELECT DISTINCT *FROM emp;

Result:-

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

3. Selecting with WHERE Clause

SELECT DISTINCT *
FROM emp
WHERE dept_no = 114 ;

Result :-

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:-

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:-

 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:-