数据库习题（Database exercises）.doc

数据库习题（Database exercises） Experiment 1, two 1) the student table adds one attribute nation (ethnic), and the data type is varchar (20). 2) delete the new attribute Nation in the student table. 3) create an index called IX_sname for the students and sort them by name. 4) delete the IX_sname index. Create database student_course (created in enterprise manager called student_course database) Use student_course Create table student (SnO, char (9), primary, key, not, null, Sname varchar (20) unique not null, Ssex char (2) not null, Sage smallint not null, Sdept char (5) not null ); Create table course (CNO, char (9), primary, key, not, null, CNAME varchar (20) unique not null, Cpno char (9) not null, Ccrebit smallint ); Create table SC (SnO char (7) not null, CNO char (9) not null, Grade numeric (4,1) ); Insert Into, student (SnO, sname, ssex, sage, sdept) Values (0215125, Chang Li , male ,19,is); Select * from student Insert Into, course (CNO, CNAME, cpno, ccrebit) Values (3, ` information systems,1,4); Select * from course Insert Into, SC (SnO, CNO, grade) Values (1015121,2,85); Select * from SC Alter, table, student, add, snation, varchar (20); Select * from student Alter, table, student, drop, column, snation; Select, SnO, sname From student Select * from student (creating indexes) create, index, IX_sname, on, student (sname); Drop index student.IX_sname; Experiment three Content 1. 1. inquire about the authors number, name and information of all authors 2. query the names of all authors, the authors information, and display the string ID number in front of each authors author number, which indicates that the information displayed is identity information 3. query author, name and city in CA state 4. query the title and date of publication between 1992.1.1-2000.12.31 (query book published in 1991) 5. inquire about the books published by each publisher 6. inquire about the number of books sold by a store 7. inquire about all book information with sales records, includi