This SQL Server tutorial, we will discuss on sql dml and ddl commands.
This is a part of SQL Server tutorial series:
- SQL Server stored procedure tutorial
- Difference between clustered and non clustered index SQL server
- Difference between primary key and unique key in SQL Server
- Difference between primary key and foreign key in SQL server
- Delete and create a new log file with minimum size in SQL Server
- Enable remote connections for SQL Server
- Sequence in SQL server
- SQL Server joins tutorial
- SQL Server Functions Tutorial
- How to Setting maximum memory in SQL server?
- Create Configure and Use SQL Server Database using Microsoft Azure
- How to create SQL Database in Microsoft Azure Portal
- Connect to Azure SQL database from management studio
SQL DML and DDL commands
1- SQL is divided into two parts: Data Manipulation Language (DML) and Data Definition Language (DDL).
2- The query and update commands form the DML part of SQL:
- SELECT – extracts data from a database
- UPDATE – updates data in a database
- DELETE – deletes data from a database
- INSERT INTO – inserts new data into a database
3- The DDL of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables.
The most important DDL statements in SQL are:
- CREATE DATABASE – creates a new database
- ALTER DATABASE – modifies a database
- CREATE TABLE – creates a new table
- ALTER TABLE – modifies a table
- DROP TABLE – deletes a table
- CREATE INDEX – creates an index (search key)
- DROP INDEX – deletes an index
4- SQL Select Statements: The SELECT statement is used to select data from a database.
SQL SELECT Syntax:
SELECT column_name(s) FROM table_name –select the content of the columns from the table.
SELECT * FROM table_name — select all the columns from the table.
(asterisk (*) is a quick way of selecting all columns.)
5- SELECT DISTINCT Statement: DISTINCT keyword can be used to return only distinct values.
SQL SELECT DISTINCT Syntax:
SELECT DISTINCT column_name(s)FROM table_name — select only the distinct column values from the table.
SQL WHERE Clause: WHERE clause is used to extract only those records which fulfill a specific criterion.
SQL WHERE Syntax:
SELECT column_name(s)FROM table_nameWHERE column_name operator value
6- Operators used in WHERE Clause:
With the WHERE clause, the following operators can be used:
- = Equal
- <> Not equal
- Greater than
- < Less than
- = Greater than or equal
- <= Less than or equal
- BETWEEN Between an inclusive range
- LIKE Search for a pattern
- IN To specify multiple possible values for a column
7- SQL AND & OR Operators:The AND & OR operators are used to filter records based on more than one condition.
The AND operator displays a record if both the first condition and the second condition are true.
Ex:SELECT * FROM Customers WHERE FirstName=’XX’AND LastName=’YY’
The OR operator displays a record if either the first condition or the second condition is true.
Ex:SELECT * FROM Customers WHERE FirstName=’xx’OR FirstName=’yy’
Combining AND & OR:
EX:SELECT * FROM Persons WHERE LastName=’yy’ AND (FirstName=’zz’ OR FirstName=’xx’)
8- SQL ORDER BY Keyword: ORDER BY keyword is used to sort the result-set by a specified column.
SQL ORDER BY Syntax:
SELECT column_name(s)FROM table_nameORDER BY column_name(s) ASC|DESC
The ORDER BY keyword sorts the records in ascending order by default.
Ex:SELECT * FROM Customers ORDER BY LastName
If we want to sort the records in a descending order,then we need to use the DESC keyword.
Ex:SELECT * FROM Customers ORDER BY LastName DESC
9- SQL INSERT Statement: The INSERT INTO statement is used to insert a new row in a table.
SQL INSERT INTO Syntax
The Insert Into statement can be written as follows:
The first form doesn’t specify the column names where the data will be inserted, only their values:
Ex:INSERT INTO table_name VALUES (value1, value2, value3,…)
The second form specifies both the column names and the values to be inserted:
Ex:INSERT INTO table_name (column1, column2, column3,…) VALUES (value1, value2, value3,…)
10- SQL UPDATE Statement: The UPDATE statement is used to update existing records in a table.
SQL UPDATE Syntax
Ex:UPDATE table_name SET column1=value, column2=value2,…WHERE some_column=some_value
11- SQL DELETE Statement: The DELETE statement is used to delete rows in a table.
SQL DELETE Syntax
Ex:DELETE FROM table_name WHERE some_column=some_value
12- Deleting All Rows: We can delete all rows in a table without deleting the table i.e. the table structure, attributes, and indexes.
Ex:DELETE FROM table_name
DELETE * FROM table_name
This SQL Server tutorial explains, SQL DML and DDL commands
I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com
Download SharePoint Online Tutorial PDF FREE!
Get update on Webinars, video tutorials, training courses etc.