--1
select distinst jno from spj join p on spj.pno=p.pno where p.color='红色'
--2
select jno from spj where qty in (select max(qty) from spj)
--3
select sno from s where not exists
(select * from p where not exists
(select * from spj where spj.pno=p.pno and spj.sno=s.sno))
--4
create view mv_spj as
select * from spj where jno in (select jno from j where jcity='北京')grant select on mv_spj to Liming
select distinst jno from spj join p on spj.pno=p.pno where p.color='红色'
--2
select jno from spj where qty in (select max(qty) from spj)
--3
select sno from s where not exists
(select * from p where not exists
(select * from spj where spj.pno=p.pno and spj.sno=s.sno))
--4
create view mv_spj as
select * from spj where jno in (select jno from j where jcity='北京')grant select on mv_spj to Liming
select distinct JNO from SPJ a join P b on a.PNO=b.PNO where b.COLOR='RED'
2. 找出购买零件数量最多的的工程的工程号
select distinct JNO from SPJ where QTY=(select max(QTY) from SPJ)
3. 找出供应了所有零件的供应商的号码
select distinct SNO from S where not exists(select * from P where not exists(select * from SPJ where S.SNO=SPJ.SNO and SPJ.PNO=P.PNO))
4. 授权用户”Liming”只能查询位于北京的工程使用零件的情况(可以使用多条SQL)create VIEW V_MYSPJ as
select * from SPJ where JNO in (select JNO from J where JCITY='北京')
goGRANT SELECT ON V_MYSPJ TO Liming WITH GRANT OPTION
select sno from j inner join spj on j.jno=spj.jno
where spj.pno in(select pno from p where color='红色')2. 找出购买零件数量最多的的工程的工程号
select sno from j
where jno in(select pno from(select top 1 pno,count(pno) from spj group by pno order by count(pno) desc)a)3. 找出供应了所有零件的供应商的号码
select sno from s where sno in(select sno from spj where pno=some(select pno from p))4. 授权用户”Liming”只能查询位于北京的工程使用零件的情况(可以使用多条SQL)
create view Liming
as
select * from SPJ where JNO in (select JNO from J where JCITY='北京')
goGRANT SELECT ON V_MYSPJ TO Liming WITH GRANT OPTION
where p.color<>'red'2,1,select spj.pno from spj inner join P on spj.pno=p.pno
where p.color='red'3,select a.sno from s inner join spj on s.sno=spj.sno4,多加个条件就可以
[email protected]