Oracle Basic Data Query

Oracle Basic Data Query

SELECT * FROM MERs WHERE customer_id not in (2, 3, 4, NULL); SELECT * FROM customers WHERE customer_id BETWEEN 1 AND 4; SELECT * FROM customers WHERE customer_id not between 1 AND 4; -- NAN indicates that it is not a number SELECT * FROM MERs WHERE customer_id is nan; -- logical operator and or not select * FROM customers WHERE dob> '01-JAN-1970 'AND customer_id> 3; SELECT * FROM MERs WHERE dob> '01-JAN-1970 'or not customer_id> 3; -- order by sort SELECT * FROM customers order by last_name; SELECT * FROM MERs order by first_name ASC, last_name DESC; -- SELECT * FROM customers order by 1 ASC, 2 DESC; -- Multi-table join query SELECT products. name, product_types.name FROM products, product_types WHERE products. product_type_id = product_types.product_type_id AND products. product_id = 3; SELECT products. name, product_types.name FROM products, product_types WHERE products. product_type_id = product_types.product_type_id order by products. name; -- use the table alias select p. NAME, PT. name from products P, product_types pt where p. product_type_id = pt. product_type_id order by p. name; -- select pt for Cartesian sets. product_type_id, P. product_id FROM product_types PT, products P; -- Multi-table join query select c. FIRST_NAME, C. LAST_NAME, P. name as product, PT. name as type from MERs C, purchases PR, products P, product_types pt where c. customer_id = pr. customer_id AND p. product_id = pr. product_id AND p. product_type_id = pt. product_type_id order by p. name;

SELECT * FROM MERs WHERE customer_id not in (2, 3, 4, NULL); SELECT * FROM customers WHERE customer_id BETWEEN 1 AND 4; SELECT * FROM customers WHERE customer_id not between 1 AND 4; -- NAN indicates that it is not a number SELECT * FROM MERs WHERE customer_id is nan; -- logical operator and or not select * FROM customers WHERE dob> '01-JAN-1970 'AND customer_id> 3; SELECT * FROM MERs WHERE dob> '01-JAN-1970 'or not customer_id> 3; -- order by sort SELECT * FROM customers order by last_name; SELECT * FROM MERs order by first_name ASC, last_name DESC; -- SELECT * FROM customers order by 1 ASC, 2 DESC; -- Multi-table join query SELECT products. name, product_types.name FROM products, product_types WHERE products. product_type_id = product_types.product_type_id AND products. product_id = 3; SELECT products. name, product_types.name FROM products, product_types WHERE products. product_type_id = product_types.product_type_id order by products. name; -- use the table alias select p. NAME, PT. name from products P, product_types pt where p. product_type_id = pt. product_type_id order by p. name; -- select pt for Cartesian sets. product_type_id, P. product_id FROM product_types PT, products P;

 

-- Multi-table join query select c. FIRST_NAME, C. LAST_NAME, P. name as product, PT. name as type from MERs C, purchases PR, products P, product_types pt where c. customer_id = pr. customer_id AND p. product_id = pr. product_id AND p. product_type_id = pt. product_type_id order by p. name; -- select e. FIRST_NAME, E. LAST_NAME, E. TITLE, E. SALARY, SG. SALARY_GRADE_ID FROM employees E, salary_grades sg where e. salary BETWEEN sg. low_salary AND sg. high_salary order by sg. salary_grade_id; -- Outer Join ('(+)' is located on the opposite side of the column containing null values) select p. name product, PT. name type from products P, product_types pt where p. product_type_id = pt. product_type_id (+) order by p. name; -- left Outer Join and right outer join -- left Outer Join select p. name product, PT. name type from products P, product_types pt where p. product_type_id = pt. product_type_id (+) order by p. NAME; -- select p. name product, PT. name type from products P, product_types pt where p. product_type_id (+) = pt. product_type_id order by p. NAME; -- restrictions on external connections 1. The outer join operator can only be used at one end of the connection, the outer join operator cannot be used at both ends -- 2. An outer join condition cannot be used at the same time and another join condition using the OR operator.

 

-- Self-join (the same table must be connected using different table aliases) select w. FIRST_NAME | ''| W. LAST_NAME | 'Works FOR '| M. FIRST_NAME | ''| M. LAST_NAME FROM employees W, employees m where w. manager_id = M. employee_id order by w. first_name; select w. FIRST_NAME | ''| W. LAST_NAME | 'Works FOR '| M. FIRST_NAME | ''| NVL (M. LAST_NAME, 'regionholders') FROM employees W, employees m where w. manager_id = M. employee_id (+) order by w. first_n Ame; -- select p. NAME, PT. name from products p inner join product_types pt on p. product_type_id = pt. product_type_id (+) order by p. name; select e. FIRST_NAME, E. LAST_NAME, E. SALARY, SG. SALARY_GRADE_ID FROM employees e inner join salary_grades sg on e. salary BETWEEN sg. low_salary AND sg. high_salary order by sg. salary_grade_id; -- use the USING Keyword (it is best not to use the table alias when USING the USING keyword; otherwise, an error occurs) select p. NAME, PT. NAME FROM Products p inner join product_types pt using (product_type_id) order by p. name; -- Multi-table join select c. LAST_NAME, P. name as product, PT. name as type from customers c inner join purchases pr using (customer_id) inner join products p using (product_id) inner join product_types pt using (product_type_id) order by p. name; -- Multi-column inner join -- SELECT * FROM -- TABLE1 inner join TABLE2 -- USING (COLUMN1, COLUMN2 ,...); -- Outer Join USING the USING Keyword -- select p for left Outer Join. NAME, PT. name from products p left outer join product_types pt using (product_type_id); -- right outer join select p. NAME, PT. name from products p right outer join product_types pt using (product_type_id); -- select p. NAME, PT. name from products p full outer join product_types pt using (product_type_id );