SQL Interview Question - part 1

What is Schema? How can you provide security to schema?

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.

You can assign an user login permissions to a single schema so that the user can only access the objects they are authorized to access.  Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

What is the difference between rule and constraint?

Rules are used for backward compatibility.  One the most exclusive difference is that we a bind rules to a data types whereas constraints are bound only to columns.  So we can create our own data type with the help of Rules and get the input according to that.

Explain different types of constraints in SQL SERVER?
1) Entity Integrity:  Ensures each row in a table is a uniquely identifiable entity. You can apply entity integrity to a table by specifying a PRIMARY KEY constraint.

2) Referential Integrity:  Ensures the relationships between tables remain preserved as data is inserted, deleted, and modified. You can apply referential integrity using a FOREIGN KEY constraint.

3) Domain Integrity:  Ensures the data values inside a database follow defined rules for values, range, and format. A database can enforce these rules using a variety of techniques, including CHECK constraints, UNIQUE constraints, and DEFAULT constraints.

Unique: CREATE TABLE Products(

    ProductID int PRIMARY KEY,

    ProductName nvarchar (40) Constraint IX_ProductName UNIQUE)

Check:  CREATE TABLE Products_2(

    ProductID int PRIMARY KEY,

    UnitPrice money CHECK(UnitPrice > 0 AND UnitPrice < 100)   )

Default:  CREATE TABLE Product_3(

   ProductID int PRIMARY KEY,

    UnitPrice NULL DEFAULT (0) )

What is an @@IDENTITY?
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement of the Identity column.

Example: CREATE TABLE new_employees

        (

         id_num int IDENTITY(1,1),

         fname varchar (20),

         minit char(1),

         lname varchar(30)

        )

INSERT new_employees (fname, minit, lname)

VALUES ('Karin', 'F', 'Josephs')

SELECT @@IDENTITY AS 'Identity'

Output:  1

What is difference between user and login?
A "Login" grants the principal entry into the SERVER instance.

A "User" grants a login entry into a single DATABASE.

One "Login" can be associated with many users (one per database).

What is transaction?  What are the acid properties?

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

        --Please feel free to comment.