Oracle Database--sql Advanced Query

I. Related content

1. Master the multi-table connection query for the SELECT statement.

2. Master the subquery of the SELECT statement.

Second, the specific operation

(a) According to the EMP table and the Dept table under the Scott scheme of the Oracle database, do the following:

1. Check the names of all employees who work for clerk and their department name.

where job= ' clerk ';

2. Query all departments and their employee information, including those with no employees.

SELECT * FROM scott.emp T1 right join scott.dept T2 on T1.deptno=t2.deptno

3. Consult all employees and their departmental information, including those who are not part of any department.

SELECT * from Scott.emp T1 left joins Scott.dept T2 on T1.deptno=t2.deptno

4. Query the name information of the employee who is working in the sales department.

Implemented with subqueries:

SELECT * from Scott.emp where deptno= (select Deptno from scott.dept where dname= ' SALES ')

To implement with a connection query:

where t2.dname= ' SALES ';

Note the changes in both the rows and columns of the two implementations.

5. Check the names of all employees and their immediate superiors.

Select T1.ename as employee name, T2.ename Manager name from Scott.emp t1,scott.emp T2 where t1.mgr=t2.empno;

6. Inquire about all employees whose entry dates are earlier than their superiors.

Select T1.ename as employee name, T2.ename Manager name from Scott.emp t1,scott.emp T2 where T1.mgr=t2.empno and t1.hiredate<t2.hiredate

7. Inquire about employee information in the same job but not in the same department.

Select T1.ename,t1.job,t1.deptno,t2.ename,t2.job,t2.deptno from scott.emp T1 cross join Scott.emp T2 where t1.job=t2.job and T1.deptno <>t2.deptno

8. Check the information of department 10th employees and their leaders.

Select T1.ename as employee name, T2.ename Manager name from Scott.emp t1,scott.emp T2 where T1.mgr=t2.empno and t1.deptno=10;

9. Use Union to merge employee information with a salary greater than 2500 with employee information for work analyst.

SELECT * FROM scott.emp where sal>2500 union select * from Scott.emp where job= ' ANALYST '

10. Through the Intersect set operation, query the employee information for which the salary is greater than 2500 and work as analyst.

SELECT * from scott.emp where sal>2500 intersectselect * from scott.emp where job= ' ANALYST

11. Use the minus collection to query for wages greater than 2500, but work is not employee information for analyst.

SELECT * FROM Scott.emp where Sal>2500minusselect * from scott.emp where job= ' ANALYST ';

12. Check all employee information for wages higher than the company's average salary.

SELECT * from Scott.emp where sal> (select AVG (SAL) from Scott.emp)

13. Inquire about all employees who work in the same job as the Smith employee.

SELECT * from Scott.emp where job= (select Job from scott.emp where ename= ' SMITH ')

14. Inquire about all employee information that is higher than the salary of the Smith employee.

SELECT * from Scott.emp where sal> (select  sal from scott.emp where ename= ' SMITH ')

15. Inquire about employee names and wages that are higher than all employees who work in department 30th.

Select Ename,sal from Scott.emp where Sal>all (select  sal from Scott.emp where deptno=30)

16. Check the employee information for departments with more than 5 departments.

where Deptno in (select Deptno from Scott.emp GROUP by DEPTNO have Count (*) >5);

17. Check the information of all employees who have a salary greater than 2000.

SELECT * from Scott.dept where Deptno in (select Deptno from Scott.emp GROUP by Deptno have min (sal) >2000)

18. Find the largest number of department information.

SELECT * from Scott.dept where Deptno in (select Deptno from (select Deptno,count (*) as                          number from                         scott.emp                          Grou P by deptno) where number = (select max (number of people) from                  (select Deptno,count (*) as                  number from                  scott.emp                  Group by Deptno)) );                

19. Query the Department information for at least one employee.

SELECT * from Scott.dept where deptno on (select Deptno from Scott.emp GROUP by DEPTNO have COUNT (*) >=1)

20. Check employee information for wages higher than the average salary of the department.

SELECT * from Scott.emp e where sal> (select AVG (SAL) from Scott.emp Group by DEPTNO have E.deptno=d EPTNO);

21. Inquire about the employees ' information and the average salary of their departments with wages higher than the average wage.

SELECT * FROM (SELECT * from Scott.emp e                     where sal> (select AVG (sal) from                     scott.emp                      Group by deptno                      hav ing E.deptno=deptno)) T1                      INNER JOIN                   (select AVG (SAL), Deptno from                     scott.emp                     Group by deptno) T2 on T1.DEPTNO=T2.DEPTNO);

22. Inquire about the department in which each employee's leader is located.

SELECT * from Scott.dept where deptno in (select distinct deptno from Scott.emp where Empno in (select D Istinct Mgr from scott.emp));

23. Inquire about departments and their employees with average wages below 2000.

SELECT * from Scott.emp t1,scott.dept T2 where T1.deptno=t2.deptno and T1.deptno in (select Deptno        From Scott.emp GROUP BY DEPTNO have avg (SAL) <2000)                            

(b) Exercise

1. What type of connection should you create if you need to connect all the rows in the Employees table to all rows in the Employees table? (B)

A. Equivalent connection B. Cartesian product C. Inner connection D. Outer connection

2. If you need to query all customers and all orders placed from the customer and order tables, and ask that the results of the query be sorted by the company name of the customer in ascending order, and then by the orders amount descending. Which of the following statements should be executed? (B)

A.select c. Customer identification, C. company name, O. Order Date, O. Customer identification, O. Amount

From customer C, order O

WHERE c. Customer identification =o. Customer identification

ORDER by Amount DESC, company name;

B. SELECT c. Customer identification, C. company name, O. Order Date, O. Customer identification, O. Amount

From customer C, order O

WHERE c. Customer identification =o. Customer identification

ORDER by company name, amount DESC;

C. SELECT c. Customer identification, C. company name, O. Order Date, O. Customer identification, O. Amount

From customer C, order O

WHERE c. Customer identification =o. Customer identification

ORDER by company name, amount;

D. SELECT c. Customer identification, C. company name, O. Order Date, O. Customer identification, O. Amount

From customer C, order O

WHERE c. Customer identification =o. Customer identification

ORDER by company name ASC, amount DESC;

3. Evaluate the following SQL statement:

SELECT E. Employee identification, E. Surname, E. Name, D. department Name

From Employee E, Department D

WHERE E. Department identification =d. Department identification

and employees. Department Identification >5000

ORDER by 4;

Which sentence has a wrong grammar? (E)

A. SELECT E. Employee identification, E. Surname, E. Name, D. department Name

B. From Employee E, Department D

C. WHERE E. Department identification =d. Sector identification

D. and employees. Department Identification >5000

E. ORDER by 4;

4. Evaluate the following statement:

SELECT department identification, AVG (salary)

From employee

WHERE Job ID <> 69 879

Group BY Department identification

Having AVG (salary) >35 000

ORDER by department identification;

Which clauses limit the return result? Please select two correct answers. (BD)

A. SELECT sector identification, AVG (salary)

B. WHERE job ID <> 69 879

C. GROUP BY Department identification

D. Having AVG (salary) >35 000

5. What is the correct order for each clause in the SELECT statement? (C)

A. SELECT

From

WHERE

ORDER by

GROUP by

Having

B. SELECT

From

Having

ORDER by

WHERE

GROUP by

C. SELECT

From

WHERE

GROUP by

Having

ORDER by

D. SELECT

From

WHERE

Having

ORDER by

GROUP by

6. Which of the following operators can be used for multiline subqueries? (A)

A.in b.<> c.= D.like

7. Assume that the database has a Customer table and an Order history table. Among them, the Customer table includes: Customer identification Number (5), name VARCHAR2 (25), credit limit number (8,2), opening date (date), the Order History table includes: Order identification number (5), Customer ID number (5), Order Date (date), total number (8,2). Which of the following scenarios needs to use a subquery to return the desired results? (D)

A. Need to display the account opening date under each customer account

B. Need to display the date on which the customer placed the order

C. Need to display all orders placed on a specific date

D. Need to display all orders with the same release date as the order numbered 25950

8. Which of the following SELECT statements should be used if you want to display the product names in the report that cost values are higher than the average cost of all products? (B)

A. Select Product name from product WHERE cost > (SELECT AVG (cost) from product);

B. SELECT product name from product WHERE cost > AVG (Cost);

C. SELECT avg (COST), product name from product WHERE cost > AVG (COST) GROUP by product name;

D. Select Product Name from (select AVG (cost) from product) WHERE cost > AVG (Cost);

9. If a single-line subquery returns a null value and uses the equals comparison operator, what results will the external query return? (B)

A. Do not return any rows B. Return all rows in a table

C. Returning a null value D. Return error

10. If you need to create a SELECT statement that contains multiple rows of subqueries, which comparison operator can I use? (A)

A.in, any and all b.like

C.between ...           And ... D.=, < and >

Select Ename,dname from scott.emp t1 inner joins scott.dept T2 on T1.deptno=t2.deptno where job= ' clerk ';

Oracle Database--sql Advanced Query