Sql Server Basic Interview Questions and Answers Part -1

Sql Server Basic Interview Questions and Answers

1) Does SQL support programming language features ?

   It is true that SQL is a language but it does not support programming as it is not a programming language, it is a command  language. We do not have conditional    statements in SQL like for loops or if..else, we only have commands which we can use to query, update , delete etc. data in the database. SQL allows us to    manipulate data in a database.

2) Which TCP/IP port does SQL Server run on? How can it be changed?

   SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties

3) Can SQL Servers linked to other servers like Oracle?

   Yes we can link with using linked servers in sql server

4) What is BulckCopy? When does it used?

    SqlBulkCopy lets you efficiently bulk load a SQL Server table with data from another source. The SqlBulkCopy class can be used to write data only to SQL Server     tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with     a IDataReader instance

     A single bulk copy operation

     Multiple bulk copy operations

     A bulk copy operation within a transaction   

5) What is Normalization ?

    Normalization means decomposing of large & complex data into simple and smaller tables.Before Normalization data can be in table format or it can be in ms excel     file or it can be raw data (data in text file)

6) What are the advantages in Normalization ?

     1) Redundency can be reduced .It means duplicate data or data repitation

     2) Inconsistency can be reduced.It means in correct data or the data which is not meaningful

7) What is Denormalization ?

    It is the reverse of Normalization.It contains data redundency or data repetation.

8) What is OLTP (Online Transation Processing)

    Data base means collection of tables.

    OLTP database contains normalized data.It is for correct data stored.It is used to maintain the data which is for 92 hundered days.In any dotnet project or     business  application OLTP database will be used.

9) What is OLAP (Online Analytical Processing)

     OLAP database contains de-normalized data.It is used to maintain historical data .Which is up to 10 to 15 years.It is used in dataware housing or analysis      services.data ware housing is in MSBI(Microsoft Business Intellegens)

10) What is First Normal Form ?

     All the cloumns in the table should be atomic.i.e The columns in the table should be single valued column repeating group or duplicate columns are not allowed in      the first normal form.

11) What is second Noraml Form ?

     It should be follow the rules of first normal form and all the columns in the table "should" functionality "depend" up on the primary key column in that      table.Parital dependencies are not allowed in second noraml from.

12) What is third Normal Form ?

    It should follow the rules of second normal form and all the columns in the table should depend up on primary key column in the table.Transistive dependency's are     not allowed in third normal form.Transistive dependency means monkey column will depends up on Non-key column in the table.

13) What is BCNF (Boyce code Normal Form)

       It should follow the rules of third normal form and column in the table should depend up on composite primary key in the table.

14) What is an Entity ?

      It is used to show the table having primary key.It can be called strong entity .

15) What is Weak Entity ?

    Weak entity to show the table with foregin key constraints.

16) What is Relation ?

    It is used to show relation between tables.i.e Relation between strong Entity's.

17) What is Weak Entity Relation ?

    It is used to show relation between strong and weak entity's

18) What is attribute ?

    Attribute means column.This symbol is used for single valued column.

19) What is Mutlti valued Attribute ?

     In Employee's details if skill's column is existing then it will be multivalued attribute.Multiple values will be given by using ' , ' will be used.

20) What is Dervied Attribute ?

     In Employee details if monthly salary and annual salary column's are existing then monthly salary  will depends on annual salary to show monthly salary column.

21) What is Recursive Relation ?

     It is used to display managers and subordinate relation.managers is also an employee will manage or Superwise other Employee's in the same department.

22) What is One to One Relationship ?

     One-to-One (1-1) relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only one           matching row. This relationship can be created using Primary key-Unique foreign key constraints.

23) What is One to Many Relationship ?

    The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This     relationship can be created using Primary key-Foreign key relationship.

24) What is Many to Many Relationship ?

   In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table,   called a junction table, whose primary key consists of the foreign keys from both table A and table B.
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 There,

    You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guide.

    I am attempting to connect to a server with SSMS 2017 using the [Active Directory - Password] authentication method. When I do, I get this:
    TITLE: Connect to Server
    Cannot connect to XXXX.
    A connection was successfully
    established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

    The certificate chain was issued by an authority that is not trusted
    I have also gone to Connection Properties, and unchecked the Encrypt Connection option. And tried the "Trust server certificate" option, and then I get "Login failed for user ''".
    Anyways great write up, your efforts are much appreciated.

    Many Thanks,
    Irene Hynes