A Beginner's Guide to SQL Table Creation

Understanding Fundamentals of SQL Table Creation

Introduction

Welcome to InsightNinja, your go-to destination for data-driven things in Data Analytics. We're thrilled to have you here, joining us on this exciting journey of exploration, discovery and knowledge sharing.

Creating tables is an essential task in SQL as they serve as the foundation for storing and organizing data. There are various ways to create tables in SQL Server, each with its own advantages and use cases.

In this post, we'll cover the most common methods for creating tables in SQL Server. 

Understanding SQL table structure

1. Column and their data types- 

    Each column in a table has a name and a data type that specifies the kind of data it can hold. Such as a name, age, gender, birth date etc.

2. Constraints-

    Constraints are applied to columns in a table to enforce certain rules for data entry. There are several types of constraints in SQL, including:

·  Primary Key in SQL

    A primary key ensures the uniqueness and integrity of each row in a table, and is essential for establishing relationships between tables in a database.

· Foreign Key in SQL- 

    A Foreign key is basically primary key of child table which can be mapped against primary key of parent table in order to establish parent-child relationship between two tables.

· NOT NULL Constraint- 

    This constraint ensures that a column cannot have NULL values.

· Unique Constraint- 

    This constraint ensures that each value in a column is unique and cannot be duplicated.

· Check Constraint- 

    This constraint is used to ensure that the values in a column meet certain criteria or conditions.

· Default Constraint- 

    This constraint is used to provide a default value for a column when no value is specified.

3. SQL  Indexes-

    An index is created on one or more columns of a table and contains a copy of the values of those columns, along with a pointer to the location of the corresponding row in the table. The index is stored separately from the table, so it does not increase the size of the table itself.

How to create a table in SQL?

1. Using the CREATE TABLE statement

The most commonly used method for creating a table in SQL Server is the CREATE TABLE statement. 

Here is Basic syntax for table creation:

    CREATE TABLE table_name(

    column1 datatype [constraints],

    column2 datatype [constraints],

    ...

    columnN datatype [constraints])

Here, table_name is the name of the table to create, and column1, column2, and so on, are the names of the table columns.

Datatype refers to the data type of the column, and constraints are optional rules that you can apply to the column.

 For example, consider the following SQL code:  

CREATE TABLE STATS(   

Match_No    INT         PRIMARY KEY,

Opposition  VARCHAR(50) NOT NULL,

RunsScored  INT         DEFAULT 0,

BallFaced   INT,

StrikeRate  FLOAT,

MatchDate   DATE,

Venue       VARCHAR(100))

This code creates a table named Stats with Six columns: ‘Match_No.’ which is primary key of table,’Opposition’ which can not be null as we have applied constraint ‘Not Null’ against it, ‘RunsScored’ as 0 by default when not specified , ‘BallFaced’,’MatchDate’,’ StrikeRate’ and ’ Venue’.

While creating table, Mapping appropriate datatype to each column is vital with respect to efficiency of table itself as well as script/code referring to that table.

Here's a quick reference table for commonly used data types.

Data Type	Description	Example
INT	An integer data type that can store whole numbers	18
FLOAT	A floating-point data type that can store decimal numbers	18.18
DECIMAL	A fixed-point data type that can store precise decimal numbers.	18.181818
VARCHAR	A variable-length character string data type	'Virat Kohli'
NVARCHAR	A variable-length Unicode character string data type	N'विराट कोहली'
DATE	A date data type that stores a date without a time	05-11-1988
TIME	A time data type that stores a time without a date	10:28:00
DATETIME	A date and time data type that stores both date and time information	05-11-1988 10:28:00

Let's insert some data into table.

INSERT INTO STATS (Match_No,Opposition,RunsScored,BallFaced,StrikeRate,MatchDate,Venue)

VALUES

(1,'South Africa',18,34,52.94 ,'2019-06-05','Southampton'),

(2,'Australia'   ,82,77,106.49,'2019-06-09','The Oval'),

(3,'Pakistan'    ,77,65,118.46,'2019-06-16','Manchester'),

(4,'Afghanistan' ,67,63,106.34,'2019-06-22','Southampton'),

(5,'West Indies' ,72,82,87.80 ,'2019-06-27','Manchester'),

(6,'England'     ,66,76,86.84 ,'2019-06-30','Birmingham'),

(7,'Bangladesh'  ,26,27,96.29 ,'2019-07-02','Birmingham'),

(8,'Sri Lanka'   ,34,41,82.93 ,'2019-07-06','Leeds'),

(9,'New Zealand' ,01,06,16.16 ,'2019-07-09','Manchester')

   

To view content of the table, simply use following SQL command:     

SELECT Match_No,Opposition,RunsScored,BallFaced,MatchDate,Venue FROM STATS

Output :

2. Using the SELECT INTO statement

The SELECT INTO statement is a quick and straightforward way to create a new table based on the result set of a SELECT statement.

For instance, consider the following SQL code for creating a table having all records where Virat has scored more than 50 runs:

    SELECT Opposition,RunsScored,BallFaced,MatchDate,Venue

    INTO STATS_Copy

    FROM STATS

    WHERE RunsScored >= 50;

This code creates a new table named Stats_Copy with the same columns as the Stats_Of_ViratKohli_WC19 table  with  rows where the RunsScored column is greater than 50.

Let’s fetch all records from copy table.

       SELECT * FROM STATS_Copy

Output:

Note that any constraints or indexes defined on the Parent table will not be copied over to the new table.

 3. Creating empty table using parent table. 

 Creating a duplicate table with no data is super easy. Here we are using comparison operator '=' , The database engine will search the table for any rows that satisfy the condition (1=2), which is never true.

 As a result, the database engine will not return any rows, but it will still return the metadata for the columns in the table.

 For instance, consider the following SQL code for creating a empty table having Same Structure (column names and datatypes) as to parent table: 

    SELECT Opposition,RunsScored,BallFaced,StrikeRate,MatchDate,Venue

    INTO Blank_Stats_Copy

    FROM STATS

    WHERE 1=2;     

 Freebie:  

 If you are willing to create a table having a column with incremental value for each record. 

 This means every time you insert a new record, a unique number will be automatically created in the   table.

 Example:

             CREATE TABLE STATS(

        Match_No     INT    IDENTITY(1,1) PRIMARY KEY,

        Opposition  VARCHAR(50) NOT NULL,

        MatchDate   DATE);

 

 In above example, ‘Match_No’ column serves as identity column.

 Let’s insert few records in the table. 

             INSERT INTO STATS(Opposition,MatchDate) VALUES

        ('Australia','2019-07-06'),

        ('Pakistan','2019-07-13'),

        ('England','2019-07-19')

 In above example, We have not mentioned our identity column while inserting records. Now lets see   resultant table.

Output:

Best Practices for table creation:

 Naming Conventions: Use consistent and descriptive names for your tables and columns. This will make it easier for other developers to understand and work with your code.

2.     Avoiding NULL values: Null values can be problematic in a database because they don't have a specific value. Instead, consider using default values or using a "not null" constraint to ensure that all columns have a value.

3.     Choosing appropriate data types: Each column in a table should have a data type that accurately  reflects the type of data that will be stored in that column.

4.     Using Primary Keys: A primary key is a unique identifier for each row in a table. It's important to   include a primary key in every table to ensure that each row can be easily identified and accessed.

5.     Using Indexes: Indexes can improve the performance of your queries by allowing the database to  quickly locate the data that you need.

           Conclusion:

 We hope you found this blog post informative and gained a better understanding of SQL table creation   using different ways. If you have any further questions or need more information, feel free to use   comments section for reaching out to us.