mysql例题_mysql练习题1

--为了方便做题创建了表--drop table if exists student;create table student(

s_id bigint,

s_name char(10),

s_birth date,

s_sex char(5));

drop table if exists course;

create table course(

c_id int,

c_name char(20),

t_id int);

drop table if exists teacher;

create table teacher(

t_id int,

t_name char(10));

drop table if exists score;

create table score(

s_id int,

c_id int,

s_score int);

insert into student values(20644101,'小明','1992-10-14','男');

insert into student values(20644102,'小红','1990-11-15','女');

insert into student values(20644103,'小江','1993-12-16','女');

insert into student values(20644104,'小月','1994-12-17','女');

insert into student values(20644105,'小青','1996-07-14','女');

insert into student values(20644106,'小龙','1995-12-12','男');

insert into student values(20644107,'小磊','1997-12-18','男');

insert into student values(20644108,'小坤','1998-06-14','男');

insert into student values(20644109,'小强','1990-05-14','男');

insert into course values(01,'语文',101);

insert into course values(02,'数学',102);

insert into course values(03,'英语',103);

insert into course values(04,'体育',104);

insert into course values(05,'物理',105);

insert into course values(06,'化学',106);

insert into teacher values(101,'赵三');

insert into teacher values(102,'钱二');

insert into teacher values(103,'孙强');

insert into teacher values(104,'李红');

insert into teacher values(105,'周五');

insert into teacher values(106,'郑前');

insert into score values(20644101,01,98);

insert into score values(20644101,02,92);

insert into score values(20644101,03,96);

insert into score values(20644101,04,90);

insert into score values(20644102,01,98);

insert into score values(20644103,02,98);

insert into score values(20644104,03,98);

insert into score values(20644105,04,90);

insert into score values(20644106,05,60);

insert into score values(20644107,06,55);

insert into score values(20644108,01,45);

insert into score values(20644109,02,90);

insert into score values(20644102,03,98);

insert into score values(20644103,04,98);

insert into score values(20644104,05,98);

insert into score values(20644105,06,90);

insert into score values(20644106,01,60);

insert into score values(20644107,02,55);

insert into score values(20644108,03,45);

insert into score values(20644109,04,90);

常见的sql笔试题和面试题上

1、查询课程编号为“001”的课程比“002”的课程成绩高的所有学生的学号。

select a.s_id from

(select s_id,s_score from score where c_id=01) a

join

(select s_id,s_score from score where c_id=02) b

on a.s_id=b.s_id

where a.s_score > b.s_score

;

2、查询平均成绩大于60分的学生的学号和平均成绩

select s_id

,avg(s_score)as avg_score

from score

group by s_id

having avg(s_score)>60;

3、查询所有学生的学号、姓名、选课数、总成绩

select a.s_id

,a.s_name

,b.co

,b.sum_score

from(select s_id,s_name from student) a

join (select s_id

,count(c_id) as co

,sum(s_score) as sum_score

from score

group by s_id) b

on a.s_id=b.s_id;

##简便select a.s_id,a.s_name,count(b.c_id),sum(b.s_score)

from student a

join score b

on a.s_id=b.s_id

group by a.s_id,a.s_name;

4.查询姓“赵”的老师的个数

select count(t_id) from teacher

where t_name like '赵%';

5.查询没学过“赵三”老师课的学生的学号、姓名

select a.s_id

,a.s_name

from student a

where s_id not in

(select s_id from score,course,teacher

where score.c_id=course.c_id

and teacher.t_id