Create Table, Select, Insert, Update, Delete Table In SQL

Create Table

 
The CREATE TABLE statement is used to create a new table in a database. In that table, if you want to add multiple columns, use the below syntax.
 
Syntax
  1. CREATE TABLE table_name (  
  2.     column1 datatype,  
  3.     column2 datatype,  
  4.     column3 datatype,  
  5.    ....  
  6. );  
The column parameters specify the names of the columns of the table.
 
The data type parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).
 
Create Table Example
  1. CREATE TABLE Employee(  
  2.     EmpId int,  
  3.     LastName varchar(255),  
  4.     FirstName varchar(255),  
  5.     Address varchar(255),  
  6.     City varchar(255)   
  7. );  
The EmpId 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 and the maximum length for these fields is 255 characters.
 

Insert Value in this Table

 
The INSERT INTO statement is used to insert new records in a table.
 
It is possible to write the INSERT INTO statement in two ways.
 
Syntax 
 
The first way specifies both the column names and the values to be inserted.
 
If you are adding values for all the columns of the table, then no need to specify the column names in the SQL query. However, make sure that the order of the values is in the same order as the columns in the table.
  1. INSERT INTO table_name (column1, column2, column3, ...)  
  2. VALUES (value1, value2, value3, ...);  
  3.   
  4.   '2nd way
  5. INSERT INTO table_name  
  6. VALUES (value1, value2, value3, ...);  
Example
 
Insert value in a 1st way. The column names are used here
  1. INSERT INTO Employee    (EmpId,LastName,FirstName,ADDRESS,City)
  2. VALUES (1, 'XYZ''ABC''India''Mumbai' ); 
  3.   INSERT INTO Employee (EmpId,LastName,FirstName,ADDRESS,City)
  4. VALUES (2, 'X', 'A', 'India', 'Pune' );
Insert value in a 2nd way.
  1. INSERT INTO Employee
  2. VALUES (3, 'XYZ''ABC''India''Mumbai' );  

Select Statment in SQL

 
The SELECT statement is used to select data from a database.
 
The data returned is stored in a result table, called the result-set.
  1. SELECT column1, column2, ...  
  2. FROM table_name;  
Here, column1, column2, ... are the field names of the table you want to select from the data. If you want to select all the fields available in the table, use the following syntax: 
  1. SELECT * FROM table_name;  
If the above query is executed, then all record is displayed. 
 
Example
  1. Select EmpId, LastName from Employee;  
  2.   
  3. Select * from Employee;  

Update Table 

 
The UPDATE statement is used to modify the existing records in a table.
 
Syntax 
  1. UPDATE table_name  
  2. SET column1 = value1, column2 = value2, ...  
  3. WHERE condition;  
Example 
  1. UPDATE Employee  
  2. SET FirstName= 'KS', City= 'Pune'  
  3. WHERE EmpId= 1;  
If the above query is executed then for EmpId= 1, "Firstname" and "City" column data will be updated. 
 

Update Multiple Rows

 
It is the WHERE clause that determines how many records will be updated. 
  1. UPDATE Employee
  2. SET City='Pune'  

Delete Statment in SQL

 
The DELETE statement is used to delete existing records in a table for a particular Record.
 
Syntax
  1. DELETE FROM table_name WHERE condition;  
Example
  1. DELETE FROM Employee WHERE EmpId=1;  
In Employee table EmpId = 1 record gets deleted. 
 

Delete All Records

 
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact,
  1. DELETE FROM table_name;    
  2.     
  3. DELETE From Employee  ;  
When the above query is executed, only table Data gets deleted.