Introduction to Sql 03

contents

1. Modifying Existing Tables
   1. ALTER TABLE
   2. UPDATE
   3. DELETE
   4. TRUNCATE & DROP
2. Fixing our Fruit Data
3. Introducing a discount column
4. Summary

At this point we've learnt how to create a table and insert data, but we haven't learnt how to make any changes to a table once created. In this lesson we'll explore the most common methods used in making changes to already existing tables.

Modifying Existing Tables

Knowing how to modify existing tables is an important part of SQL. Before we make any changes to our fruit data we'll quickly cover the various methods.

ALTER TABLE

The ALTER TABLE statement in SQL is used to add, remove, or modify columns in an existing table. It can also be used to rename the table itself.

Its usage is as follows:

-- Renaming the Table
ALTER TABLE table_name
RENAME TO new_table_name;

-- Adding a new column
ALTER TABLE table_name
ADD new_column datatype;

-- Dropping a column
ALTER TABLE table_name
DROP COLUMN column_name;

-- Renaming a column
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

-- Modify a column's datatype
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;
						

UPDATE

The typical format is shown below.

UPDATE table_name
SET [modification]
WHERE [condition];
						

For now just remember its basic structure. We'll use it in a real example later where it'll make more sense.

DELETE

Delete is similar to update, but instead of a modification the rows are simply deleted.

DELETE FROM table_name
WHERE [condition];
						

TRUNCATE & DROP

While they're not exactly performing 'modifications', two other commands we can use are TRUNCATE and DROP. Drop does exactly what you'd think; it drops the table (or database). TRUNCATE is different in that the table itself remains, but any data within it is removed.

DROP TABLE table_name;
DROP DATABASE db_name;

TRUNCATE TABLE table_name;
						

Fixing our Fruit Data

Looking back at our fruit data we have the following issues:

1. The average_weight column in the fruits table doesnt specify what measurement it being used.
2. There's a typo in our customer table, with 'Wellington' spelt as 'Wallington'.
3. It turns out customer_id = 13 was a typo; it should be 3.
4. The customer_name for Maple Fruits Co. and London Fruit Traders both contain lower case words.
5. The email_address for Maple Fruits Co. contains leading and ending white spaces.
6. contact_number contains inconsistent formatting.
7. Banana Central Ltd. and Global Fruit Co. never made a transaction and were found to be out of business. They can be removed.

We'll use ALTER, UPDATE and DELETE on our table to make the required changes.

ALTER TABLE fruits
RENAME COLUMN average_weight TO average_weight_grams;

UPDATE customers
SET state = 'wellington'
WHERE state = 'wallington';

UPDATE orders
SET customer_id = 3
WHERE customer_id = 13;

UPDATE customers
SET customer_name = INITCAP(customer_name);

UPDATE customers
SET email_address = TRIM(email_address); 

UPDATE customers
SET contact_number = REPLACE(contact_number, '+61 ', '0');

DELETE FROM customers
WHERE customer_name IN ('Banana Central Ltd.', 'Global Fruit Co.');
						

Introducing discounts

As it turns out, orders over a certain amount recieve cashback. The discounts are as follows:

• Over $2,000 = $80 cashback
• Over $5,000 = $300 cashback
• Over $10,000 = $750 cashback

To add discounts to our orders table we need to add a new column using ALTER TABLE.

ALTER TABLE orders
ADD COLUMN discount_price_aud FLOAT;
						

Now we'll add the discounted price data to it using UPDATE.

UPDATE orders
SET discount_price_aud = (
    CASE
        WHEN order_price_aud >= 10000 THEN order_price_aud * 0.925
        WHEN order_price_aud >= 5000 THEN order_price_aud * 0.94
        WHEN order_price_aud >= 2000 THEN order_price_aud * 0.96
        ELSE order_price_aud
    END
);
						

Next, try adding two more columns: one to calculate the amount of money saved in each order, and a boolean column stating whether or not a discount was applied. Name the columns discount_amount and is_discounted.

ALTER TABLE orders
ADD COLUMN discount_amount FLOAT;

UPDATE orders
SET discount_amount = order_price_aud - discount_price_aud;


ALTER TABLE orders
ADD COLUMN is_discounted BOOLEAN;

UPDATE orders
SET is_discounted = (
    CASE
        WHEN order_price_aud >= 1000 THEN TRUE
        ELSE FALSE
    END
);
						

While using ALTER TABLE and UPDATE to add discounts works, they're not the best tools for the job. Soon we'll see an alternative technique to achieve the same goal. For now, go ahead and remove the three new columns using what we learnt earlier.

Summary

In this lesson we learnt how to use ALTER TABLE, UPDATE, DELETE, TRUNCATE, and DROP to make changes to our tables and the values within them.

In the next lesson we'll begin learning some of SQL's more advanced functionalities used to structure queries more effectively.