Measure the test taker's understanding about the operations in a table in El.

Measure the test taker's understanding about the operations in a table in El.

Goal: Learn about table creation and modification and related operations.

Preface:

Oracle: The table is created in the user solution, rather than directly belongs to the database!

Sqlserver: The table directly belongs to the database!

1. 1. Create a table:
2. Keywords:Create
3. Create TableTable Name (
4. Column name 1, data type, other attributes,
5. Column name 2, data type, other attributes,
6. ...
7. Other attributes of the last data type
8. );
10. Note: in oracle, when creating constraints, like SQL Server, they are also directly written.Primary Key,Check(Xxx), and so on, but the only difference is
11. Foreign key constraints have some changes:
12. Eg: typeIdIntCostraint (typeId) refereces TypeInfo (typeId );
13. Instead:
14. Eg: typeIdInt Foreign Key(TypeId) refereces TypeInfo (typeId );-- (Some of them are written in this way, but I have never written it !)
16. 2. Table Modification
17. A) add a column:
18. Alter TableTable NameAddField Name Data Type attribute;
19. B) Modify columns:
20. Alter TableTable NameModifyColumn Name Data Type attribute;
21. C) delete a column:
22. Alter TableTable NameDrop ColumnColumn name;
23. D) modify the table name:
24. Alter TableRenameToNew table name;
26. 3. Create a new table through an existing table (and enter existing data into the new table)
27. Create TableNew table nameAs SelectColumn name 1, column name 2 ,....FromOld table name;
29. 4. Table space operations:
30. A) create a tablespace:
31. CreateTablespace name datafile ='Tablespace path (the file name often ends with dbf )' SizeXXM;
32. Note:SizeThe unit of the following number is not MB, but M, eg:Size50 m;
34. B) create a temporary tablespace:
35. Create TemporaryTablespace temporary tablespace name tempFile ='Path (same as the file name )' SizeXxM;
37. C) Specify the tablespace for the user.
38. Alter UserUser NameDefaultTablespace name;
40. D) specify a tablespace for the table (which must be specified during creation ):
41. Create TableXXX (
42. XXX XX XXX
43. ) Tablespace name;
45. E) delete a tablespace:
46. 1) only perform logical deletion, that is, delete the structure, but the physical file still exists:
47. DropTablespace name incuding contents;
48. 2) Delete All:
49. Drop TableName: including contentsAndDatafile;
51. F) modify the tablespace size:
52. Alter DatabaseDatafile ='Path (and file name) of the newly added data file )'Resize XXM;