SQL语言——高级步骤

SQL语言——高级方法

一、GROUP BY

原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
写出此查询语句

select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course

如果score>60,sign(score-60)=1;

       score==60,sign(score-60)=0;

       score<60,sign(score-60)=-1;

一、create a view

USE Northwind // 使用Northwind这个数据库

GO

CREATE VIEW vwSample

As

SELECT CustomerID, CompanyName, ContactName FROMCUSTOMERS

GO

use a view

SELECT * from vwSample

drop a view

DROP VIEW vwSample

Creating Views with theSCHEMABIND Option

使用了这个选项之后，view里面引用到的表的shema就不能被改变了。

Creating a view withthe SCHEMABINDING option locks the tables being referred by the view andprevents any changes that may change the table schema.

Notice two importantpoints while creating a view with SCHEMABINDING OPTION:

• The objects should be referred to by their owner names [two part name].
   
• SELECT * is not permitted.

Here’s an example fora view with the SCHEMABIND OPTION:

CREATE VIEW vwSample

With SCHEMABINDING

As

SELECT

          CustomerID,

          CompanyName,

          ContactName

FROM DBO.CUSTOMERS -- Two part name [ownername.objectname]

GO