Sql语句练习总结查询语句

练习一

现有关系数据库表如下:

学生表:学号 char(6) 姓名 性别 身份证号
课程表:课号 char(6) 名称
成绩表:id,学号,课号,分数
用sql实现如下两道题:
1.检索姓马的女同学情况(姓名,身份证号)
2.检索一门或者一门以上课程成绩大于等于90的所有学生信息(学号,姓名)

建表语句如下:

create table stu(
stuNo char(6),
name varchar(20),
gender varchar(4),
idcard varchar(18)
);

insert  into stu(stuno,name,gender,idcard) values('100001','马霞','女','232301198006013421');
insert  into stu(stuno,name,gender,idcard) values('100002','马立军','男','232301198006013422');
insert  into stu(stuno,name,gender,idcard) values('100003','黄蓉','女','232301198006013423');

create  table course(
cno varchar(6),
cname varchar(20)
);
insert into course(cno,cname) values('1','语文');
insert into course(cno,cname) values('2','数学');
insert into course(cno,cname) values('3','英语');

create table score(
id int primary key auto_increment,
stuno char(6)  references stu(stuno),
cno char(6)    references course(cno),
score numeric
);

insert into score(stuno,cno,score) values('100001','1',80);
insert into score(stuno,cno,score) values('100001','2',80);
insert into score(stuno,cno,score) values('100001','3',95);

insert into score(stuno,cno,score) values('100002','1',70);
insert into score(stuno,cno,score) values('100002','2',80);
insert into score(stuno,cno,score) values('100002','3',85);

insert into score(stuno,cno,score) values('100003','1',60);
insert into score(stuno,cno,score) values('100003','2',70);
insert into score(stuno,cno,score) values('100003','3',80);

答案

-- 1.检索姓马的女同学情况(姓名,身份证号)
use practice
select name, idcard from stu where name like "马%" and gender = "女"
-- 2.检索一门或者一门以上课程成绩大于等于90的所有学生信息(学号,姓名) 
select a.stuno, b.name from score a, stu b 
where a.stuno=b.stuno
and a.score >= 90
--或者使用关联查询
select stu.stuno, stu.name, course.cname, score.score
from stu join score on stu.stuno=score.stuno
join course on score.cno=course.cno
where score.score>=90

练习二

题目

有三张表: 学生表student , 课程Coures 学生课程表 sc, 学生可以选修多门课程,一门课程可以被多个学生选修,通过sc表关联, 详细要求如下:
1.写出建表语句:
2.写出SQL语句 查询选修了所有课程的学生
3.写出SQL语言 查询选修了至少2门以上的课程的学生

语句

create database tt default char set utf8;

create table student (
id int(10) primary key,
name varchar(20)
);

create table course(
     id int(10) primary key,
     name varchar(20)
);

create table sc(
      sid int(10) references student(id),
             cid  int(10) references  course(id),
            grade  int(3)
);


插入如下记录:
insert into student values(1,'feifei');
insert into student values(2,'jingjing');
insert into student values(3,'nannan');
insert into student values(4,'yuanyuan');
insert into student values(5,'jiejie');

insert into course  values(1,'corejava');
insert into course  values(2,'c++');
insert into course  values(3,'jdbc');
insert into course  values(4,'hibernate');

insert into sc  values(1,1,98);
insert into sc  values(2,1,97);
insert into sc  values(3,1,94);
insert into sc  values(4,1,92);
insert into sc  values(5,1,93);

insert into sc  values(1,2,94);
insert into sc  values(2,2,92);
insert into sc  values(3,2,95);
insert into sc  values(5,2,97);

insert into sc  values(1,3,92);
insert into sc  values(2,3,92);
insert into sc  values(4,3,91);

insert into sc  values(1,4,99);
insert into sc  values(3,4,89);

答案

#写出SQL语句 查询选修了所有课程的学生

select name from student where id in 
(select sid from sc group by sid having count(*) = (select count(*) from course))

#写出SQL语言 查询选修了至少2门以上的课程的学生
select name from student where id in 
(select sid from sc group by sid having count(*) >= 2)

练习三

题目

两张表 student和score;
student:学号,姓名,性别,年龄;
score:学号,语文,数学,英语

1.查询tom的学号,姓名,性别,语文,数学,英语
2.查询语文比数学好的同学
3.查询姓名相同的学生学号

建表语句如下:

create table student (
    stuno int(8) primary key,
    sname varchar(12),
    sex varchar(2) default '男',
    age int
);

create table  score(
stuno int(8),
chinese int(3),
math int(3),
english int(3)
);

insert into student(stuno,sname,sex,age)
values(1,'tom','男',22);

insert into student(stuno,sname,sex,age)
values(2,'terry','男',22);

insert into student(stuno,sname,sex,age)
values(3,'marry','女',23);

insert into student(stuno,sname,sex,age)
values(4,'marry','女',23);


insert into score(stuno,chinese,english,math)
values(1,70,80,90);

insert into score(stuno,chinese,english,math)
values(2,60,80,70);

insert into score(stuno,chinese,english,math)
values(3,70,85,95);

insert into score(stuno,chinese,english,math)
values(4,98,85,95);

答案

-- 1.查询tom的学号,姓名,性别,语文,数学,英语

select st.stuno, st.sname, st.sex, st.age, sc.chinese, sc.math, sc.english
from student st join score sc 
on st.stuno=sc.stuno
where st.sname='tom'

-- 2.查询语文比数学好的同学

select st.stuno, st.sname,sc.chinese,sc.math
from student st join score sc
on st.stuno=sc.stuno
where sc.chinese>sc.math


-- 3.查询姓名相同的学生学号
select stuno, sname
from student group by sname
having count(*)>1

select stuno, sname 
from student 
where sname in(select sname
from student group by sname
having count(*)>1)