粤嵌打卡第31天（数据库常见24题型------- 查询）

首先我们创建数据库基本元素

SELECT `EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO` 
FROM
  `scott`.`emp` 
LIMIT 0, 1000 ;

CREATE DATABASE IF NOT EXISTS students DEFAULT CHARACTER SET = 'utf8';
USE students;

#创建基本数据表
#班级表

CREATE TABLE batch(
       batchcode INT NOT NULL,
       batchname VARCHAR(30)
);

ALTER TABLE batch
ADD CONSTRAINT batch_bcode_pk PRIMARY KEY(batchcode);

INSERT INTO batch VALUES(96561,'工业工程96561班');
INSERT INTO batch VALUES(96571,'工业工程96571班');
INSERT INTO batch VALUES(96572,'工业工程96572班');
INSERT INTO batch VALUES(94381,'经济管理94381班');
INSERT INTO batch VALUES(96581,'质量管理96581班');
INSERT INTO batch VALUES(96171,'会计96171班');


#学生基本信息表

CREATE TABLE student(
       studno INT NOT NULL,
       studname VARCHAR(30),
       batchcode INT
);

ALTER TABLE student
ADD CONSTRAINT student_sno_pk PRIMARY KEY(studno);

ALTER TABLE student
ADD CONSTRAINT student_bcode_fk FOREIGN KEY(batchcode) 
REFERENCES batch(batchcode);

INSERT INTO  student VALUES(1057,'张三',96561);
INSERT INTO  student VALUES(1058,'李四',96561);
INSERT INTO  student VALUES(1059,'王五',96571);
INSERT INTO  student VALUES(1060,'马六',96571);
INSERT INTO  student VALUES(1061,'丁七',94381);
INSERT INTO  student VALUES(1062,'张涛',96171);

#课程信息表
CREATE TABLE course(
       courseid VARCHAR(10) NOT NULL,
       coursename VARCHAR(20)
);

ALTER TABLE course
ADD CONSTRAINTS course_cid_uk UNIQUE(courseid);

INSERT INTO course VALUES('A01','JAVA');
INSERT INTO course VALUES('A02','JSP');
INSERT INTO course VALUES('A03','Struts');
INSERT INTO course VALUES('A04','Oracle');
INSERT INTO course VALUES('A05','Spring');
INSERT INTO course VALUES('B01','经济管理');
INSERT INTO course VALUES('B02','国际贸易');
INSERT INTO course VALUES('B03','会计原理');
INSERT INTO course VALUES('B04','外贸函电');
INSERT INTO course VALUES('B05','马克思主义原理');

# 成绩表

CREATE TABLE score(
         studno INT NOT NULL,
         courseid VARCHAR(10) NOT NULL,
         grade INT
);

INSERT INTO score VALUES(1057,'A01',85);
INSERT INTO score VALUES(1057,'A02',77);
INSERT INTO score VALUES(1057,'A03',20);
INSERT INTO score VALUES(1057,'A04',59);
INSERT INTO score VALUES(1057,'A05',80);
INSERT INTO score VALUES(1058,'A01',79);
INSERT INTO score VALUES(1058,'A02',73);
INSERT INTO score VALUES(1058,'A03',62);
INSERT INTO score VALUES(1057,'B01',95);
INSERT INTO score VALUES(1058,'B01',88);
INSERT INTO score VALUES(1058,