Shulou(Shulou.com)06/01 Report--

Prepare the col empno for 9999transfercol ename for a10 tname for job for a10, the col mgr for 9999, the hiredate for a12, the comm for 9999, the deptno for, the deptno for, the tname for, the 40, the pagesize 80

-- create a new table xxx_emp, copy the structure in the emp table, and copy all the data create table xxx_empas select * from emp of the emp table at the same time

Review the four categories of SQL92/99 standards

1) DML (data manipulation language): select,insert,update,delete

2) DDL (data definition language): create table,alter table,drop table,truncate table

3) DCL (data control language): grant select any table to scott/revoke select any table from scott

(4) TCL (transaction Control language): commit,rollback,savepoint to rollback point

Insert a record into the emp table (method 1: in the order of the table default structure) insert into table name values. Grammar

Insert into emp values (1111), "Jack", "IT", 7788, "sysdate, 1000, 100," 40)

Insert a record into the emp table (method 2: in custom order) insert into table name (column name) values. Grammar

Insert into emp (ENAME,EMPNO,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('MARRY',2222,'IT',7788,sysdate,1000,100,40)

Insert null values into the emp table (method 1: insert null values by display)

Insert into emp values (333 SISIZHANGJIZHANGJIZHANGZHANGZHANGZHANGZHANGZHANGZHANGZHANG)

Insert null values into the emp table (method 2: implicitly insert null values), provided that the inserted field allows null values to be inserted

Insert into emp (ENAME,EMPNO,JOB,MGR,HIREDATE,SAL,DEPTNO) values ('SOSO',4444,'IT',7788,sysdate,1000,40)

[& placeholders should be used for insert] use & placeholders, dynamic input values, & can be used in any DML statement, in the values clause, such as'& ename' and & sal

Insert into emp values & empno,'&ename','&job',&mgr,&hiredate,&sal,&comm,&xxxxxxxx)

Note: & is a placeholder provided by the sqlplus tool. If it is a string or date type, add the''character, but the numeric type does not need to add the' 'character.

[& placeholder applied to select table name] use & placeholder, dynamic input value, & can be used in any DML statement, in the from clause

Select * from & table

[& placeholder applied to select column name] use & placeholder, dynamic input value, & can be used in any DML statement, in the select clause

Select empno,ename,&colname from emp

[& placeholders should be used for where] use & placeholders, dynamic input values, & can be used in any DML statement, in the where clause

Select * from emp where sal > & money

[& placeholders apply to group by and having] use & placeholders, dynamic input values, & can be used in any DML statement, in group by and having clauses

Select deptno,avg (sal) from empgroup by & deptnohaving avg (sal) > & money

Delete all records in the emp table

Delete from emp

Copy all the employees of department 20 in the xxx_ emptable to the emp table and insert it in bulk, the insert into table name select. Grammar

Insert into empselect * from xxx_empwhere deptno=20

Increase SMITH' 's salary by 20%

Update emp set sal=sal*1.2 where ename = upper ('smith')

Set the salary of 'SMITH' to the average salary of department 20, which is an unknown condition. Give priority to sub-query.

First: the average salary of department 20

Select avg (sal) from emp where deptno=20

Second: set the salary of 'SMITH' to 2207

Update emp set sal=2207 where ename = 'SMITH'

Subquery:

Update emp set sal = (select avg (sal) from emp where deptno=20) where ename = 'SMITH'

Delete employees whose wages are lower than the average wages of all departments, this is a condition unknown, give priority to sub-queries

First: check the average wages of all departments

Select avg (sal) from emp group by deptno

Second: delete employees whose wage ratios (*, *, *) are low.

Delete from emp where sal

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.