SQL Essentials: A Journey from Fundamentals to Advanced Concepts

Published on 01 January 2019 | by Santosh Kumar Tripathi

What is a constraint?

Constraint can be used to specify the limit on the data type of table. A constraint can be specified while creating or altering the table statement.

How many types of constraints are in SQL Server?

NOT NULL Constraint: Ensures that a column cannot have a NULL value.
DEFAULT Constraint: Provides a default value for a column when none is specified.
UNIQUE Constraint: Ensures that all values in a column are different.
PRIMARY Key: Uniquely identified each row/record in a database table.
FOREIGN Key: Uniquely identified rows/records in any other database table.
CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
INDEX: Used to create and retrieve data from the database very quickly.

What is a primary key?

A primary key is a combination of fields that uniquely specify a row. This is a special kind of unique key, and it has an implicit, NOT NULL constraint. It means Primary key values cannot be NULL. They are frequently defined on an identity column.

What is a unique key?

You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table. A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

What are the differences between the Primary Key and the Unique key?

The basic differences between the Primary Key and the Unique key are as follows.

By default, the Primary Key will generate a Clustered Index, whereas the Unique Key will Generate a Non-Clustered Index.
Primary Key is a combination of Unique and NOT NULL Constraints so it can’t have duplicate values or any Null, whereas for Oracle UNIQUE Key can have any number of NULL whereas for SQL Server it can have only one NULL.
A table can have only one PK but it can have any number of UNIQUE keys.

What is a foreign key?

A foreign key (FK) is a column or combination of columns that are used to establish and enforce a link between the data in two tables.

In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

It is possible to allow NULL values in the foreign keys. if the FOREIGN KEY column is NULLABLE. The PRIMARY KEY column must be NOT NULL.

What is a CHECK Constraint?

CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For example, salary >= 15000 AND salary <= 100000.

Domain integrity ensures that all the data items in a column fall within a defined set of valid values.

What is Auto Increment?

Autoincrement keyword allows the user to create a unique number to be generated when a new record is inserted into the table. IDENTITY keyword can be used in SQL SERVER.

Mostly this keyword can be used whenever PRIMARY KEY is used.

What is a join?

This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.

What are the types of join?

Inner Join
Outer Join
- Left Join or Left Outer Join
- Right Join or Right Outer Join
- Full Join or Full Outer Join
Cross Join (Cartesian)
Self-Join

Explain each type of join.

Inner join - Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

Select * from Emp E JOIN Dept D on E.DepartmentID = D.DepartmentID

Left Join - The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

Select * from Emp E LEFT JOIN Dept D on E.DepartmentID = D.DepartmentID

Select * from Emp E LEFT JOIN Dept D on E.DepartmentID = D.DepartmentID WHERE D.DepartmentID IS NULL

Right Join -A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

Select * from Emp E RIGHT JOIN Dept D on E.DepartmentID = D.DepartmentID

Select * from Emp E RIGHT JOIN Dept D on E.DepartmentID = D.DepartmentID WHERE D.DepartmentID IS NULL

Select * from Emp E RIGHT JOIN Dept D on E.DepartmentID = D.DepartmentID WHERE D.DepartmentID IS NULL OR E.DepartmentID IS NULL

Full Join -A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Select * from Emp E FULL JOIN Dept D on E.DepartmentID = D.DepartmentID

Cross join- Cross joins return all rows from the left table. Each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products. THERE IS no “WHERE” or “ON” clause. If we use the “WHERE” clause it will behave like an inner join.

Select * from Emp E CROSS JOIN Gift G

Self-Join - Use a self-join when you want to create a result set that joins records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one instance of the table name. This table alias helps the query processor determine whether columns should present data from the right or left version of the table.

Select * from Emp E SELF JOIN EMP M onEmpID = M.ManegerID

What is normalization?

Normalization, organising data in a database.

Eliminating redundant data (for example, storing the same data in more than one table) Ensuring data dependencies make sense (only storing related data in a table).

What is Denormalization?

Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.

What is a View?

A view can be thought of as either a virtual table or a stored query (SELECT statement). A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. A view is used to do any or all of these functions:

Restrict a user to specific rows in a table.
Restrict a user to specific columns.
Join columns from multiple tables so that they look like a single table.
Aggregate information instead of supplying details. Like the sum of a column.
views can perform DML operations(Insert, Update, Delete).


    CREATE VIEW VW_STUDENT AS SELECT STD_ID, STD_FNAME, STD_LNAME FROM STUDENT

What is an Index?

index is an on-disk structure associated with a table or views.
It speeds the retrieval of rows from the table or view.
An index contains keys built from one or more columns in the table or view.
These keys are stored in a structure that allows SQL Server to find the row or rows associated with the key values quickly and efficiently.

What are all the different types of indexes?

Without an index, the table is called a heap.

Unique Index: This does not allow the field to have duplicate values. A unique index automatically applied to the primary key is defined.
Clustered Index: -A table can have only one clustered index. Clustered indexes physically rearrange the data that users insert into your tables. This means telling the database to store values close to one another on disk. It is nothing but dictionary-type data where actual data remains.
NonClustered Index: The nonclustered Index does not alter the physical order of the table and maintains the logical order of data. Each table can have 999 nonclustered indexes. Its Non-Clustered Index contains pointers to the data that is stored on the data page. It is a kind of index backside of the book where you see only the reference of a kind of data.

What is the impact of an index on Insert, Delete and Update?

Indexes improve performance for search in the way we have indexed. Simultaneously, they potentially hurt when data is modified due to overhead.

What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?

Microsoft Press Books and Book On-Line (BOL) refers to it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for the fast storing of data. Many times it is better to drop all indexes from the table and then do the bulk of inserts and restore those indexes after that.

Can I have a primary key without a clustered index and vice versa?

Primary Key is a logical construct, designed to uniquely identify each row in your table. That's why it has to be unique and non-null.

The clustering Index is a physical construct that will (initially) physically sort your data by the clustering key and arrange the SQL Server pages accordingly.

By default, A primary key constraint is backed by a clustered index.

You can have a table with a non-clustered primary key or a clustered table without a primary key. Technically both are possible. But it's sensible?

ALTER TABLE Student ADD CONSTRAINT PK_Student PRIMARY KEY NONCLUSTERED(StudentID);

What is a Cursor?

A cursor is a set of rows together with a pointer that identifies a current row. OR

The cursor is a database object to retrieves data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use a cursor when we need to update records in a database table in a singleton fashion means row by row.

What is the life cycle of a Cursor?

Declare Cursor: - A cursor is declared by defining the SQL statement that returns a result set.
Open: - A Cursor is opened and populated by executing the SQL statement defined by the cursor.
Fetch: - When the cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
Close: - After data manipulation, we should close the cursor explicitly.
Deallocate: - Finally, we need to delete the cursor definition and release all the system resources associated with the cursor..

What is the syntax for all these?

Syntax to Declare Cursor


    DECLARE cursor_name CURSOR
    [LOCAL | GLOBAL] --define cursor scope
    [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
    [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
    [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
    FOR select_statement --define SQL Select statement
    FOR UPDATE [col1,col2,...coln] --define columns that need to be updated

Syntax to Open Cursor


    OPEN [GLOBAL] cursor_name --by default it is local
    Syntax to Fetch Cursor
    FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
    FROM [GLOBAL] cursor_name
    INTO @Variable_name[1, 2, ..n]

Syntax to Close Cursor

CLOSE cursor_name --after closing it can be reopened

Syntax to Deallocate Cursor

DEALLOCATE cursor_name --after deallocation, it can't be reopened

Give an example of a cursor?


    SET NOCOUNT ON
    DECLARE @Id int, @name varchar(50), @salary int

    DECLARE cur_emp CURSOR
    STATIC FOR
    SELECT EmpID,EmpName,Salary from Employee

    OPEN cur_emp
    IF @@CURSOR_ROWS > 0
     BEGIN

     FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
     WHILE @@Fetch_status = 0
     BEGIN
         PRINT 'ID : '+convert(varchar(20),@Id)+',Name:'+@name+ ',Salary:'+convert(varchar(20),@salary)
         FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
     END
    END
    CLOSE cur_emp
    DEALLOCATE cur_emp
    SET NOCOUNT OFF