Oracle entry to master Part 3-query 1

Oracle entry to master Part 3-query 1 Table query (1) introduction to basic oracle Table query www.2cto.com in our tutorial, we use several tables (emp, dept) Existing by scott) it demonstrates how to use the select statement. The select statement is very useful in software programming. Emp employee table clerp employee salesman Sales manager analyst president mgr superior No. hiredate start time sal monthly salary comm rewards deptno Department dept Department table www.2cto.com deptno Department No. accounting Finance Department research R & D department operations business department location of loc Department salgrade salary level grade level losal minimum wage hisal highest wage simple query statement View table structure DESC emp; to query all columns, SELECT * FROM dept. Do not use select * set timing on if you are not moved. Enable the show operation time switch and display the query time below. Create table users (userId VARCHAR2 (10), uName VARCHAR2 (20), uPassw VARCHAR2 (30); insert into users VALUES ('a0001', 'Ah Ah ah ', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); -- increase the data volume by about tens of thousands of lines from your own replication. This can be used to test the SQL statement execution efficiency of INSERT INTO users (userId, UNAME, UPASSW) SELECT * FROM users; select count (*) FROM users; COUNT the number of rows <! -- [If! SupportLineBreakNewLine] --> <! -- [Endif] --> query the specified column SELECT ename, sal, job, deptno FROM emp; how to cancel repeated rows distinct select distinct deptno, job FROM emp; query the department where SMITH is located, working, salary SELECT deptno, job, sal FROM emp WHERE ename = 'Smith '; Note: oracle is case sensitive to content, so ename = 'Smith 'and ename = 'Smith' are different <! -- [If! SupportLineBreakNewLine] --> <! -- [Endif] --> use the arithmetic expression nvl null. Question: How to display the annual salary of each employee? SELECT sal * 13 + nvl (comm, 0) * 13 "annual salary", ename, comm FROM emp; Use the column alias SELECT ename "name ", sal * 12 AS "annual income" FROM emp; how to handle null values using the nvl function to handle how to connect strings (|) SELECT ename | 'is a' | job FROM emp; use the where clause. Question: How do I display employees whose salaries are higher than 3000? SELECT * FROM emp WHERE sal> 3000; question: how do I find employees who have joined the company after 1982.1.1? SELECT ename, hiredate FROM emp WHERE hiredate> '1-January 1, January-1982 '; question: How do I display employees with salaries ranging FROM 2000 to 3000? SELECT ename, sal FROM emp WHERE sal> = 2000 AND sal <= 3000; how to use the like operator %: represents 0 to multiple characters _: represents any single character problem: how do I display the name and salary of an employee whose first character is S? SELECT ename, sal FROM emp WHERE ename like's % '; how do I display the names and salaries of all employees whose third character is uppercase O? SELECT ename, sal FROM emp WHERE ename like '_ O %'; use in the where condition. Question: How do I display employees with empno 7844,783? SELECT * FROM emp WHERE empno in (7844,783 9,123,456); Use the is null operator. Question: How can I display employees without superiors? Incorrect syntax: select * from emp where mgr = ''; Correct syntax: SELECT * FROM emp WHERE mgr is null;