《数据库系统系统原理》实验课报告.docx

1、 Find the name, loan number and loan amount of all customers; rename the column name loan_number as loan_id. 2、 Find the names of all customers whose street includes the substring “Main”. 3、Find all customers who have a loan, an account, or both: 4、Find all customers who have both a loan and an account. 5、Find all customers who have an account but no loan. 6、Find the average account balance at the Perryridge branch. 7、 Find the number of tuples in the customer relation. 8、 Find the number of depositors in the bank. 9、 Find the number of depositors for each branch. 10、Find the names of all branches where the average account balance is more than $1,200. 11、Find all loan number which appear in the loan relation with null values for amount. 12、Find all customers who have both an account and a loan at the bank. 13、Find all customers who have a loan at the bank but do not have an account at the bank 14、Find all customers who have both an account and a loan at the Perryridge branch 15、Find all branches that have greater assets than some branch located in Brooklyn. 16、Find the names of all branches that have greater assets than all branches located in 1、创建一个School数据库，该数据库的主数据文件逻辑名称为SCHOOL_data，物理文件名为School.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为School_log，物理文件名为School.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。 2、用SQL语句建立上述表，自定义主键和外键，对于student表建立约束条件：ssex仅能取male或female；sage在18和22之间。并完成下面的查询语句。 1、查询所有选修过“Bibliometrics”课的学生的姓名和成绩； 2、查询考试成绩不及格的学生的个数； 3、查询名字中至少含有一个“z”字符的学生的姓名、学号和性别； 4、查询选修了“Introduction to the Internet”课程的学生的学号及其成绩，查询结果按分数的降序排列； 5、查询“Zuo li”同学选修课程的总学时(time)数 6、查询年龄不大于20岁的学生的平均考试成绩； 7、查询 “computer science”专业学生选修 “Database System”的人数； 8、查询同时选修课程“Database System”和“Introduction to the Internet”的学生姓名； 9、查询选修的课程中含有“Wang gang”同学所有选修课程的学生姓名。 10、查询“Information Technology for Information Management”考试成绩为空的学生姓名及专业名称。 11、查询“computer science”专业学生每个人的选修课总学分。 12、查询个人考试平均成绩高于专业平均成绩的学生姓名 13、查询个人考试平均成绩高于女生平均成绩的男生姓名 14、查询比“computer science”专业所有学生年龄都大的学生姓名。 15、查询考试成绩仅有一科不及格学生姓名