Oracle scott User table and related SQL statements

1. table Structure under scott: employee table EMPNo. name type description 1 EMPNONUMBER (4) employee No. 2ENAMEVARCHAR2 (10) employee name 3JOBVARCHAR2 (9) Position 4 www.2cto.com MGRNUMBER (4) lead no. 5HIREDATEDATE employment date 6 SALNUMBER (7, 2) monthly salary, salary 7 COMMNUMBER () bonus, Commission 8 DEPTNONUMBER (2) Department number table DEPTNo. name type description 1 DEPTNONUMBER (2) Department No. 2DNAMEVARCHAR2 (14) department name 3LOCVARCHAR2 (13) Department location wage level table SALGRADENo. www.2cto.com name type description 1GRADENUMBER salary level 2LOSALNUMBER minimum wage 3HISALNUMBER highest wage bonus table BONUSNo. name type description 1ENAMEVARCHAR2 (10) employee name 2JO BVARCHAR2 (9) Position 3SALNUMBER salary 4COMMNUMBER bonus 2. SQL-SELECT (query) Statement: I) query statement format: [SQL] SELECT * | specific column alias FROM table name; www.2cto.com ii) query of Individual columns: [SQL] SELECT empno, ename, job FROM emp; iii) alias for the returned column: [SQL] SELECT empno number, ename name, job FROM emp; iv) use DISTINCT to directly remove all duplicate columns: [SQL] SELECT {DISTINCT} * | specific column alias FROM table name; v) display format setting: Requirement: No: 7369 of employees, whose name is SMITH and whose job is CLERK, can use the string connection operation provided by Oracle, which is represented by "|. If you want to add some display information, all other fixed information should be included. [SQL] SELECT ':' | empno | 'employee, name:' | ename | ', job:' | job FROM emp; vi) in the query, you can also use four arithmetic functions: for example, to find the name and annual salary of each employee. [SQL] SELECT ename, sal * 12 FROM emp; of course, you can also create an alias for column name sal * 12. Avoid Chinese: [SQL] SELECT ename, sal * 12 income FROM emp; 3. SQL limit query (WHERE clause) Statement: You must specify a limit for query based on the specified conditions. Restricted query syntax: [SQL] SELECT {DISTINCT} * | specific column alias FROM table name {WHERE condition (s)} www.2cto.com example: I) query Information about all employees whose salaries are greater than 1600: [SQL] SELECT * FROM emp WHERE sal> 1600; ii) query information about employees who receive monthly bonuses: use NOT [SQL] SELECT * FROM emp WHERE comm IS NOT NULL; <span style = "white-space: pre"> </span> -- the bonus IS the comm field, if it is not null, OK is returned. Ii. v) also query the information of employees who do not have a monthly bonus: [SQL] SELECT * FROM emp WHERE comm IS NULL; iii) AND-query the basic salary over 1000, employee information that can receive the bonus at the same time: [SQL] SELECT * FROM emp WHERE sal> 1000 AND comm IS NOT NULL; iv) OR-query, the basic salary IS greater than 1000, or the employee information that can receive the bonus. [SQL] SELECT * FROM emp WHERE sal> 1000 OR comm IS NOT NULL; · use NOT to obtain the inverse, changing the true condition to false and false to true. V):... AND... Syntax format: field... Minimum... AND... Maximum... [SQL] SELECT * FROM empWHERE sal BETWEEN 1500 AND 3000; The above is equivalent to: [SQL] SELECT * FROM empWHERE sal> = 1500 AND sal <= 3000; v. v) The date indicates that a single quotation mark is required. "'": [SQL] SELECT * FROM emp WHERE hiredate BETWEEN '1-January-81' AND '31-December-81 '; vi) IN syntax format: Field IN (value 1, value 2 ,......, Value n) [SQL] SELECT * FROM emp WHERE empno IN (72.16,7499, 7521); www.2cto.com can also use the NOT inversion: field NOT IN (value 1, value 2 ,......, Value n) [SQL] SELECT * FROM emp WHERE empno not in (72.16,7499, 7521); NOT only limited to numbers: [SQL] SELECT * FROM emp WHERE ename IN ('Smith ', 'allen', 'King'); vii) LIKE uses two wildcard characters in LIKE: · "%" can match content of any length [SQL] SELECT * FROM emp WHERE ename LIKE '_ M % '; · "_" can match a length of content [SQL] SELECT * FROM emp WHERE ename LIKE '% M % '; · '%' can query all [SQL] SELECT * FROM emp WHERE ename LIKE '%'; viii) <> and! = No. [SQL] SELECT * FROM emp WHERE empno <> 7369; [SQL] SELECT * FROM emp WHERE empno! = 7369; 4. syntax format of order by words: [SQL] SELECT {DISTINCT} * | specific column alias FROM table name {WHERE condition (s)} {Field 1 of ORDER BY sorting, the sorting Field 2 ASC | DESC} www.2cto.com-> ASC indicates ascending, DESC indicates descending example: Sort employees in ascending order of wages: [SQL] SELECT * FROM emp ORDER BY sal ASC; example: Query Information about all employees in 10 departments. The queried information is sorted FROM high to low. If the salary is equal, [SQL] SELECT * FROM emp WHERE deptno = 10 ORDER BY sal DESC, hiredate ASC; note: the sorting operation is executed at the end of the entire SQL statement.