ORACLE SQL Exercise (v)

--date converted to string

SELECT Date_hire, To_char (Date_hire, ' Month dd yyyy ') HIRE

From Employee_pay_tbl;

SELECT Employee_tbl. emp_id,

Employee_tbl. Last_Name,

Employee_pay_tbl. POSITION

From Employee_tbl, EMPLOYEE_PAY_TBL

WHERE Employee_tbl. emp_id = Employee_pay_tbl. emp_id;

SELECT E.emp_id,ep. Salary,ep. Date_hire,e.last_name

From Employee_tbl e,employee_pay_tbl EP

WHERE e.emp_id = EP. emp_id

and EP. SALARY > 20000;

SELECT e.emp_id,e.last_name,p.position

From Employee_tbl e,employee_pay_tbl P

WHERE e.emp_id <> p.emp_id;

The outer join returns all records in a table, even if the corresponding record does not exist in the second table.

SELECT P.prod_desc, O.qty

From Products_tbl P, Orders_tbl O

WHERE p.prod_id = o.prod_id;

-Show all products, regardless of whether they have orders

--oracle SQL syntax

SELECT P.prod_desc, O.qty

From Products_tbl P, Orders_tbl O

WHERE p.prod_id = o.prod_id (+); --Table with ' + ' is a table with no matching records

--ansi SQL syntax

SELECT P.prod_desc, O.qty

From Products_tbl P

Left JOIN orders_tbl O

on p.prod_id = o.prod_id;

--Self-coupling

-When the required data is in the same table, and we have to make some comparisons to the records, we can use the self-coupling.

--list the names of employees and their supervisors

SELECT E1.name,e2.name

From EMP e1,emp E2

WHERE E1. mgr_id = E2. emp_id

The--e1 table can be seen as a table that holds employee information, and the E2 table is the table that holds the supervisor information.

--Using the base table

SELECT C.cust_name, P.prod_desc

From Customer_tbl C, Products_tbl P, Orders_tbl O

WHERE c.cust_id = o.cust_id

and p.prod_id = o.prod_id;

SELECT e.city, AVG (EP. SALARY)

From Employee_tbl E, Employee_pay_tbl EP

WHERE e.emp_id = EP. emp_id

GROUP by E.city;

SELECT e.emp_id, E.last_name, E.first_name, EP. SALARY, EP. BONUS

From Employee_tbl E

JOIN Employee_pay_tbl EP

On e.emp_id = EP. emp_id;

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

ORACLE SQL Exercise (v)