Oracle Common SQL Scenario application (not finished ...) ）

--1, create a student information sheet stuinfo, fields including number, name, gender, age, date of entry

Sql>create TABLE Stuinfo (Snochar (4), sname Varchar2 (ten), sex int,age int,sdate date);

Table created

--2, creating a stuinfo table primary KEY constraint

Sql>alter TABLE stuinfo ADD CONSTRAINT pk_stu PRIMARY KEY (SNO);

Table Altered

Executed in 0.952 seconds

--3, increase the enrollment date by sysdate default value, note that sysdate must be added with NOT null

Sql>alter TABLE stuinfo MODIFY sdate DEFAULT sysdate not NULL;

Table Altered

Executed in 0.546 seconds

--4, add 5 simulation Data

sql> INSERT into Stuinfo values (' 001 ', ' lilei ', ' 0 ', ' + ', default);

sql> INSERT into Stuinfo values (' 002 ', ' Xiaofang ', ' 1 ', ' n ', to_date (' 2014-09-01 ', ' yyyy-mm-dd '));

sql> INSERT into Stuinfo values (' 003 ', ' Weiwei ', ' 1 ', ' + ', to_date (' 2008-08-08 ', ' yyyy-mm-dd '));

sql> INSERT into Stuinfo values (' 004 ', ' Xiaochao ', ' 0 ', ' ', to_date (' 2009-10-08 ', ' yyyy-mm-dd '));

sql> INSERT into stuinfo values (' 005 ', ' Tom ', ' 0 ', ' ', to_date (' 2007-03-08 ', ' yyyy-mm-dd '));

--5, retrieving stuinfo table

Sql> select * from Stuinfo;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4D/7D/wKioL1RR_FLgDNjdAACIeor74xI042.jpg "title=" 1.jpg " alt= "Wkiol1rr_flgdnjdaacieor74xi042.jpg"/>

--6, create a score table, including the number of students, course code, scores, which is the foreign key of this table

Sql>create TABLE Score (Gnochar (4), Class Int,score number (4,2), CONSTRAINT fk_no FOREIGN KEY (gno) REFERENCES Stuinfo ( SNO));

Table created

--7, testing a record inserted into the score table that does not exist in the Stuinfo table Sno

Sql>insert into score values (' 006 ', 1212,96.33);

ORA-02291: violation of full constraint conditions (EPM. FK_NO)- parent keyword not found

--8, for tables that have foreign keys, are required to be associated with the parent table when inserting or updating, so you must create an index at the external key

Sql>create INDEX Inx_score on score (GNO);

Index created

Executed in 0.062 seconds

--9, renaming the Stuinfo table to student

Sql>alter TABLE stuinfo RENAME to student;

--10, Increase checkfor student table--age between 1 and 120

sql> ALTER TABLE student ADD constraintage_check Check (age>1 and age<=120);

--11, test insert data to student table age is 126

sql> INSERT into student VALUES (' 006 ', ' Dushuai ', 0, ' 126 ', to_date (' 1989-01-05 ', ' yyyy-mm-dd '));

ORA-02290: violation of CHECK constraint conditions (EPM. Age_check)

--12, The age of the student-006 School is updated to 26

sql> UPDATE student SET age=26 wheresno= ' 006 ';

1 row updated

Executed in 0.078 seconds

--13, test to Delete student, check to delete the normal

Sql>drop TABLE student;

ORA-02449: unique in the table / primary key is referenced by foreign key

Description: If the primary key or unique value in the table is referenced by another table foreign key, either delete the foreign key or Drop Post-Add Cascade constraint The delegate is deleted by Cascade;

correct Sql>drop TABLE student CASCADE CONSTRAINT;

--14,Oracle Flashback Technology, which restores the drop table from the Recycle Bin

sql> FLASHBACK TABLE student to before DROP;

Done

Executed in 0.296 seconds

--15, querying the contents of the Recycle bin

Sql>select r.original_name,r.type,r.droptime from RecycleBin R;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4D/7D/wKioL1RR_GKihVfiAACM4QMiNjY373.jpg "title=" 2.jpg " alt= "Wkiol1rr_gkihvfiaacm4qminjy373.jpg"/>

--16, add a whole comment to the table

Sql>comment on table student is ' Student information table ';

--17, Adding comments for the Age column in the student table

Sql>comment on COLUMN student.age is ' age needs to be greater than 1 years old and less than or equal to - age ';

--18,Create a table, use only the table structure in the student table, and do not use the data

Sql>create TABLE Student_temp as select* from student WHERE 1=2;

Description: Because 1=2 conditions are not tenable, so only the Student the structure of the table is copied to the new table;

Similarly, if the condition is true, the table structure and data will be copied to the new table!

The new table can only inherit the structure and data of the old table, and cannot inherit the constraints of primary key, foreign key, index, etc.

--19, setting table as read-only mode

Sql>alter TABLE student_temp1 READ only;

Description: The table is set to read-only and cannot be inserted, updated, etc. DML operation, but allows the deletion of tables;

Sql>insert into Student_temp1values (' 007 ', ' Wangzi ', ' 0 ', ' 98 ', to_date (sysdate, ' yyyy-mm-dd '));

ORA-12081: The table "EPM" is not allowed . " STUDENT_TEMP1 " for update operations

sql> DROP TABLE student_temp1;

Table dropped

Executed in 1.7 seconds

--20, restoring read-only tables to read-write status

sql> ALTER TABLE student_temp1 READ WRITE;

Table Altered

Executed in 0.265 seconds

--21,student_temp1 table New Notes column, type varchar2($)

sql> ALTER TABLE student_temp1 ADD Note varchar2 (200);

Table Altered

Executed in 1.264 seconds

--22, set the note table in the STUDENT_TEMP1 table to not used (unused)

sql> ALTER TABLE student_temp1 SET UNUSED (note);

Table Altered

Executed in 1.794 seconds

Description: Use set unused to label the columns in the table while reducing the number of redo in the drop column

Note that the column data after set unused cannot be recovered (except through data Backup), and then, when the system overhead is low , the table column that marks set unused is deleted, in the following format:

sql> ALTER TABLE student_temp1 dropunused COLUMNS;

Table Altered

Executed in 2.247 seconds

This article is from the "Oralce Learning path" blog, make sure to keep this source http://dushuai.blog.51cto.com/9461011/1569765

Oracle Common SQL Scenario application (not finished ...) ）