CREATE DATABASE university;
use university;
CREATE TABLE COURSE
(
Course_name CHAR(30) not null,
Couese_number CHAR(15) not null,
Credit_hours int(3) not null,
Department CHAR(10) not null
);insert into COURSE values
('Intro to Computer Science', 'CS1310', 4, 'CS'),
('Data Structures', 'CS3320', 4, 'CS'),
('Discrete Mathematics', 'MATH2410', 3, 'MATH'),
('Database', 'CS3380', 3, 'CS');
CREATE TABLE SECTION
(
Section_identifier int(3) not null,
Course_number CHAR(15) not null,
Semester CHAR(10) not null,
Year int(2) not null,
Instructor CHAR(15) not null
);insert into SECTION values
(85, 'MATH2410', 'Fall', 04, 'King'),
(92, 'CS1310', 'Fall', 04, 'Anderson'),
(102, 'CS3320', 'Spring', 05, 'Knuth'),
(112, 'MATH2410', 'Fall', 05, 'Chang'),
(119, 'CS1310', 'Fall', 05, 'Anderson'),
(135, 'CS3380', 'Fall', 05, 'Stone');
CREATE TABLE GRADEREPORT
(
Student_number int(5) not null,
Section_identifier int(5) not null,
Grade CHAR(2) not null
);insert into GRADEREPORT values
(17, 112, 'B'),
(17, 119, 'C'),
(8, 85, 'A'),
(8, 92, 'A'),
(8, 102, 'B'),
(8, 135, 'A');CREATE TABLE PREREQUISITE
(
Course_number CHAR(10) not null,
Prerequisite_number CHAR(10) not null
);insert into PREREQUISITE values
('CS3380', 'CS3320'),
('CS3380', 'MATH2410'),
('CS3320', 'CS1310');select * from course;
select * from section;
select * from gradereport;
select * from prerequisite;我遇到了一个问题
现在题目让我输出所有由Knuth(Instructor)教的课的课程名(Course_name)我开始是这样写的:
select * from section where Instructor = 'Knuth';后来觉得不符合题意
输出的到底应该是什么
是
Data Structures CS3320 4 CS
还是仅仅是
Data Structures这条语句该怎么写??
谢谢各位,小弟刚学请多指教
where section.instructor='Knuth'楼主的 COURSE 的Couese_number 字段是不是给错了,应该是Course_number
select course_name from course {where Course_number 等于 Section 里的Course_number where where Instructor = 'Knuth'}{}里面是我的思路,但是我不知道具体Mysql语法是啥
继续请教,谢谢!
那个的确拼错了
我试了mr_mablevi兄的语句居然是错了,试了很多次。
刚想回个帖子
顺便重新启动了下Mysql
结果发现又可以最惊喜的是我本来写的语句貌似也是对的select course_name from course where Course_number in(select Course_number from section where Instructor = 'Knuth');
请问两位,这样写到底可以吗?
和mr_mablevi兄写的相比有什么优缺点吗?谢谢!!