Cyber Monday Deal : Flat 30% OFF! + free self-paced courses  - SCHEDULE CALL

sddsfsf

How To Create Database Table-All You Need To know

A collection of related data stored in a table format within a database is called a table. A table is a collection of data elements (values) in relational and flat file databases that employ a model of horizontal rows and vertical columns that can be identified by name; the unit at the intersection of a row and a column is the cell.

A table can have any number of rows despite having a predetermined number of columns. One or more values found in a particular column subset can be used to identify each row.The primary key is a specific selection of columns used to identify rows uniquely. We will learn about create table syntax for various database systems and how to create table in sql server.

Different Types of Database Tables

This article will teach us about various MySQL and SQL Server methods for creating tables.

Core Table

Lookup Table

List Table

Detail Table

Cross Table

Your database has a "main" table like this one. The database simply wouldn't exist in the first place without these.

A "reference" table with categories, types, keywords, tags, or other "classification" labels, is called a Lookup table. These categories typically signify a limited scale. Some examples are quality, priority, severity, frequency, etc.

Similar to a Lookup table, this is a different kind of "reference" table with a longer list of records and frequent additions. Therefore, "List" Some examples are Reasons, Objectives, Genres, and Other Information. It might be referred to as the "Growable. Lookup" table.

Specific items subordinate to the records in a Core table are contained in a Detail table. Some examples are Request Subtleties, Occasion Logs, Episode Reports, Client Remarks, etc.

Only establishing a many-to-many relationship between two other tables is the purpose of a Cross-Reference table. While not required, these two additional tables are typically Core tables.

How to Create Table in MySQL Syntax

The syntax for creating table in MySQL is as follows:

Create TABLE table_name
(
Column1 datatype,
Column2 datatype,
Column3 datatype
……
)

The column parameters specify the name of the column in the table.

Following are a few MYSQL create table syntax examples.

Example 1

The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

The PersonID column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and will hold characters; the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

Example 2

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

The syntax for creating table in mysql is as follows.

CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;

The following SQL creates a new table called "TestTables" (which is a copy of the "Customers" table):

Example is

CREATE TABLE TestTable AS SELECT customername, contactname FROM customers;

How to Create Table in SQL Server

The SQL Server Create Table Syntax is as Below

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

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

Following are a few examples of Create Table function

Example 1

The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

The PersonID column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and will hold characters; the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

Example 2

We will create a table from another table.

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

The syntax is as follows.

CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;

The following SQL creates a new table called "TestTables" (which is a copy of the "Customers" table): 

CREATE TABLE TestTable AS

SELECT customername, contactname
FROM customers;

Creating a Table Using SQL Server Management Studio

You can also create a table using SQL Server Management Studio. Following are the steps.

Step 1

Log in to SQL Server Management Studio

Step 2

The following screen appears. Select the database in which you want to create the table. Expand it. Select Table and right-click on it. Select new and table.

Step 3

The following screen appears. Provide the necessary column details and datatypes and click on save. The table will be created.

What are Temporary Tables in SQL Server?

In SQL Server, a database table that only exists for a short time on the server is called a temporary table. A temporary table stores a subset of the data in a normal table for a predetermined time.

When you have a lot of records in a table but only frequently need to work with a small subset of them, temporary tables are a good option. In this case, you can filter the data once and save it in a temporary table instead of filtering it multiple times to get the subset.

Permanent and temporary tables are nearly identical. When the last connection to the query window that created the temporary table is broken, temporary tables are created in TempDB and are automatically deleted. We can store and process intermediate results with Temporary Tables. When we require temporary data storage, temporary tables come into play. Data is an integral part of businesses, and there’s great demand for administrators in SQL; go through our SQL DBA career path if you want to set yourself up for this role.

Types of Temporary Tables

There are two types of temporary tables

  • Local temporary tables
  • Global temporary tables

Local Temporary Table

Global Temporary Table

Only the session that created the Local Temp Table can access it. When the connection that created it is closed, it is deleted by default. A single "#" is used as a table name prefix when creating a Local Temporary Table.

Additionally, the "DROP TABLE #EmpDetails" query allows the user to remove this temporary table. The name of the table will have random numbers added to it. When the stored procedure is finished running, the Temporary Table created within it is automatically deleted.

Global Temporary Tables are Dropped when the last connection referencing the table closes. They are visible to all connections. A distinct table name must accompany the global table name. The Table Name will not include any random numbers after it. Add the "##" image before the table name to make a Worldwide Impermanent Table.

How to Create Temporary Tables

The mysql table creation syntax for creating a temporary table is as follows:

CREATE TABLE #name_of_temp_table (
 column_1 datatype,
 column_2 datatype,
 column_3 datatype,
 .
 .
column_n datatype
)

How to Delete a Table

We have learned different ways of creating a table. Now we will learn different ways to delete a table.

Using Transact SQL

Log into SQL Server.

To delete a table using transact SQL, you have to execute the following command

Use 
Drop table 

Here table name is the table's name. The database name is the name of the database where the table belongs. 

Using SQL Server Management Studio

1)    Log into SQL Server Management Studio

2)    Select the desired database and expand.

3)    Go to tables.

4)    Select the table you want to delete and right-click on it.

5)    Select delete.

Conclusion

Over the last few paragraphs, we have learned about different ways of creating a table in MySQL and SQL Server. We have also learned about the creation of temporary tables. How to delete a database table is also covered in this write-up. This will give the reader enough interest to study the table creation subject further. Or else you can also enroll in an online SQL server training course and shape your ever-growing SQL career.

cta14 icon

SQL Testing Training

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

4 days 14 Dec 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

10 days 20 Dec 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

4 days 14 Dec 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

4 days 14 Dec 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

3 days 13 Dec 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

4 days 14 Dec 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

7 days 17 Dec 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

10 days 20 Dec 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

11 days 21 Dec 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

4 days 14 Dec 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

17 days 27 Dec 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

10 days 20 Dec 2024