select distinct
publish
from
books
select
*
from
quest
where
age > 20
select
isbn,
title,
publish
from
books
where
publish in ('ソシム','翔泳社')
select
l_name,
f_name,
email,
prefecture
from
usr
where
prefecture <> '東京都'
order by
prefecture
select
l_name,
f_name,
email,
prefecture
from
usr
where
prefecture not in ('東京都','千葉県')
select
name,
sex,
prefecture
from
quest
where
age >= 30 and age < 40
select
name,
answer1,
answer2
from
quest
where
answered > '2006/01/01'
select
user_id,
isbn
from
rental
where
returned = 1
select
answer2
from
quest
where
answer2 is not null
select
title,
publish,
price
from
books
where
price < 5000
select
title,
publish,
publish_date
from
books
where
title like'%SQL%'
select
*
from
author
where
name like '山田%'
select name from quest where name not like '%子'
select
isbn,
title,
price
from
books
where
title like 'SQL__'
select
l_name,
f_name
from
employee
where
l_name_kana like 'ア%'
select distinct
publish
from
books
where
publish like '%社'
select
isbn,
title,
price
from
books
where
publish in ('ソシム','翔泳社') and price >= 3000
select
*
from
quest
where
sex='女' and age >= 20
select
l_name,f_name,email
from
usr
where
prefecture='東京都' and email like '%examples.com'
select
answer2
from
quest
where
answer2 is not null
select
*
from
employee
where
l_name='山田' and f_name='奈美'
select
isbn,title
from
books
where
publish in ('秀和システム','ソシム') and price >= 3000
select
answer1,answer2
from
quest
where
answer2 is not null or answer2 <> '' order by answer1 asc
select
title,price
from
books
where
price between 2500 and 3000 order by price
select
l_name,f_name,prefecture
from
usr
where
prefecture in ('東京都','千葉県','神奈川県')
order by
l_name_kana asc, f_name_kana asc
select
*
from
rental
where
rental_date < '2005/12/1'
order by
rental_date
select
*
from
access_log
where
access_date between '2006/1/1' and '2006/1/31'
order by
referer desc,ip_address desc
select top 5
l_name,
f_name,
last_update
from
employee
select
title,
publish_date
from
books
order by
publish_date
select top 10
name,answer1,answer2
from
quest
order by answered
select top 10
user_id,isbn,rental_date
from
rental
where
returned=1 order by rental_date asc
select top 10
page_id,
referer,
ip_address,access_date
from
access_log
order by
access_date desc
select
publish,
avg(price)
from
books
group by
publish
select
sex,
max(age) as 最大年齢,
min(age) as 最小年齢
from
quest
group by
sex
select
prefecture,
sex,
avg(answer1)
from
quest
group by
prefecture,sex
select
s_id as 店舗ID,
sum(s_value) as 累計売上
from
sales
group by
s_id
select
publish,
max(price)
from
books
group by
publish
select
page_id,
count(*)
from
access_log
group by
page_id
order
by page_id
select
'ISBN：'+ isbn as ISBNコード,
title as 署名,convert(varchar,datepart(yyyy,publish_date))
+'年'+convert(varchar,datepart(mm,publish_date))
+'月'+convert(varchar,datepart(dd,publish_date))+'日' as 発刊日
from
books
select
prefecture as 都道府県,
floor(avg(answer1)) as 平均評価
from
quest
group by
prefecture
select
isbn as ISBNコード,
convert(varchar,datepart(yyyy,rental_date))
+'年'+convert(varchar,datepart(mm,rental_date))
+'月'+convert(varchar,datepart(dd,rental_date))+'日' as 発刊日
from
rental
where
returned=0
order by
rental_date asc
select
l_name + f_name as 利用者名,
prefecture + city + o_address as 住所
from
usr
select
floor(avg(price))
from
books
group by
publish
select
depart_id,
class,
l_name + f_name as 利用者名
from
employee
order by
depart_id desc,
class desc
select
prefecture,
avg(answer1)
from
quest
group by
prefecture
having
avg(answer1) < 2
select
prefecture,
avg(age)
from
quest
group by
prefecture
having avg(age) >= 32 and avg(age) < 50
select
prefecture,
max(age)
from
quest
where
sex='男'
group by
prefecture
having
max(age) > 50
select
author_id,
count(isbn)
from
author_books
group
by author_id
having
count(isbn) >= 2
select
publish,
category_id,
count(*)
from
books
group by
publish,
category_id
having
count(*) < 3
select
depart_id,
count(*)
from
employee
where
sex=2
group by
depart_id
having count(*) > 3
select
referer,
count(*)
from
access_log
where
access_date >= '2006/1/1'
group by
referer
having
count(*) < 5
order by
count(*) desc
select
referer,
count(*) as カウント数,
case
when count(*) >= 50 then 'A'
when count(*) >= 10 then 'B'
else 'C'
end
from
access_log
group by
referer
select
name,
answer1,
case
when answer1=1 then '役に立たない'
when answer1=2 then '普通'
else 'ためにたった'
end
answer2,
answered
from
quest
order by
answered
select
isbn,
count(*),
case
when count(*) >= 10 then '好評'
when count(*) >= 5 then '普通'
else '不評'
end
from
rental
group by
isbn
select
referer,
count(*),
case
when count(*) >= 50 then 'A'
when count(*) >= 10 then 'B'
else 'C'
end
from
access_log
group by
referer
having
count(*) >= 3
order by
count(*) desc
select
author_id as 著者ＩＤ,
count(*) as カウント数,
case
when count(*) >= 4 then '多い'
when count(*) >= 2 then '普通'
else '少ない'
end as 評価
from
author_books
group by
author_id
order by
count(*) desc
select
l_name + f_name as 名前
,class
,case
when class in ('部長','課長') then '管理職'
when class in ('主任','担当') then '総合職'
else '一般職'
end as 役職クラス
from
employee
select
e.l_name,
e.f_name,
d.depart_name,
e.class
from
employee as e
inner join
depart as d
on
e.depart_id = d.depart_id
where
e.retired <> 1
order by
e.depart_id asc,
e.s_id asc
select
e.l_name + e.f_name,
e.s_id,
t.r_date,
t.work_time
from
employee as e
inner join
time_card as t
on
e.s_id = t.s_id
where
t.r_date >= '2005/12/1' and t.r_date <= '2005/12/31'
order by
e.s_id asc,
t.r_date asc
select
sh.s_name,
sl.s_value
from
shop as sh
inner join
sales as sl
on
sh.s_id = sl.s_id
where
sl.s_date = '2005-12'
order by
sl.s_value
select
p.p_id,
p.p_name,
sum(o.quantity),
p.price,
sum(p.price * o.quantity)
from
order_desc as o
right outer join
product as p
on
o.p_id = p.p_id
group by
p.p_id,
p.p_name,
p.price
order by
sum(p.price * o.quantity) desc
select
u.user_id,
count(*)
from
usr as u
left outer join
rental as r
on
u.user_id = r.user_id
group by
u.user_id
order by
count(*) desc
select
d.depart_id,
d.depart_name
from
depart as d
left outer join
employee as e
on
e.depart_id=d.depart_id
where
e.depart_id is null
select
r.isbn,
count(*)
from
rental as r
left outer join
books as b
on
b.isbn=r.isbn
group by
r.isbn
order by
count(*) desc
select
e.s_id,
e.l_name,
e.f_name,
avg(t.work_time)
from
employee as e
left outer join
time_card as t
on
e.s_id=t.s_id
group by
e.s_id,
e.l_name,
e.f_name
order by
avg(t.work_time) desc
select
cc.c_title,
cn.c_title
from
contents as cc
inner join
contents as cn
on
cc.next_id=cn.c_id
order by
cc.c_id asc
select
cp.c_title,
cn.c_title
from
contents as cp
inner join
contents as cn
on
cn.next_id=cp.c_id
select
mc.title,
mc.page_id,
mn.parent
from
menu as mc
inner join
menu as mn
on
mn.page_id=mc.parent
order by
mc.page_id
select
e2.l_name + e2.f_name as 部下,
e1.l_name + e1.f_name as 上司
from
employee as e1
inner join
employee as e2
on
e1.s_id=e2.b_id
order by
e2.s_id
select
b.title,
a.name,
b.publish_date
from
(books as b inner join author_books as ab on b.isbn=ab.isbn)
inner join author as a on ab.author_id=a.author_id
where
b.publish='ソシム'
order by
b.publish_date desc
select
d.depart_name,
e.l_name + e.f_name,
t.r_date,
t.work_time
from
(employee as e inner join depart as d on e.depart_id=d.depart_id)
inner join time_card as t on t.s_id=e.s_id
where
e.s_id='DA00001'
and
t.r_date between '2005/12/1' and '2005/12/31'
--t.r_date >= '2005-12-1' and t.r_date <= '2005-12-1'
order by
t.r_date
select
cp.c_id,
cp.c_title,
cc.c_id,
cc.c_title,
cn.c_id,
cn.c_title
from
(contents as cp inner join contents as cc on cp.next_id=cc.c_id)
inner join contents as cn on cc.next_id=cn.c_id
order by
cc.c_id
select
om.order_date,
om.po_id,
u.l_name + u.f_name,
p.p_name,
p.price,
od.quantity
from
((order_main as om inner join usr as u on om.user_id=u.user_id)
inner join order_desc as od on od.po_id=om.po_id)
inner join product as p on p.p_id=od.p_id
where
delivery_date is null
select
s_id,
s_name
from
shop
where
s_id not in (select
s_id
from
sales
where s_date='2005-12')
select
name,
age,
answer1,
answer2
from
quest
where
age > (select avg(age) from quest)
order by
answer1 asc
select
depart_id,
depart_name
from
depart
where
depart_id not in (select depart_id from employee)
select
so.s_name,
sa.s_value
from
sales as sa inner join shop as so on sa.s_id=so.s_id
where
s_date='2005-12'
and
sa.s_value < (select
avg(s_value)
from
sales
where s_date='2005-12')
select
us.l_name + us.f_name
from
usr as us
where
not exists(select
*
from
rental as re
where
re.user_id=us.user_id)
select
u.l_name + u.f_name ,
u.user_id
from
usr as u
where
(select
count(*)
from
rental as r
where
r.user_id=u.user_id) <= 0
select
u.l_name_kana + u.f_name_kana
from
usr as u
union
select
e.l_name_kana + e.f_name_kana
from
employee as e
order by
1
select
q.name_kana
from
quest as q
where
sex='女'
union all
select
l_name_kana + f_name_kana
from
employee
where
sex=2
order by
1
select
l_name_kana + f_name_kana
from
usr
except
select
name_kana
from
quest
select
l_name_kana + f_name_kana
from
usr
where
prefecture='千葉県'
union
select
name_kana
from
quest
where
prefecture='千葉県'
