木其工作室 qq:928900200
Students can submit the same assignment multiple times. All of them are recorded, but only the last one for one assignment is graded. The deadline and timestamp are the number of milliseconds since the epoch. There is an if expression in SQL. It has the format if(condition,value1,value2). If the condition is true, then value1 is the value, otherwise value2 is the value.
create table Assignment (
id int primary key,
name varchar(10) not null unique,
description varchar(255),
topic varchar(1023) not null,
deadline long not null
);
create table Role (
id int primary key auto_increment,
name varchar(255) not null unique
);
create table Person (
id int primary key,
email varchar(255) not null unique,
name varchar(255) not null,
encryptedPassword varchar(255),
role int not null,
foreign key(role) references Role(id)
);
create table Submission (
id int primary key auto_increment,
submittedBy int not null,
foreign key(submittedBy) references Person(id) on update cascade on delete no action,
remoteAddress varchar(1023) not null,
timestamp long not null,
submittedFor int not null,
foreign key(submittedFor) references Assignment(id) on update cascade on delete no action,
filename varchar(1023) not null,
content blob not null,
contentType varchar(255)
);
create table Duty (
submittedBy int not null,
foreign key(submittedBy) references Person(id) on update cascade on delete cascade,
gradedBy int not null,
foreign key(gradedBy) references Person(id) on update cascade on delete no action,
submittedFor int not null,
foreign key(submittedFor) references Assignment(id) on update cascade on delete cascade,
primary key(submittedBy, submittedFor)
);
insert into Role(name) values('student');
insert into Role(name) values('TA');
insert into Role(name) values('instructor');
1. In this course, students receive a bonus if they submit their assignment early and no penalty if they are late, 1 point per hour early. List all students. For each student show the student name and for each assignment show the bonus (as a floating point number).
2. List all students by name who have submitted all of the first 5 assignments on time.
3. A new student named Angel Bose has been added to the course. The Person table has already been updated, and there is only one student with this name. Adjust the Duty table for each assignment so that the TA who is grading the smallest number of students in each assignment will given the duty of grading this new student.
4. A TA named Juan Lee has taken a new job. Explain how to update the tables so as to remove this TA from the database, and equitably redistribute the TA duties to the other TAs. You may use a series of commands.
Express your queries using SQL.
相关推荐
可视化sql查询语句生成器,通过点击选择,生成Sql查询语句
SQL查询语句全
sql查询语句详细实例教程sql查询语句详细实例教程
SQL查询语句,里面的内容很详细,对SQL学习很有用的。
SQL查询语句大全集锦(经典珍藏)SQL查询语句大全集锦(经典珍藏)
SQL查询语句大全SQL查询语句大全SQL查询语句大全SQL查询语句大全SQL查询语句大全SQL查询语句大全SQL查询语句大全SQL查询语句大全SQL查询语句大全
sql_2008_sql查询语句大全.很好用。。适合SQLserver 2008的初学者!!!!!!!
SQL查询语句大全集锦(超经典)
学生成绩管理系统 数据库设计(内含sql查询语句).pdf学生成绩管理系统 数据库设计(内含sql查询语句).pdf学生成绩管理系统 数据库设计(内含sql查询语句).pdf学生成绩管理系统 数据库设计(内含sql查询语句).pdf学生成绩...
SQL查询语句的性能优化与索引,写得还不错,可以看看了
SQL查询语句生成器源代码
学生成绩管理系统_数据库设计(内含sql查询语句) (2).pdf学生成绩管理系统_数据库设计(内含sql查询语句) (2).pdf学生成绩管理系统_数据库设计(内含sql查询语句) (2).pdf学生成绩管理系统_数据库设计(内含sql查询语句)...
最全sql查询语句练习题汇总(面试必备) 最全sql查询语句练习题汇总(面试必备) 最全sql查询语句练习题汇总(面试必备) 最全sql查询语句练习题汇总(面试必备) 最全sql查询语句练习题汇总(面试必备) 最全sql查询语句练习...
SQL查询语句精华使用简要,帮助你初步了解数据库~~~
SQL经典查询语句,sql查询语句,sql常用查询语句 用SQL建表,建库,建试图···等等 sql基本查询,高级查询,牛逼查询 等等···只有你想不到的,没有你不想要的···
SQL查询语句的练习SQL查询语句的练习SQL查询语句的练习SQL查询语句的练习SQL查询语句的练习SQL查询语句的练习SQL查询语句的练习
Oracle常用SQL查询语句,Oracle常用SQL查询语句,Oracle常用SQL查询语句
sql查询语句集合sql查询语句集合sql查询语句集合sql查询语句集合sql查询语句集合sql查询语句集合sql查询语句集合sql查询语句集合