Swust's MYSQL Go Over Review

preview

UPDATE t_class
SET
grade = subject,
subject = subject_eng,
subject_eng = NULL
WHERE 1 = 1

Q1:20127007,name,gender,birthday

A1:(考察了数据类型)
SELECT
a.stu_name,
a.stu_gender,
a.stu_birthday
FROM
t_student a
WHERE
a.stu_id = ‘20127007’

Q2:会计1201,find student’s id & name

A2:(学会联合表查询)
SELECT
b.stu_id,
b.stu_name
FROM
t_class a
INNER JOIN t_student b ON a.cls_id = b.cls_id
WHERE
a.cls_name = ‘会计1201’

Q3:会计,2012,find classes with student’s count

A3:(学会分组)
SELECT
a.cls_name,
COUNT(*) students
FROM
t_class a
INNER JOIN t_student b ON a.cls_id = b.cls_id
WHERE
a.subject = ‘会计’
AND
a.grade = 2012
GROUP BY
a.cls_name

Q4:20127007 find all failure course

A4:（学会去重）
SELECT
DISTINCT b.course_name
FROM
t_mark a
INNER JOIN t_course b ON a.course_id = b.course_id
WHERE
a.stu_id = ‘20127007’
AND
a.scroll < 60.00

Q5:Add a new course,17317,‘DB Founddation’,3.5

A5:
INSERT INTO t_course
(course_id, course_name, course_credit)
VALUES
(17317, ‘DB Foundation’, 3.5);

Q6:会计,2012,‘英语[1]’ scroll

A6:
SELECT
AVG(c.scroll) Average
FROM
t_class a
INNER JOIN t_student b ON a.cls_id = b.cls_id
INNER JOIN t_mark c ON b.stu_id = c.stu_id
INNER JOIN t_course d ON c.course_id = d.course_id
WHERE
a.subject = ‘会计’
AND
a.grade = 2012
AND
d.course_name = '英语[1]

Q7:会计,2012,‘英语[1]’ failure sudents

A7:
SELECT
b.stu_id,
b.stu_name
FROM
t_class a
INNER JOIN t_student b ON a.cls_id = b.cls_id
WHERE
a.subject = ‘会计’
AND
a.grade = 2012
AND
EXISTS(
SELECT 1 FROM t_mark c
INNER JOIN t_course d ON d.course_id = c.course_id
WHERE
c.stu_id = b.stu_id
AND
c.scroll < 60
AND
d.course_name = ‘英语[1]’
)

Q8:会计,2012,5-course-failures

A8:
SELECT
b.stu_id,
b.stu_name,
COUNT(DISTINCT d.course_id)

FROM
t_class a
INNER JOIN t_student b ON a.cls_id = b.cls_id
INNER JOIN t_mark c ON b.stu_id = c.stu_id
INNER JOIN t_course d ON c.course_id = d.course_id
WHERE
a.subject = ‘会计’
AND
a.grade = 2012
AND
c.scroll < 60
GROUP BY
b.stu_id,
b.stu_name
HAVING
COUNT(DISTINCT d.course_id) >= 5