数据库SQL语句

/*1列出与“胡一凡”在同一个系学习的学生的学号、姓名、专业和班级*/ SELECT stu_num,stu_name,profession,class FROM stinfo_table

WHERE dept = (SELECT dept FROM stinfo_table WHERE stu_name = '胡一凡') AND stu_name <> '胡一凡';

/*2列出上计算机基础课的学生的学号和姓名*/

SELECT ST.stu_num,ST.stu_name FROM stinfo_table ST,course_table SC,chengji_table C

WHERE SC.cou_name = '计算机基础' AND SC.cou_num = C.cou_num AND C.stu_num = ST.stu_num;

/*3列出各门课的平均成绩、最高成绩、最低成绩和选课人数，按课程号升序排列*/ SELECT AVG(C.chengji),MAX(C.chengji),MIN(C.chengji),COUNT(C.stu_num) FROM course_table SC, chengji_table C WHERE SC.cou_num = C.cou_num GROUP BY SC.cou_num ORDER BY SC.cou_num;

/*4查询计算机系学生总人数*/ SELECT COUNT(*) FROM stinfo_table ST

WHERE ST.dept = '计算机';

/*5列出年龄最大和最小的学生的学号、姓名、专业和班级*/ SELECT stu_num,stu_name,profession,class FROM stinfo_table ST

WHERE ST.stu_age = (SELECT MAX(stu_age) FROM stinfo_table);

/*6列出会计电算化专业和某门课90分以上的学生学号和姓名*/ SELECT ST.stu_num,ST.stu_name FROM stinfo_table ST,chengji_table C

WHERE ST.profession = '会计电算化' AND C.chengji > 90 AND ST.stu_num = C.stu_num;

/*8创建各科平均成绩临时表，要求表中列出课程名和平均成绩*/ Select 课程名,AVG（成绩） Into #平均成绩

From 课程表，成绩表 Where

Group by 成绩表.课程号

/*9在学生表中插入孙莉的所有数据并且插入周鹏的学号、姓名、年龄和专业*/

INSERT INTO stinfo_table VALUES('199801144','孙里','女','21','计算机','计算机软件','计应1班');

INSERT INTO stinfo_table(stu_num,stu_name,stu_age,profession) VALUES('201000000','丫丫','21','计算机软件');

/*假如已将计算机系的学生单独建了一个表jsj，其中包括学号、姓名、性别和专业四个字段，字段类型和字段宽度都与学生表相同，现在要 从学生表提取数据插入到jsj表，应执行如下操作 1.将学生表中的所有年龄都加2

2.将计算机系全体学生的01号课成绩置零 3.讲周鹏同学的系别改为“电子”，专业改为“无线电”，学号改为“199803130” 4.从jsj表中将周鹏的记录删除，该同学已经转系

5.带有子查询删除，删除成绩表中会计电算化专业全体学生的所有记录*/ UPDATE stinfo_table SET stu_age = stu_age + 2;

UPDATE chengji_table SET chengji = 0 WHERE cou_num = '01' AND stu_num IN (SELECT stu_num FROM stinfo_table WHERE dept = '计算机');

UPDATE stinfo_table SET dept = '电子', profession = '无线电' WHERE stu_name = '周鹏';

DELETE FROM chengji_table WHERE stu_num IN (SELECT stu_num FROM stinfo_table WHERE profession = '会计电算化');

SELECT 学号,课程号,成绩 FROM 成绩

WHERE 学号 IN ( SELECT 学号 FROM 成绩 GROUP BY 学号 HAVING COUNT(课程号) = 1 )