CREATE

The DDL command CREATE is used to create the database or any objects such as a table, index, function, view, and store procedures.

1. CREATE DATABASE

Firstly we must create the database for our store. The CREATE query below allows you to create a new database.

1CREATE DATABASE musicstore;

2. CREATE TABLE

We firstly want to create a table containing all of the information on the albums we sell. Within the CREATE statement, we must first define the structure of our table, including column names, data types, and the size of the data to be added to each column.

1CREATE TABLE albums (
2    album_id INT,
3    title VARCHAR(150),
4    artist VARCHAR (80),
5    year_published SMALLINT,
6    price DECIMAL (6,2) NOT NULL,
7    genre VARCHAR(20) NOT NULL,
8    PRIMARY KEY (album_id));

From there we can use Data Manipulation Language (DML) specifically the command INSERT to populate the table with the data from our albums.

3. CREATE VIEW

Suppose we run a query and want to save the results-set as a table, in this situation we can create a view using the CREATE command. Because a view displays current data, the database engine recreates it each time a user queries it.

1CREATE VIEW DE_view AS
2SELECT album_id, title, artist, price
3FROM albums
4WHERE genre = ‘Dance/Electronic’;

We can then run queries on our view as if it was a separate table.

1SELECT title, artist
2FROM DE_view;

4. CREATE INDEX

Using the CREATE command we can create indexes for our table. Indexes are used to retrieve data from a database more quickly than would otherwise be possible. The indexes are not visible to users; they are only used to speed up searches and queries.

As an example, our database becomes increasingly populated with albums over time, to the point where searching becomes extremely slow. Using the following command, we create an index on a single column title or more than one column title and artist.

1-- One column index
2CREATE INDEX index_album
3ON albums (title);
4
5-- Multiple column index
6CREATE INDEX index_album_m
7ON albums (title, artist);

In addition, we can make indexes unique in order to maintain data integrity by ensuring that no two rows of data in a table have the same key value.

1CREATE UNIQUE INDEX index_album_u
2ON albums (title);

5. CREATE PROCEDURE

The CREATE command can also be used to create a stored procedure which is basically a prepared SQL code that can be saved and reused over and over again.

1DELIMITER //
2CREATE PROCEDURE SelectAllAlbums()
3BEGIN
4	SELECT * FROM albums;
5END //
6DELIMITER ;

To execute our stored procedure we simply run the command below. Handy right?

1CALL SelectAllAlbums();