sql 语句查询大全

select * from j --工地 select * from p --零件 select * from s --供应商 select * from spj --1.查询向某工程供应零件P1的供应商姓名，其住所与工地相同 select sname from s,j,spj where s.sno=spj.sno and j.jno=spj.jno and j.city=s.city and spj.pno='p1' --2．(1)建立视图view_test，该视图说明北京供应商的情况（在试卷上导出语句）， ---据此视图查询这类供应商中，姓名为N3的供应商的代号及状况乘以100 select * from j select * from s create view VIEW_TEST as select * from s where city='北京' select sno,status*100 from VIEW_TEST where sname='n3' --（2）查询工程名称的首字母为J的工程的代号及工地 select * from j select jno,city from j where jname like 'j%'

--3（1）建立视图view_test，其提供关于供应商S1所供应零件的情况 --并据此视图查询S1供应商供应零件的代号及数量，并按数量降序排列 select * from s where sno='s1' select * from p select * from spj select p.*,spj.qty from p,spj where p.pno=spj.pno and spj.sno='s1'

create view view_test1 as

create view view_test1 as select p.*,spj.qty from p,spj where p.pno=spj.pno and spj.sno='s1' select pno,qty from view_test1 order by qty desc --（2）查询零件种类在12与17的零件名称 select * from p select pname from p where weight between 12 and 17 --4．（1）建立视图view_test，其提供关于住在北京，状况大于等于20的供应商的情况，并据此视图查询状况为30的供应商姓名 create view view_test as select * from s where city='北京' and status>=20 select sname from view_test where status=30 --（2）查询j1工程所使用的s1供应商提供的零件代号及数量 select pno,qty from spj where jno='j1' and sno='s1' --5．（1）建立视图view_test1，其提供关于重量在14与19之间的零件的情况， --并据此视图查询重量为17的零件的名称及颜色 select * from p create view view_test2 as select * from p where weight between 14 and 19 select pname,color from view_test2 where weight=17 --（2）查询供应商记录，并按状况升序排序 select * from s order by status asc (升序) (desc 降序) --6.（1）查询供应商S1所供应的零件按零件分组后的总量在200以上的记录 select * from spj

--select pno from spj where sno=‘s1’ group by pno having sum(qty)>200 --（2）查询零件名称为PN4的零件情况 ---select * from p where pname=‘pn4’ --7．查询住在同一城市的供应商记录。 --（1）select * from s s1,s s2 where s1.city=s2.city and s1.sno>s2.sno --查询零件表P城市名中个第二个字是“京”的零件、代号及名称 --（2）select pno,pname from p where city like ‘_京%’ --8．查询住在上海并且向工地在南京的项目提供零件的供应商情况 --（1）select s.* from s,j,spj where s.sno=spj.sno and j.jno=spj.jno and s.city=‘上海’ and j.city=‘南京’ --查询零件表P种类为12或14的零件的产地及名称 --（2）select pname,city from p where weight=12 or weight=14 ---9.查询工程代号为j2的工程所使用的所有零件的名称及数量 ---（1）select p.pno,sum(qty) from p,spj where p.pno=spj.pno and jno=‘j2’ group by p.pno --查询供应商表S中状况为空值的供应商代号 --（2）select sno from s where status is null --10．查询没有使用北京产的零件的工程代号 --（1）select jno from j where jno not in(select jno from spj,p where spj.pno=p.pno and p.city=‘北京’) --查询使用名称为PN3零件的工程的所在地 --（2）select j.city from j,spj,p where j.jno=spj.jno and spj.pno=p.pno and p.pname=‘pn3’ --11．查询住在上海的供应商提供的北京产的，且工程在南京的项目使用的零件，供应商及工作的情况 --（1）select p.*,s.*,j.* from p,s,j,spj where p.pno=spj.pno and s.sno=spj.sno and j.jno=spj.jno and s.city=‘上海’ and p.city=‘北京’ and j.city=‘南京’ --查询向J1工程提供零件的供应商姓名 --（2）select s.sname from s,spj where s.sno=spj.sno and spj.jno=‘j1’ --12．建立视图view_test，该视图提供关于使用上海产的零件的工程的情况；并据此视图查询使用此类零件的工程名称 --（1）create view view_test as select j.* from j,spj,p where j.jno=spj.jno and spj.pno=p.pno and p.city=‘上海’（有重复） --create view view_test as select j.* from j where jno in (select jno from spj,p where spj.pno=p.pno and p.city=‘上海’) --（无重复）

--select jname from view_test --（2）select jno from spj group by jno having sum(qty)=700 or sum(qty)=400 --13．建立视图view_test11,该视图提供零件数量大于200的供应商情况，并据此视图查询为j4工程提供这类零件的供应商代号 --（1）create view view_test11 as select * from s where sno in( select sno from spj  group by sno having sum(qty)>200)      select v.sno from view_test v,spj where v.sno=spj.sno and spj.jno=‘j4’ --查询数量大于700的供应零件的情况，按数量降序排列 --（2）select p.* from p,spj  where p.pno=spj.pno and p.pno in (select p.pno from p,spj where p.pno=spj.pno  group by p.pno having sum(qty)>=700 ) order by qty desc --14．查询按城市分组后，平均状况大于20的供应商情况 select * from s where city in(select city from s group by city having AVG(status)>20) select * from s --（1）select * from s where city in (select city from s group by city having avg(status)>20) --查询颜色为红和蓝的零件的情况 select * from p where color='红' or color='蓝' --（2）select * from p where color=‘红’ or color=‘蓝’ ---15．查询同住在北京的供应商情况=

--（1）select * from s where city=‘北京’ --查询按城市分组后，平均重量大于15的零件名称 --（2）select pname from p where city in (select city from p group by city having avg(weight)>15 select * from course select * from score select * from student   select * from teacher

--21．（1）查询至少有2名男生的班号

--select class from student where sex=‘男’ group by class having count(no)>=2 --（2）查询student表中不姓“王”的同学记录 --select * from student where name not like ‘王%’ --22.（1）查询男教师及其所上的课程 select * from teacher select * from course select cname from teacher ,course where sex='男' and no=tno

--select teacher.*,course.cname from teacher,course where teacher.no=course.tno and sex='男' --（2）查询student表中的最大和最小的birthday的日期值 select max(birthday) minBrithday,min(birthday )maxBrithday from student --23.（1）查询student表中每个学生的姓名和年龄 select name,YEAR(GETDATE())-YEAR(birthday)年龄 from student

select name,year(getdate())-year(birthday) from student --（2）查询和”李军”同性别并同班的同学name select s2.name from student s1,student s2 where s1.name='李军' and s2.sex=s1.sex and s2.class=s1.class and s2.name<>'李军' --24.（1）查询和”李军”同性别的所有的同学和姓名 select name from student where sex=(select sex from student where name=‘李军’) and name<>‘李军’ --（2）查询所有选修计算机导论课程的男同学的成绩表 select degree from score,student,course where student.no=score.no and score.cno=course.cno and sex=‘男’ and cname=‘计算机导论’