Learn Python

Learn Data Structure & Algorithm

Learn Numpy

Learn Pandas

Learn Matplotlib

Learn Seaborn

Learn Statistics

Learn Math

Learn MATLAB

Learn Machine learning

Learn Github

Learn OpenCV

Learn Deep Learning

MySQL Introduction

MySQL Setup

MySQL Operators

MySQL Basic Works

MySQL Create Table

MySQL insert and get values

MySQL Condition

MySQL GROUP BY

MySQL Case

MySQL sorting and calculate avg,sum,count

MySQL Update

MySQL Time

MySQL String

MySQL Rollback & Commit

MySQL Join and Concatenate

MySQL Index

MySQL Date

MySQL View

MYSQL With Python

Learn MongoDB

Learn Web scraping

Learn Excel

Learn Power BI

Learn Tableau

Learn Docker

Learn Hadoop

Everything about creating a SQL table

How to create a table in the selected database?

Syntax:
create table table_name ( column_name dtype,column_name dtype);

Example:
create table books_store_1(books varchar(100),price int, issue_date DATETIME);

You can't give space between a column name words. You can use underscore as a separator.

You must write the data type of each column beside column name and have to give a comma between columns.

The data type of string is varchar(in the bracket you have to write the number that how many letters a column can take in a cell).

The data type of integer is int or bigint. You can store too many numbers in bigint and int can take 11 numbers.

The data types of date and time are:
DATE-->YYYY-MM-DD
TIME-->HHH:MM:SS or HH:MM:SS
DATETIME-->YYYY-MM-DD HH:MM:SS
TIMESTAMP-->YYYY-MM-DD HH:MM:SS
YEAR--> YYYY
You can pass any like DATE, DATETIME, etc according to you need.

SQL DEFAULT Constraint

DEFAULT""
If a missing value is in the column then the missing cell will fill by a default value null. If you want to fill the missing cells with another default value then write DEFAULT" " beside the data type of that column which column null value you want to change. Inside of the double quotation write the default value that you want.

Syntax:
create table table_name ( column_name dtype DEFAULT"Missing",column_name dtype DEFAULT"No_value");

Example:
create table books_store_1(books varchar(100) deafult"Missing",price int);

SQL NOT NULL Constraint

If you want that a column that can't have a missing value then write NOT NULL while creating that column .

Syntax:
create table table_name ( column_name dtype NOT NULL,column_name dtype NOT NULL);

Example:
create table books_store_1(books varchar(100) deafult"Missing",price int NOT NULL);

SQL UNIQUE Constraint

If you want that a column can have unique values only then write UNIQUE while creating the column.

Syntax:
create table table_name ( column_name dtype NOT NULL UNIQUE ,column_name dtype NOT NULL);

Example:
create table books_store_1(books varchar(100) deafult"Missing" UNIQUE ,price int NOT NULL);

SQL PRIMARY KEY Constraint

What is primary key?

Suppose you have a table and in the table you have three columns id, books and price. By the id column you can uniquely identify the rows of the table. So id can be used as the primary key of the table. So we can say that, use or make primary key that column, by which you can uniquely identify each record or row of a table.

Syntax:
create table table_name(column_name dtype NOT NULL PRIMARY KEY, column_name dtype NOT NULL UNIQUE ,column_name dtype NOT NULL);

Example:
create table books_store_1(id int PRIMARY KEY,books varchar(100) deafult"Missing" UNIQUE ,price int NOT NULL);

SQL FOREIGN KEY Constraint

What is foreign key?

A FOREIGN key is used to link multiple tables together. A FOREIGN key in a table used to point The PRIMARY key of another table. We do this so that we can connect multiple tables. We can say that to create a link between multiple tables we use it.

Suppose you have a table that has three columns, ID, Product_name, and Name.
You have another column that has two columns, ID and Total_sell.
Here Id column of the second table points to the Id column of the first table.
Here Id column of the first table is the primary key and the Id column of the second table is the foreign key.

Syntax:
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );

SQL CHECK(condition) Constraint

To set conditions use CHECK(condition). Here inside the bracket write the condition. You can use AND and OR to set multiple conditions.

Syntax:
create table table_name(column_name dtype NOT NULL PRIMARY KEY, column_name dtype NOT NULL UNIQUE ,column_name dtype CHECK(condition));

Example:
create table books_store_1(id int PRIMARY KEY,books varchar(100) deafult"Missing" UNIQUE ,price int CHECK(price>150));

Here we set the condition that the price column can have a value less than 100. It means that we only can't put value less than 100 in this column.

SQL auto_increment Constraint

If you don't want to fill a column and you want that the column value will automatically increase and will fill the row with that increased value, then use auto_increment.

Syntax:
create table table_name(column_name dtype auto_increment , column_name dtype NOT NULL UNIQUE ,column_name dtype CHECK(condition)L);

Example:
create table books_store_1(id int PRIMARY KEY auto_increment,books varchar(100) deafult"Missing" UNIQUE ,price int CHECK(price>150));

Now we don't need to put value in the id column.

How to create table using existing table?

Syntax:
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name

Example:
CREATE TABLE books_store_2 AS
SELECT price, books
FROM books_store_1

CodersAim is created for learning and training a self learner to become a professional from beginner. While using CodersAim, you agree to have read and accepted our terms of use, privacy policy, Contact Us