📚Back-end Notes

5.Create and read

Oct 04, 20246 min read

CREATE and DROP database

CREATE DATABASE database_name;
DROP DATABASE database_name;

TIP

I always use a meaningful and relevant name when creating a new database. This helps to make it easier to document my work.

NOTE

Database names must also be unique and can only have a maximum of 63 characters. If my database name doesn’t meet these requirements, then an error message will appear.

CREATE TABLE statement

CREATE TABLE table_name (column1_name DATATYPE...);

WARNING

Be aware that before you can create tables, you must already have a database on the server. In other words, you can’t build tables if there’s no database to create them in.

CREATE TABLE customers(customerName VARCHAR(100), phoneNumber INT);

ALTER TABLE statement

No table is ever static database developers are always restructuring tables. Sometimes they need to add new columns. Delete old ones or edit the data they contain. You can complete many of these basic restructuring actions using sequel syntax.

The first part of an alter statement is the altar and table keywords.

These keywords informed the database that there was a table whose contents must be altered. This is followed by the name of the table to be altered. I then include the AD keyword. This keyword tells sequel that there’s one or more items to be added to the table.

There are other keywords I could include here instead.

ALTER TABLE table_name ADD (column_name DATA TYPE);

I insert a pair of parentheses within these parentheses, I declare the name of a new column to be added to the table along with this data type.

ALTER TABLE students ADD (age INT, country VARCHAR(50), nationality VARCHAR(255));
ALTER TABLE students DROP COLUMN nationality;
ALTER TABLE sutdents MODIFY country VARCHAR(100);

INSERT statement

INSERT INTO table_name (column1_name, column2_name, column3_name)
VALUES (value1, value2, value3);

NOTE

It’s important to remember that each value corresponds to a specific column and so should reflect the same datatype and order.

Adding multiple rows in a table

INSERT INTO table_name (column1_name, column2_name, column3_name)
VALUES
(value1, value2, value3),
(value1, value2, value3),
(value1, value2, value3);
INSERT INTO players(ID, name, age, start_date) VALUES (1, "Yuval", 25, "2020-10-15");

WARNING

It’s important to use the correct format of year, month, and day when entering dates in a table. Otherwise, an error message will appear.

CURRENT_DATE()

INSERT INTO players(ID, name, age, start_date) VALUES (1, "Yuval", 25, CURRENT_DATE());

Creating tables

This reading covers the process of creating a table in a database. You’ve learned about creating and dropping a database using CREATE DATABASE and DROP DATABSE SQL commands. You also learned the SQL syntax of creating a table in the database using the CREATE TABLE syntax and modifying the structure of an already created table using ALTER TABLE. 

The main objective of this reading is to help you develop a more practical understanding of how the CREATE TABLE statement works. You’ll also learn how to use its syntax correctly. Furthermore, this reading also highlights some important points to note when creating a database table.

Important points on creating a table

You know already that in SQL, the CREATE TABLE syntax is available in the Data Definition Language (DDL) subset which is used to create a new database table.

Here are some important points to remember when using the CREATE TABLE syntax. These points are also useful for creating tables in general.

  • Always give a meaningful name to your table as well as the columns or fields of the table. 
  • The data types of columns or fields may vary from one database system to another. For example, NUMBER is supported in Oracle database for integer values whereas INT is supported in MySQL.  
  • It’s good practice to refer to the list of data types supported by the database system that you are using, namely MySQL, SQL Server and Oracle. 
  • Remember to specify the appropriate length for data types where applicable.  
    • For example, VARCHAR is the preferred data type to use if you are storing text-based data because it saves space. VARCHAR takes up 1 byte per character, plus another 2 bytes to hold length. So, for example, if you set a VARCHAR(100) data type to a first name column and store a value of Jen in it, then it would take up 3 bytes (for J, E, and N) plus another 2 bytes, that is 5 bytes in total to store that value. 

Using the CREATE TABLE statement

Let’s inspect the CREATE TABLE syntax more closely. In this example, let’s look at the syntax to create a table named customers in the sample database. The table includes the following columns/fields:

  • CustomerId
  • FirstName
  • LastName
  • Company
  • Address
  • City
  • State
  • Country
  • PostalCode
  • Phone
  • Fax
  • Email
  • SupportRapid

The table is created using the following syntax:

CREATE TABLE customers
    (CustomerId INT, 
    FirstName VARCHAR(40), 
    LastName VARCHAR(20), 
    Company VARCHAR(80), 
    Address VARCHAR(70), 
    City VARCHAR(40),
    State VARCHAR(40), 
    Country VARCHAR(40), 
    PostalCode VARCHAR(10), 
    Phone VARCHAR(24), 
    Fax VARCHAR(24), 
    Email VARCHAR(60), 
    SupportRapid INT ); 

The main syntax here is the CREATE TABLE syntax.

You then provide the name of the table, in this case customers.

Then a pair of parentheses should follow. Within the parenthesis the column or field names are given followed by the data type of each and the size or length of data that can be stored.  

CustomerId and SupportRapidfiles are of type INT because they store numeric values. The rest of the fields have a data type of VARCHAR but have different lengths. 

The lengths are specified according to the amount of data (the number of characters) that need to be stored in these fields. 

At the end of the statement, remember to put the semicolon.

SELECT statement

SELECT column FROM table;

Retrieve data from multiple columns

SELECT col1, col2 FROM table_name;

Retrieve all data from table

SELECT * FROM table_name;

INSERT INTO SELECT statement

When working with tables, there might be instances where you need to retrieve information from one or more tables in order to populate columns in another table. You can complete these actions using the INSERT INTO SELECT statement.

INSERT INTO select

Essentially, the INSERT INTO select statement is used to query data from a column within a source table and place the results of that query in the column within a target table.

INSERT INTO target_tbl (column_name)
SELECT column_name
FROM source_tbl;

Additional resources

The following resources are some additional reading material that provide extra knowledge on SQL DDL and DML commands:

DatabaseDataSQLSyntaxQueryCRUD

Previous one → 4.SQL data types | Next one → 6.Update and delete

Graph View

Backlinks