Creating and Modifying Tables in SQL

1. Introduction

In this tutorial, we will learn how to create and modify tables in SQL. SQL or Structured Query Language is used for managing and manipulating databases. Specifically, we will focus on the creation and modification of tables, which are a vital part of any database.

By the end of this tutorial, you will be able to create tables, modify existing tables by adding or deleting columns, and changing the data type of columns in SQL.

Prerequisites: Basic understanding of databases and SQL syntax.

2. Step-by-Step Guide

2.1 Creating Tables

To create a table in SQL, we use the CREATE TABLE statement. The syntax is as follows:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Here, table_name is the name of the table you want to create. column1, column2, etc. are the names of the columns. datatype defines the type of data that can be stored in each column (e.g., INT, VARCHAR, DATETIME), and constraint defines any restrictions on the data (e.g., NOT NULL, UNIQUE).

2.2 Modifying Tables

To modify an existing table in SQL, we use the ALTER TABLE statement. The syntax is as follows:

• Adding a column:

ALTER TABLE table_name
ADD column_name datatype constraint;

• Deleting a column:

ALTER TABLE table_name
DROP COLUMN column_name;

• Changing the data type of a column:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

3. Code Examples

3.1 Creating a table

Let's create a table named Employees with three columns: ID, Name, and DOB.

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    DOB DATE
);

In this code:

• ID is of type INT and is the primary key.
• Name is of type VARCHAR with maximum length 100 and cannot be null.
• DOB is of type DATE.

3.2 Adding a column

Let's add a column named Email to the Employees table.

ALTER TABLE Employees
ADD Email VARCHAR(255);

In this code, we're adding a new column Email of type VARCHAR with a maximum length of 255.

3.3 Deleting a column

Let's delete the DOB column from the Employees table.

ALTER TABLE Employees
DROP COLUMN DOB;

This code drops the column DOB from the Employees table.

3.4 Changing the data type of a column

Let's change the data type of the ID column to BIGINT.

ALTER TABLE Employees
ALTER COLUMN ID BIGINT;

This code changes the data type of the ID column from INT to BIGINT.

4. Summary

In this tutorial, we learned how to create and modify tables in SQL. We learned how to add and delete columns from a table and how to change the data type of a column.

The next step in learning SQL would be understanding how to insert, update, and delete data from these tables. You might also want to explore how to create relationships between different tables.

5. Practice Exercises

Try out the following exercises to practice what you've learned:

1. Create a table Students with columns StudentID, FirstName, LastName, DOB, and Email.
2. Add a new column PhoneNumber to the Students table.
3. Change the data type of StudentID from INT to VARCHAR.
4. Delete the Email column from the Students table.

Solutions:

1. Creating the Students table:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DOB DATE,
    Email VARCHAR(255)
);

1. Adding PhoneNumber column to the Students table:

ALTER TABLE Students
ADD PhoneNumber VARCHAR(15);

1. Changing the data type of StudentID:

ALTER TABLE Students
ALTER COLUMN StudentID VARCHAR(50);

1. Deleting the Email column:

ALTER TABLE Students
DROP COLUMN Email;

Keep practicing and exploring more about SQL!