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
