Getting productive

Up until now, Cocoa Confections has stored data on only two tables within its database — customers and orders:

CREATE TABLE customers (
	customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	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,
);

CREATE TABLE orders (
	order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	customer_id INT NOT NULL,
	order_placed_date DATE NOT NULL,
	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:

Attribute	Description	Example
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?

I've got an answer

I need a hint

Hint: Use a CREATE TABLE statement

To insert a new table into the database, we'll need to use a CREATE TABLE statement command to make a new table called products.

I've got an answer

I need another hint

Hint: Three fields

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.

I've got an answer

I need another hint

Hint: Using the INSERT command

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: id, name, and 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.

I've got an answer

I need another hint

Hint: Setting id to NULL

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 number:

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 AUTO_INCREMENT feature.

Show me the answer!

Does your answer use a CREATE TABLE command?

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?

Yup, checks out!

I didn't think of that

Do you have the correct fields in your table?

The new table you create, products, should have one field for each of the three data points identified in Emma's table schema above: id, name, and price. Does your CREATE TABLE command reference these three fields?

Yup, checks out!

I didn't think of that

Did you INSERT a 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?

Yup, checks out!

I didn't think of that

Are you allowing the id field 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?

Yup, checks out!

I didn't think of that

The solution

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 products:

CREATE TABLE products (
	    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	    name VARCHAR(255) NOT NULL,
	    price INTEGER NOT NULL
	);

The 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.

The 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 command:

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 id, 'Blueberry pie' for the name, and 2500 for the price.

One common point of confusion here is why we set the id to 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);

SQL Practice Pack: 0 of 11 lessons completed

Next lesson