一、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