Types of SQL Commands
SQL consists of several commands that are used to communicate with the database. SQL commands are nothing but a set of instructions that performs specific tasks, functions, and queries on the data. In this blog, we will discuss Types of SQL Commands.
Types of SQL commands
1.DDL(Data Definition Language)
In this category, it consists of commands that change the definition or structure of the database like creating/adding a table in the existing database or dropping a table from the database. It includes commands like:
- create
- Drop
- Alter
- Truncate
- Create
Create command is used to create new tables and databases in the SQL
Query to create a database:
create database database_name;
e.g create database pooja_info;
Query to create a table:
create table table_name(columnName1 datatype, columnname2 datatype, columnname 3
datatype…….);
e.g create table student(id int, name varchar(89), marks int);
- Drop
Drop command is used to drop/remove databases and tables from MySQL
Query to drop a database:
Drop database database_name;
e.g drop database pooja_info;
Query to drop a table:
Drop table table_name;
e.g drop table student;
- Alter
Alter command is used to perform an operation on the columns means alter command is used to change the definition or structure of the table by adding a column in the already existing table, removing column from the table, modifying the datatype of the already existing column of the table, and renaming the column of the table
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
Query to add column into the table:
alter table stud add column newcol_name datatype;
e.g alter table student add column city varchar;
This above query can add the column at the end of the table. But if you want to add the column at the specific position or at the middle of the table then you can use the AFTER clause in the query like:
alter table stud add column newcol_name datatype AFTER olcolumname;
e.g alter table student add column city varchar AFTER name;
To add a column at the first position we can use the FIRST clause in the query like:
alter table stud add column newcol_name datatype FIRST;
e.g alter table student add column city varchar FIRST;
Query to add Multiple columns to table:
alter table student add column gender char(6),
add column height double,
add column weight double ;
Query to remove/drop column from a table:
alter table tablename drop column col_name;
E.g alter table student from column marks;
Query to modify/change the column:
alter table tablename MODIFY column columnname datatype;
E.g alter table student MODIFY column phone varchar(10);
Query to rename the column:
alter table tablename RENAME to newTableName;;
E.g alter table student RENAME to stud;
- Truncate
Truncate command is used to delete all of the records or data from table table. Truncate deletes only the records not the structure of the table
Query to rename the column:
Truncate table tablename;
E.g Truncate table student;
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
- DML(Data Manipulation Language)
DML commands are used to perform operations on the
data of the table. It adds the new data into a table using the insert command then DML is used to perform changes to existing data using the update command and DML is used to delete data from the table using the delete command. Enhance your career prospects and gain in-demand skills. Enroll now in SQL Training in Pune and become a SQL master!
DML consists of the following commands:
- Insert
- update
- Delete
- Insert
Query to insert data in all of the columns of the table:
Let us consider the below stud table
mysql> desc stud;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| marks | int | YES | | NULL | |
| city | varchar(45) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
insert into table_name values(value1,value2…… );
E.g Insert into stud values(10,”yash”,78,”pune”);
Query to insert data into specific columns of the table:
insert into table_name(column1,column2…..) values(value1,value2…… );
E.g Insert into stud(id,city) values(11,”pune”);
Query to insert multiple rows using single insert statement
Insert into stud values(12,”ujjwal”,78,”pune”),
(13,”akash”,60,”mumbai”),
(14,”riya”,79,”chennai”),
(15,”arya”,88,”banglore”);
After inserting records, table looks like given below:
mysql> select * from stud;
+——+——-+——-+———-+
| id | name | marks | city |
+——+——-+——-+———-+
| 10 | yash | 78 | pune |
| 11 | NULL | NULL | pune |
| 12 | ujjwal | 78 | pune |
| 13 | akash | 60 | mumbai |
| 14 | riya | 79 | chennai |
| 15 | arya | 88 | banglore |
+——+——-+——-+———-+
Note: Master SQL with our comprehensive SQL course in Pune. Learn database querying, manipulation, and management to enhance your data skills. Join us today!
- Update
Update command is used to change the existing data of the table. At a time of updating data we need to specify new value and old value
Query to update specific record into table
update table_name set col_name=new_value where condition;
=> Updating city of id 14
E.g update stud set city =’nagpur’ where id=14;
Query to update multiple records of the table
update table_name set col_name=new_value where condition;
E.g update stud set city=”bhopal” where city=”pune” ;
Query to update ALL records of the table
update table_name set col_name=new_value;
E.g update stud set marks=90; #do not specifying where clause
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
Query to update multiple records within same column
=>Write a query that updates the name of ujjwal ,akash and riya
update stud set name=
case
when name=”ujjawal” then “ujjawal kumar”
when name=”akash” then “akash sharma”
when name=”riya” then “riya kumari”
else name
end
- Delete
Delete command is used to delete single or multiple records from the table
Query to delete single record from the table
delete from table_name where condition;
E.g delete from stud where id=12;
Query to delete multiple records from the table
delete from table_name where condition;
E.g delete from stud where city=”nagpur”;
Do visit our channel to learn more: Click Here
Author:-
Pooja Nandode-Bhavsar
Call the Trainer and Book your free demo class for SQL now!!!
© Copyright 2020 | SevenMentor Pvt Ltd.