JAVA下的SQL_System的建立

USE pubs
GO
SELECT notes
FROM titles
WHERE notes LIKE '50%% off when 100 or more copies are purchased' 
   ESCAPE '%'
GO

<test_expression> BETWEEN <begin_expression> AND <end_expression>
下面的语句用来查找书籍的价格在$20到$30之间的所有记录。
SELECT   price, title
FROM     titles
WHERE    price BETWEEN 20.00 AND 30.00
GO

SELECT   price, title
FROM     titles
WHERE    price NOT BETWEEN 10.00 AND 40.00
GO

下面的语句用来查找作者姓氏在“del Castillo”到“Hunter”之间的所有记录：
SELECT   au_lname
FROM     authors
WHERE    au_lname BETWEEN 'del Castillo' AND 'Hunter'
GO

IS NULL关键字使用一个查询条件来查找空白行。在下例中，将查找书籍名称中advance没有内容的所有记录。
SELECT   title , advance
FROM     titles
WHERE    advance IS NULL
GO

IS NOT NULL的用法与IS NULL相同，但是意义相反。
SELECT   title , advance
FROM     titles
WHERE    advance IS NOT NULL
GO

IN关键字用来进行子查询，它的语法：
<test_expression> IN (<subquery>)
或者
<test_expression> IN (<list of values>)
实例代码：
SELECT   job_id , job_desc
FROM     jobs
WHERE    job_desc IN ('Operations Manager', 
                      'Marketing Manager',
                      'Designer')
GO

下面的实例应用了两次IN关键字：
SELECT   fname, lname , job_id --uter query
FROM     employee
WHERE    job_id IN ( SELECT job_id    --Inner query, or subquery
                     FROM   jobs
                     WHERE  job_desc IN ('Operations Manager',
                                         'Marketing Manager',
                                         'Designer'))
GO

IN关键字往往可以加上NOT操作。实例如下：
FROM     publishers
WHERE    state NOT IN ('MA',
                                          'NY')
GO

下列语句用来查看ANSI nulls的状态：
sp_dboption "pubs", "ANSI nulls"
GO

如果 ANSI nulls被设置成OFF，则可以通过下面的语句改变设置。
sp_dboption 'pubs', 'ANSI nulls', TRUE
GO

EXISTS (<subquery>)
实例代码：
SELECT   au_fname, au_lname
FROM     authors 
WHERE    EXISTS (SELECT au_id
                 FROM   titleauthor
                 WHERE  titleauthor.au_id = authors.au_id)
GO