Advanced SQL Programming Fall 2002
In-class examples: Top n values; tempdb; control-of-flow
1> use pubs2
2> go
/* */
/* Find top three prices: */
/* */
/* Using nested subqueries: */
/* */
1> select distinct price
2> from titles
3> where price >=
4> (select max(price)
5> from titles
6> where price < (select max(price)
7> from titles
8> where price <
9> (select max(price) from titles)))
10> go
price
------------------------
20.95
21.59
22.95
(3 rows affected)
/* */
/* Check results (OK to do it this way for small table only!) */
/* */
1> select distinct price
2> from titles
3> order by price desc
4> go
price
------------------------
22.95
21.59
20.95
20.00
19.99
14.99
11.95
10.95
7.99
7.00
2.99
NULL
(12 rows affected)
/* */
/* Using self-joins: */
/* */
1>
2> select max(p.1.price), max(p2.price), max(p3.price)
3> from titles p1, titles p2, titles p3
4> where p1.price > p2.price
5> and p2.price > p3.price
6> go
------------------------ ------------------------ ------------------------
22.95 21.59 20.95
(1 row affected)
/* */
/* "Dummy" table (as in Celko text) cannot be used in Sybase - */
/* interpreted as keyword. Second example shows that Sybase */
/* cannot use the SQL-92 construct (select...) as column. */
/* */
1> select max(price) from titles (select price from titles) MyPrice
2> from Dummy
3> go
Msg 156, Level 15, State 1:
Line 2:
Incorrect syntax near the keyword 'Dummy'.
1> select (select price from titles) as MyPrice
2> from Dummy
3> go
Msg 156, Level 15, State 1:
Line 2:
Incorrect syntax near the keyword 'Dummy'.
1> select (select price from titles) as MyPrice
2> from MyTable
3> go
Msg 208, Level 16, State 1:
Line 1:
MyTable not found. Specify owner.objectname or use sp_help to check whether the
object exists (sp_help may produce lots of output).
1> quit
============================================================================
/* Temporary tables: Create in own database, start name with # */
/* or create in tempdb, where tables stay until system restarted.*/
/* */
1> use tempdb
2> go
1> sp_tables
2> go
table_qualifier table_owner
table_name table_type
remarks
------------------------------ ------------------------------
------------------------------ ------------
----------------------------------------------------------------
tempdb dbo
sysalternates SYSTEM TABLE
NULL
tempdb dbo
sysattributes SYSTEM TABLE
.........................etc.............................................
(22 rows affected)
(return status = 0)
===============================================================================
/* Control-of-flow examples */
1> use pubs2
2> go
/* If */
/* Print a message if a certain zipcode is found: */
/* */
1> if exists (select postalcode from authors
2> where postalcode = "94705")
3> print "Berkeley author"
4> go
Berkeley author
/* If ... else (with begin ... end to allow more than one */
/* statement after if. */
/* Print a message if max. book price is greater than some */
/* value; otherwise list all prices that are less than max. */
1> if (select max(price) from titles) > 20
2> begin
3> select max(price) MaxPrice from titles
4> print "This is the highest price."
5> end
6> else
7> select distinct price from titles
8> having price < max(price)
9> go
(0 rows affected)
MaxPrice
------------------------
22.95
(1 row affected)
This is the highest price.
/* Change initial value to show the "else" condition: */
1> if (select max(price) from titles) > 25
2> begin
3> select max(price) MaxPrice from titles
4> print "This is the highest price."
5> end
6> else
7> select distinct price from titles
8> having price < max(price)
9> go
(0 rows affected)
price
------------------------
2.99
7.00
7.99
10.95
11.95
14.99
19.99
20.00
20.95
21.59
(10 rows affected)
/* Another example using if, with tables from my database. */
/* */
use evelyn
go
/* Current relevant data in tblBudget and tblSpending */
1> select * from
2> tblBudget, tblSpending
3> where tblBudget.regionID = tblSpending.regionID
4> go
regionID budgetAmt regionID spendingAmt
-------- ------------------------ -------- ------------------------
1 5,000.00 1 100.00
1 0.00 1 100.00
1 5,000.00 1 100.00
1 0.00 1 100.00
2 500.00 2 50.00
(5 rows affected)
/* If ... else using budget amount higher than any in my tables: */
1> if exists (select * from
2> tblBudget, tblSpending
3> where tblBudget.regionID = tblSpending.regionID
4> and tblBudget.budgetAmt > 10000)
5> print "Some large budgets"
6> else
7> print "No large budgets"
8> go
No large budgets
/* If ... else using lower budget amount: */
1> if exists (select * from
2> tblBudget, tblSpending
3> where tblBudget.regionID = tblSpending.regionID
4> and tblBudget.budgetAmt > 4000)
5> print "Some large budgets"
6> else
7> print "No large budgets"
8> go
Some large budgets
/* Case */
/* Before trying example, check for contract column in pubs2: */
1> use pubs2
2> go
1> select * from titles where 0 = 1
2> go
title_id
title
type pub_id price advance
total_sales
notes
pubdate contract
--------
--------------------------------------------------------------------
------------ ------ ------------------------ -----------------------
-----------
--------------------------------------------------------------------
-------------------------- --------
(0 rows affected)
/* */
/* If contract = 1, show value as "Yes"; otherwise "No". */
/* */
1> select title_id, "Contract?"=
2> case
3> when contract=1 then "Yes"
4> when contract=0 then "No"
5> end
6> from titles
7> go
title_id Contract?
-------- ---------
BU1032 Yes
BU1111 Yes
BU2075 Yes
BU7832 Yes
MC2222 Yes
MC3021 Yes
MC3026 No
PC1035 Yes
PC8888 Yes
PC9999 No
PS1372 Yes
PS2091 Yes
PS2106 Yes
PS3333 Yes
PS7777 Yes
TC3218 Yes
TC4203 Yes
TC7777 Yes
(18 rows affected)
/* */
/* Similarly, with criteria; e.g., for computing books only. */
/* */
1> select title_id, "Contract?"=
2> case
3> when contract=1 then "Yes"
4> when contract=0 then "No"
5> end
6> from titles
7> where type like '%comp%'
8> go
title_id Contract?
-------- ---------
PC1035 Yes
PC8888 Yes
PC9999 No
(3 rows affected)
/* While */
/* (In my database) Increase prices as long as they don't go */
/* higher than a specified value; show intermediate values. */
1> use evelyn
2> go
/* */
/* Products table with initial prices: */
/* */
1> select * from products
2> go
prodname price quantity
-------------------- ------------------------ --------
Canned soup 0.88 50
Salt 0.88 25
Cornflakes 1.54 144
aaa NULL NULL
(4 rows affected)
/* */
/* Update prices: */
/* */
1> while (select max(price) from products) < 1.95
2> begin
3> select prodname, price
4> from products
5> where price is not null
6> update products
7> set price = price * 1.1
8> where price is not null
9> end
10> go
(0 rows affected)
prodname price
-------------------- ------------------------
Canned soup 0.88
Salt 0.88
Cornflakes 1.54
(3 rows affected)
(3 rows affected)
(0 rows affected)
(3 rows affected)
(3 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
Canned soup 0.97
Salt 0.97
Cornflakes 1.70
(3 rows affected)
(3 rows affected)
(0 rows affected)
prodname price
-------------------- ------------------------
Canned soup 1.07
Salt 1.07
Cornflakes 1.87
(3 rows affected)
(3 rows affected)
(0 rows affected)