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