1.查询每个工程项目及其零件使用情况。(两表连接)
命令:select J.*,SPJ.* from J,SPJ where J.jno = SPJ.jno
或者:select J.jno,J.jname,J.city,SPJ.sno,SPJ.pno,SPJ.qty from J,SPJ where J.jno = SPJ.jno(自然连接)
2.查询每个工程项目及其零件使用情况,包括没有使用零件的工程项目。(两表外连接)
命令:select J.jno,J.jname,J.city,SPJ.sno,SPJ.pno,SPJ.qty from J LEFT OUTER JOIN SPJ on( J.jno = SPJ.jno )
3.查询使用供应商S1供应的零件P1的工程号和工程名(两种方法:连接,嵌套)
命令:select J.jno,jname from J,SPJ where J.jno = SPJ.jno and SPJ.sno = 'S1' and SPJ.pno = 'P1'(连接)
select jno,jname from J where jno in (select jno from SPJ where sno = 'S1' and pno = 'P1')(嵌套)
4.查询查询每个工程项目的工程号、工程名、使用的零件名、零件的供应商名及数量(多表连接)
命令:select J.jno,J.jname,SPJ.pno,S.sname,SPJ.qty from J,SPJ,S where J.jno = SPJ.jno and S.Sno = SPJ.sno
5.查询与供应商“东方红”在同一城市的供应商名(同表嵌套)
命令:select sname from S where city in (select