经典mysql数据库面试题_【数据库】MySQL经典面试题（练习）

id number(32) NOT NULL,

name varchar(10) DEFAULT NULL,

sax varchar(10) DEFAULT NULL,

age number(6) DEFAULT NULL,

PRIMARY KEY (id)

)

insert into tbl_students (id, name, sax, age) values('2','李四','男','21');

insert into tbl_students (id, name, sax, age) values('3','张三','女','17');

insert into tbl_students (id, name, sax, age) values('4','李四','男','12');

insert into tbl_students (id, name, sax, age) values('6','凤姐','女','20');

insert into tbl_students (id, name, sax, age) values('5','凤姐','女','20');

insert into tbl_students (id, name, sax, age) values('7','田七','男','18');

insert into tbl_students (id, name, sax, age) values('1','田七','男','18');

insert into tbl_students (id, name, sax, age) values('8','张三','男','17');

答案：

mySql

DELETE FROM student WHERE sid NOT IN (SELECT sid FROM ((SELECT MIN(sid) sid FROM student GROUP BY sName,sSex ))t)

oracle：

DELETE FROM student WHERE sid NOT IN(SELECT MIN(sid) sid FROM student GROUP BY sName,sSex )

二、查询各科成绩都及格的学员

(要求查询出参加考试的各科成绩都高于60分，不管参加了多少科考试)

要求结果：

表：

CREATE TABLE tbl_score (

id NUMBER(10) NOT NULL,

username varchar(20) DEFAULT NULL,

course varchar(20) DEFAULT NULL,

score NUMBER(10) DEFAULT NULL,

PRIMARY KEY (id)

)

数据：

insert into tbl_score (id, username, course, score) values('1','张三','语文','50');

insert into tbl_score (id, username, course, score) values('2','张三','数学','80');

insert into tbl_score (id, username, course, score) values('3','张三','英语','90');

insert into tbl_score (id, username, course, score) values('4','李四','语文','70');

insert into tbl_score (id, username, course, score) values('5','李四','数学','80');

insert into tbl_score (id, username, course, score) values('6','李四','英语','80');

insert into tbl_score (id, username, course, score) values('7','王五','语文','50');

insert into tbl_score (id, username, course, score) values('8','王五','英语','70');

insert into tbl_score (id, username, course, score) values('9','赵六','数学','90');

答案：

select username,scor from tbl where id not in (select id from tbl where score < 60)

MySQL题型练习

表(MYSQL)

Student(sid,Sname,Sage,Ssex) 学生表

CREATE TABLE student (

sid varchar(10) NOT NULL,

sName varchar(20) DEFAULT NULL,

sAge datetime DEFAULT '1980-10-12 23:12:36',

sSex varchar(10) DEFAULT NULL,

PRIMARY KEY (sid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Course(cid,Cname,tid) 课程表

CREATE TABLE course (

cid varchar(10) NOT NULL,

cName varchar(10) DEFAULT NULL,

tid int(20) DEFAULT NULL,

PRIMARY KEY (cid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SC(sid,cid,score) 成绩表

CREATE TABLE sc (

sid varchar(10) DEFAULT NULL,

cid varchar(10) DEFAULT NULL,

score int(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Teacher(tid,Tname) 教师表

CREATE TABLE taacher (

tid int(10) DEFAULT NULL,

tName varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据：(MySQL)

insert into taacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');

insert into student(sid,sName,sAge,sSex) values ('1001','张三丰','1980-10-12 23:12:36','男'),('1002','张无极','1995-10-12 23:12:36','男'),('1003