sql

SQL Create Table Example

This article focuses on the functionality and usage of the SQL Create Table command.

A Relational Database Management System stores data in form of relations. A relation comprises tuples and attributes. In layman terms, relation means a table comprising of rows and columns. A tuple is a row, whereas an attribute is a column.

You can also check this tutorial in the following video:

CREATE TABLE SQL Tutorial – video

1. Pre-requisites

You can implement the example of this article in two ways:

  1. By installing SQL in your local machine.
  2. On any online SQL compiler.

2. About Create Table Command in SQL

SQL Create Table command is used to create a new table in the database. You can also create a new table from a pre-existing table. Note that to create a table, you need to have an existing database.

3. SQL Create Table Syntax

Now, let us look at the syntax for the Create Table command.

CREATE TABLE tableName (
columnName1 datatype1,
columnName2 datatype2,
columnName3 datatype3);

4. SQL Create Table Example

  1. First of all, create a database. Let us create a database named example using the Create Database command.
Create Database example;

Then you have to use the database created above to create a table.

Use example;
SQL Create Table - Create database
SQL Create database and use database

2. With that, you can now create a table using the Create table command. Let us say – we are creating a table named student data. The table has 3 columns. The columns are studentName, studentId, and studentAge where the first column is of type varchar, the others are integers.

CREATE TABLE studentData( studentName varchar(20), studentId number, studentAge number);
SQL Create Table -  Create table & describe
SQL Create table & describe table command

The describe command is used to check the structure of the table that you created using SQL Create table command.

5. Primary Key

A primary key is a column whose data can be used to uniquely identify each data record. In this step, I will insert four records into the StudentData table and define the studentId column as the primary key.

Insert statement

insert studentData(studentName ,  studentId , studentAge ) values('Mary Zheng', 1, 50);
insert studentData(studentName ,  studentId , studentAge ) values('Tom He', 2, 56);
insert studentData(studentName ,  studentId , studentAge ) values('Tina  Sheng', 3, 16);
insert studentData(studentName ,  studentId , studentAge ) values('John Cheng', 4, 25);

primary key

alter table studentData add primary key (studentId )

alter table output

mysql> alter table studentData add primary key (studentId );
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

6. Foreign Key

A foreign key is a column which links to a primary key in another table. Primary and foreign keys are the keys to link data among the tables. In this example, I will create two tables and link the data with primary and foreign keys.

create courseData

--create courseData
CREATE TABLE courseData( courseName varchar(20), courseId INT,  PRIMARY KEY (courseId ));

insert into courseData(courseName , courseId ) values('Math', 1);
insert into courseData(courseName , courseId ) values('English', 2);

create student_course and link with foreign keys

--create student_course table
CREATE TABLE student_course(
id INT NOT NULL AUTO_INCREMENT, courseId INT,studentId INT, 
PRIMARY KEY (id),
FOREIGN KEY (courseId) REFERENCES courseData(courseId),
FOREIGN KEY ( studentId) REFERENCES studentData( studentId));

--insert the data
insert into student_course(courseId,studentId) values(1,1);
insert into student_course(courseId,studentId) values(2,1);
insert into student_course(courseId,studentId) values(1,2);
insert into student_course(courseId,studentId) values(2,3);
insert into student_course(courseId,studentId) values(1,4);
insert into student_course(courseId,studentId) values(2,2);
Create table with Foreign keys

7. Select Data from Joined Tables

In this step, I will select the data from joined tables based on the primary and foreign keys.

select with join

select * 
from student_course sc, studentData s, courseData c
where sc.courseId = c.courseId
and sc.studentId = s.studentId;

select result

mysql> select *
    -> from student_course sc, studentData s, courseData c
    -> where sc.courseId = c.courseId
    -> and sc.studentId = s.studentId;
+----+----------+-----------+-------------+-----------+------------+------------+----------+
| id | courseId | studentId | studentName | studentId | studentAge | courseName | courseId |
+----+----------+-----------+-------------+-----------+------------+------------+----------+
|  1 |        1 |         1 | Mary Zheng  |         1 |         50 | Math       |        1 |
|  3 |        1 |         2 | Tom He      |         2 |         56 | Math       |        1 |
|  5 |        1 |         4 | John Cheng  |         4 |         25 | Math       |        1 |
|  2 |        2 |         1 | Mary Zheng  |         1 |         50 | English    |        2 |
|  4 |        2 |         3 | Tina  Sheng |         3 |         16 | English    |        2 |
|  6 |        2 |         2 | Tom He      |         2 |         56 | English    |        2 |
+----+----------+-----------+-------------+-----------+------------+------------+----------+
6 rows in set (0.01 sec)

mysql>

8. Summary

This article discusses the SQL Create Table command’s functionality and usage through the example. SQL Create Table command is one of the most fundamental commands in SQL. It also touches upon the relational database terminologies – relations, tuples, attributes, primary, foreign key, insert, and select statements.

9. Download the source code

You should download the source code to implement the functionality of SQL Create Table command.

Download
You can download the full source code of this example here: SQL Create Table Example

Last updated on Feb. 24th, 2022

Simran Koul

Simran has graduated as a Bachelor of Engineering in Computer Science from Chitkara University. She has undergone a 6-months long comprehensive industrial training at the reputed Centre for Development of Advanced Computing (C-DAC), where she worked on a project including the likes of Java, JSP, Servlets while the UI-UX through the pioneering HTML, CSS and JS. Her inquisitive nature and the seed of curiosity keeps her on the toes to find material to write about. Along with her interests in Software Development, she is an ardent reader and always ready-to-write writer.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button