SQL Server/T-SQL Tutorial/Query/Some
ANY and SOME
5> CREATE TABLE stores(
6> stor_id char(4) NOT NULL,
7> stor_name varchar(40) NULL,
8> stor_address varchar(40) NULL,
9> city varchar(20) NULL,
10> state char(2) NULL,
11> zip char(5) NULL
12> )
13> GO
1> insert stores values("1","B","567 Ave.","Tustin", "CA","92789")
2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745")
3> insert stores values("3","T","679 St.", "Portland", "OR","89076")
4> insert stores values("4","F","89 St.", "Fremont", "CA","90019")
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE discounts(
4> discounttype varchar(40) NOT NULL,
5> stor_id char(4) NULL ,
6> lowqty smallint NULL,
7> highqty smallint NULL,
8> discount dec(4,2) NOT NULL
9> )
10> GO
1>
2> insert discounts values("Initial Customer", NULL, NULL, NULL, 10.5)
3> insert discounts values("Volume Discount", NULL, 100, 1000, 6.7)
4> insert discounts values("Customer Discount", "8042", NULL, NULL, 5.0)
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3>
4> SELECT stor_id AS "Store ID", stor_name AS "Store Name"
5> FROM stores
6> WHERE stor_id != SOME
7> (SELECT stor_id FROM discounts WHERE stor_id IS NOT NULL)
8> GO
Store ID Store Name
-------- ----------------------------------------
1 B
2 N
3 T
4 F
(4 rows affected)
1>
2> drop table stores;
3> drop table discounts;
4> GO
less than SOME
2>
3> CREATE TABLE stores(
4> stor_id char(4) NOT NULL,
5> stor_name varchar(40) NULL,
6> stor_address varchar(40) NULL,
7> city varchar(20) NULL,
8> state char(2) NULL,
9> zip char(5) NULL
10> )
11> GO
1> insert stores values("1","B","567 Ave.","Tustin", "CA","92789")
2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745")
3> insert stores values("3","T","679 St.", "Portland", "OR","89076")
4> insert stores values("4","F","89 St.", "Fremont", "CA","90019")
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE discounts(
4> discounttype varchar(40) NOT NULL,
5> stor_id char(4) NULL ,
6> lowqty smallint NULL,
7> highqty smallint NULL,
8> discount dec(4,2) NOT NULL
9> )
10> GO
1>
2> insert discounts values("Initial Customer", NULL, NULL, NULL, 10.5)
3> insert discounts values("Volume Discount", NULL, 100, 1000, 6.7)
4> insert discounts values("Customer Discount", "8042", NULL, NULL, 5.0)
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> SELECT stor_id AS "Store ID", stor_name AS "Store Name"
2> FROM stores
3> WHERE stor_id < SOME
4> (SELECT stor_id FROM discounts WHERE stor_id IS NOT NULL)
5> GO
Store ID Store Name
-------- ----------------------------------------
1 B
2 N
3 T
4 F
(4 rows affected)
1>
2> drop table stores;
3> drop table discounts;
4> GO
1>