Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

SQL Server constraints interview questions and answers

SQL Server interview questions on data integrity constraints

In this article I will share some frequently asked data integrity constraints related interview questions and answers in SQL server.

1. What is table constraint?

 Constraints are used to specify rules on data to ensure quality data stored in table.

2. What are the Different Types of constraints?

=> Primary key constraint.

=> Foreign Key constraint.

=> Unique Key constraint.

=> Not Null constraint.

=> Check constraint

=> Default constraint

3. What is default constraint?

The DEFAULT constraint provides a default value to a column when an INSERT statement doesn't explicitly assign a particular value.

More info.. http://codechef4u.com/post/2015/05/04/default-constraint-in-sql-server

4. Explain primary key and foreign key constraints?

Foreign key is a column or field in current table and primary key of another table, the foreign key points to another table using primary key of that table.

The PRIMARY KEY constraint uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

More info.. http://codechef4u.com/post/2015/04/20/how-to-create-primary-key-constraint

http://codechef4u.com/post/2015/05/03/foreign-key-constraints

5. Explain PRIMARY KEY, FOREIGN KEY and Unique key?

PRIMARY KEY:

The PRIMARY KEY uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

FOREIGN KEY:

Foreign key is a column or field in current table and primary key of another table, the foreign key points to another table using primary key of that table.

Unique key:

Unique kay enforces uniqueness or prevent duplicate data on columns.

Single null value is allowed with unique key.

6. What is UNIQUE KEY Constraint?

Unique constraints are rules that enforce uniqueness or prevent duplication on column data.

More info.. http://codechef4u.com/post/2015/05/05/unique-constraint-in-sql-server

7. What is CHECK Constraint?

CHECK constraints are rules used to limit values that are accepted by one or more columns.

For example, Products with unit price between INR 5000 to INR 100000 are allowed only. This prevents product unit price from being entered beyond the regular price range.

   The logical expression would be the following: UnitPrice>= 5000 AND UnitPrice <= 100000

More info.. http://codechef4u.com/post/2015/04/24/check-constraints-in-sql-server

8. What is NOT NULL Constraint?

 

9. What is the difference between primary key and unique key?

Primary Key:

Primary Key can't accept null values.  

By default, Primary key is clustered index.

We can have only one Primary key in a table

Unique Key:

Unique key can accept only one null value.

We can have more than one unique key in a table.

Unique key is a unique non-clustered index.

10. What is data integrity and list types of data intigrity?

Data integrity is the consistency and accuracy of data which is stored in database.

For data integrity RDBMS follows ACID rules, enforcing data integrity ensures the quality of the data in the database.

Types:

Entity Integrity (Unique constraints, primary key)

Domain Integrity (Check constraints, null constraints, default constraints, foreign key)

Referential Integrity (Foreign key and check constraints)

User-Defined Integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

11. Can you add constraint on table that has already some data?

Yes we can, you require to modify data in some cases for example if you want to add null constraint and

Table contains some null value in that’s case you have to add some data in column.

12. What is User-Defined Integrity?

User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

13. What is referential Integrity?

Referential integrity preserves the defined relationships between tables when records are entered or deleted. In SQl server, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints).

14. What is domain Integrity?

Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).

15. What is entity Integrity?

Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).

16. While creating foreign key you don’t want to check referential integrity, which keyword you have to use?

You required WITH NOCHECK in such case.

17. Can we apply unique constrains on multiple columns?

Yes that is possible.

18. How many primary keys are possible with single table?

Only one.

subquery interview questions and answers

In this article I will share some frequently asked subquery related interview questions and answers in SQL server.

Sub-query questions and answers:

1. What is subquery? Explain the Properties of a Subquery?

Select Query inside another query is a sub query, Select statement embedded in DML statement or nested in outer query.

You can use sub queries in SELECT, INSERT, UPDATE, DELETE whenever expressions are used.

Properties:

1.       Sub query cannot include compute or for browse clause.

2.       Must include sub query in parentheses.

3.       Subquery can include where ,group by, having clauses these are optional.

4.       Up to 32 levels sub query can be nested.

5.       You can use order by clause only when top clause included.

2. What are different types of subquery?

1.   Self contained sub query
2.   Co-related sub query

3. What is Self contained sub query?

Sub query is completely independent and do not require any input from outer query called self contained sub query.

4. Explain different types of Self contained sub query?

There are three types of self contained sub queries

1. Self contained scalar sub query.

2. Self contained multi valued query.

3. table-valued sub query.

 

5. What is self contained scalar sub query?

A self-contained sub query that returns single value called self-contained scalar sub query.

 More info http://codechef4u.com/post/2015/04/09/self-contained-sub-query

 

6. What is self contained multi valued query?

Self contained multi-valued sub query still return a single column but it may produce multiple values for the column.

 More info http://codechef4u.com/post/2015/04/10/self-contained-multi-valued-sub-query

7. What is table-valued sub query?

Sub query returns a whole table; multiple columns, multiple rows.

  More info http://codechef4u.com/post/2015/04/09/table-valued-sub-query

8. What is Co-related sub query?

Sub query depends on the outer query for its values. This means that the sub query is executed repeatedly, once for each row that might be selected by the outer query.

More info http://codechef4u.com/post/2015/04/09/correlated-sub-queries

9. How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?

Yes we can write using Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.


10. Exists predicate is possible with subquery? If yes explain?

T-SQL supports predicate like EXISTS with sub query, Exists accept sub query as input returns true if any rows exist in sub query else return false.

More info http://codechef4u.com/post/2015/04/09/exists-predicate-with-sub-query

 

11. What happens to null value with EXISTS using subquery?

If sub query returns null value, exists return true, I was surprised with result but its fact.
Exists statement resolves null value with True.

More info http://codechef4u.com/post/2015/04/09/exists-predicate-with-sub-query

 

12. Explain the subqueries with comparison operators?

Comparison operators can be used (like <, >, =, !> etc). Sub queries used with comparison operators must return a single value rather than a list to avoid error. Hence the nature of the database must be knows before executing such sub queries.

Example:

SELECT Productid FROM Products WHERE categoryId= (SELECT top 1 Id FROM Categories WHERE CategoryTiltle=’Electronics’ and DeletedDate is null)

13. Explain Subqueries with IN and NOT IN?

In this example subquery returns products from Products table where discount>1000. Using IN first all Products are selected and compared to each row of the sub-query.

Not in is opposite to IN (Vice versa)

IN example:

SELECT * FROM Products WHERE Id in(select productid from Discounts Where Discount_amt >1000); 

Not IN example:

SELECT * FROM Products WHERE Id not in(select productid from Discounts Where Discount_amt> 1000); 


trigger interview questions and answers

In this article I will share some frequently asked trigger related interview questions and answers in SQL server.

Frequently asked trigger interview questions: 

1.  Explain Trigger and trigger types?

A trigger is a special kind of a store procedure that executes in response to certain action performed on the table like insertion, deletion or updating of data

Basically there are two logical types

a. DDL triggers
b. DML triggers

DDL triggers you can categorize:

1.  Transact-SQL DDL Trigger
2. CLR DDL Trigger

DML trigger types are:

1. INSTEAD OF DML trigger
2. AFTER DML trigger

There is one special type trigger called logon trigger in sql server.

2. What are DDL Triggers and types of DDL trigger?

DDL triggers fire in response to an event happening on the server. However, DDL triggers do not fire in response to UPDATE, INSERT, or DELETE (DML) statements on a table or view. Instead, they fire in response to Data Definition Language (DDL) statements that start with the keywords CREATE, ALTER, and DROP.

DDL triggers you can categorize:

1.  Transact-SQL DDL Trigger
2. CLR DDL Trigger

3. What are DML triggers and types of DML triggers?

DDL triggers fire in response to UPDATE, INSERT, or DELETE (DML) statements on a table or view.

DML trigger types are:

1. INSTEAD OF DML trigger
2. AFTER DML trigger

4. What is Transact-SQL DDL Trigger?

A special type of Transact-SQL stored procedure that executes one or more Transact-SQL statements in response to a server-scoped or database-scoped event. For example, a DDL Trigger may fire if a statement such as ALTER SERVER CONFIGURATION is executed or if a table is deleted by using DROP TABLE.

5. What is CLR DDL Trigger?

Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.

Example:

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
   PRINT 'You must disable Trigger "safety" to drop or alter tables!'
   ROLLBACK;

 

6. What is AFTER DML trigger?

AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed successfully.

More … http://codechef4u.com/post/2015/04/15/after-trigger

7. What is INSTEAD OF DML trigger?

DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. Therefore, they can be used to perform error or value checking on one or more columns and the perform additional actions before insert, updating or deleting the row or rows.

More … http://codechef4u.com/post/2015/04/15/instead-of-trigger

8. What is logon trigger?

Special type of trigger fire when LOGON event of Sql Server is raised is called lo logon trigger. You can use this trigger to audit Sql server activities, control server sessions, such as to track login activity or limit the number of sessions for a specific login.

More … http://codechef4u.com/post/2015/04/16/logon-trigger

9. Why the Trigger Fires Multiple Times in Single Login?

Reason multiple SQL Server services are running as well as intelligence is turned on.

10. Is it possible to create trigger on views?

Yes, only InsteadOf trigger.

11. How many triggers are possible per table?

One InsteadOfTrigger and any After Triggers.

12. When multiple after triggers are attached to sql table, how to control the order of execution?

Using sp_settriggerorder procedure.

13. What is the difference between For Trigger and after trigger?

It’s same.