Creating a sql table,insertion of data and deletion of data from table.
In the SQL database for creating a table, we use a command called CREATE TABLE.
SQL CREATE TABLE Statement
A Table is a combination of rows and columns. For creating a table we have to define the structure of a table by adding names to columns and providing data type and size of data to be stored in columns.
Syntax:
CREATE table table_name(Column1 datatype (size),column2 datatype (size),..columnN datatype(size));Here table_name is name of the table, column is the name of column
SQL CREATE TABLE Example
Let us create a table to store data of Customers, so the table name is Customer, Columns are Name, Country, age, phone, and so on.
CREATE TABLE Customer( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(50), LastName VARCHAR(50), Country VARCHAR(50), Age INT CHECK (Age >= 0 AND Age <= 99), Phone int(10));
Insert Data into Table
To add data to the table, we use INSERT INTO, the syntax is as shown below:
Syntax:
//Below query adds data in specific column, (like Column1=Value1)//Insert into Table_name(Column1, Column2, Column3)Values (Value1, value2, value3);//Below query adds data in table in sequence of column name(Value1 will be added in Column1 and so on)//Insert into Table_nameValues (Value1, value2, value3);//Adding multiple data in the table in one go//Insert into Table_nameValues (Value01, value02, value03),(Value11, value12, value13),(Value21, value22, value23),(ValueN1, valueN2, valueN3)
Example Query
This query will add data in the table named Subject
-- Insert some sample data into the Customers tableINSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'), (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'), (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'), (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'), (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');
SQL DELETE Statement
SQL DELETE is a basic SQL operation used to delete data in a database. SQL DELETE is an important part of database management DELETE can be used to selectively remove records from a database table based on certain conditions. This SQL DELETE operation is important for database size management, data accuracy, and integrity.
Syntax:
DELETE FROM table_nameWHERE some_condition;
Parameter Explanation
Some_condition: condition to choose a particular record.
table_name: name of the table
Note: We can delete single as well as multiple records depending on the condition we provide in the WHERE clause. If we omit the WHERE clause then all of the records will be deleted and the table will be empty.
The sample table is as follows GFG_Employees:
Query:
Assume we have created a table named GFG_Employee which contains the personal details of the Employee including their id, name, email and department etc. as shown below −
CREATE TABLE GFG_Employees ( id INT PRIMARY KEY, name VARCHAR (20) , email VARCHAR (25), department VARCHAR(20));INSERT INTO GFG_Employees (id,name,email,department) VALUES (1,Jessie,jessie23@gmail.com,Developmet),(2,Praveen,praveen_dagger@yahoo.com,HR),(3,Bisa,dragonBall@gmail.com,Sales),(4,Rithvik,msvv@hotmail.com,IT),(5,Suraj,srjsunny@gmail.com,Quality Assurance),(6,Om,OmShukla@yahoo.com,IT),(7,Naruto,uzumaki@konoha.com,Development);Select * From GFG_Employee
Deleting Single Record
You can delete the records named Rithvik by using the below query:
Query
DELETE FROM GFG_Employees WHERE NAME = 'Rithvik';
Deleting Multiple Records
Delete the rows from the table GFG_Employees where the department is "Development". This will delete 2 rows(the first row and the seventh row).
Query
DELETE FROM GFG_Employees WHERE department = 'Development';
Delete All of the Records
To remove all the entries from the table, you can use the following query:
Query
DELETE FROM GFG_EMPLOyees; Or
DELETE * FROM GFG_EMPLOyees;
Output
All of the records in the table will be deleted, there are no records left to display. The table GFG_EMPLOyees will become empty.
output
Important Note:
DELETE is a DML (Data Manipulation Language) command hence operation performed by DELETE can be rolled back or undone.
👍