Sql Interview Questions Part-1

1) What is Database ?

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.

Example: School Management Database, Bank Management Database.

2) What is DBMS ?

 Data Base Management System

 A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a  database rather than saving it in file systems.

 1)DBMS Contains set of standards or rules and they are converted into concepts or features.

 2)DBMS used to store ,manage(insert,update,delete),access the data.

 3)In DBMS data will be stored in the form of Tables.Every Table will contain two dimenstions(Rows & Columns).

3) What is RDBMS ?

  Relational Database Management System

  RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of   the table. It also provides relational operators to manipulate the data stored into the tables.

  1) In RDBMS can store large(Terabytes TB) amounts of data
  2) In RDBMS will support SQL(Structured Query Language)(/SEQUEL(Structured Engilsh Query Language) Language
  3) In RDBMS can join multiple tables
 4) In RDBMS Multiple Relations can be created between tables (one to one ,one to many, many to one, many to many)

4) What are the popular Database Management Systems in the IT Industry?

  Sql Server,Oracle,Sysbase etc...

5) What is a Table?

  A table is and object.It is representing an Entity.like an Employee,Student,Customer,Mobile,Car,Bike etc....

  If you want to create a Table(Entity).First we need to Identify the attributes of that Entity and those attributes should be the COLUMN of the Table(Entity)

  Note: A Table should never be puralized.

6) What are the different DML commands in SQL?

   DML (Data Manipulation Language)
        DML delas with data directly.It contains the commands are INSERT,UPDATE,DELETE

7) What are the different DCL(Data Control Language) commands in SQL?

   DCL also delas with data only but security of the data.It contains commands are GRANT,REVOKE,DENY

8) What are the different DQL commands in SQL?

   DQL delas with data but for retriving data from database,It contains commands are SELECT

9) What are the different DDL commands in SQL?

    DDL (Data Definition Language)
         DDL delas with the structure of the data but not with data directly.It contains commands are  CREATE,ALTER,DROP,TRUNCATE

10) What are the different TCL commands in SQL?

   TCL (Transaction Control Language)
       TCL delas with Transaction Management.It conatins commands are COMMIT,ROLLBACK,SAVE

11) What is schema? 

 A schema is a collection of database objects of a User.

12) Which TCP/IP port does SQL Server run?

   By default its 1433

13) List out the ACID properties and explain?

    -> Atomcity taken from greekword called atom.Atom means individual.Atomcity means all the commands in the transaction will execute ot none of them will execute.
    -> Transcation means set of sql commands will execute as a unit.
    -> Transcation will work with ACID properties
          a) A- Atomicity
          b) C- Consistancy
          c) I- Isolation
          d) D- Durability

14) Which are the most commonly used SQL joins?

    Most commonly used SQL joins are INNER JOIN and (left/right) OUTER JOIN.

15) How to create a Database in sql server?

   Create Database TestDB  --TestDB is Database Name

16) What are the authentication modes in SQL Server?

   Two types of authentication modes in SQL Server
      1) Windows authentication mode

      2) Sql server authentication mode

17) What is the most common trace flags used with SQL Server?

    The most common trace flags used with SQL Server are:

    Deadlock Information: 1204, 1205, 1222
    Network Database files: 1807
    Log Record for Connections: 4013
    Skip Startup Stored Procedures: 4022
    Disable Locking Hints: 8755
    Forces uniform extent allocations instead of mixed page allocations 1118 ? (SQL 2005 and 2008)

18 )What is CTE?

  A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.

19) What is Sql(SEQUEL)?

   SEQUEL(sql) stands for (Structured English Query Language)

   SEQUEL is a Language i.e Designed for Communicating with a RDBMS.Which is designed by IBM in 19974 by adopting mathematical prinicipales relational algebra and    calculas.

   SEQUEL known as SQL is controlled by today ANSI.Which gives the specifications for sql that has to be implemented by the various database vendars.

20) What is DataType ?

    Datatype is an attribute that specifies the type of a data that the column can hold like : integer data,character data,strings data etc-------- and so on

21) What is meant by oreder by clause ?

     This is used for arranging the data of a table in ascending or descending order with the in the output

      Select * from Employee order by salary

      Select * from Employee order by salary desc

22) What is Not null constraint ?

    if these constraint is imposed on any column.That column will not allow null values in to it.

     Create Table Employee(Eno in Not Null,Ename varchar(50)

23) What is check constraint ?

    If these constraint is imposed on a column.we can verify that the value of the column according to a specification.

     Create Table Employee(Eno int,Ename varchar(50),Salary Decimal(9,2) constraint salary-ck check(salary between 3000 and 25000))

24) What is Primary Key ?

    A primary key constraint will not allow nulls and duplicates because its combination of not null and unique constraint.while imposing a primary key constraint on a     table make sure the table has only one primary key constraint imposed on it.because we can never impose multiple primary key constraint on a table.where as if     required composite primary key is allowed.

     Create Table Employee(Eno int constraint Eno-pk primary key,Ename varchar(50),Salary Decimal(9,2))

25) What is  Foreign Key?

    Foregin key is used for establishing master detail relation ships between the tables so that we can verify the existince of a related row in another table.

     Create Table Employee(EmpNo int constraint EmpNo-Pk primary key,Ename varchar(50),DeptNo int,constraint DeptNo-fk foregin key(Deptno) references Department    (DeptNo))

26) What is Unique Key? 

   if a unique constraint is imposed on a cloumn that column will not allow duplicate values in to it.

    Create Table Employee(Eno int unique,Ename varchar(50),Salary Decimal(9,2))

27) What is Identity ?

    Identity is an optional that is available for generating sequential values into a column. i.e when we impose these identity on any column of a table from then on     words.it takes the responsibility of inserting to the values to that cloumn for every new record we insert in the table.

28) What is the usage of NVL function?

    NVL function is used to convert NULL value to a actual value

29) How to sort a column using a column alias ?

     Yes. You can use column alias in the ORDER BY clause for sorting.

30) What is the usage of DISTINCT keyword?

     DISTINCT keyword is used to fetched value is only a non-duplicate value.
About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

1 comment

  1. Hi Buddie,

    Hip Hip Hooray! I was always told that slightly slow in the head, a slow learner. Not anymore! It’s like you have my back. I can’t tell you how much I’ve learnt here and how easily! Thank you for blessing me with this effortlessly ingestible digestible content.

    I need to install security update KB2716429 on my Windows Server 2008R2 to update the SQL Server 2005 SP4 Windows Internal Database from version 2005.90.5000.0 to version 2005.90.5069.0.
    The SQL Server Instance is microsoft##ssee.
    The file that needs to be updated is C:\windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Binn\sqlservr.exe
    When I tried to install the patch, I got this message "This machine does not have a product that matches this installation package"

    THANK YOU!! This saved my butt today, I’m immensely grateful.

    Kind Regards,