Before going through SQL Server Interview Questions and Answers Day – 2, I would like to recommend you to go through previous article :
SQL Server Interview Questions and Answers Day – 1
What is SQL command?
SQL are a set of instructions which is responsible to create, select, delete database as well database tables. In other words SQL commands are responsible to create and do all the manipulation in the database as well as database tables.
How many types of SQL commands?
SQL command has been divided into five major groups depending on their functionality.
i. Data Definition Language (DDL)
DDL are used to create, modify, and drop the structure of database as well as database objects like table, views, store procedures, etc.
Create, Alter, Drop, and Truncate keyword used with DDL commands.
ii. Data Manipulation Language (DML)
DML commands are used to insert, modify, and delete data from the database tables.
Insert, Update, and Delete keyword is used with DML commands.
iii. Data Query Language (DQL)
DQL is used to retrieve the data from the database tables.
Only select keyword is used with DQL command.
iv. Transaction Control Language (TCL)
TCL commands are used to provide a stable point for the user during changes in database .
There are following keyword is used with TCL.
- COMMIT keyword used to saves database transactions
- ROLLBACK used to undoes database transaction if any exception or error generated during modification.
- SAVEPOINT used to creates point within groups of transactions in which to ROLLBACK.
We can use Transaction by “Set Transaction Transaction_name“.
v. Data Control Language (DLC)
DCL keyword is used to implement security on the database object like table, views etc.
Grant and Revoke Command is used with DLC commands.
What is Data Administration Commands?
Data administration command allows us to perform audits and perform analyses on operations within the database.
There are following two data administration commands:
- START AUDIT
- STOP AUDIT
What are the SQL predefined syntax and keyword?
We can perform several tasks like create, modify, delete, truncate, delete etc. with database as well as database objects. To perform these tasks we have some default syntax and main keyword which are as follows:
- CREATE DATABASE: used to create database in SQL server.
//Syntax: CREATE DATABASE DatabaseName For ex.: Create database Code_Adda (Here Code-add is used as database name)
- ALTER DATABASE: used to modify database details.
//Syntax: Alter database Old_database_Name Modify Name = New_Database_Name For Ex.: Alter database Code_Adda Modify Name = CodeAdda (Here Code_Adda Modify with Codeadda)
- DROP DATABASE: used to drop created database
//Syntax: DROP DATABASE database_Name
- CREATE TABLE: used to create new table inside the database.
//Syntax: CREATE TABLE Tbl_Name(id int, name varchar(50),date_of_birth datetime) For ex.: create table Member(id int, Name varchar(50), dob datetime)
- ALTER TABLE: used to modify table details.
//Syntax: ALTER TABLE tbl_Name ADD Column_Name datatype For ex: ALTER TABLE Member add Address varchar(150)
- DELETE TABLE: used to delete data from table.
//Syntax: DELETE FROM tbl_name OR DELETE FROM tbl_Name WHERE id=@Id;
- DROP TABLE: used to drop table from database.
//Syntax: DROP TABLE tbl_Name
- CREATE INDEX: used to create index on table
//Syntax: CREATE INDEX index_Name ON tbl_name(Column_Name)
- DROP INDEX: used to drop index from table.
//Syntax: DROP INDEX tbl _Name.Column_Name
- INSERT INTO: used to insert data in table or tables.
//Syntax: INSERT INTO tbl_name (column1,column2) VALUES (value1, value2)
- SELECT: used to get data from table or tables.
//Syntax: Select * from tbl_Name Or Select Column1, column2 from tbl_Name
- UPDATE: used to update data in table or tables.
//Syntax: Update tbl_name set Column_Name=value, column2=value2
- CREATE PROCEDURE: used to create stored procedure.
//Syntax: create procedure proc_Name ( @parameter datatype ) As Begin Select * from tbl_name End
- ALTER PROCEDURE: used to modify existing stored procedure from database.
//Syntax: Alter Procdure proc_Name ( @parameter datatype ) AS Begin Select * from tbl_Name where column2=@Parameter End
- DROP PROCEDURE: used to delete stored procedure.
//Syntax: DROP procedure proc_Name
What is the difference between Truncate and Delete?
The difference between Truncate and Delete are followings: