Views

        View is a virtual table. The columns in a view are columns from one or more physical tables in the database. View is similar to a table, but it is not stored in the database. It is a query stored as an object. User defined views are two types.

• Simple views
• Complex views

See also:

• SCHEMABINDING
• WITH CHECK OPTION
• Encrypting Views

Complex View

        If we create a view on more than one table then the view is complex view.

Example:

            Below link is having sample table creation:

                        Employee table

                        Department table

            View Creation:

CREATE VIEW Sample_Comp_View

AS

SELECT

      E.ID

      ,E.FirstName

      ,E.LastName

      ,E.Gender

      ,E.Designation

      ,E.ManagerID

      ,E.Dept_ID

      ,E.Salary

      ,E.Commission

      ,E.HireDate

FROM EMPLOYEE E

JOIN DEPT D ON E.DEPT_ID = D.DeptID

Simple View

       If we crate a view on a single table, then the view is simple view. We can perform DML operation on simple view. Those operations will reflect on the base table.

Syntax:

CREATE VIEW <View Name>

AS

SELECT Column-1,Column-2,….., Column-N

FROM <Table Name>

Example:

Sample table creation:

CREATE TABLE Sample_Tbl (ID int, Name varchar(20))

INSERT INTO Sample_Tbl VALUES (1,'A')

View Creation:

CREATE VIEW Sample_View

AS

SELECT ID, NAME FROM  Sample_Tbl

Commonly used String Functions

            Several functions are available in SQL server, in those commonly used functions are

• String functions
• SUBSTRING()
• LTRIM()
• RTRIM()
• LEFT()
• RIGHT()
• REPLACE()
• LOWER()
• UPPER()
• LEN()
• ASCII()
• CHAR()
• REVERSE()
• CHARINDEX()
• PATINDEX()
• STUFF()

SUBSTRING(): Using SUBSTRING function we can extract part of the string from given string.

            Syntax: SUBSTRING (Expression, start, length)

            Example: SELECT SUBSTRING('SQL server',1,3);

LTRIM(): It removes leading blank spaces of a string.

            Syntax: LTRIM (String)

            Example: SELECT LTRIM('   SQL SERVER')

RTRIM(): It removes trailing blank spaces of a string.

Syntax: RTRIM (String)

            Example: SELECT RTRIM('SQL SERVER   ')

LEFT(): Returns the left most characters of a string.

Syntax: LEFT (string, length)

Example: SELECT LEFT('SQL SERVER',3)

RIGHT(): Returns the right most characters of a string.

Syntax: RIGHT (string, length)

Example: SELECT RIGHT('SQL SERVER',6)

REPLACE(): Returns a string with all the instances of a substring replaced by another substring.

Syntax: REPLACE (find, replace, string)

Example:

SELECT REPLACE('This is SQL','SQL','structured query language')

LOWER(): Using this function we can change the string case to lower.

            Syntax: LOWER (String)

            Example: SELECT LOWER('SQL Server')

UPPER(): Using this function we can change the string case to upper.

            Syntax: UPPER (String)

            Example: SELECT UPPER('SQL Server')

LEN(): This function returns length of the string.

            Syntax: LEN (String)

            Example: SELECT LEN('SQL Server')

ASCII(): It returns the ASCII code value of the leftmost character of a character expression.

            Syntax: ASCII (String)

            Example: SELECT ASCII('SQL Server')

CHAR(): It converts an int ASCII code to a character.  

            Syntax: CHAR (Integer expression)

           

            Example: select CHAR(83)

REVERSE(): It returns a character expression in reverse order. 

            Syntax: REVERSE (String)

            Example: SELECT REVERSE('SQL Server')

CHARINDEX (): Char Index returns the first occurrence of a string or characters within another string.

Example: I want to find the first occurrence of the letter ‘r’ from the string “SQL Server”.

           

            SELECT CHARINDEX('R','SQL Server')

PATINDEX(): As a contrast PatIndex is used to search a pattern within an expression.

            Syntax: PATINDEX ( '%pattern%' , expression)

Here the first argument takes a pattern with wildcard characters like '%' (meaning any string) or '_' (meaning any character).

Example: SELECT PATINDEX('%BC%','ABCD')

STUFF(): Using this function we can replace specific length of characters with another set of characters.

Syntax: STUFF (character_expression1, start, length, character_expression2)

            Example: SELECT STUFF('SQL Server is useful',5,6,'Database')

Commonly used Date Functions

          Several functions are available in SQL server, in those commonly used functions are

Date time functions:

• GETDATE()
• DATEDIFF()
• DATEPART()
• DATENAME()
• YEAR()
• MONTH()
• DAY()

GETDATE(): The GETDATE() function extracts current date time from the server.

Example: Select GETDATE()

DATEDIFF(): We can find the date difference between two date time elements.

            Syntax: DATEDIFF(datepart, startdate, enddate)

            Example:

            select DATEDIFF (YY,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (MM,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (DD,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (HH,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (MM,'2011-01-01 00:02:25','2012-01-01 00:02:25');

DATEPART(): We can select a part of date or time.

            Syntax: DATEPART(datepart, date)

            Example:

            SELECT DATEPART(YYYY,'2012-01-01 02:05:00');

SELECT DATEPART(MM,'2012-01-01 02:05:00');

SELECT DATEPART(DD,'2012-01-01 02:05:00');

SELECT DATEPART(HH,'2012-01-01 02:05:00');

SELECT DATEPART(MM,'2012-01-01 02:05:00');

SELECT DATEPART(SECOND,'2012-01-01 02:05:05');

DATENAME(): We can find the date name from date element.

            Example:

SELECT DATENAME(MM,'2012-01-01 02:05:00');

SELECT DATENAME(DW,'2012-01-01 02:05:00');

YEAR(): We can extract year part from the date time element.

            Example: SELECT YEAR('2012-01-01 02:05:00');

MONTH(): We can extract month part from the date time element.

            Example: SELECT MONTH('2012-01-01 02:05:00');

DAY(): We can extract day part from the date time element.

            Example: SELECT DAY('2012-01-01 02:05:00');

TCL Commands

TCL (Transactional Control Language):-

            It is used to manage different transactions occurring within a database.

Commit: Saves work done in transactions.

Rollback                   :           Restores database to original state since the last

Commit command in transactions.

Save Transaction   :           Sets a save point within a transaction.

DCL Commands

DCL (Data Control Language):-

            It is used to create roles, permissions and referential integrity as well it is used to control access to database by securing it.

Grant              :           Gives user’s access privileges to database

Revoke          :           Withdraws user’s access privileges to database given with

the Grant command.

DML Commands

DML (Data Manipulation Language):-

            It is used to extract, Insert, Update and Delete the data in database.

Select                        :           Extract data from a table.

Insert                         :           Insets data into a table.

Update                      :           Updates existing data from table.

Delete                        :           Deletes data from table.

DDL Commands

DDL (Data Definition Language):-

            It is used to create, modify, truncate and drop the table (Database object) in database.

Create             :           Creates tables in the database

Alter                :           Alters table tables in the database

Drop                :           Deletes table in the database.

Truncate         :            Deletes all records from a table and resets table identity to

initial value.

Sample Dept Table with Data

CREATE TABLE DEPT

(

DeptID INT

,Name VARCHAR(30)

,Location VARCHAR(30)

);

Sample data insertion Script:

INSERT INTO DEPT VALUES

(10,'Design','Hyd'),

(20,'Sales','Bang'),

(30,'Accounts','Che'),

(40,'Maintenance','Pune'),

(50,'Engineering','Delhi');

See Also: Sample Employee Table creation with data