Introduction to Databases (Experiment II) database and table operations __ Database

Database Experiment Two

1. Create a database

--**************************--*** 1. Set Up Database SC * * * *--************************** Create db SC Go------------------- -----------use, locate database sc----------------------------usage sc go

2. Create data table

 --**************************--*** 2. Create Datasheet * * * *--**************************------------------------------1) Creating a table Student----------------------------create TABLE Student (Sno char (9) Primary key,/*sno is the primary code column level integrity constraint entity integrity/sname C Har (a) unique,/*sname unique value, do not repeat/Ssex char (2), Sage smallint,/* type smallint*/sdept char (20)/* in the department * *; //* To add/*------------------------------2) CREATE table Course----------------------------creating Tables Course (Cno char (4) Primary key,/* column-level integrity constraints, CNO is the main code/Cname char (4),/*cpno meaning is the first class * * ccredit smallint, foreign key (CPNO) refer Ences Course (Cno)/* table-level integrity constraints, CPNO is the outer code, the referenced table is Course, the referenced column is cno*/); ----------------------------------------------------------3) to create a table SC----------------------------creating table SC (Sno char (9), Cno char (4), Grade smallint, primary key (SNO,CNO),/* Main code has two properties, must be defined as table-level integrity/foreign KEY (Sno) references Stud ENT (SNO),/* table-level integrity constraints, Sno is a foreign code, referenced table is student*/foreign key (Cno) references Course (Cno)/table-level integrity constraints, CNO is the outer code, referred to as the course*/ ); ----------------------------Go

3, insert data table

 --**************************--*** 3. Insert Data * * *--**************************-------------------------------1) Insert Student data----------------------------INSERT into Student values (' 200215121 ', ' Li Yong ', ' Men ', ', ' CS ') * * insert a tuple into the table Student /INSERT into Student values (' 200215122 ', ' Liu Chen ', ' female ', ' is ')/* insert a tuple into the table Student */INSERT INTO Student values (' 200215123 ' , ' wangmin ', ' female ', ' MA ')/* insert a tuple into the table Student/INSERT INTO Student values (' 200215125 ', ' zhangli ', ' Male ', ' is ')/* insert a tuple into table Student * /-----------------------------------------------------------2) Insert course data------------------------------ Note Order (antecedent problem) Note Add an empty element with null INSERT INTO Course values (' 2 ', ' math ', null,2)/* Insert a tuple into the table Course/INSERT INTO Course values (' 6 ', ' Data processing ', null,2/* Insert a tuple into the table Course/INSERT INTO Course values (' 4 ', ' OS ', ' 6 ', 3)/* Insert a tuple into the table Course/INSERT INTO Course VALUES (' 7 ', ' Pascal language ', ' 6 ', 4)/* Insert a tuple into the table Course/INSERT INTO Course values (' 5 ', ' data structure ', ' 7 ', 4)/* Insert a tuple into the table Course * * INSERT into Course values (' 1 ', ' Database ', ' 5 ', 4)/* Insert a tuple into the table Course */INSERT INTO Course VALUES (' 3 ', ' Information system ', ' 1 ', 4)/* Insert a tuple/-----------------------------------------------------------3 into the table course to insert SC data ----------------------------inserts into SC values (' 200215121 ', ' 1 ', 92)/* Insert a tuple into the table SC/INSERT INTO SC values (' 200215121 ', ' 2 ', 85) * * Insert a tuple into the table SC/INSERT INTO SC values (' 200215121 ', ' 3 ', 88)/* Insert a tuple into the table SC/INSERT INTO SC values (' 200215122 ', ' 2 ', 90)/* Insert a tuple into the table SC */INSERT INTO SC values (' 200215122 ', ' 3 ', 80)/* Insert a tuple into the table SC * *------------------------ ----

4. Query data table

--**************************--*** 4. Query data * * *--**************************----------------------------SELECT * FROM Student SELECT * FROM Course SELECT * FROM SC Select Student.sno as number, sname as name, CNAME as course name, grade as score from Student,c OURSE,SC where Student.sno=sc.sno and Course.cno=sc.cno by course name, results desc----------------------------