/* In-class examples, 9/16/02 Advanced SQL/Sybase */
/* */
1> use pubs2
2> go
/* */
/* Use of ceiling() and floor() to obtain next higher or lower */
/* whole number. */
/* */
1> select price, ceiling(price), floor(price)
2> from titles
3> where price between 2 and 15
4> go
price
------------------------ ------------------------ ------------------------
11.95 12.00 11.00
2.99 3.00 2.00
2.99 3.00 2.00
10.95 11.00 10.00
7.00 7.00 7.00
7.99 8.00 7.00
11.95 12.00 11.00
14.99 15.00 14.00
(8 rows affected)
/* */
/* Examples of rounding with significant figures kept before */
/* or after the decimal point. */
/* */
1> select price, round(price, 1) pos, round(price, -1) neg
2> from titles
3> where price between 2 and 15
4> go
price pos neg
------------------------ ------------------------ ------------------------
11.95 12.00 10.00
2.99 3.00 0.00
2.99 3.00 0.00
10.95 11.00 10.00
7.00 7.00 10.00
7.99 8.00 10.00
11.95 12.00 10.00
14.99 15.00 10.00
(8 rows affected)
/* */
/* Use of convert function to convert numeric to money */
/* */
1> select avg(price) from titles
2> go
------------------------
14.77
(1 row affected)
1> select avg(price*0.95) from titles
2> go
----------------------------------------------------
14.02793750000000000
(1 row affected)
1> select convert(money, avg(price*0.95)) from titles
2> go
------------------------
14.03
(1 row affected)
/* */
/* Find actual length of titles and convert to fit on line. */
/* */
1> select title_id, title from titles
2> go
title_id
title
--------
--------------------------------------------------------------------------------
BU1032
The Busy Executive's Database Guide
BU1111
Cooking with Computers: Surreptitious Balance Sheets
BU2075
You Can Combat Computer Stress!
BU7832
Straight Talk About Computers
MC2222
Silicon Valley Gastronomic Treats
MC3021
The Gourmet Microwave
MC3026
The Psychology of Computer Cooking
PC1035
But Is It User Friendly?
PC8888
Secrets of Silicon Valley
PC9999
Net Etiquette
PS1372
Computer Phobic and Non-Phobic Individuals: Behavior Variations
PS2091
Is Anger the Enemy?
PS2106
Life Without Fear
PS3333
Prolonged Data Deprivation: Four Case Studies
PS7777
Emotional Security: A New Algorithm
TC3218
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
TC4203
Fifty Years in Buckingham Palace Kitchens
TC7777
Sushi, Anyone?
(18 rows affected)
1> select title_id, char_length(title) length from titles
2> go
title_id length
-------- -----------
BU1032 35
BU1111 52
BU2075 31
BU7832 29
MC2222 33
MC3021 21
MC3026 34
PC1035 24
PC8888 25
PC9999 13
PS1372 63
PS2091 19
PS2106 17
PS3333 45
PS7777 35
TC3218 63
TC4203 41
TC7777 14
(18 rows affected)
1> select col_length("titles", "title")
2> go
---
80
(1 row affected)
1> select convert(char(64), title) ShortenedTitle from titles
2> go
ShortenedTitle
----------------------------------------------------------------
But Is It User Friendly?
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Cooking with Computers: Surreptitious Balance Sheets
Emotional Security: A New Algorithm
Fifty Years in Buckingham Palace Kitchens
Is Anger the Enemy?
Life Without Fear
Net Etiquette
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Prolonged Data Deprivation: Four Case Studies
Secrets of Silicon Valley
Silicon Valley Gastronomic Treats
Straight Talk About Computers
Sushi, Anyone?
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking
You Can Combat Computer Stress!
(18 rows affected)
/* */
/* Find position of the letter x in notes field. */
/* */
1> select patindex("%x%", notes), title_id from titles
2> go
title_id
----------- --------
0 BU1032
0 BU1111
109 BU2075
0 BU7832
0 MC2222
0 MC3021
0 MC3026
0 PC1035
0 PC8888
0 PC9999
39 PS1372
0 PS2091
6 PS2106
0 PS3333
0 PS7777
0 TC3218
0 TC4203
0 TC7777
(18 rows affected)
/* */
/* Find the x in notes where patindex returned value > 0 */
/* */
1> select notes from titles
2> where title_id = 'BU2075' or title_id = 'PS1372'
3> go
notes
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations.
A must for the specialist, this book examines the difference between those who hate and fear computers and those who think they are swell.
(2 rows affected)
/* */
/* Use substring() to see all of long note. */
/* */
1> select substring(notes, 1, 70), substring(notes, 71, 70), right(notes, 70)
2> from titles
3> where title_id = 'BU2075'
4> go
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
The latest medical and psychological techniques for living with the el
ectronic office. Easy-to-understand explanations.
r living with the electronic office. Easy-to-understand explanations.
(1 row affected)
/* */
/* Example using stuff() - a replace function. */
/* */
1> select phone from authors
2> go
phone
------------
408 496-7223
415 986-7020
415 548-7723
408 286-2428
415 834-2919
913 843-0462
415 658-9932
415 836-7128
707 938-6445
415 585-4620
615 297-2723
503 745-6402
415 935-4228
615 996-8275
219 547-9982
415 843-2991
415 354-7128
415 534-9219
301 946-8853
415 836-7128
707 448-4982
801 826-0752
801 826-0752
(23 rows affected)
/* */
/* Change 415 area code to 815 */
/* */
1> select stuff(phone, 1, 3, "815") from authors
2> where phone like '415%'
3> go
------------
815 986-7020
815 548-7723
815 834-2919
815 658-9932
815 836-7128
815 585-4620
815 935-4228
815 843-2991
815 354-7128
815 534-9219
815 836-7128
(11 rows affected)