Create and manage tables-2

 

6. Total

1. query tables of other users

A) tables of other users do not belong to the user's space.

B) If you want to query tables of other users, use the usernames of other users as the prefix.

I. Select * From userb. employees;

Ii. Select * From usera. employees;

2. The default value or pseudo column is invalid.

A) You can specify the default value for the insert operation.

B) Values, expressions, and SQL statements can all be used as default values.

C) Other column names

D) The default value must be of the same type as the column.

Create Table hire_dates (hire_date date default sysdate );

3. Create a table

A) syntax

Create Table dept (deptno number (2 ),

Dname varchar2 (14 ),

Loc varchar2 (13 ));

B) confirm

Describe Dept

4. Data Types

 

5. Create a table using a subquery

A) use the as subquery option to create a table and insert data.

1. Create Table [(column, clumn…)]

As subquery;

B) the specified columns and the columns in the subquery must correspond one to one.

C) define a column by default of the column name

Example: Create Table dept80

As
Select employee_id, last_name,

Salary * 12 annsal,

Hire_date
From employees
Where department_id = 80;

Table created.

6. alter table statement

A) append a new column.

Alter table talbe add (column datatype [Default expr] [, column datatype]…);

B) modify existing columns

Alter table talbe modify (column datatype [Default expr] [, column datatype]…);

C) define default values for newly appended Columns

Alter table talbe drop (column datatype [Default expr] [, column datatype]…);

D) delete a column

Alter table table_name rename column old_column_name to new_column_name

7. delete a table

A) Data and structure are deleted.

B) all running related transactions are committed.

C) all related indexes are deleted.

D. The drop TABLE statement cannot be rolled back.

8. Change the object name.

A) execute the rename statement to change the name of the table, view, sequence, or synonym.

Rename dept to detail_dept table Renamed

B) It must be the object owner.

9. Clear the table

A) truncate TABLE statement

I. Delete all data in the table

Ii. Release the table's storage space

Truncate table detail_dept;

Table truncated.

B) The truncate statement cannot be rolled back.

C) You can use the delete statement to delete data.

10. Constraints

A) constraints are table-level restrictions.

B) if dependency exists, the constraint can prevent data deletion errors.

C) Type of constraints

I. Not null

Ii. Unique

Iii. Primary Key

Iv. Foreign key

1. Foreign key: A table-level constraint is defined in the subtable.

2. References: Specifies the columns in the table and parent table.

3. On Delete cascade: when the parent table is deleted, records of the child table are deleted cascade.

4. On Delete set NULL: Set the foreign key value of the dependent records of the sub-table to null.

4.

V. chech

1. Reference currval, nextval, level, and rownum

2. Call the sysdate, uid, user, and userenv functions.

3. query records of another table

11. constraint rules

A) You can customize the constraints or use the sys_cn format naming constraints of Oracle server.

B) constraints:

I. create constraints when creating a table

Ii. After the table structure is created

C) constraints can be defined at the column or table level.

D) view constraints through data dictionary