view concept , Auto increment coloumn , SQL Function , create database command

 SQL Server: VIEW

Learn how to create, update, and drop VIEWS in SQL Server (Transact-SQL) with syntax and examples.

What is a VIEW in SQL Server?

A VIEW, in essence, is a virtual table that does not physically exist in SQL Server. Rather, it is created by a query joining one or more tables.

Create VIEW

Syntax

The syntax for the CREATE VIEW statement in SQL Server (Transact-SQL) is:

 

 

CREATE VIEW stuview AS SELECT student.name , student.city FROM student

 

CREATE VIEW stuview1 AS SELECT student.name , student.city FROM student where student.city='NGP'

Update VIEW

You can modify the definition of a VIEW in SQL Server without dropping it by using the ALTER VIEW Statement.

 ALTER  VIEW stuview1 AS SELECT student.name , student.city FROM student where student.city='pune'

Drop VIEW

Once a VIEW has been created in SQL Server, you can drop it with the DROP VIEW Statement.

 

DROP VIEW view_name;

Auto increment coloumn

 

CREATE TABLE Persons (

    Personid int IDENTITY(1,1) PRIMARY KEY,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Age int

);

SELECT @@VERSION;

ABS Description

In SQL Server (Transact-SQL), the ABS function returns the absolute value of a number.

 

SELECT ABS(-24);

Result: 24

 

SELECT ABS(-24.6);

Result: 24.6

 

 

Case Description

In SQL Server (Transact-SQL), the CASE statement has the functionality of an IF-THEN-ELSE statement. You can use the CASE statement within a SQL statement.

 

SELECT product_id,

CASE product_id

  WHEN 1 THEN 'TechOnTheNet.com'

  WHEN 2 THEN 'CheckYourMath.com'

  ELSE 'BigActivities.com'

END

FROM products;

 

Cast Description

In SQL Server (Transact-SQL), the CAST function converts an expression from one datatype to another datatype. If the conversion fails, the function will return an error. Otherwise, it will return the converted value.

 

SELECT CAST(14.85 AS int);

 

select SUM(CAST(amt as INT)) from inventa;

 

 

Ceiling Description

In SQL Server (Transact-SQL), the CEILING function returns the smallest integer value that is greater than or equal to a number.

 

SELECT CEILING(32.65);

Result: 33

 

 

 

 

 

SQL Server: CHARINDEX Function

This SQL Server tutorial explains how to use the CHARINDEX function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the CHARINDEX functions returns the location of a substring in a string. The search is NOT case-sensitive.

 

 

SELECT CHARINDEX('n', 'TechOnTheNet.com');

6

 

SQL Server: CONVERT Function

This SQL Server tutorial explains how to use the CONVERT function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the CONVERT function converts an expression from one datatype to another datatype. If the conversion fails, the function will return an error. Otherwise, it will return the converted value.

 

SELECT CONVERT(int, 14.85);

 

 

 

SELECT  'This is SQL Exercise, Practice and Solution';

 

SELECT 10 + 15

 

SELECT 10 + 15 - 5 * 2;

create database  mynrsolution4u