Oracle exercises-rich people from Chicago

Title: From the Scott User's emp/dept table, find "the richest man from Chicago".

Ideas:

1. This man is from Chicago.

2. This man is the richest, and the richest in Chicago.

Two-step query:

1. Find out all the people from Chicago

2. Find the richest man from Chicago

: 1, open the Run Interface:window key +r--> input cmd

: 2. Query the Scott system database using the Sqlplus query tool: C:\users\admin>sqlplus scott/zhang1622505426 --sqlplus database name/Your password

: 3. What tables are included in this Scott database: Sql> Select table_name from User_tables;

: 4. Check the EMP table in the Scott database: Sql> select * from EMP;

: 5. Query the Dept table in the Scott database: Sql> select * FROM dept;

: 6, in order to make the query data on a line display can be adjusted by this statement: Sql> Set Linesize 100

: 7, check all the people in Chicago: Sql>select e.* from EMP e joins Dept D on (E.DEPTNO=D.DEPTNO) where d.loc= ' CHICAGO '; --join keyword can be connected query, on the following specify the conditions of the connection

or sql>select e.* from emp e,dept D where D.deptno=e.deptno and d.loc= ' CHICAGO ';

: 8. Use the max () function to find out all the information about the richest people in Chicago: Sql> Select e.* from emp e,dept D where E.deptno=d.deptno and d.loc= ' CHICAGO ' and sal= (select Max (SAL) from EMP e,dept   D where E.deptno=d.deptno and d.loc= ' CHICAGO '); --select Max (sal) from EMP e,dept D where E.deptno=d.deptno and d.loc= ' CHICAGO '); is to find out what the highest wages in Chicago are

or: Find the name of the richest person in Chicago by way of order by: Sql> Select ename from (select e.*,d.* to emp e,dept D where E.deptno=d.deptno and d.loc= ' CHICAGO ' ORDER by sal Desc) where Rownum=1; --order by: sort; order BY sal Desc: The sequence of flashbacks according to the salary; Rownum=1: Represents the first row after sorting ;

Oracle exercises-rich people from Chicago