Oracle Basic Data Query

Source: Internet
Author: User

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 );

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.