Oracle latest SQL adequacy drill 1

Oracle latest SQL adequacy drill 1

Drop table example; create table example (ID Number (4) not null, name varchar (25), constraint example_id primary key (id); drop SEQUENCE example_sequence; create sequence example_sequenceINCREMENT BY 1 -- add several start with 1 each time -- count NOMAXVALUE from 1 -- do not set the maximum value NOCYCLE -- accumulate all the time, do not cycle NOCACHE -- do not CREATE a buffer; drop TRIGGER example_triger; create trigger example_triger before insert on example for each row when (new. id is null) -- starts the trigger to generate the idnumber begin select example_sequence.nextval into: new. id from dual; end; insert into example (name) values ('zhang san'); insert into example (id, name) values (111, 'Li si '); insert into example (name) values ('item 3 1'); insert into example (name) values ('item 3 2'); insert into example (name) values ('zhang San 3'); select * from example; drop table xxx; create table xxx as select * from example; -- copy the data and structure of a table, but the constraints will not be copied select * from xxx; -- modify the primary key -- 1 query the primary key SELECT * from user_cons_columns c where c. table_name = 'example '; SELECT * from user_cons_columns c where c. table_name = 'xxx'; -- 2 Delete the primary key alter table example drop constraint example_id; -- 3 add the new primary key constraint alter table example add constraint example_id primary key (id ); -- 4 add the alter table example add age number; -- 5 delete the alter table example drop column age; -- 6 modify the column (prerequisite for modifying the column type: Delete the existing database) alter table example modify age varchar (2); alter table example modify id varchar (20); -- 7 add constraint -- foreign key alter table example add constraint fk_example_xxx foreign key (pid) references xxx (pid); insert into example (name, age) values ('zhang San 1', 1); insert into example (name, age) values ('zhang San 2 ', 2); insert into example (name, age) values ('zhang San 3', 113); insert into xxx (name, pid) values ('zhang San 2', 21 ); insert into xxx (name, pid) values ('item 3 3', 1111); select * from example; select * from xxx; drop table xxx; create table xxx as select * from example; select * from xxx; -- alter table xxx add pid number (10); ----- process the copy table constraint alter table xxx rename column id to sid; alter table xxx add id number; update xxx set id = sid; alter table xxx drop column sid; ----- process the add field and add pid varchar2 (20) for the primary key alter table xxx ); update xxx set pid = id; alter table xxx add constraint xxx_pid primary key (pid); select * from xxx; ---- add the foreign key alter table xxx add constraint fk_xxx foreign key (id) references example (id); -- delete the constraint alter table xxx drop constraint fk_example_xxx; --------- modify the field type of existing data (the first four steps will cause the constraints in the table to be lost) -- 1 rename the field alter table example rename column id to sid; -- 2 add the id field alter table example add id varchar2 (200); -- 3 update data update example set id = sid; -- 4 Delete the backup data field alter table example drop column sid; -- 5 add the constraint alter table example add constraint exmaple_id primary key (id); alter table example add unique (age); alter table example drop unique (age ); -- query the uniqueness constraints of a table (including names and columns) select column_name from user_cons_columns cu, user_constraints au where cu. constraint_name = au. constraint_name and cu. table_name = 'example '; ----- view (if the permission is insufficient, grant connect, resource, dba to your instance) create table bbb as select * from example; create or replace view exam_sum_v1 (name, age) as select d. name, d. age FROM bbb d; insert into exam_sum_v1 (name, age) values ('lisii', 1221); insert into exam_sum_v1 (name, age) values ('lisii', 1221 ); insert into exam_sum_v1 (name, age) values ('lisii', 0000); insert into exam_sum_v1 (name, age) values ('lisii', 111); select * from exam_sum_v1; select * from bbb; alter table bbb drop column id; select * from example; select * from xxx; -- USER_TAB_COLS records the column information of the User table SELECT USER_TAB_COLS.TABLE_NAME as table name, USER_TAB_COLS.COLUMN_NAME as column name, USER_TAB_COLS.DATA_TYPE as data type, USER_TAB_COLS.DATA_LENGTH as length, USER_TAB_COLS.NULLABLE as is empty, USER_TAB_COLS.COLUMN_ID as column number, user_col_comments.comments as remarks FROM USER_TAB_COLS inner join user_col_comments on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME and comment = USER_TAB_COLS.COLUMN_NAME; -- select col associated with all fields in the table. column_name, uc. constraint_type, case uc. constraint_type when 'p' then' √ 'else' 'end "PrimaryKey" from user_tab_columns col left join user_cons_columns ucc on ucc. table_name = col. table_name and ucc. column_name = col. column_name left join user_constraints uc on uc. constraint_name = ucc. constraint_name and uc. constraint_type = 'P' where col. table_name = 'example '; -- query the foreign key field name, referenced table name, and applied field name in a table select distinct (col. column_name), r. table_name, r. column_name from user_constraints con, user_cons_columns col, (select t2.table _ name, t2.column _ name, t1.r _ constraint_name from user_constraints t1, user_cons_columns t2 where t1.r _ constraint_name = t2.constraint _ name and t1.table _ name = 'example ') r where con. constraint_name = col. constraint_name and con. r_constraint_name = r. r_constraint_name and con. table_name = 'example ';