use SQLEx;
1.
select name from tblMember
where pk = (select who from tblRent
where what = 5);
name
-----
신숙주
2.
select * from tblCountry;
select name from tblCountry
where area = (select max(area) from tblCountry);
name
------------------------------
중국
3.
select name from tblGroup
where salary >= (select avg(salary) from tblGroup);
name
----------
김시민
대조영
성삼문
신숙주
안중근
유관순
윤봉길
이윤복
이율곡
이자겸
장보고
조광조
한명회
4.
select salary from tblStaff
where name = (select staff from tblProject
where prjName = '노조협상건');
salary
-----------
145
5.
select addr from tblMember
where bYear = (select min(bYear) from tblMember);
addr
--------------------------------------------------
12-3번지 301호
6.
select name from tblMember
where pk in(select who from tblRent
where what = 3)
name
-----
유관순
이율곡
7.pubs.stores 에서 'Bookbeat'란 상점에서 판매한 책중 가장 판매량이 많은 책의 제목?
use pubs;
select * from stores
select * from sales
select * from titles
select title from titles
where title_id = (select title_id from sales
where qty = (select max(qty) from sales
where stor_id = (select stor_id from stores
where stor_name = 'Bookbeat')))
title
--------------------------------------------------------------------------------
But Is It User Friendly?
8. pubs.titles에서 'mod_cook', 'trad_cook'에 속한 책들 중 가장 가격이 높은 책의 제목?
select title from titles
where price = (select max(price) from titles
where type in( 'mod_cook', 'trad_cook'));
title
--------------------------------------------------------------------------------
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
CREATE TABLE CompanyMemberAddress
(
cuniq char(4) not null primary key,
cname nvarchar(4) not null,
cage int check([cage] BETWEEN 15 AND 60) not null,
clzip char(3) not null,
crzip char(3) not null,
claddress varchar(50) not null,
craddress varchar(50) not null,
ctel varchar(20) null,
cposition nchar(2) CHECK(cposition LEN(3)),
cdepart nchar(3) not null,
cextension nchar(3) check(cast(cextension as int) between 111 and 999) not null,
cbirth datetime null,
csex nchar(1) not null
);
INSERT INTO CompanyMemberAddressTest3 VALUES('A001', '김종현', '18', '121', '111', '서울시어쩌구저쩌구', '아아아아', '010-111-4444', '사원', '영업부', '111', '1984-12-19', '남');
