SQL CheatSheet (Alter Table Example)

SUBMITTED BY: Guest

DATE: July 21, 2014, 11:38 p.m.

FORMAT: Text only

SIZE: 3.8 kB

Raw Download

HITS: 1188

Report
  1. SQL: ALTER TABLE Statement
  3. Learn how to use the SQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with lots of clear, concise examples). We've also added some practice exercises that you can try for yourself.
  4. Description
  6. The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.
  7. Add column in table
  8. Syntax
  10. To add a column in a table, the SQL ALTER TABLE syntax is:
  12. ALTER TABLE table_name
  13. ADD column_name column-definition;
  15. Example
  17. Let's look at a SQL ALTER TABLE example that adds a column.
  19. For example:
  21. ALTER TABLE supplier
  22. ADD supplier_name varchar2(50);
  24. This SQL ALTER TABLE example will add a column called supplier_name to the supplier table.
  25. Add multiple columns in table
  26. Syntax
  28. To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:
  30. ALTER TABLE table_name
  31. ADD (column_1 column-definition,
  32. column_2 column-definition,
  33. ...
  34. column_n column_definition);
  36. Example
  38. Let's look at SQL ALTER TABLE example that adds more than one column.
  40. For example:
  42. ALTER TABLE supplier
  43. ADD (supplier_name varchar2(50),
  44. city varchar2(45));
  46. This SQL ALTER TABLE example will add two columns, supplier_name as a varchar2(50) field and city as a varchar2(45) field to the supplier table.
  47. Modify column in table
  48. Syntax
  50. To modify a column in an existing table, the SQL ALTER TABLE syntax is:
  52. ALTER TABLE table_name
  53. MODIFY column_name column_type;
  55. Example
  57. Let's look at SQL ALTER TABLE example that modifies a column.
  59. For example:
  61. ALTER TABLE supplier
  62. MODIFY supplier_name varchar2(100) not null;
  64. This SQL ALTER TABLE example will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.
  65. Modify multiple columns in table
  66. Syntax
  68. To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:
  70. ALTER TABLE table_name
  71. MODIFY (column_1 column_type,
  72. column_2 column_type,
  73. ...
  74. column_n column_type);
  76. Example
  78. Let's look at SQL ALTER TABLE example that modifies more than one column.
  80. For example:
  82. ALTER TABLE supplier
  83. MODIFY (supplier_name varchar2(100) not null,
  84. city varchar2(75));
  86. This SQL ALTER TABLE example will modify both the supplier_name and city columns.
  87. Drop column in table
  88. Syntax
  90. To drop a column in an existing table, the SQL ALTER TABLE syntax is:
  92. ALTER TABLE table_name
  93. DROP COLUMN column_name;
  95. Example
  97. Let's look at SQL ALTER TABLE example that drops (ie: deletes) a column from a table.
  99. For example:
  101. ALTER TABLE supplier
  102. DROP COLUMN supplier_name;
  104. This SQL ALTER TABLE example will drop the column called supplier_name from the table called supplier.
  105. Rename column in table
  106. Syntax
  108. To rename a column in an existing table, the SQL ALTER TABLE syntax is:
  110. ALTER TABLE table_name
  111. RENAME COLUMN old_name to new_name;
  113. Example
  115. Let's look at SQL ALTER TABLE example that renames a column in a table.
  117. For example:
  119. ALTER TABLE supplier
  120. RENAME COLUMN supplier_name to sname;
  122. This SQL ALTER TABLE example will rename the column called supplier_name to sname.
  123. Rename table
  124. Syntax
  126. To rename a table, the SQL ALTER TABLE syntax is:
  128. ALTER TABLE table_name
  129. RENAME TO new_table_name;
comments powered by Disqus