SQL Server Interview Questions

1.   What is DATABASE?
Collection of interrelated data is called database.
Database is two types.
1. Database (On-line Transaction process database) :- running the business
2. DataWareHouse (On –Line Analytical Process Database):- Analyzing the business .

• Databases are created to keep details about day-to-day transactions.

2.What is DBMS (Database Management System)?
DBMS is a specification set of features.
Any software which implements specifications of DBMS is called DBMS software.
Ex: Sql Server, Oracle
Using DBMS software users can create and manage database.
USER
↓
DBMS Software
↓
Database
                                                                                                                                                                3.Different types of keys in RDBMS?

• Primary key:-

     Primary key is used to uniquely identify the records.

In the Below table Primary key is “Empid”

EX:

Empid	Salary	Name
100	2000	A
101	5000	B
102	4000	C

• Composite key:-

     If a Combination of uniquely identify the records present in the table, then those combination keys is called “composite key”.

Student ID(SID)	Course ID(CID)	Student name(SNAME)	Course name(CNAME)	Date of Completion(DOC)
100	10	A	C	26/10
100	11	A	C++	31/11
101	11	B	C	26/10

In the Above table SID, CID are called as Combination key

• Candidate key:-

     Attributes which are eligible for primary keys are called as candidate keys.

Vehicle no	Engine no	Vehicle Type	Price
AP09 2345	12345	NANO	1,00,000
AP09 9988	56789	TAVERA	3,25,000
AP12 7777	12567	SUMO	2,00,000

In the above table Vehicle no and Engine No is called as Candidate keys.
If we take one as primary key (vehicle no), other will automatically considered as Secondary key (Engine No).
A table may contain many candidate keys but only one primary key is present.
A primary key can be candidate key but a candidate key cannot be primary key.

• Foreign key:-

     Foreign key is used to establish relationship between two tables.
4.What are the versions of Sql Server ?
Sql Server 6.5
Sql Server 7.0
Sql server 8.0(2000)
Sql Server 9.0(2005)
Sql Server 10.0(2008)
5.What are the default databases installed in the server?
Master: The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
Model:-The model is essentially a template database used in the creation of any new user database created in the instance.
MSDb:- The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
TempDb:- The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
Once the server is closed again Temporary database is again recreated freshly.
6.What is a Query?
Instruction, command, request given to perform some operations.
7.What are the types of Sub languages in SQL SERVER?
Depends on operations performed by user, SQL is categorized into following Sub languages

1. DDL( Data Definition language)
2. DML(Data manipulation Language)
3. DRL(Data Retrieval Language)
4. TCL(Transaction Control language)
5. DCL(Data Control Language)

DDL:-
Data definition is also called Metadata
DDL Commands:
          CREATE: To Create Data Definition
          ALTER: To Modify Data Definition
          DROP: - To Delete Data Definition
          TRUNCATE: - Deletes all the data in the table. It makes table as Empty set.
Data definition can exist without data but data never exist without data definition.
DML Commands
          INSERT:- To insert Data/Record
          UPDATE:- To Modify data
          DELETE: – To delete Record(s).
In Sql Server 2008 one more command is introduced.
          MERGE: - It is Combination of insert & Update. It also called as UPSERT.
DRL Commands
          SELECT:- It is used to retrieve data from database
TCL Commands
          COMMIT:- To save Transactions
          ROLLBACK:-To cancel transactions
DCL Commands
          GRANT:- To give the permissions.
          REVOKE:- To Take back the permissions