Create and Alter Views

CREATE VIEW

Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:

• To focus, simplify, and customize the perception each user has of the database.
• As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
• To provide a backward compatible interface to emulate a table whose schema has changed.

Syntax

---

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

Example:

Create View Customer_view
AS
Select customerid, customername,city from customers

The above statement will create a view named customer_view

To Alter view:

Example:

Alter view customer_view
As
Select customerid, customername,city,country from customers

Create and alter tables using T-SQL syntax (simple statements).

To create table:

Create table table_name (

Column_name1 data_type(size)

Column_name2 data_type(size)

)

Simple create table Example :

Create table customers(cno int not null, customer_name varchar(25) )

Create table by adding Primary key:

Create table customers(cno int not null constraint pk_cno Primary key, customer_name varchar(25))

Create table by adding identity property to a column :

Create table customers (cno int not null identity(1,1) , customer_name varcahr(25) )

Adding a column to an Existing table:

Syntax:

ALTER table table_name ADD column_name data_type(size)

Example:

ALTER table customers ADD city varcahr(25)

Changing the data type  of a column :

Syntax:

Alter table table_name Alter column column_name  changed_data_type(size)

Example:

Alter table customers Alter column customer_name nvarchar(25)

Dropping a column from an existing table :

Syntax:

ALTER table table_name DROP COLUMN column_name

Example:

ALTER table customers DROP COLUMN city

Adding primary key to an existing table :

Syntax:

ALTER table table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column_name)

Example:

ALTER table customers ADD CONSTRAINT pk_cno PRIMARY KEY(cno)

Adding Foreign Key to a column :

Syntax:

ALTER table table_name_2 ADD FOREIGN KEY(column_name) REFERENCES table_name_1(column_name)

Example:

ALTER table orders ADD FOREIGN KEY(cust_id) REFERENCES customers(cno)

Consider that there are two table customers(table 1) and orders (table 2) in table 2 (i.e orders) there is cust_id column to which we need to make as foreign key. Which can be done by executing the above query

How to drop a constraint :

Syntax:

ALTER table table_name DROP CONSTRAINT constraint_name

Example:

ALTER table customers DROP CONSTRAINT pk_cno

70-461 Exam objectives

70-461 Exam objectives

1. Create Database Objects (24%)
1. Create and alter tables using T-SQL syntax (simple statements).
2. Create and alter views (simple statements).
3. Design views.
4. Create and modify constraints (simple statements).
5. Create and alter DML triggers.
2. . Work with Data (27%)
1. Query data by using SELECT statements.
2. Implement sub-queries.
3. . Implement data types.
4. Implement aggregate queries.
5. Query and manage XML data.
3. Modify Data (24%)
1. Create and alter stored procedures (simple statements).
2. Modify data by using INSERT, UPDATE, and DELETE statements.
3. Combine datasets.
4. Work with functions.
4. Troubleshoot & Optimize (25%)
1. Optimize queries.
2. Manage transactions.
3. Evaluate the use of row-based operations vs. set-based operations.
4. Implement error handling.