How to Create a Table and Insert Data in SQL

If you know one or two things in about using a computer, you definitely might have heard about the term SQL. But maybe you don’t know what that is, and you have never bothered to understand it. Well, this is not rocket science, especially for you, but a person who is utterly green about technology it probably is. So first of all, what is the meaning of SQL? TO start with the initials SQL stand for Structured Query Language.

A not so detailed definition of SQL is that this is what is used when communicating to a database. Again, what is a database? A database is an electronic system where one can easily access data. Not only it obtains the data, but also the user can also manipulate it as well as update it. Enterprises use databases to store, manage, and retrieve information.

The new-age database is maintained by what we know as a DBMS, which stands for the database management system. Examples of databases comprise of Microsoft Access, FileMaker, FoxPro, Oracle, Clipper, dBASE, RDBMS, PostgreSQL, MySQL, and SQL Server. The simplest definition of what a database is a gathering of information, professionally called data, which is stored in a server.

Therefore SQL, according to the American National Standards Institute, ANSI, and SQL, is the standard language for the database management system to relate. An SQL statement is used to undertake tasks like updating the data on the existing database, retrieving that data from the database and equally, managing it.

Application of SQL

As said earlier, SQL has many uses, including data integration script, analytical queries, retrieving information, and so many other applications.

Data integration script

SQL is used to write data integration scripts by a database administrator or a developer

Analytical queries

Data analysts use structured question language for setting as well as running analytical questions all the time.

Retrieve data

A database administrator uses SQL to retrieve a subset of the data within a database for analytics application and also processing transactions. Commonly used SQL elements include insert, select, delete, add, create, alter, and truncate.

Important SQL applications

SQL modifies the index structures as well as a database table. Also, the administrator can add, delete, and update the rows of the information using SQL.

Benefits of SQL

There is a myriad of benefits of why one should use SQL here are just a few reasons and benefits.

SQL Standard

The first standard for this language was developed in 1986; the International National Standards followed suite in 1987. Today we use the latest standard that was established in 2011.

Portable

It runs on PCs, mainframes, servers, and mobile devices. This language also runs in local systems, the internet, and the intranet. SQL databases can be comfortably moved from a system to another without any compilations.

Open Source

SQL is open source, meaning that you can use it at a low cost with large communities.

It is easy to learn and use

This language consists of English statements, meaning that you can quickly learn and use it. Writing an SQL query has never been easier.

It is a high-value skill to have

Many jobs such as IT support, business data analysis, and web development require a candidate who is good at SQL. It is the reason we want to teach you how you can create a table and insert data in the Structured Query Language.

Creating a Table in SQL and Inserting DATA

First of all, there are various ways of creating tables and inserting data in SQL. To start with,

One can create a table using the create statement; for instance, you want to create a table titled pupils. Use the syntax below.

CREATE TABLE PUPILS (
  Pupilid int IDENTITY (1.1) NOT NULL, 
  Firstname varchar (200), 
  Lastname varchar (200), 
  Email varchar (100)
)

Therefore, this syntax will get you the table Pupil, where the pupil id is not null. If you want to insert the data into that table titled ‘Pupil’ here is what you do using the first method.

Insert into Pupils, 
(
  Pupilid, Firstname, Lastname, email
) 
Values 
  (
    1, ’Watson’, ’Keter, ’’ Watsonketer@gmail.com’
  )

You can verify this query to get the following result

The second method is how you can insert values into the table using a different table. See below.

For instance, we already have a table named Pupils, and here we want to insert the table’s values into the other table called Pupildemo.

Create table ‘Pupildemo’

CREATE TABLE Pupildemo (
  Pupilid int IDENTITY (1.1) NOT NULL, 
  Firstname varchar (200), 
  Lastname varchar (200), 
  Email varchar (100)
)

At this juncture, to insert data of table ‘Pupils’ into the table ‘Pupildemo’, you can use the following statement.

Insert into Pupildemo (
  Pupilid, Firstname, Lastname, email
) Then, 
SELECT 
  Pupilid, 
  Firstname, 
  Lastname, 
  email 
from 
  Pupils

You can verify this statement results to get the results using this method.

SELECT, FROM Pupildemo, and you will get this result.

Note: For you to insert the data on one table to the other, the data type of the column needs to be the same.

Rename the current table

Alter the name of ‘Pupildemo’ table to Pupilscopy

ALTER TABLE 
  Pupildemo RENAME TO Pupilscopy

Consider the table ‘Pupils’ that is given down here, and ADD column ‘Phone’ in that table.

Here, you are required to use the ALTER TABLE command to add a column to the existing table.

Here is what happens

ALTER TABLE 
  Pupils, 
  then, 
ADD 
  Phone INT Null

This is what you get

Please Note –
If you use the ALTER TABLE statement to add another column to the existing table, and don’t add values, you get a default Null on those columns. It is why the phone row has Null since we did not add any phone data.