6 Powerful Steps to Master Tables in SQL: Creation, Modification, and Management

Tables in SQL

• Tables in SQL are the basic pieces of a relational database that is built on the structured query language (SQL).
• The data are stored in such a way that is well-organized, like a spreadsheet.
• A table is a SQL database that is made up of rows and columns, each row being a record, and each column being a field within the record.
• Those with no clue what a table is should get started with the necessary information.
• For example, individuals who want to deal with relational databases can follow these steps: designing, managing, and querying the same databases.

What is a Table in SQL?

• A tables in SQL is a set of data stored in rows and columns, related to each other.
• A column has a specific data type and the table is recognized by its name.
• The rows of a table correspond to individual records, while the columns describe the attributes of those records.
• For example, in a table with information about employees, each row would correspond to an individual employee, and the columns could be things like EmployeeID, FirstName, LastName, DateOfBirth, and Salary.
• Thus, a basic model of a table could be thusly structured:

• Consequently, EmployeeID, FirstName, LastName, DateOfBirth, and Salary are columns, and each row contains data about a specific employee.

1. Creating a Tables in SQL

• The SQL CREATE TABLE statement is used to create a table. This command defines the table structure, including its name and the columns it will contain.
• Syntax:

CREATE TABLE table_name (

column1 data type constraint,

column2 data type constraint,

column3 data type constraint,

...

);

Example: Creating an Employees Table

• Let’s create a table called Employees where we can store information about employees.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DateOfBirth DATE,

HireDate DATE,

Salary DECIMAL(10, 2)

);

Explanation:

• EmployeeID INT PRIMARY KEY: The EmployeeID column is an integer, and it is the primary key. A primary key ensures that each record in the table is unique.
• FirstName VARCHAR(50): This field is the same as that of the employee’s first name. It is 50 characters long, and it enters the variable-length type VARCHAR.
• LastName VARCHAR(50): Similar to the first name, the value entered here will actually store the last name of the employee.
• DateOfBirth DATE: This complete data set is complete using the employees’ date of birth in DATE format.
• HireDate DATE: The columno is used to input the employee’s hire date.
• Salary DECIMAL(10, 2): This column is used to store the salary of the employee that can take values as high as 100 million and have a decimal point up to two digits (cents).

2. Inserting Data into a Table

• When you have created a table, the INSERT INTO statement lets you insert data into it. The new records are inserted as the table’s new rows.
• Syntax:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

Example: Inserting Data into the Employees Table

INSERT INTO Employees (EmployeeID, FirstName, LastName, DateOfBirth, HireDate, Salary)

VALUES (1, 'John', 'Doe', '1985-07-12', '2020-01-15', 60000.00);

INSERT INTO Employees (EmployeeID, FirstName, LastName, DateOfBirth, HireDate, Salary)

VALUES (2, 'Jane', 'Smith', '1990-02-25', '2021-05-20', 70000.00);

• This command inserts two rows of data into the Employees table. Each row corresponds to an individual employee’s information.

3. Modifying a Table

• Sometimes, you may need to change the structure of an existing table by adding, deleting, or modifying columns. This is done using the ALTER TABLE statement.

Syntax for Adding a Column:

ALTER TABLE table_name

ADD column_name datatype;

Example: Adding a New Column to the Employees Table

ALTER TABLE Employees

ADD Email VARCHAR(100);

• This command will attach an Email column to the Employees table, thus you will be able to archive the employee email addresses.

Syntax for Dropping a Column:

ALTER TABLE table_name

DROP COLUMN column_name;

Example: Dropping the Email Column

ALTER TABLE Employees

DROP COLUMN Email;

• The Employees table no longer contains the Email column.

4. Querying Data from a Table

• One of the ways to source the data is through the table, which allows you to extract it by utilizing the SELECT statement.
• Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Example: Retrieving All Employee Data

SELECT * FROM Employees;

• This is the query that selects all the rows (*) associated with every record in the Employees table.

Example: Filtering Data with a WHERE Clause

SELECT FirstName, LastName, Salary

FROM Employees

WHERE  salary;> 60000

• This query selects only the names of employees who have a salary of 60,000 and above and the Salary columns.

5. Constraints on Tables in SQL

A constraint is a rule applied to table columns to ensure data integrity. The common types of constraints are:

• PRIMARY KEY: It makes sure that every record in the table is unique by a unique identifier.
• FOREIGN KEY: It is a mechanism of referential integrity where the column in the table is linked to the primary key of another table.
• NOT NULL: It makes sure that a column can not have the NULL value.
• UNIQUE: It ensures that all values in a column are unique.

Example: Adding Constraints to a Table

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY,

DepartmentName VARCHAR(100) NOT NULL,

ManagerID INT,

FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)

);

In this instance:

• DepartmentID is the primary key of the Departments table.
• DepartmentName is a must-fill form required by the NOT NULL condition.
• ManagerID is not just a regular key in this case, but it is known as a foreign key, referencing the column EmployeeID of the Employees table which is its base. Thus it serves as a link between the two tables.

6. Deleting a Table

• If you want to no longer see the table, you write DROP TABLE statement and click the delete.
• Syntax:

DROP TABLE table_name;

Example: Dropping the Employees Table

DROP TABLE Employees;

• Thus, the entire Employees table is erased, and even the existing data will be lost. Keep this command in mind when you want the table created back. Just remember, once deleted, it cannot be restored back.