What are SQL Tables? Exploring Table Operations & Techniques

Get a grip on SQL tables - from basic structures to advanced operations, become a data management maestro with ProjectPro!

Recipe Objective - What are SQL Tables? Exploring Table Operations & Techniques 

SQL tables are the foundational elements of relational databases, serving as containers for structured data. Understanding SQL tables and the operations associated with them is crucial for effective data management. Check out this recipe to step into the world of SQL tables, uncovering the core concepts, techniques, and best practices that empower you to create, manipulate, and optimize tables to meet your data storage and retrieval needs.

What are SQL Tables?

SQL tables are the primary data storage containers within a relational database. They are structured as a collection of rows and columns, where each row represents a single record, and each column represents a specific attribute or field. Tables are used to store and organize related data, making it easy to retrieve, update, and analyze information.

Exploring Table Operations in SQL 

Table operations in SQL involve a set of actions and commands used to manipulate, query, and manage data within database tables. Here are the fundamental operations for working with relational databases: 

How to Create a Table in SQL? 

A table is a database object. It is the most basic form of organized data, in which information is represented in rows and columns. A table is referred to as a relation, while a row is referred to as a tuple. We can create a table in SQL using the “CREATE TABLE” statement.

Learn to Implement Deep Learning Techniques for Medical Image Segmentation 

Syntax:
CREATE TABLE table_name(
Column1 datatype,
Column2 datatype,
...);

Let us create a table customer in our CompanyA database. The customer table will have the following columns –
• Customer ID
• Customer Name
• Contact
• Age
• City
• Country

Code:

CREATE TABLE CUSTOMER(

customer_id numeric,
customer_name varchar(100),
age integer,
city varchar(50),
country varchar(70)
);

The empty table will now look something like –
Customer_id Customer_name Contact City Country

How to Rename a Table in SQL?

You may want to rename your table at some point in time. You can easily do this using the “ALTER TABLE” statement.

Syntax:
ALTER TABLE tablename RENAME to new_tablename
Let us rename our “customer” table to “customers”

Code:

ALTER TABLE customer RENAME TO customers;

How to List the Tables in a Database in MySQL?

You can list the names of the tables in your database using the “SHOW TABLES” statement in MySQL.

Syntax:
SHOW TABLES;

Code:

SHOW TABLES;

Output:

+--------------------+

| Tables_in_companya |

+--------------------+

| customers          |

+--------------------+

1 row in set (0.05 sec)

How to View Columns of a Table in MySQL?

It is very simple to view the structure of a table along with the column details in MySQL using the following query.

Syntax:
SHOW COLUMNS FROM table_name;

Code:

SHOW COLUMNS FROM customers;

Output:

+---------------+---------------+------+-----+---------+-------+

| Field         | Type          | Null | Key | Default | Extra |

+---------------+---------------+------+-----+---------+-------+

| customer_id   | decimal(10,0) | NO   | PRI | NULL    |       |

| customer_name | varchar(100)  | YES  |     | NULL    |       |

| age           | int           | YES  |     | NULL    |       |

| city          | varchar(50)   | YES  |     | NULL    |       |

| country       | varchar(70)   | YES  |     | NULL    |       |

+---------------+---------------+------+-----+---------+-------+

5 rows in set (0.08 sec)

As we can see, the table customers have 5 columns and customer_id is the primary key.

How to Copy a Table in SQL?

We can copy all the contents from one table to another table with the help of the SELECT statement in SQL.

Syntax:

Method 1-
First, create an empty table
CREATE TABLE new_table_name LIKE original_table_name;

Then insert data into the table
INSERT INTO new_table_name SELECT * FROM original_table_name;

Method 2-
CREATE TABLE new_table_name AS SELECT * FROM original_table_name;

The difference between methods one and two is that method two only copies the structure and data of the original table. However, in method one all the attributes including indexes are copied.

Code:

-- method one

--creating an empty table
CREATE TABLE new_table LIKE customers;

--inserting data into the table
INSERT INTO new_table SELECT * FROM customers;

-- method two
CREATE TABLE new_customers AS SELECT * FROM customers;

How to Alter a Table?

SQL gives its users the liberty to make changes to the table even after it has been created. You can add a column to the table, modify a column definition (like we did to add constraints), rename a column name, and even drop a column. All of this can be done using the “ALTER” statement. Let us see the syntax and examples for each of the above-mentioned alterations.

Example 1: How to Add a Column to the Table in SQL? 

Syntax:

ALTER TABLE table_name ADD new_column_name column_definition;

We will add a new column called email to the customers table.

Code:

ALTER TABLE customers ADD email char;

Example 2: How to Modify a Column Declaration in SQL? 

Syntax:
ALTER TABLE table_name MODIFY column_name column_definition;

We want the values of the email column to be UNIQUE

Code:

ALTER TABLE customers MODIFY email char UNIQUE;

Example 3: How to Rename a Column in SQL? 

Syntax:
ALTER TABLE table_name RENAME COLUMN old_column_name to new_column_name;

We will rename the email column of the customers table to customer_email.

Code:

ALTER TABLE customers RENAME COLUMN email to customer_email;

Example 4: How to Drop a Column in SQL? 

Syntax:
ALTER TABLE table_name DROP Column column_name ;

We will drop the customer_email column from the customers table.

Code:

ALTER TABLE customers DROP customer_email;

How to Delete a Table in SQL?

To delete rows from a table you can make use of the DELETE statement along with the WHERE clause. If the WHERE clause is not specified, all the rows of the table are deleted.

Unlike the TRUNCATE command, the DELETE command does not free up the space occupied by the table.

Syntax:
To delete a row based on a condition –
DELETE FROM table_name WHERE(condition);

To delete all the rows –
DELETE FROM table_name;

Code:

DELETE FROM new_customers WHERE customer_id = 101;

How to Truncate a Table in SQL?

Truncating your table means deleting your table and its data completely. There is no way to retrieve the data again using the flashback table statement. Even the rollback process cannot be done once you truncate a table. The TRUNCATE statement deletes all rows from the table and frees up the enclosing space.

You can truncate a table using the “TRUNCATE TABLE” statement.

Syntax:
TRUNCATE TABLE table_name;

Code:

TRUNCATE TABLE new_table;

How to Drop a Table in SQL?

One must drop a table after a lot of careful consideration because once you drop a table, the data is lost forever. You can drop a table using the “DROP TABLE” statement.

Syntax:
DROP TABLE table_name;

Code:

DROP TABLE new_customers;

SQL Join Operations 

SQL join operations are essential for combining rows from two or more tables based on a related column between them. These operations allow users to retrieve data that is distributed across multiple tables in a database. The common columns used for joining are typically primary and foreign keys that establish relationships between tables. There are several types of join operations, including inner join, outer join, cross join, and self join. Each type serves a specific purpose and offers different ways to merge data from multiple tables.

Types of SQL Joins

  1. Inner Join: This operation returns only the rows that have matching values in both tables. It filters out the rows that do not have corresponding values in both tables. The syntax for the inner join is as follows:

  1. Left Join (or Left Outer Join): This operation returns all the rows from the left table and the matched rows from the right table. If there are no matching rows in the right table, it returns NULL values. The syntax for the left join is as follows:

  1. Right Join (or Right Outer Join): This operation is similar to the left join, but it returns all the rows from the right table and the matched rows from the left table. If there are no matching rows in the left table, it returns NULL values. The syntax for the right join is as follows:

  1. Full Join (or Full Outer Join): This operation returns all rows when there is a match in one of the tables. If there are no matches, it still returns all rows from both tables, with NULL values in the columns that do not have a match. The syntax for the full join is as follows:

  1. Self Join: This operation is used to join a table to itself, typically when the table has a foreign key that references its own primary key. It allows for comparisons between rows within the same table. The syntax for the self join is as follows:

  1. Cross Join (or Cartesian Join): This operation produces the Cartesian product of the two tables, meaning it combines each row from the first table with every row from the second table. The syntax for the cross join is as follows:

How to Join 2 Tables in SQL? 

To join two tables in SQL, you can use the various types of join operations mentioned above, depending on the specific requirements of your query. Here's an example of using an inner join to combine two tables:

In this example, table1 and table2 are the names of the tables you want to join, and common_column represents the column that both tables share. Adjust the column names and table names accordingly based on your specific database schema.

How to Join Multiple Tables in SQL? 

Joining three or more tables in SQL allows you to combine data from multiple sources and retrieve information that is distributed across a complex database. To join three tables, you can use various types of join operations such as INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, or a combination of these, depending on your data retrieval requirements.

Example - SQL Join on 3 Tables 

Let's consider a scenario where you have three tables: orders, customers, and products. You want to retrieve a list of orders that includes information about the customer who placed the order and the product that was ordered. Here's how you can achieve this using SQL:

 

The resulting query retrieves a list of orders along with the names of the customers who placed them and the names of the products ordered. By performing these multiple joins, you can create a comprehensive dataset that combines data from three separate tables.

Explore More About SQL Table Operations with ProjectPro!

SQL tables are the fundamental building blocks of relational databases, serving as containers for organizing and storing data. Understanding the operations and techniques associated with SQL tables is crucial for anyone working with data, whether in data science, big data, or database management. Practical experience is key to mastering these skills, and ProjectPro offers an invaluable resource for this purpose. With its extensive repository of over 270+ solved end-to-end project solutions on data science and big data, ProjectPro empowers learners to apply their knowledge in real-world scenarios. By leveraging the expertise and guidance provided by ProjectPro, individuals can develop a deep understanding of SQL table operations and techniques, enhancing their proficiency and making them well-equipped to tackle data-related challenges in their careers. 

What Users are saying..

profile image

Anand Kumpatla

Sr Data Scientist @ Doubleslash Software Solutions Pvt Ltd
linkedin profile url

ProjectPro is a unique platform and helps many people in the industry to solve real-life problems with a step-by-step walkthrough of projects. A platform with some fantastic resources to gain... Read More

Relevant Projects

Detectron2 Object Detection and Segmentation Example Python
Object Detection using Detectron2 - Build a Dectectron2 model to detect the zones and inhibitions in antibiogram images.

Time Series Forecasting with LSTM Neural Network Python
Deep Learning Project- Learn to apply deep learning paradigm to forecast univariate time series data.

End-to-End Snowflake Healthcare Analytics Project on AWS-2
In this AWS Snowflake project, you will build an end to end retraining pipeline by checking Data and Model Drift and learn how to redeploy the model if needed

Build Deep Autoencoders Model for Anomaly Detection in Python
In this deep learning project , you will build and deploy a deep autoencoders model using Flask.

Linear Regression Model Project in Python for Beginners Part 1
Machine Learning Linear Regression Project in Python to build a simple linear regression model and master the fundamentals of regression for beginners.

Azure Deep Learning-Deploy RNN CNN models for TimeSeries
In this Azure MLOps Project, you will learn to perform docker-based deployment of RNN and CNN Models for Time Series Forecasting on Azure Cloud.

Learn Object Tracking (SOT, MOT) using OpenCV and Python
Get Started with Object Tracking using OpenCV and Python - Learn to implement Multiple Instance Learning Tracker (MIL) algorithm, Generic Object Tracking Using Regression Networks Tracker (GOTURN) algorithm, Kernelized Correlation Filters Tracker (KCF) algorithm, Tracking, Learning, Detection Tracker (TLD) algorithm for single and multiple object tracking from various video clips.

Mastering A/B Testing: A Practical Guide for Production
In this A/B Testing for Machine Learning Project, you will gain hands-on experience in conducting A/B tests, analyzing statistical significance, and understanding the challenges of building a solution for A/B testing in a production environment.

Recommender System Machine Learning Project for Beginners-1
Recommender System Machine Learning Project for Beginners - Learn how to design, implement and train a rule-based recommender system in Python

NLP Project on LDA Topic Modelling Python using RACE Dataset
Use the RACE dataset to extract a dominant topic from each document and perform LDA topic modeling in python.