insert into
usr
values
(
'A200507',
'鈴木',
'徳次郎',
'スズキ',
'トクジロウ',
'群馬県',
'群馬市北町',
'1-1-1',
'040-999-9999',
NULL
)
insert
usr
(
user_id,
l_name,
f_name,
l_name_kana,
f_name_kana,
tel
)
values
(
'B200506',
'神田',
'愛',
'カンダ',
'アイ',
'040-888-8888'
)
insert into
quest
(
name,
name_kana,
sex,
prefecture,
answer1,
answer2,
answered
)
values
(
'佐々木三郎',
'ササキサブロー',
'男',
'東京都',
3,
'興味深い内容です。',
'2009/4/4'
)
insert into
usr
(
user_id,
l_name,
f_name,
email
)
select
s_id,
l_name,
f_name,
'admin@wings.msn.to'
from
employee
where
last_update >= '2005-01-01'
insert into
time_card
(
s_id,
r_date,
work_time
)
select
s_id,
CURRENT_TIMESTAMP,
0
from
employee
where
class in ('主任','担当','アシスタント')
insert into
sales(
s_id,
s_date,
s_value
)
select
s_id,
'2009-4',
0
from
shop
update
books
set
price = price*1.05
update
books
set
isbn='ISBN'+isbn,
price=price/1.05
update
usr
set
email=NULL
update
product
set
price=price*1.1
update
books
set
isbn=REPLACE(isbn,'ISBN','')
update
employee
set
class =
(
case
class
when '部長' then 'リーダ'
when '課長' then 'マネージャ'
else class
end
)
update
quest
set
answer1=3,
answer2='',
answered=CURRENT_TIMESTAMP
where
id=3
update
books
set
publish='WINGS出版'
where
publish='山田出版'
update
employee
set
class='主任',
last_update = CURRENT_TIMESTAMP
where
l_name='山田' and f_name='奈美'
update
books
set
price=price*0.9
where
publish='ソシム'
update
rental
set
returned=9
where
returned=1
update
books
set
category_id='Z9999'
where
category_id
not in
(
select category_id from category
)
update
employee
set
depart_id='Z99'
where
depart_id not in
(
select depart_id from depart
)
update
employee
set
depart_id='Z99'
where
not exists(select
*
from
depart
where
employee.depart_id=depart.depart_id)
update
books
set
title = title + (select
category_name
from
category
where
category.category_id=books.category_id)
delete from sales where s_date<='2005-11'
delete from
rental
where
rental_date<='2004/12/31' and returned=9
delete from
access_log
where
access_date='2005/6/1' or referer=''
delete from
employee
where
last_update<='2000/3/31' and retired=1
delete from
books
where
isbn not in (select isbn from rental group by isbn)
delete from
books
where
not exists (select * from rental where rental.isbn=books.isbn)
delete from
employee
where
depart_id not in (select depart_id from depart)
delete from
employee
where
not exists(select
depart_id
from
depart
where
employee.depart_id=depart.depart_id)
delete from
sales
where
not exists(select
*
from
shop
where
sales.s_id=shop.s_id)
delete from
books
where
isbn in (select isbn from rental where returned='9')
