SQL Queries are categorized as follows :
Data Definition Language (DDL):
DDL is used to define the data structure and create of the components of our database like tables.
Example: create database, create table, create store procedure, create function.
create table: we create the columns of the table with data type
CREATE TABLE Account (
ID int IDENTITY(1,1) NOT NULL,
DepositAmount float NOT NULL,
WithdrawAmount float NULL,
TransactionDate datetime NULL,
MerchantName nvarchar(50) NOT NULL,
MerchantAddress nvarchar(50) NULL,
MerchantPhone nvarchar(50) NULL,
CustomerName nvarchar(50) NULL,
CustomerAddress nvarchar(50) NULL,
CustomerPhone nvarchar(50) NULL,
LastUpdateDate datetime] NULL,
LastUpdateBy nvarchar(50) NULL
)
Data Manipulation language (DML)
DML is used to modify the data that is stored inside the tables
example: insert, update, delete.
Insert into table: we want to insert data to the created table
example:
INSERT INTO [dbo].[Account] (
[DepositAmount]
,[WithdrawAmount]
,[TransactionDate]
,[MerchantName]
,[MerchantAddress]
,[MerchantPhone]
,[CustomerName]
,[CustomerAddress]
,[CustomerPhone]
,[LastUpdateDate]
,[LastUpdateBy])
VALUES (
600,
25,
1/1/2021,
'Sears',
'100 Georgia Ave',
'202-655-4566',
'John Doe',
'2 test drive',
'202-655-5555',
1/12/2021,
'admin'
)
Update table: you want to modify the data that you already inserted
Example:
UPDATE [dbo].[Account]
SET [DepositAmount] = 600
,[WithdrawAmount] = 100
,[TransactionDate] = 1/2/2021
,[MerchantName] = 'Best buy'
,[MerchantAddress] = '1 test st'
,[MerchantPhone] = '252-545-6454'
,[CustomerName] = 'Fred'
,[CustomerAddress] = '2 test drive'
,[CustomerPhone] = '252-656-2666'
,[LastUpdateDate] = 2/2/2021
,[LastUpdateBy] = 'Jimmy'
WHERE id = 1
delete table
that is to completely remove the table rows from data base. We can also specify the row we want to delete as follows:
delete Account
where id = 2
If we don't specify the where condition, then all the rows will be completely wipe out.
truncate table
This is the time when you want to delete all the rows of the table completely resetting it as if the table is recreated. One way to check this is once you run this command, if you have an auto incremented column like ID, this command will reset it back to 1.
truncate table Account
drop table
This command will delete this table from database
drop table Account
Data Control Language (DCL)
used to provide access/permission to database