articles

Home / DeveloperSection / Articles / Basic SQL Query

Basic SQL Query

Anonymous User7312 04-Jul-2011

There are lots of SQL commands which are used in database. Here we will try to


explore all the SQL commands with help of SQL Query.

Data Definition Language (DDL) Command:

· CREATE: Create command is used for creating database structure.

 Example: 
CREATE TABLE CREATE_COMMAND_EXAMPLE
(
-- CREATE COLUMN NAME OF TABLE  <CREATE_COMMAND_EXAMPLE>
ID INT,
NAME  VARCHAR(20),
USER_ADDRESS VARCHAR(20),
DOB DATE
)


·  ALTER: Alter command is used for altering database structure.

Example:
  ----// ADD ADDITIONAL COLUMN NAME MOBNO
ALTER TABLE CREATE_COMMAND_EXAMPLE ADD  MOBNO VARCHAR(10)

·DROP: Drop command is used for delete object from database.used for delete


object from database.

Example:            
  ----// DROP TABLE FROM DATABASE
DROP TABLE CREATE_COMMAND_EXAMPLE

· TRUNCATE: Truncate command is used for remove all record form tables,

including all spaces allocated for the records are removed.

 Example:
  ------// TRUNCATE ALL VALUES FROM TABLE
TRUNCATE TABLE CREATE_COMMAND_EXAMPLE

· RENAME: Rename command are used for rename object name.

 Example:

----// HERE USED STORED PROCEDURE TO RENAME THE TABLE
EXEC SP_RENAME 'CREATE_COMMAND_EXAMPLE' ,'RENAME_COMMAND_TABLE'

·   COMMNET: Comment command are used for add comments to data dictionary.

  Example:

--THIS IS COMMENT COMMAND USED IN SQL SERVER

(Double hyphen used for comment command )

 Data Manipulation Language(DML) Command: 

·  SELECT: Select command is used for select data from database table.

Example:

  ----- SELECT ALL DATA FROM RENAME_COMMAND_TABLE TABLE
SELECT * FROM RENAME_COMMAND_TABLE    

· INSERT: Insert command is used for insert data into database table.

  Example:

   --- INSERT DATA INTO TABLE RENAME_COMMAND_TABLE
INSERT INTO RENAME_COMMAND_TABLE VALUES ('101','ARUN SINGH','ALLAHABAD','6 JULY 1989')                                                  

· DELETE:  Delete command is used for deletes all records from a table, the space

for the records remain.

Example:

--- DELETE ALL DATA FROM TABLE RENAME_COMMAND_TABLE
DELETE FROM RENAME_COMMAND_TABLE

 

·  UPDATE:  Update command is used for updates existing data into database

table.

   Example:

-- UPDATE COLUMN DATA EITHER USE OF WHERE CLAUSE OR WITHOUT ANY CONDITION
UPDATE RENAME_COMMAND_TABLE SET ID = '102'               

·  MERGE:  Merge command is for merge data within database table perform

insert, update command.

  Example:
---- HERE WE WANT TO DELETE THAT  RECORD WHERE STUDENT HAVE MORE THAN 360 MARK FROM STUDENT_MARK
 
MERGE STUDENT_MARKS AS STMA
USING (SELECT * FROM STUDENT_DETAIL) AS STDE
ON STMA.[SID]= STDE.[ID]
WHEN MATCHED AND STMA.MARK > 360 THEN DELETE;

·  LOCKTABLE: Lock table command is used for concurrency control within database.

Example:
--- SELECT TABLE STUDENT_MARKS WITH LOCK
 SELECT * FROM STUDENT_MARKS WITH (HOLDLOCK)

Data Control Language (DCL) Command:

·  GRANT: Grant command is used for gives user’s access privileges to database.

Example:
   --- SYNTAX
--- GRANT EXEC ON <OBJECT NAME LIKE STORED PROCEDURE> TO <USER NAME>
GRANT EXEC ON USP_CALTABLETEST TO 'ARUN'

 

· REVOKE: Revoke command is used for withdraw access privileges given with the GRANT command.

Example:
---- use master database to revoke
use master
go
REVOKE VIEW SERVER STATE FROM sa

                                                                 


Updated 04-Mar-2020
I am a content writter !

Leave Comment

Comments

Liked By