Home > DeveloperSection > Articles > Basic SQL Query

Basic SQL Query


Database Database 
Ratings:
0 Comment(s)
 3513  View(s)
Rate this:

Basic SQL Query

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.

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

 

 

                                                                       

 

 

 

 


Don't want to miss updates? Please click the below button!

Follow MindStick