You learnt in a previous lesson how to create a table. Once you created it, it is possible to change its properties. You do this using “alter table”.
Adding a column
To add a column called “month” of type integer in the table “revenues”, we use the following syntax:
If we want the column “month” to show just on the right hand side of the column “year”, the syntax is the following:
If we do not indicate where to put the new column, it will be the last column in the table. As a result, it will appear completely at the right hand side of the results when using “select *” in the select statement.
Changing a column name
To change the name of the column “year” to “revenue_year” in the table “revenues” (assuming that the column is of type integer:
Note that you have to indicate the column type again, even if you want only the change the column name.
Changing the properties of a column
Example: we want the column city to be of type varchar(70) instead of varchar(60). We also want to indicate that this column should not accept null values:
Note that city appears twice. This is because with the change keyword, we can also change the column name at the same time as changing the properties. In our example, we do not want to change the column name. As a result, the old column name (city) and the new column name (city) are the same.
Deleting a column
To delete the column “revenu_year” from the “revenues” table:
Carefull: if there are some rows in the table, it will not only delete the column. It will also delete the information inside the column for all rows.
Renaming the table
To change the name of the table “revenues” to “sales_revenue”: