CREATE TABLE [dbo].[xsb](
 [ID] [char](6) NOT NULL primary key,
 [name] [char](8) NOT NULL,
 [sex] [bit] NOT NULL,
 [birthday] [date] NULL,
 [specialty] [char](16) NULL,
 [course] [int] NULL,
 [remark] [varchar](1) NULL)
 
 CREATE TABLE [dbo].[kcb](
 [Cid] [int] NOT NULL primary key,
 [Cname] [char](16) NOT NULL,
 [beginsemester] [int] NULL,
 [grade] [int] NULL)
 
 CREATE TABLE [dbo].[cjb](
 [ID] [char](6) NOT NULL,
 [Cid] [int] NOT NULL,
 [grades] [int] NULL)
 
 INSERT INTO [xsb](ID, name, sex, birthday ,specialty, course)
 SELECT '080101', 'Tom', 1 ,'1990-02-02', 'Computer', 0   
 UNION ALL 
 SELECT '080102', 'Jam', 1, '1991-01-02' , 'Computer',0    
 UNION ALL 
 SELECT '080103', 'Mary', 0 ,'1989-10-02', 'Accounting' ,0   
 UNION ALL 
 SELECT '080104', 'Lucy', 0 ,'1991-02-19' ,'Fashion design', 0   
 UNION ALL 
 SELECT '080105', 'Jack', 1 ,'1990-11-02', 'Accounting', 0   
 UNION ALL 
 SELECT '080106', 'Tracy', 0 ,'1990-06-02' ,'Fashion design', 0   
 UNION ALL 
 SELECT '080107', 'Amy', 0 ,'1992-02-02' , 'Computer', 0   
 
 INSERT INTO kcb
 SELECT 1 ,'Computer', 1, 40
 UNION ALL 
 SELECT 2, 'accounting', 2, 30
 UNION ALL 
 SELECT 3 ,'fashion design', 3 ,30
 
 INSERT INTO [cjb]
 SELECT '080101', 1, 96
 UNION ALL 
 SELECT '080101', 2, 68
 UNION ALL 
 SELECT '080101', 3 ,78
 UNION ALL 
 SELECT '080102', 3 ,99
 UNION ALL 
 SELECT '080102', 2 ,66
 UNION ALL 
 SELECT '080103', 2 ,87
 UNION ALL 
 SELECT '080104', 1 ,100
 
 UPDATE a
 SET a.course=b.course
 FROM xsb a INNER JOIN 
 (SELECT c.id,SUM(a.grades*(b.grade*0.01) ) course
 FROM xsb c INNER JOIN cjb a ON c.id=a.id 
   INNER JOIN kcb b ON a.cid=b.cid
   GROUP BY c.id) b ON a.id=b.id