1> /* Advanced SQL Programming Fall 2002*/
2> /* */
3> /* Take-home exercises for Nov. 4th, 2002 */
4> /* */
5> use evelyn
1> /* */
2> /* Average, max, and min price of each supplier's products. */
3> /* */
4> select suplrnum, avg(price), max(price), min(price)
5> from product
6> group by suplrnum
suplrnum
-------- ------------------------ ------------------------
------------------------
111 149.00 149.00
149.00
222 49.00 49.00
49.00
333 179.99 400.00
19.99
444 52.24 89.99
29.00
555 26.66 39.99
19.99
666 31.66 49.99
19.99
777 20.00 20.00
20.00
(7 rows affected)
1> /* */
2> /* Variations: Suppliers with more than one product. */
3> /* */
4> select suplrnum, avg(price), max(price), min(price)
5> from product
6> group by suplrnum
7> having count(name) > 1
suplrnum
-------- ------------------------ ------------------------
------------------------
333 179.99 400.00
19.99
444 52.24 89.99
29.00
555 26.66 39.99
19.99
666 31.66 49.99
19.99
777 20.00 20.00
20.00
(5 rows affected)
1> /* */
2> /* Suppliers whose products sell at different prices. */
3> /* */
4> select suplrnum, avg(price), max(price), min(price)
5> from product
6> group by suplrnum
7> having max(price) <> min(price)
suplrnum
-------- ------------------------ ------------------------
------------------------
333 179.99 400.00
19.99
444 52.24 89.99
29.00
555 26.66 39.99
19.99
666 31.66 49.99
19.99
(4 rows affected)
1> /* */
2> /* Supplier from Japan. */
3> /* */
4> select p.suplrnum, avg(price), max(price), min(price), country
5> from product p, supplier s
6> where p.suplrnum = s.suplrnum
7> and country = 'JAPAN'
8> group by p.suplrnum, country
suplrnum
country
-------- ------------------------ ------------------------
------------------------ --------------------
333 179.99 400.00
19.99 JAPAN
(1 row affected)
1> /* */
2> /* Pubs2 database: Average prices for titles: */
3> /* with known price */
4> /* using 0 if no price */
5> /* by book type */
6> /* by publisher */
7> /* */
8> use pubs2
1> /* */
2> select avg(price)
3> from titles
------------------------
14.77
(1 row affected)
1> /* */
2> select avg(price)
3> from titles
4> where price is not null
------------------------
14.77
(1 row affected)
1> /* */
2> select avg(isnull(price, 0)) AvgUsingZero
3> from titles
AvgUsingZero
------------------------
13.13
(1 row affected)
1> /* */
2> select type, avg(price)
3> from titles
4> group by type
type
------------ ------------------------
UNDECIDED NULL
business 13.73
mod_cook 11.49
popular_comp 21.48
psychology 13.50
trad_cook 15.96
(6 rows affected)
1> /* */
2> select pub_id, avg(price)
3> from titles
4> group by pub_id
pub_id
------ ------------------------
0736 9.78
0877 15.41
1389 18.98
(3 rows affected)
1> /* */
2> /* Revenue for Binnet & Hardley, trad. cook., without discount.*/
3> /* */
4> select p.pub_id, t.title_id, convert(char(6), price) Price, total_sales,
5> total_sales * price BeforeDiscount
6> from titles t, publishers p
7> where t.pub_id = p.pub_id
8> and pub_name like 'Bin%'
9> and type like 'trad%'
pub_id title_id Price total_sales BeforeDiscount
------ -------- ------ ----------- ------------------------
0877 TC3218 20.95 375 7,856.25
0877 TC4203 11.95 15096 180,397.20
0877 TC7777 14.99 4095 61,384.05
(3 rows affected)
1> /* */
2> /* Revenue for Binnet & Hardley, trad. cook., with discount. */
3> /* (Corrected after class: price*sum(qty)*(100-discount)/100)*/
4> /* */
5> select p.pub_id, d.title_id, convert(char(6), price) Price,
6> sum(qty) Number_Sold, convert(numeric(8,6), discount) Discount,
7> convert(char(12), (convert(smallmoney, sum(qty)*price*(100-discount)/100)))
8> ActualRevenue
9> from salesdetail d, titles t, publishers p
10> where d.title_id = t.title_id
11> and t.pub_id = p.pub_id
12> and pub_name like 'Bin%'
13> and type like 'trad%'
14> group by d.title_id, p.pub_id, price, discount
pub_id title_id Price Number_Sold Discount ActualRevenue
------ -------- ------ ----------- ----------- -------------
0877 TC3218 20.95 210 40.000000 2639.70
0877 TC3218 20.95 40 45.000000 460.90
0877 TC3218 20.95 125 46.700000 1395.79
0877 TC4203 11.95 2487 46.700000 15840.57
0877 TC4203 11.95 8700 50.000000 51982.50
0877 TC4203 11.95 53 50.500000 313.51
0877 TC4203 11.95 476 51.700000 2747.40
0877 TC4203 11.95 580 57.200000 2966.47
0877 TC4203 11.95 2500 60.500000 11800.63
0877 TC4203 11.95 300 62.200000 1355.13
0877 TC7777 14.99 155 40.000000 1394.07
0877 TC7777 14.99 1350 46.700000 10786.05
0877 TC7777 14.99 2590 50.000000 19412.05
(13 rows affected)
1> /* */
2> /* (How many _people_ had been President (up to Bill Clinton) - */
3> /* discuss in class. */
4> /* */
/* */
/* Trigger to change customer status to 0 when order shipped. */
/* */
3. Trigger to change customer status to 0 once order has been shipped.
create trigger status_update
on orderdetail
for update
as
/* Column to be updated in orderdetail. */
if update(shipdate)
/* If a date was inserted where previously null (i.e., updated) */
if exists (select od.shipdate
from orderdetail od, inserted i, deleted d
where od.shipdate = i.shipdate
and d.shipdate is null)
/* Update customer status working back from changed shipdate. */
update customer
set status = '0'
from customer c, ordermaster o, orderdetail od, inserted i,
deleted d
where c.custnum = o.custnum
and o.ordnum = d.ordnum
and od.shipdate = i.shipdate
and d.shipdate is null
/* Message if no change. */
else print 'Order not shipped - customer status unchanged.'
/*==============================================================*/
/* */
/* Trigger to delete "more-than-day-old" records on insert. */
/* */
I have to use a Sybase database [...] which traces only some values in
one table of the database. I use it via ODBC (Open DataBase
Connectivity). [...] There is one difficulty:
I need a trigger which is accessed at every INSERT INTO SQL clause and
has to look, if one day is over (I think he means: if one day has
passed). If yes, it should delete all records which are older than one
day. Can someone write a little example [that does something like
this]?
Proposed solution:
=================
Without knowing more details of your precise requirements (like: what
does "a day is over" exactly mean in your data model), I guess the
basic idea isn't very difficult. What about the below code ? I've just
made some quick-'n-dirty assumptions to keep it simple...
create trigger your_insert_trigger on your_table for insert
as
begin
declare @lastdate datetime
select @lastdate = max(your_datetime_column) from your_table
if datediff(dd, @lastdate, getdate()) >= 1
begin
delete your_table
where datediff(dd, your_datetime_column, getdate()) > 1
end
end
/* */
5> dump tran evelyn with truncate_only