SQL Basics - Creating and Changing Tables (Last Lesson)

Up until now, we dealt with retrieving, inserting, and manipulating data. Today, we'll discuss manipulating the table itself.

CREATING A NEW TABLE

To create a new table, we use the CREATE TABLE clause. When we create the table, we must specify the name of the new table, and the name and datatypes of the columns, with each column separated by a comma. For example, to creat the table 'OrderItems' that we've been working with, we do the following:

CREATE TABLE OrderItems
(
order_num INTEGER NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
item_price DECIMAL(8,2) NOT NULL
)

As you can see, we specified the datatype, and if it allows a NULL value or requires you to givwe a value. You can also define a default value, as we've done with the quantity column. This creates the table we have used in this tutorial.

CHANGING AN EXISTING TABLE

To make changes to an existing table we use the ALTER TABLE clause. Different applications have very different rules as to when you're allowed to alter a table, and with what information. Refer to your application documaentation for your specific case. If and when you do add a column to the table, here's how it's done:

ALTER TABLE Orders
ADD payment_method CHAR(20);

and when your boss is ready to kill you for what you did, you can remove a column:

ALTER TABLE Orders
DROP COLUMN payment_method;

Now your boss is ready to fire you for wasting time, so you can really mess him up by deleting the whole table:

DROP TABLE Orders;

That's by far the easiest thing to do so far. I wonder why that is. The guy that wrote this language must've have loads of scores to settle.

THERE IS MUCH MORE TO THE SQL LANGUAGE. BUT I THINK FOR NOW WE'LL LEAVE IT AT THIS. WE WILL BEGIN SQL INJECTION SOON. BUT FIRST I WOULD LIKE TO WRITE ABOUT SETTING UP OUR OWN SQL SERVER SO THAT WE CAN PRACTICE SQL INJECTION WITHOUT GETTING ARRESTED. THEY PROBABLY WOULD BLOCK THIS BLOG FROM JAIL COMPUTERS. WE'LL BE IN TOUCH REAL SOON.