实验要求

建表成功前遇到的各种问题

create table department
    (dept_name      varchar(20), 
     building       varchar(15), 
     budget             numeric(12,2) check (budget > 0),
     primary key (dept_name)
    );
 
create table course
    (course_id      varchar(8), 
     title          varchar(50), 
     dept_name      varchar(20),
     credits        numeric(2,0) check (credits > 0),
     primary key (course_id),
     foreign key (dept_name) references department
        on delete set null
    );

上面这段建表代码运行报错，原因是Cannot add foreign key constraint，经过各种查询后发现，首先要满足约束的两个属性的类型要一致，这里是一致的，都是varchar(8), 那为什么还是报错呢，后来发现，要把foreign key (dept_name) references department改成foreign key (dept_name) references department(dept_name)才行。也就是说要在references后面的表名要加括号表里的属性才行，不能像老师给的那样直接省略。

insert into classroom values ('南疆雨林', '101', '500');
insert into classroom values ('嵩山少林', '514', '10');
insert into classroom values ('湖底深渊', '3128', '70');
insert into classroom values ('精武馆', '100', '30');
insert into classroom values ('精武馆', '120', '50');

插入数据时上述插入代码报错，原因是Incorrect string value: ‘\xF0\x9F\x98\xAD’, 其后发现mac的默认编码格式是latin2, 中文编码是utf-8，所以解决方案是，在建表的creat语句的最后，加上/span>改成ENGINE=MyISAM DEFAULT CHARSET=utf8;即可，例如正确的代码为：

create table department
    (dept_name      varchar(20), 
     building       varchar(15), 
     budget             numeric(12,2) check (budget > 0),
     primary key (dept_name)
    )ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
create table course
    (course_id      varchar(8), 
     title          varchar(50), 
     dept_name      varchar(20),
     credits        numeric(2,0) check (credits > 0),
     primary key (course_id),
     foreign key (dept_name) references department(dept_name)
        on delete set null
    )ENGINE=MyISAM DEFAULT CHARSET=utf8;

答案：

select id,name 
from student 
where dept_name like '%功%' ;

select id, name, tot_cred
from student 
where (((dept_name like '邪门学院') or (dept_name like '兵器学院')) and tot_cred > 50) ;

select course.course_id, title, sec_id
from course, teaches
where course.course_id = teaches.course_id and id = '83821';

select course.course_id, title, sum(credits)
from course, teaches
where course.course_id = teaches.course_id and id = '83821'
group by course.course_id, title;

select id, sum(credits)
from teaches, course
where course.course_id=teaches.course_id
group by id;

select course.course_id, title, avg(salary)
from course, instructor, teaches
where instructor.id=teaches.id and teaches.course_id=course.course_id and instructor.dept_name='内功学院'
group by course.course_id, title
order by avg(salary);

select distinct title
from course, section
where course.course_id=section.course_id and building='南疆雨林';

select id, name
from student
where tot_cred=0;

select course.course_id, title, grade
from course, takes
where course.course_id=takes.course_id and grade is null;

select a.course_id, a.title SUB, b.course_id, b.title PRE
from course a, course b, prereq
where a.course_id=prereq.course_id and b.course_id=prereq.prereq_id;