Designing Advanced Database Objects - Views

A view is simple a SELECT statement that has been given a name and stored in a database. The main advantage of view is that once it's created it acts like a table for any other SELECT statements that you wish to right.

Syntax

 1. Create View

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

2. Update a View

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

3. Drop a View

DROP VIEW view_name

view always shows up-to-date data. The database engine always recreate the data using view's SQL statement, every time a  user queries a view.

Create view 

 

Queries from view

Drop view

SQL Server 2008 Tools - Log File Viewer

The log File Viewer is launched from within SQL Server Management Studio. To open it , follow these steps.

1. Expand the Management node in Object Explorer.
2. Expand SQL Server Logs.
3. Right-click on a log and select "View SQL Server Log."

One of the benefits of the Log File Viewer is that it allows consolidation of practically all the logs the DBAs are most interested in. SQL Server logs, SQL Agent logs, and Operating System logs can be opened
in the same window for easy correlation of system and SQL Server events.

Configuring Permissions on Database Objects

Click Start, click Run, in the Open box, type %SystemRoot%\system32\compmgmt.msc /s, and then click OK to open the Computer Management program.

Under System Tools, expand Local Users and Groups, right-click Users, and then click New User.

In the User name box type Any name you want..

In the Password and Confirm password box, type a strong password, and then click Create to create a new local Windows user.

Writing Transact-SQL (Part 4)

Read the data in a Table

We use SELECT statement  to read the data in a table. The SELECT statement is one of the most important T-SQL statement.

• Type and execute the following statements to read the data in the Products table.

          SELECT ProductID, ProductName, Price, ProductDescription
          FROM dbo.Products
           GO

• You can use an asterisk to select all the columns in the table. This is often used in ad hoc queries. You should provide the column list in you permanent code so that the statement will return the predicted columns, even if a new column is added to the table later.

         SELECT * FROM Products
         GO

• You can omit columns that you do not want to return. The columns will be returned in the order that they are listed.

         SELECT ProductName, Price
         FROM dbo.Products
         GO

• Use a WHERE clause to limit the rows that are returned to the user.

           SELECT ProductID, ProductName, Price, ProductDescription
           FROM dbo.Products
           WHERE ProductID < 60
           GO

• You can work with the values in the columns as they are returned. The following example performs a mathematical operation on the Price column. Columns that have been changed in this way will not have a name unless you provide one by using the AS keyword.

          SELECT ProductName, Price * 1.07 AS CustomerPays
          FROM dbo.Products
          GO

Writing Transact-SQL (Part 3)

Following query to change the ProductName of the second product from Screwdriver, to Flat Head Screwdriver.

Writing Transact-SQL (Part 2)

Insert data in a Table

Basic Syntax is : INSERT <table name> (<column list>) Values (<value list>)

To insert data into a table

Execute the following statement to insert a row into the Products table that was created in the previous task. This is the basic syntax.

-- Standard syntax
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
    VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
GO

The following statement shows how you can change the order in which the parameters are provided by switching the placement of the ProductID and ProductName in both the field list (in parentheses) and in the values list.

-- Changing the order of the columns
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
    VALUES ('Screwdriver', 50, 3.17, 'Flat head')
GO

The following statement demonstrates that the names of the columns are optional, as long as the values are listed in the correct order. This syntax is common but is not recommended because it might be harder for others to understand your code. NULL is specified for the Price column because the price for this product is not yet known.

-- Skipping the column list, but keeping the values in order
INSERT dbo.Products
    VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')
GO

The schema name is optional as long as you are accessing and changing a table in your default schema. Because the ProductDescription column allows null values and no value is being provided, the ProductDescription column name and value can be dropped from the statement completely.

-- Dropping the optional dbo and dropping the ProductDescription column
INSERT Products (ProductID, ProductName, Price)
    VALUES (3000, '3mm Bracket', .52)
GO

Writing Transact-SQL (Part 1)

In this tutorial serious show you how to create a database,create a table , insert data,update data,read data delete data and then delete the table. And also you will learn to create views and stored procedures and configure a user to the database and the data.

T-SQL statements can be written and submitted to the Database Engine in the following ways.

• By using SQL Server Management Studio
• By using the sqlcmd utility
• By connecting form an application that you create

To Create a database

You can use the following query to create the database.

Switch the query Editor connection to the TestDatabase database.

For the you can use following query.

USE TestDatabase
GO


Create a table
You can use following query to create a simple table named Products. The Products table consists of 4 columns. The ProductID column is the primary key of the table. This query contains an optional element (dbo.) called a schema. The schema is the database object that owns the table. If you are an administrator , dbo is the default schema. dbo stands for database owner.