SELECT Clause in SYBASE TSQL

SELECT Clause :-

select <column-list> from <table-name> 
select au_lname, au_fname from authors 
select title_id, type, price, price * .1 from titles 
select * from publishers 
  (not recommended)

string concatenation: 
select au_lname + ", " + au_fname from authors

name ouput columns yourself - column alias 
select title_id, type, price "original price", price * .1 discount from titles 
select "Full Author Name" = au_lname +", " + au_fname from authors

remove duplicates with distinct: 
select distinct type from titles 
select distinct city, state from authors 
  (here distinct refers to a combination of city and state, so that each column by itself may have duplicate entries)

filtering rows with where 
select <column-list> from <table-name> where <condition> 
select au_lname, au_fname from authors where state="CA"

equality and inequality operators: = , <>  or != , > , >= , < , <= , !< , !> 
select type, title_id, price from titles where price * total_sales < advance 
(can be applied to string  comparison - default sorting order is ASCII)

logical OR and AND 
select au_id, city, state 
from authors 
where state="CA" or city="Salt Lake City"

between and Ranges of Data: 
<expression> between <expression> and <expression> 
select title_id, price from titles where price between $5 and $10 
equivalent to 
select title_id, price from titles where price >= $5 and price <= $10

not between: 
select title_id, price from titles where price not between $5 and $10 
equivalent to 
select title_id, price from titles where price >= $5 and price <= $10

in (...) : 
select title_id, price from titles where type in ("mod_cook", "trad_cook", "business") 
equivalent to 
select title_id, price from titles where type = "mod_cook" 
  or type = "trad_cook" or type = "business"

not in (...) 
select title_id, price from titles where type not in ("mod_cook", "trad_cook", "business")

wildcards with like: 
% - any number (0 to many) of any characters 
_ (underscore) - any single character 
[ ]   any single character from those listed in the brackets (this is only for Sybase) 
[%[ - actually match the % character 
[^A-C] - matches any character except A,B,C

select au_lname, au_fname, city, state from authors where city like "Spring%" 
select type, title_id, price from titles where title_id like "B_1342" 
select type, title_id, price from titles where title_id like "B[A-Za-z0]1342 
     Note - if you need to include the '_' character in your pattern - use 'escape' word, for example: 
            select * from titles where title_id like 'ABC\_%' escape('\')

ordering result sets with order by: 
select au_lname, au_fname from authors order by au_lname 
select au_lname, au_fname from authors order by au_lname, au_fname

order by position in the select list: 
select title_id, price, total_sales, price*total_sales "total dollar sales" 
from titles 
order by 4

Ascending and Descending Ordering 
select title_id, price, total_sales, price*total_sales "total dollar sales" 
from titles 
order by price*total_sales desc

default sort order is ascending. Example: sort by type (ascending) and then by total_sales (desc): 
select title_id, price, total_sales 
from titles 
order by type, total_sales desc

order by columns not Appearing in the Select List: 
select au_lname, au_fname from authors order by city, state

agregate functions: 
sum( ) - total numeric, 
avg( ) - average numeric, 
min( ) - lowest numeric or sorting string or earliest date, 
max( ) - highest numeric or sorting string or latest date, 
count( ) - returns the number of non-null expressions, 
count(*) - returns number of rows found

select avg(price) from title 
select avg(price) "avg" from titles where type = "business"

select avg(price) "avg", sum(price) "sum" from titles 
where type in ("business","mod_cook")

counting rows with count(*): 
select count(*) from authors where state="CA"

agregates functions discard null values

sub-agregates with group by: 
select type, avg(price) "avg", sum(price) "sum" from titles 
where type in ("_business", "mod_cook") 
group by type

When two or more columns are included in group by statement, agregates are based on unique combinations of these columns: 
select type, pub_id, avg(price) "avg", sum(price) "sum" from titles 
where type in ("_business", "mod_cook") 
group by type, pub_id

In order for aggregates to properly subtotal (or subaverage or subcount) by non-aggregate values, all non-aggregate columns in the select list should be repeated in the group by clause (see color above).

Filtering results with having: 
where - selects rows before averaging: 
select type, avg(price) from titles where price > $10 group by type 
having select rows from the result set: 
select type, avg(price) from titles where price > $10 group by type having avg(price) > $20

Example: find duplicates of au_id: 
select au_id, count(*) from authors 
group by au_id 
having count(*) > 1

Worktable - a temporary table which is created before distinct, having or order by are applied.

select type, avg(price) from titles where pub_id="1289" 
group by type 
having avg(price) > $15 
order by avg(price) desc