Essential SQL

Jan 24, 2016

Define a Table

Every product will have different features available, particularly
with respect to datatypes and the level of constraints you can set
during a create table statement. However, the standard includes much
of what you need on a day to day basis. As an example, lets create
three tables, orders, items, and customers.

createtablecustomers(customer_numberintegerprimarykey,-- some dialects like postgres allow a serial type here.first_namevarchar(20)notnull,last_namevarchar(20)notnull,street_addressvarchar(128)notnull,cityvarchar(32)notnull,statecharacter(2)notnull,zipcharacter(5)notnull);createtableitems(item_idintegerprimarykey,-- therefore must be unique and not nulldescriptiontextnotnull);createtableorders(customer_numberintegerreferencescustomers(customer_number)notnull,item_idintegerreferencesitems(item_id)notnull,qtyintegernotnulldefault1);

Some basic facts about the above, there are some arbitrary limits in the definition, states have a two character code, zip codes are five digits,names are limited by length, as are street addresses. In the case of the order, there is a default of 1 for quantity, this makes some sense. There are not null prescriptions, the default behavior
in sql is to allow columns to be unspecified. The reasoning behind this is twofold, either that represents unknown information, we’re sure Tony James has a gender, but unclear which it was, so a gender field would allow a null
to represent a missing value, or represents an empty or not-applicable set. The danger in the first is that it’s difficult
to reason about the proper outcome, the danger in the second is that it’s a violation of first normal form.

Insert data into a table

So now we have some tables, lets populate one with a few values. First, let’s create two customers:

Usually the INSERT statement will give an output that shows the number of rows affected (should be two here). Quick takeaway,
since each column was set not null by definition, we need to add to each of them. The
list of column names before values is not strictly needed, if ommitted, you will need to know what order
the create table listed the columns in. If nothing else, it serves as very clear documentation of what you intend to do.
It is possible to add columns later to a table, and code that generates inserts as above with explicit
column names will be able to weather a reorganization, but code that counted on the implicit ordering of columns will be
more likely to need adjustment. Let the database system worry about column ordering.

With any luck, you’ll see a table like this one when you confirm you inserted the data:

So now that Victor and I have accounts at the store, we should be lured in by attractive pricing on fabulous items.
Let’s create a few baubles to hawk:

insertintoitems(item_id,description)values(1,'Yelmo de Mambrino - bronze basin with a chin strap to keep you dry'),(2,'Collier de la Reine - fine diamond necklace, sadly this did not protect Marie Antoinette from the guillotine');

At this point, I realize our items table is missing a short name, only has a fulltext description. If all we
sell are apples and tomatoes, then the description is the short name. A cursory glance above shows this is not necessarily
the case.

Alter Table to improve design

We can add two possible fixes. We can drop the items table and create a new one, or we can just add a new column. Because
of the foreign key references to item_id in the orders table, we’ll just add a name column:

altertableitemsaddcolumnnamevarchar(128);-- now we want to fill in the names:updateitemssetname='Yelmo de Mambrino'whereitem_id=1;updateitemssetname='Collier de la Reine'whereitem_id=2;-- now we want to enforce every item be named going forward:altertableitemsaltercolumnnamesetnotnull;altertableitemsaddunique(name);

So that wasn’t too bad. I haven’t tested that in mysql, it might be different. Basic ideas, you can add columns apart from
create table statements using alter table statements, you can add constraints (not null column, unique column) using alter table.
And most importantly, UPDATE allows you to change values in a row. Here I used the item_id because I only had two rows, for larger tables you would likely want an update to follow a specific select option.

Update data in the table

Let’s remove just one more bit of redundancy, right now the item name is in two places (name, description). Let’s fix the descriptions by cutting the part before that dash:

-- showing how we get to the final step, let's experiment with substring to find the magic answer-- first, use a regex to get the part from the dash forwardselectsubstring(descriptionfrom'-\ .*$')fromitems;substring-------------------------------------------------------------------------------------------bronzebasinwithachinstraptokeepyoudry-finediamondnecklace,sadlythisdidnotprotectMarieAntoinettefromtheguillotine(2rows)-- second, use a range to start at the third letter (first two are dash, space):selectsubstring(substring(descriptionfrom'-\ .*$')from3)fromitems;substring----------------------------------------------------------------------------------------bronzebasinwithachinstraptokeepyoudryfinediamondnecklace,sadlythisdidnotprotectMarieAntoinettefromtheguillotine(2rows)-- Now, update all rows to use description as descriptionupdateitemssetdescription=substring(substring(descriptionfrom'-\ .*$')from3);-- double check our work:selectdescriptionfromitems;description----------------------------------------------------------------------------------------bronzebasinwithachinstraptokeepyoudryfinediamondnecklace,sadlythisdidnotprotectMarieAntoinettefromtheguillotine(2rows)

So now, after we altered items to have three columns, we can insert using the three column format

insertintoitems(item_id,name,description)values(3,'Bálsamo de Fierabrás','this mixture can heal your wounds, or make you forget them in your pains');