How to use Constructs in Batches for Conditional Execution: SQL

SQL Server allows you to use programming constructs in the batches for conditional execution of statements. For example, you need to retrieve data based on a condition. If the condition is not satisfied, a message should be displayed.

The SQL Server allows you to use the following constructs to control the flow of statements:

• IF…..ELSE statement
• CASE statement
• WHILE statement

Using the IF….ELSE Statement

You can use the IF…..ELSE statement for conditional execution of SQL statements. A particular action is performed when the given condition on evaluates to TRUE and another action is performed when the given condition evaluates to FALSE.
The syntax of IF….ELSE statement is:

IF Boolean_expression
{sql_statement | statement_block}
ELSE
{sql_statement | statement_block}]
Where,

• Boolean_expression specifies the condition that evaluates to either TRUE or FALSE. Sql_statement specifies a T-SQL statement.
• Statement_block is a collection of T-SQL statements.

The following example retrieves the pay rate of an employee from the EmployeePayHistory table to a variable, @Rate. The value of the @Rate variable is compared with the value 15 by using the <(less than) comparison operator. Based on the condition, different messages are displayed.

DECLARE @Rate money
SELECT @Rate = Rate FROM HumanResources.EmployeeHistory
WHERE EmployeeID = 23
IF @Rate < 15
PRINT ‘Review of the rate is required’
ELSE
BEGIN
PRINT ‘Review of the rate is not required’
PRINT ‘Rate =’
PRINT @Rate
END
GO

In the preceding example, the IF statement checks if the rate variable is storing a value less than 15. If the result is true, the PRINT statement displays “Review of the rate is required” else it displays “Review of the rate is not required”. Further, the next PRN statement displays the value of the rate.

Consider another example, where a check is performed to see the existence of the sales department. If the Sales department exists, all the details are displayed otherwise, a user-defined message is displayed.

IF EXISTS (SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’)
BEGIN
SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’
END
ELSE
PRINT ‘Department details not available’
GO

How to Perform Searching Data by Using a Full-Text Search

After the full-text index has been created on a table, you can query the table by using the full-text predicates. The full-text predicates are used to specify how the search string should be searched in the table.
Predicates can be used to enhance the search so that the result have so focus on the search criteria. Sql programming have a list of predicates to be used.

The following predicates can be used while performing the full-text search:

FREETEXT: When the search criteria are given, FREETEXT searches for any variation of a word or a group of words given in the search column. FREETEXT is used for the prefix searches.
Considering the previous scenario of the bike racing competition, you can use the FREETEXT predicate to obtain the desired output, as shown in the following statement:

SELECT Description FROM Production.ProductDescription WHERE FREETEXT (Description, ‘race winners’)

CONTAINS: This predicate is used in queries when you want to search for a specific phrase or for the exact match. It also searches for the proximity of words within a text. For example, you can use the following statement to search for the words ‘Ride’ near the word ‘Bike’ in the ProductDescription table:

SELECT Description FROM Production.ProductDescription
WHERE CONTAINS (Desctiption, ‘ride NEAR bike’)

Populating the Full-Text Index in Search: SQL Server

After creating the full-text index, you need to populate it with the data in the columns enabled for full-text support. The SQL Server full-text search engine populates the full-text index through a process called population. Population involves filling the index with words and their location in the data page. When a full-text index is created, it is populated by default. In addition, the SQL Server automatically updates the full-text index as the data is modified in the associated tables.

However, the SQL Server does not keep a list of changes made to the indexed data when the CHANGE_TRACKING option is off. This option is specified while creating the full-text index by using the CREATE FULLTEXT INDEX statement.

If you do not want the full-text index to be populated when it is created using the CREATE FULLTEXT INDEX statement, then you must specify NO POPULATION along with the CHANGE TRACKING OFF option. To populate the index, you need to execute the ALTER FULLTEXT INDEX command along with the START FULL, INCREMENTAL, or UPDATE POPULATION clause.

For example, to create an empty full-text index on the ProductDescription table, you can execute the following statement:

CREATE FULLTEXT INDEX ON Production. ProductionDescription (Description)
KEY INDEX PK_ProductDescription_ProductDescriptionID
WITH CHANGE_TRACKING OFF, NO POPULATION

To populate the index you need to execute the following statement:
ALTER FULLTEXT INDEX ON Production. ProductDescription START FULL POPULATION

The preceding statement will populate the full-text index created on the ProductDesctiption table.

Similar to regular SQL indexes, full-text indexes can also be updated automatically as the data is modified in the associated tables. This repopulation can be time-consuming and adversely affect the usage of resources of the database server during periods of high database activity. Therefore, it is better to schedule repopulation of full-text indexes during periods of low database activity. You can specify the following types of full-text index population methods to repopulate the index:

Full Population

You can use this method when you need to populate the full-text catalog or the full-text index for the first time. After that, you can the maintain the indexes by using change tracking or incremental populations.
During a full population of a full-text catalo, index entries are built for all the rows in all the tables covered by the catalog. If a full population is requested for a table, index entries are built for all the rows in that table.

Change Tracking-Based Population

The SQL Server maintains a record of the rows that have been modified in a table set up for full-text indexing. These changes are propagated to the full-text index.

Incremental Timestamp-Based Population

The incremental population method updates the full-text index with the data that has been changed since the last time the index was refreshed. For an incremental population refresh to work, the indexed table must have a column of the timestamp data type. If a table does not have a column of the timestamp data type, then only a full population refresh can be done.

Configuring Full-Text Search in SQL Server

The full-text query feature in the SQL Server enables users to search for a wide range of text in the SQL tables. Consider an example. The sales management team of AdventureWorks, Inc. makes frequent searches on the ProductDescription table to develop marketing strategies. The search is based on the data stored in the Description column of the table.

A bike racing competition is scheduled to begin in Texas. The sales manager of AdventureWorks wants to see the details of all the bikes that are related to racing, so that a marketing strategy can be designed to increase the sale of these bikes. Specifically, he wants a list of all the bikes that have the keyword ‘race winner’ in the description.

As the data is large, the search query takes a long time to retrieve data from the table. In this scenario, you can apply a full-text index on the Description column of the ProductDescription table to improve the speed of searching.

To retrieve the required details by using full-text search, you need to configure full-text search on the database. For this, you need to perform the following tasks:

• Enable the full-text search in the database.
• Create a full-text catalog.
• Create a unique index.
• Create a full-text index.
• Populate the full-text index.

Enabling the Full-Text Search in the Database

Before using the full text search feature of the SQL Server, you need to enable the database using the following statement:
USE AdventureWorks
GO
Sp_fulltext_database enable
GO

Creating a Full-Text Catalog

A full-text catalog serves as a container to store full-text indexes. After enabling the full text search, you need to create a full-text catalog. A full-text catalog is a container that contains full-text indexes. A full-text catalog may have multiple full-text indexes. You can create a full-text catalog by using the following command:
CREATE FULLTEXT CATALOG cat1 AS DEFAULT

Creating a Unique Index

After creating the full-text catalog, you need to identify a unique index on the table. This unique index will be mapped to the values in the full-text index. You can use an existing unique index defined on the table, or create a new one. For example, you can create a unique index on the Production.ProductDescription table, as shown in the following statement:

CREATE UNIQUE INDEX Ix_Desc ON Production.ProductDescription
(ProductDescriptionID)

Creating a Full-Text Index

After you have created the full-text catalog and a unique index, you can create a full-text index on the table. A full-text index stores information about significant words and their location within a given column. You can use this information to compute full-text queries that search for rows with particular words or combinations of words. Full-text indexes can be crated on the base tables but not on the views or the system tables.

There are certain words that are used often and may hinder a query. These words are called noise words and are excluded from the search string. For example, if you search string is “Who is the governor of California”, a full-text search will not look for words, such as ‘is’ and ‘the’. Some noise words are a, an, the, and are.

Based on the preceding scenario, you can create a full-text index on the ProductionDescription table. This index is based on the lx_desc index created earlier on the Description column of the table.
Note: You can also create full-text index in the Object Explorer window by right-clicking the table, on which you need to create the full-text index, and selecting Full-Text index-Define Full-Text Index.

Altering, Dropping and Renaming Views in SQL Server

In addition to creating view in sql server, database developer also need to manage them. Management of a view includes altering, dropping, or renaming described below.

Altering views

If you define a view with a SELECT * statement and then alter the structure of the underlying tables by adding columns, the new columns do not appear in the view. Similarly, when you select all the columns in a CREATE VIEW statement, the columns list is interpreted only when you first create the view. To add new columns in the view, you must alter the view.

You can modify a view without dropping it. This ensures that permissions on the view are not lost. You can modify a view without affecting its dependent objects. To modify a view, you need to use the ALTER VIEW statement. The syntax of the ALTER VIEW statement is:

ALTER VIEW view_name [ (column_name) ]
[WITH ENCRYPTION]
AS select_statement
[WITH CHECK OPTION]
Where,

• View_name specifies the view to be altered.
• Column_name specifies the name of the column(s) to be used in a view.
• WITH ENCRYPTION option enerypts the text of the view in the syscomments view.
• AS specifies the action to be performed by the view.
• Select_statement specifies the SELECT statement that defines a view
• WITH CHECK OPTION forces the data modification statements to follow the criteria given in the SELECT statement.

For example, you created a view to retrieve selected data from the Employee and EmployeeDepartmentHistory tables. You need to alter the view definition by including the LoginID attribute from the Employee table.

To modify the definition, you can write the following statement:

ALTER VIEW vwEmployeeDepData
AS
SELECT e.EmployeeID, LoginID, MaritalStatus, DepartmentID
FROM HumanResources.Employee e JOIN
HumanResources.EmployeeDepartmentHistory d
ON e.EmployeeID = d.employeeID

The preceding code alters the view definition by including the LoginID attribute from the Employee Table.

Dropping Views

You need to drop a view when it is no longer required. You can drop a view from a database by using the DROP VIEW statement. When a view is dropped, it has no effect on the underlying table(s). Dropping a view removes its definition and all the permissions assigned to it.

Further, if you query any view that references a dropped table, you receive an error message. Dropping a table that references a view does not drop the view automatically. You have to use the DROP VIEW statement explicitly.
The syntax of the DROP VIEW statement is:  DROP VIEW view_name

For example, you can use the following statement to remove the vwEmployeeDepData view:
DROP VIEW vwEmployeeDepData

The preceding statement will drop the vwEmployeeDepData view from the database.

You can drop multiple views with a single DROP VIEW statement. The names of the view that need to be dropped are separated by commas in the DROP VIEW statement

Renaming Views

At times, you might need to change the name of a view. You can rename a view without dropping it. This ensures that permissions on the view are not lost. A view can be renamed by using the sp_rename system stored procedure.
The syntax of the sp_rename procedure is:  Sp_rename old_viewname, new_viewname
Where,

• Old_viewname is the view that needs to be renamed.
• New_viewname is the new name of the view.

For example, you can use the following statement to rename the vwSal view:
Sp_rename vwSal, vwSalary

The preceding command renames the vwSal view as vwSalary.

While renaming views, you must ensure the following:

• The view must be in the current database.
• The new name for the view must follow the rules for identifiers.
• The view can only be renamed by its owner.
• The owner of the database can also rename the view.

How to Update Data in tables: SQL

Programmer need to modify the data in the database when the specifications of a customer, a client, a transaction, or any other data maintained by the organization undergo a change.

For example, if a client changes his address or if the quantity of a product ordered is changed, the required changes need to be made to the respective rows in the tables. You can use the UPDATE statement to make the changes. Updating ensures that the latest and correct information is available at any point of time. One column of a row is the smallest unit of an update.

You can update data in a table by using the UPDATE DML statement. The syntax of the UPDATE statement is:

UPDATE table_name SET column_name = value [, column_name = value]
[FROM table_name]
[WHERE condition]
Where,

• Table_name specifies the name of the table you have to modify.
• Column_name specifies the columns you have to modify in the specified table.
• Value specifies the value(s) with which you have to update the column(s) of the table. Some valid values include an expression, a column name, and a variable name. The DEFAULT and NULL keywords can also be supplied.
• FROM table_name specifies the table(s) that is used in the UPDATE statement.
• Condition specifies the rows that you have to update.

Guidelines for Updating Data

You need to consider the following guidelines while updating data:

• An update can be done on only one table at a time.
• If an update violates integrity constraints, then the entire update is rolled back.

The following statement updates the AddressLine2 attribute of AddressID 104

UPDATE Address
SET AddressLine2 = ‘Plaza Palace’
WHERE AddressID = 104

Consider another example where you need to update the Title of an employee, Lynn Tsoflias to ‘Sales Executive’, in the Employee table. To perform this task, you need to refer to the contact table to obtain the Contact ID. You can update the details by using the following statement:

UPDATE HumanResources.Employee SET Title = ‘Sales Executive’
FROM HumanResources.Employee e, Person.Contact c
WHERE e.contactID = c.ContactID
AND c.FirstName = ‘Lynn’ and c.LastName = ‘Tsoflias’

When the preceding command is executed, the Title will be changed to ‘Sales Executive’.

Delete data from database and related table.

How to Apply Constraints on Columns in SQL Programming?

In SQL Programming, consider an example where a user entered a duplicate value in the EmployeeID column of the Employee table. This would mean that two employees have same employee ID. This would further results in erroneous results when anybody queries the table. As a database developer, you can prevent this by enforcing data integrity on the table by using constraints.

Constraints define rules that must be followed to maintain consistency and correctness of data. A constraint can either be created while creating a table or can be added later. When a constraint is added after the table is created, it checks the existing data. If there is any violation, then the constraint is rejected.

A constraint can be created by using by using either of the following statements:

CREATE TABLE statement
ALTER TABLE statement
A constraint can be defined on a column while creating a table. It can be created with the CREATE TABLE statement. The syntax of adding a constraint at the time of table creation is:
CREATE TABLE table_name
(
Column_name CONSTRAINT constraint_name constraint_type [, CONSTRAINT
)
Where

• Column_name is the name of the column on which the constraint is to be defined.
• Constraint_name is the name of the constraint to be created and must follow the rules for the identifier.
• Constraint_type is the type of constraint to be added.

Constraints can be divided into the following types:

• Primary key constraint
• Unique constraint
• Foreign key constraint
• Check constraint
• Default constrain

Create and Manage Tables

Types of Data Integrity

What are the types of Data Integrity in SQL Programming?

If checks are not applied while defining and creating tables, the data stored in the tables can become redundant. For example, if you do not store the data about all the employees with complete address details, then the data would not be useful.

Similarly, if a database used by the Human Resource department stores employee contact details in two separate tables, the details of the employees might not match. This would results inconsistency and confusion.
Therefore, it is important to ensure that the data stored in tables is complete and consistent. The concept of maintaining consistency and completeness of data is called data integrity. Data integrity is enforced to ensure that the data in a database is accurate, consistent, and reliable. It is broadly classified into the following categories:

• Entity integrity: ensures that each row can be uniquely identified by an attribute called the primary key column contains unique value in all the rows. In addition, this column cannot be NULL. Consider a situation where there might be two candidates for an interview with the same name ‘Jack’. By enforcing entity integrity, the two candidates can be identified by using the unique code assigned to them. For example, one candidate can have the code 001 and the other candidate can be 002.
• Domain integrity: Ensures that only a valid range of values is stored in a column. It can be enforced by restricting the type of data, the range of values, and the format of the data. For example, you have a table called Branchoffice with a column called offices are located in ‘Berkeley’, ‘Boston’, ‘Chicago’, ‘Dallas’, ‘Munchen’, ‘New Jersey’, ‘New York’, ‘Paris’, and ‘Washington’. By enforcing domain integrity, you can ensure that only valid values (as per the list specified) are entered in the City column of the BranchOffice TABLE.
• Referential integrity: Ensures that the values of the foreign key match the value of the corresponding primary key. For example, if a bicycle has been ordered and an entry is to be made in the OrderDetail table, then that bicycle code should exist in the product table. This ensures that an order is placed only for the bicycle that is available.
• User-defined integrity: Refers to a set of rules specified by a user, which do not belong to the entity, domain, and referential integrity categories.

When creating tables, the SQL Server allows you to maintain integrity by:

• Applying constraints
• Applying rules
• Using user-defined types

Create and Manage tables

How to Create or Manage Tables using Query in SQL Programming

As a database developer, you need to create tables to store data. While creating tables in a relational database. You need to specify certain rules and constraints for columns that specify the kind of data to be stored. In addition, you need to specify the relationships between various tables.

If the table that you are creating needs to store a large volume of data, you can create partitioned table. This helps in improving the performance of the queries.

In addition to creating tables, you are responsible for managing tables. The management of tables involves modifying tables to add columns or to change the rules imposed on the table. It also involves deleting tables, when not required.

Creating a Table

In SQL Server, programmer can create a table by using the CREATE TABLE statement. The syntax of the CREATE TABLE statement is:

CREATE TABLE
 [database_name . [ scheme_name ] .] table_name
  ({ <column_definition> | <computed_column_definition> }
  [ <table_constraint> ] [ ,…n])
 [ ON { partition_scheme_name (partition_column_name ) |
Filegroup
 | “default” } ]
 [ {TEXTIMAGE_ON { filegroup | “default” } ]
[ ; ]
Where

• database_name specifies the name of the database where the table is created if you do not specify a database name, the table is created in the current database.
• Schema_name specifies the schema name where the new table belongs. Schema is a logical group of database objects in a database. Schemas help in improving manageability of objects in a database.
• table_name specifies the new table name. The table name can be a maximum of 128 characters.
• Column_name specifies the name of the column and must be unique in the table. It can be a maximum of 128 characters.
• Computed_column_definition specifies the expression, which produces the value of the computed column. A computed column does not exist physically in the memory but it is used to generate a computed value. For example, if you have the order quantity stored in one column and the unit price in another column, you can use compute_column_definition to find the total price of the products. The following SQL query displays the use of computed_column_definition: totalPrice AS OrderQty * UnitPrice
• table_constraint is an optional keyword that specifies the PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.
• Partition_scheme_name specifies the partition scheme name that defines the file groups on which the partition of a table is mapped. Ensure that the partition scheme exist within the database.
• partition_column_name specifies the column name on which a partitioned table will be partitioned.
• TEXTIME_ON { filegroup | “default”} are keywords that specify that the text, ntext, image, xml, varchar (max), nvarcar (max), varbinary (max), and CLR user-defined type columns are stored on the specified filegroup. If there are no large value columns in the table, TEXTIMAGE_ON is not allowed.

Consider the following example. The management of Adventure Works, Inc. needs to maintain the leave details of the employees. For this, you need to create a table, EmployeeLeave, in the HumanResources schema, with the following details.

EmployeeID int  NOT NULL
LeaveStartDate date  NOT NULL
LeaveEndDate date  NOT NULL
LeaveReason Varchar(100) NOT NULL
LeaveType char(2)  NOT NULL

You can use the following statement to create the table:
(
CREATE TABLE HumanResources.EmployeeLeave
LeaveStartDate datetime NOT NULL,
LeaveEndDate datetime NOT NULL,
LeaveReson varchar (100),
LeaveType char(2) NOT NULL
)

Guidelines to Create Tables

When creating tables, programmer need to consider some guidelines like column names within a table must be unique, but the same column name can be used in different tables within a database. The table name can be of maximum 128 characters.

Apply constraints on columns