1.select a.sid from suppliers a,catalog c where a.sid=c.sid 2.select a.sid,b.pid,b.pName,c.price from suppliers a,parts b,catalog c where a.sid=c.sid and b.pid=c.pid order by a.sid desc,b.pid desc 3.select b.pid,count(b.sid) from parts b group by b.pid having count(b.sid)>2 4.selet top 1 b.pName from suppliers a,parts b,catalog c where a.sid=c.sid and b.pid=c.pid order by c.price desc 5.select .... 不想写了
--2. select a.sid,a.pid from 价目表 a where exists(select 1 from 价目表 where a.pid= pid ) order by price desc --3. select pid from 价目表 group by pid having count(1)>1 --4 select top 1 * from 价目表 where sid ='SONY' order by price desc --5 select * from 价目表 where price<200
写完算了select a.sName,b.pName,c.Price from suppliers a,parts b,catalog c where a.sid=c.sid and b.pid=c.pid and c.pirce<200
--(1)找到供应所有商品的供货商的ID select sid from 价目表 where pid in (select pid from 零件表) --(2)找出提供同一种零件的供应商ID,其中第一个供应商的供应价格比第一个供应商的价格贵 select * from (select C.*,S.sName,S.address from suppliers s join (select C.* from catalog C join parts P on C.pid=P.pid)C on C.sid=s.sid) T where not exists (select * from catelog where T.sid=sid and price<T.price)
1.select distinct sid from catalog 2.这个不好弄,后面的条件不对. 3.select pid from catalog a where exists (select 1 from catalog where pid=a.pid group by pid having count(*)>1) 4.select top 1 a.pName from parts a inner join catalog b on a.pid=b.pid inner join suppliers c on b.sid=c.sid where c.sName="SONY" order by b.price 5.select pid from catalog a where not exists (select 1 from catalog where pid=a.pid and price>=200)
--(3)找出至少有2个不同供应商供货的零件ID select T.* from (select S.*,C.pid from suppliers s join catalog c on s.sid=c.sid) T where exists(select * from catalog where T.pid=pid and T.sid<>sid) having count(T.sid)>2 --(4)找出由SONY供应的最贵的零件 select pid,max(price) from catalog where sid in (select sid from suppliers where sName='SONY')
4.select top 1 a.pName from parts a inner join catalog b on a.pid=b.pid inner join suppliers c on b.sid=c.sid where c.sName="SONY" order by b.price desc
--(1)找到供应所有商品的供货商的ID select sid from 价目表 where pid in (select pid from 零件表) --(2)找出提供同一种零件的供应商ID,其中第一个供应商的供应价格比第一个供应商的价格贵 select * from (select C.*,S.sName,S.address from suppliers s join (select C.* from catalog C join parts P on C.pid=P.pid)C on C.sid=s.sid) T where not exists (select * from catelog where T.sid=sid and price<T.price) --(3)找出至少有2个不同供应商供货的零件ID select T.* from (select S.*,C.pid from suppliers s join catalog c on s.sid=c.sid) T where exists(select * from catalog where T.pid=pid and T.sid<>sid) having count(T.sid)> --(4)找出由SONY供应的最贵的零件 select pid,max(price) from catalog where sid in (select sid from suppliers where sName='SONY')
suppliers values ('s0001','IBM','Rome:XXX Apart:AAA SSS Road') ('s0002','Intel','Rome:sss Apart:www yree Road') ('s0003','HP','Rome:eee Apart:TTT OOO Road') ('s0004','SONY','Rome:ccc Apart:uuu SiS Road') ('s0005','Lenovo','Rome:lll Apart:lll lala Road')parts values ('p0001','DDR-RAM','BLACK') ('p0002','SD-RAM','BLACK') ('p0003','Monitor','White') ('p0004','Monitor','Silver') ('p0005','keyboard','white') ('p0006','keyboard','black') ('p0007','mouse','red') ('p0008','webCamera','red') catalog values ('s0001','p0002',300) ('s0002','p0005',100) ('s0003','p0001',300) ('s0003','p0002',270) ('s0003','p0003',1000) ('s0003','p0004',1200) ('s0003','p0005',100) ('s0003','p0006',100) ('s0004','p0002',300) ('s0004','p0003',1500) ('s0004','p0004',1500) ('s0004','p0005',55) ('s0005','p0007',20) ('s0003','p0007',20) ('s0003','p0008',30) 第一题为什么不是 select a.sid from suppliers a ,parts b catalog c where a.sid=c.sid and b.pid=c.pid and c.pid=all(select pid from parts ) 其他都可以了
1.select a.sid from suppliers a,catalog c where a.sid=c.sid
2.select a.sid,b.pid,b.pName,c.price from suppliers a,parts b,catalog c
where a.sid=c.sid and b.pid=c.pid order by a.sid desc,b.pid desc
3.select b.pid,count(b.sid) from parts b group by b.pid having count(b.sid)>2
4.selet top 1 b.pName from suppliers a,parts b,catalog c where a.sid=c.sid and b.pid=c.pid order by c.price desc
5.select .... 不想写了
--2.
select a.sid,a.pid from 价目表 a where exists(select 1 from 价目表 where a.pid= pid )
order by price desc
--3.
select pid from 价目表
group by pid
having count(1)>1
--4
select top 1 *
from 价目表
where sid ='SONY'
order by price desc
--5
select *
from 价目表
where price<200
where a.sid=c.sid and b.pid=c.pid and c.pirce<200
select sid from 价目表 where pid in (select pid from 零件表)
--(2)找出提供同一种零件的供应商ID,其中第一个供应商的供应价格比第一个供应商的价格贵
select * from (select C.*,S.sName,S.address from suppliers s join (select C.* from catalog C join parts P on C.pid=P.pid)C on C.sid=s.sid) T where not exists (select * from catelog where T.sid=sid and price<T.price)
2.这个不好弄,后面的条件不对.
3.select pid from catalog a where exists (select 1 from catalog where pid=a.pid group by pid having count(*)>1)
4.select top 1 a.pName from parts a inner join catalog b on a.pid=b.pid inner join suppliers c on b.sid=c.sid where c.sName="SONY" order by b.price
5.select pid from catalog a where not exists (select 1 from catalog where pid=a.pid and price>=200)
select T.* from (select S.*,C.pid from suppliers s join catalog c on s.sid=c.sid) T
where exists(select * from catalog where T.pid=pid and T.sid<>sid) having count(T.sid)>2
--(4)找出由SONY供应的最贵的零件
select pid,max(price) from catalog where sid in (select sid from suppliers where sName='SONY')
select sid from 价目表 where pid in (select pid from 零件表)
--(2)找出提供同一种零件的供应商ID,其中第一个供应商的供应价格比第一个供应商的价格贵
select * from (select C.*,S.sName,S.address from suppliers s join (select C.* from catalog C join parts P on C.pid=P.pid)C on C.sid=s.sid) T where not exists (select * from catelog where T.sid=sid and price<T.price)
--(3)找出至少有2个不同供应商供货的零件ID
select T.* from (select S.*,C.pid from suppliers s join catalog c on s.sid=c.sid) T
where exists(select * from catalog where T.pid=pid and T.sid<>sid) having count(T.sid)>
--(4)找出由SONY供应的最贵的零件
select pid,max(price) from catalog where sid in (select sid from suppliers where sName='SONY')
('s0001','IBM','Rome:XXX Apart:AAA SSS Road')
('s0002','Intel','Rome:sss Apart:www yree Road')
('s0003','HP','Rome:eee Apart:TTT OOO Road')
('s0004','SONY','Rome:ccc Apart:uuu SiS Road')
('s0005','Lenovo','Rome:lll Apart:lll lala Road')parts values
('p0001','DDR-RAM','BLACK')
('p0002','SD-RAM','BLACK')
('p0003','Monitor','White')
('p0004','Monitor','Silver')
('p0005','keyboard','white')
('p0006','keyboard','black')
('p0007','mouse','red')
('p0008','webCamera','red')
catalog values
('s0001','p0002',300)
('s0002','p0005',100)
('s0003','p0001',300)
('s0003','p0002',270)
('s0003','p0003',1000)
('s0003','p0004',1200)
('s0003','p0005',100)
('s0003','p0006',100)
('s0004','p0002',300)
('s0004','p0003',1500)
('s0004','p0004',1500)
('s0004','p0005',55)
('s0005','p0007',20)
('s0003','p0007',20)
('s0003','p0008',30)
第一题为什么不是
select a.sid from suppliers a ,parts b catalog c where a.sid=c.sid and
b.pid=c.pid and c.pid=all(select pid from parts )
其他都可以了