ORACLE SQL Exercise (vi)

--Use a subquery to find an indeterminate value

--Sub-query and select statement

SELECT e.emp_id, E.first_name, E.last_name, EP. Pay_rate

From Employee_tbl E, Employee_pay_tbl EP

WHERE e.emp_id = EP. emp_id

and EP. Pay_rate <

(SELECT pay_rate from employee_pay_tbl WHERE emp_id = ' 443679012 ');

The following statement is used as a subquery in a later example

SELECT pay_rate from employee_pay_tbl WHERE emp_id = ' 220984332 ';

--sub-query and insert statement

The--insert statement inserts the results returned by the subquery into another table

INSERT into Rich_employees

SELECT e.emp_id, E.last_name, E.first_name, EP. Pay_rate

From Employee_tbl E, Employee_pay_tbl EP

WHERE e.emp_id = EP. emp_id

and EP. Pay_rate >

(SELECT pay_rate from employee_pay_tbl WHERE emp_id = ' 220984332 ');

--sub-query and UPDATE statement

--The subquery is used in conjunction with the UPDATE statement to update one or more fields in a table

Not to be continued ....

This article from "Technology Station" blog, declined reprint!

ORACLE SQL Exercise (vi)