数据库实验用表.doc

CREATE DATABASE stu_DB ON PRIMARY (NAME=stu_DBData, FILENAME=F:\管理科学系2008-2009\数据库原理及应用\STU_DB\stu_DB.mbf, SIZE=25MB, MAXSIZE=50MB, FILEGROWTH=2MB) LOG ON (NAME=stu_DBLog, FILENAME=F:\管理科学系2008-2009\数据库原理及应用\STU_DB\stu_DB.lbf, SIZE=10MB, MAXSIZE=20MB, FILEGROWTH=25%) COLLATE Chinese_PRC_CI_AS CREATE TABLE students ( id INT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), major VARCHAR(30), current_credits INT ); INSERT INTO students (id, first_name, last_name, major, current_credits) VALUES (10000, Scott, Smith, Computer Science, 11); INSERT INTO students (id, first_name, last_name, major, current_credits) VALUES (10001, Margaret, Mason, History, 4); INSERT INTO students (id, first_name, last_name, major, current_credits) VALUES (10002, Joanne, Junebug, Computer Science, 8); INSERT INTO students (id, first_name, last_name, major, current_credits) VALUES (10003, Manish, Murgatroid, Economics, 8); INSERT INTO students(id, first_name, last_name, major, current_credits) VALUES (10004, Patrick, Poll, History, 4); INSERT INTO students(id, first_name, last_name, major, current_credits) VALUES (10005, Timothy, Taller, History, 4); INSERT INTO students(id, first_name, last_name, major, current_credits) VALUES (10006, Barbara, Blues, Economics, 7); INSERT INTO students(id, first_name, last_name, major, current_credits) VALUES (10007, David, Dinsmore, Music, 4); INSERT INTO students(id, first_name, last_name, major, current_credits) VALUES (10008, Ester, Elegant, Nutrition, 8); INSERT INTO students(id, first_name, last_name, major, current_credits) VALUES (10009, Rose, Riznit, Music, 7); INSERT INTO STUDENTS(id, first_name, last_name, major, current_credits) VALUES (10010, Rita, Razmataz, Nutrition, 8); INSERT INTO students(id, first_name, last_name, major, current_credits) VALUES (10011, Shay, Shariatpanahy, Computer Science, 3); CREATE TABLE major_stats ( major VARCHAR(30), tot