数据库实验2

一、Change the data in the table

UPDATE t_class 
SET 
   grade = subject,
   subject =subject_eng, 
   subject_eng = NUll;

二、find student’s name by id

SELECT 
     a.stu_name,
     a.stu_gender,
     a.stu_birthday
 FROM  t_student a
 WHERE a.stu_id='20127002'

三、Output gender by male or female

SELECT 
  a.stu_name,
  CASE 
  WHEN a.stu_gender=1 then 'Male'
   ELSE 'Female'
   END gender,
  a.stu_birthday
FROM  t_student a
WHERE a.stu_id='20127002'

四、List students in Class, named=会计1201

SELECT 
  b.stu_name,
  b.stu_id
FROM  t_class a
INNER JOIN t_student b 
  ON a.cls_id=b.cls_id 
WHERE a.cls_name = '会计1201'

链接表的使用：INNER JOIN [需要连接的表] ON [ 表] [连接条件]

五、Find class size of Subject 会计 Grade 2012

SELECT 
 a.cls_name,
 COUNT(*) size
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