Question 2: Getting productive
Up until now, Cocoa Confections has stored data on only two tables within its database —
CREATE TABLE `customers` ( `customer_id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) NOT NULL, `last_name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `address` varchar(255) DEFAULT NULL, `city` varchar(255) DEFAULT NULL, `state` varchar(2) DEFAULT NULL, `zip_code` varchar(5) DEFAULT NULL, PRIMARY KEY (`customer_id`) ); CREATE TABLE `orders` ( `order_id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) NOT NULL, `order_placed_date` date NOT NULL, PRIMARY KEY (`order_id`), KEY `customer_id` (`customer_id`), FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) );
These tables have allowed Emma, the owner of Cocoa Confections, to store basic information about her company. But the business is growing, and Emma wants to be able to store even more information. Specifically, she'd like to be able to track information on individual
products as well.
Products are the things that Cocoa Confections sells: cookies, brownies, cakes, pies, and more. Each
product has the following attributes:
|ID||Unique identification number used to refer to the product within the database||56|
|Name||Name of the product||Blueberry pie|
|Price||Sale price of the product, in US cents||2500|
Can you help Emma restructure her database by writing a query to
CREATE a new table and then
INSERT a row for the
'Blueberry pie' product, which costs $25?
To insert a new table into the database, we'll need to use a
CREATE TABLE statement command to make a new table called
Since there are three unique attributes for each product, we'll need a total of three fields within our new
products table. The first will be a
PRIMARY KEY that represents the
id of the product. The second will hold the product's
name; since we know the name of each product will contain a limited number of characters, the
VARCHAR field type is probably best for this. The last will hold the product's
price, in US cents; since the price in cents will always be an integer, we can use the
INTEGER field type.
Once we've created our table, we'll need to use an
INSERT command to create a new table row to represent the
'Blueberry pie' product. Our
INSERT command will need to reference each of the existing fields within our table:
price. Remember, to insert our values into the
products table, we'll need to start our command with
INSERT INTO products, then specify which fields will contain which values.
As we construct our table, we should ensure that the
id field acts as the
PRIMARY KEY for the table, and use the
AUTO_INCREMENT feature to ensure that each
product has a successive unique
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
When we perform an
INSERT query to add a new row to this table, we'll have to be sure to exclude the
id field, or to set it to
NULL, so that SQL can automatically generate an
id for the new row using the
In order to insert a new table into Cocoa Confections' database, you'll need to use SQL's
CREATE TABLE command. Does your solution include this command?
The new table you create,
products, should have one field for each of the three data points identified in Emma's table schema above:
price. Does your
CREATE TABLE command reference these three fields?
INSERTa new row?
After creating the table, you'll need to use an
INSERT command to add a new row representing the
'Blueberry pie' product. Did you remember to use an
INSERT command at the end of your solution?
idfield to automatically increment?
If you constructed your table correctly, you won't have to specify a product
id number yourself in the
INSERT query — SQL will automatically do it for you. Is that the case with your query?
Awesome! Now that you've gone through the table creation and row insertion process, let's review the model solution step by step:
First, we'll use the
CREATE TABLE command to generate a new table called
CREATE TABLE products ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, price INTEGER NOT NULL );
id field is an integer identification number used as the
PRIMARY KEY for the table. This means that no two rows in our table can have the same
id. We'll use the
NOT NULL command to ensure that SQL does not let us insert a row that does not contain an
id. And, we'll tell SQL to use
AUTO_INCREMENT to automatically generate an
id whenever a product is added to our database.
name field will be text, so we'll use the
VARCHAR field type to contain all of our product names. We'll specify a limit of
255 characters, since no product name will be more than 255 characters. Specifying product name character limits will help us cut down on the size of our database. We'll also use the
NOT NULL modifier to ensure that SQL does not allow us to add a product to our table without a name.
Finally, we'll add a
price field to store the price of the product, in US cents. It may be tempting to use a
DECIMAL field type here, but since US cents will always be whole numbers, we'll use an
INTEGER field type instead. Using
INTEGER field types to store product prices in cents is a common coding convention used frequently around the world. We'll also use that
NOT NULL modifier to ensure that every product has a price.
Next, let's work on adding the
'Blueberry pie' product to our database using the
INSERT INTO products (id, name, price) VALUES (NULL, 'Blueberry pie', 2500);
This is a fairly simple query which inserts a row into our new
products table with information on the
'Blueberry pie' product. We'll tell the database to use
NULL for the product
'Blueberry pie' for the
2500 for the
One common point of confusion here is why we set the
NULL. After all, didn't we specify
NOT NULL when creating the
id field of the table?
We sure did — and we can use
NULL here for the
id because of a commonly-used feature of SQL:
AUTO_INCREMENT. Since we used
AUTO_INCREMENT when creating the
id field, SQL will automatically generate a new
id for each row added to the table. When inserting a new row, we can set the
id of that row to
NULL, and SQL will know that it needs to generate its own
id that is one digit higher than the last
id in the table.
If we didn't want to include the
id field in our
INSERT query, we could also write it like this — and we'd get the same result:
INSERT INTO products (name, price) VALUES ('Blueberry pie', 2500);