数据库系统概论SQL查询习题课.ppt

第一次习题课 关系代数和SQL查询 按照课本79页创建学生 课程数据库 usemasterCREATEDATABASES TGOuseS TCREATETABLEStudent SnoCHAR 9 PRIMARYKEY 列级完整性约束条件 Sno是主码 SnameCHAR 20 UNIQUE Sname取唯一值 SsexCHAR 2 SageSMALLINT SdeptCHAR 20 Studnet表 CREATETABLECourse 创建课程表Course CnoCHAR 4 PRIMARYKEY 列级完整性约束条件 Cno是主码 CnameCHAR 40 CpnoCHAR 4 Ccreditfloat FOREIGNKEY Cpno REFERENCESCourse Cno 表级完整性约束条件 Cpno是外码 被参照表是Course 被参照的字段是Cno Course表 CREATETABLESC SnoCHAR 9 CnoCHAR 4 Gradefloat PRIMARYKEY Sno Cno 主码由两个属性 Sno Cno 构成 必须作为表级完整性进行定义 FOREIGNKEY Sno REFERENCESStudent Sno 表级完整性约束条件 Sno是外码 被参照表是Student FOREIGNKEY Cno REFERENCESCourse Cno 表级完整性约束条件 Cno是外码 被参照表是Course SC表 查询学习1号课程的学生最高分数 selectMAX Grade maxgradefromSCgroupbyCnohavingCno 1 查询各个课程号与相应的选课人数 selectcno COUNT fromSCgroupbycno 查询每个学生及其选修课程的情况 包括学生姓名和课程名 成绩 Selectsname cname gradefromStudent Course SCwhereStudent Sno SC SnoandCourse Cno SC Cno 查询选修2号课程且成绩在90分以上的所有学生 selectStudent fromStudent SCwhereStudent Sno SC SnoandSC Cno 2 andGrade 90 查询与 刘晨 在同一个系学习的学生 selectStudent fromStudentwhereSdeptIn selectSdeptfromStudentwhereSname 刘晨 selectX fromStudentXwhereExists select fromStudentYwhereX Sdept Y SdeptandY Sname 刘晨 查询其他系中比信息系某一学生年龄小的学生姓名和年龄 selectSname SagefromStudentwhereSage IS selectSname SagefromStudentwhereSage IS 按照课本71页创建供应数据库 usemasterCREATEDATABASEDB SPJGOuseDB SPJCREATETABLES 创建供应商表S SNOchar 3 PRIMARYKEY 列级完整性约束条件 Sno是主码 SNAMEchar 10 NULL STATUSchar 2 NULL CITYchar 10 NULL 供应商表S S 供应商代码 供应商名称 状态 所在城市 CREATETABLEP 创建零件表 PNOchar 3 PRIMARYKEY 列级完整性约束条件 Pno是主码 PNAMEnchar 10 NULL COLORchar 4 NULL WEIGHTintNULL 零件表P P 零件代码 零件名 颜色 重量 CREATETABLEJ 创建工程项目表 JNOchar 3 PRIMARYKEY 列级完整性约束条件 Jno是主码 JNAMEchar 10 NULL CITYchar 10 NULL 工程项目表J J 工程项目代码 工程项目名 项目所在城市 CREATETABLESPJ 创建供应情况表 SNOchar 3 JNOchar 3 PNOchar 3 QTYintNULL PRIMARYKEY SNO JNO PNO 主码由个属性 SNO JNO PNO 构成 必须作为表级完整性进行定义 FOREIGNKEY SNO REFERENCESS SNO 表级完整性约束条件 SNO是外码 参照表S的SNO FOREIGNKEY JNO REFERENCESJ JNO 表级完整性约束条件 JNO是外码 参照表J的JNO FOREIGNKEY PNO REFERENCESP PNO 表级完整性约束条件 PNO是外码 参照表P的PNO 供应情况表SPJ SPJ 供应商代码 零件代码 工程项目代码 供应数量 求没有使用天津供应商生产的红色零件的工程号JNO selectJNOfromJwherenotexists select fromSPJ S PwhereSPJ JNO J JNOandSPJ SNO S SNOandSPJ PNO P PNOandS CITY 天津 andP COLOR 红 selectJNOfromJExceptselectSPJ JNOfromSPJ S PwhereSPJ SNO S SNOandSPJ PNO P PNOandS CITY 天津 andP COLOR 红 求至少使用了供应商S1所供应的全部零件的工程号JNO selectdistinctJNOfromSPJSPJZwherenotexists select fromSPJSPJXwhereSNO S1 andnotexists select fromSPJSPJYwhereSPJY PNO SPJX PNOandSPJY JNO SPJZ JNOandSPJY SNO SPJX SNO 求供应商S1提供给工程J1的零件供应数量的总和 selectSUM QTY fromSPJwhereSNO S1 andJNO J1 求各供应给工程项目J1零件供应量的最大值 最小值和平均值 selectMAX QTY maxqty MIN QTY minqty AVG QTY avgqtyfromSPJgroupbyJNOhavingJNO J1 求各供应商供应零件供应量的最大值 最小值和平均值及供应商号 selectSNO MAX QTY maxqty MIN QTY minqty AVG QTY avgqtyfromSPJgroupbySNO 求供应给各个工程项目的工程号 供应量及供应商的个数 selectJNO SUM QTY COUNT distinctsno fromSPJgroupbyJNO 求被供应4种以上零件的工程项目的工程号及工程名称 selectJNO JNAMEfromJwhereJNOIN selectJNOfromSPJgroupbyJNOhavingCOUNT PNO 4 求各个供应商供应量总数大于200的零件号码 颜色 重量和供应商个数 selectcountsno P PNO Pname col