1> /* SQL/Sybase Programming - Fall 2000 */
2> /* In-class / take-home assignment, 10/7-10/14 */
3> /* */
4> use pubs2
1> /* */
2> /* Authors with last names between 6 and 12 letters long. */
3> /* */
4> select au_lname as LastName, char_length(au_lname) as Length
5> from authors
6> where char_length(au_lname) between 6 and 12
LastName Length
---------------------------------------- -----------
Bennet 6
Carson 6
DeFrance 8
Greene 6
Gringlesby 10
Hunter 6
Karsen 6
Locksley 8
MacFeather 10
McBadden 8
O'Leary 7
Panteley 8
Ringer 6
Ringer 6
Straight 8
Stringer 8
Yokomoto 8
del Castillo 12
(18 rows affected)
1> /* */
2> /* How long is your first name? Last name? Complete name? */
3> /* */
4> select char_length('Evelyn'),
5> char_length('Stevens'),
6> char_length('Evelyn V. Stevens')
----------- ----------- -----------
6 7 17
(1 row affected)
1> /* */
2> /* Complete names in uppercase of authors whose first name */
3> /* starts with A, B, or C. */
4> /* */
5> select upper(au_fname) + ' ' + upper(au_lname)
6> from authors
7> where au_fname like 'A%' or au_fname like 'B%' or au_fname like 'C%'
-------------------------------------------------------------
ABRAHAM BENNET
CHERYL CARSON
ANN DULL
BURT GRINGLESBY
CHASTITY LOCKSLEY
ALBERT RINGER
ANNE RINGER
AKIKO YOKOMOTO
(8 rows affected)
1> /* */
2> /* Show the following dates and/or times: */
3> /* a. The complete current date and time */
4> /* b. Date only, with year in two-digit format */
5> /* c. Date only, with year in four-digit format */
6> /* d. Time only */
7> select getdate()
8> select convert(char(8), getdate(), 1)
9> select convert(char(12), getdate())
10> select convert(char(8), getdate(), 8)
--------------------------
Oct 13 2000 3:50PM
(1 row affected)
--------
10/13/00
(1 row affected)
------------
Oct 13 2000
(1 row affected)
--------
15:50:26
(1 row affected)
1> /* */
2> /* Pick a date from several years ago and do the following: */
3> /* a. Show the complete date */
4> /* b. Show only the year */
5> /* c. Show only the month */
6> /* d. Years and months that have passed (separately) */
7> /* e. Date 100 years from that date */
8> /* */
9> select '7/1/1971'
10> select datepart(yy, '7/1/1971')
11> select datename(mm, '7/1/1971')
12> select datediff(yy, '7/1/1971', getdate())
13> select datediff(mm, '7/1/1971', getdate())
14> select dateadd(yy, 100, '7/1/1971')
--------
7/1/1971
(1 row affected)
-----------
1971
(1 row affected)
------------------------------
July
(1 row affected)
-----------
29
(1 row affected)
-----------
351
(1 row affected)
--------------------------
Jul 1 2071 12:00AM
(1 row affected)
1>
2> /* */
3> /* What day of the week (by name) is January 1st, 2001? */
4> /* */
5> select datename(dw, 'Jan 1 2001')
------------------------------
Monday
(1 row affected)