[LTU]1016資料庫系統employee table

create table employee(
fname char(20) not null,
minit char(20) not null,
lname char(20) not null,
ssn int(10) not null primary key,
bdate date,
address varchar(50),
sex char(2) not null,
salary int(10) not null,
super_ssn int(10),
dno char(2));

insert into employee values
('John','B','Smith',123456789,'1965-01-09','731 Fondren Houston TX','M',30000,333445555,'5'),
('Franklin','T','Wong',333445555,'1955-12-08','638 Voss Houston TX','M',40000,888665555,'5'),
('Alicia','J','Zelaya',999887777,'1968-01-19','3321 Castle Spring TX','F',25000,987654321,'4'),
('Jennifer','S','Wallace',987654321,'1941-06-20','291 Berry Bellaire TX','F',43000,888665555,'4'),
('Ramesh','K','Narayan',666884444,'1962-09-15','975 Fire Oak Humble TX','M',38000,333445555,'5'),
('Joyce','A','English',463463463,'1972-07-31','5631 Rice Houston TX','F',25000,333445555,'5'),
('Ahmad','V','Jabbar',987987987,'1969-03-29','980 Dallas Houston TX','M',25000,987654321,'4'),
('James','E','Borg',888665555,'1937-11-10','450 Stone Houston TX','M',55000,NULL,'1'),
('Josh','B','Bush',234516789,'1963-10-28','722 Fondren Houston TX','F',35000,998765432,'1');

[LTU]1008資料庫系統test1

1.擷取所有主修'CS'的大四學生
mysql> select Name from STUDENT where Major='CS';
+-------+
| Name  |
+-------+
| Smith |
| Brown |
+-------+
2 rows in set (0.00 sec)

2.擷取所有在2007及2008年由King教授所開得課程名稱
mysql> select Course_name from COURSE,SECTION where COURSE.Course_number=SECTION.Course_number and Instructor='King' and Year between '07' and '08';
+----------------------+
| Course_name          |
+----------------------+
| Discrete Mathematics |
+----------------------+
1 row in set (0.02 sec)

3.對每一學期由king教授所開得課程擷取其課程編號, 學期(semester), 學年及修課的學生人數
mysql> select Course_number,Semester,Year,count(Student_number) as count from SECTION,GRADE_REPORT where SECTION.Section_identifier=GRADE_REPORT.Section_identifier and Instructor='King';
+---------------+----------+------+-------+
| Course_number | Semester | Year | count |
+---------------+----------+------+-------+
| MATH2410      | Fall     | 07   |     1 |
+---------------+----------+------+-------+
1 row in set (0.01 sec)

[LTU]1009資料庫系統5個table

create table STUDENT(
Name varchar(10),
Student_number int(3),
Class int(2),
Major varchar(20));

insert into STUDENT values('Smith',17,1,'CS'),('Brown',8,2,'CS');

create table COURSE(
Course_name varchar(50),
Course_number varchar(10),
Credit_hours int(3),
Department varchar(20));

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),
Course_number varchar(10),
Semester varchar(10),
Year varchar(2),
Instructor varchar(20));

insert into SECTION values(85,'MATH2410','Fall','07','King'),(92,'CS1310','Fall','07','Anderson'),(102,'CS3320','Spring','08','Knuth'),(112,'Math2410','Fall','08','Chang'),(119,'CS1310','Fall','08','Anderson'),(135,'CS3380','Fall','08','Stone');

create table GRADE_REPORT(
Student_number int(3),
Section_identifier int(3),
Grade varchar(1));

insert into GRADE_REPORT values(17,112,'B'),(17,119,'C'),(8,85,'A'),(8,92,'A'),(8,102,'B'),(8,135,'A');

create table PREREQUISITE(
Course_number varchar(10),
Prerequisite_number varchar(10));

insert into PREREQUISITE values('CS3380','CS3320'),('CS3380','MATH2410'),('CS3320','CS1310');