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

Database Experiment Two

1. Create a database

--**************************--*** 1. Building a Database SC * * *--************************** Create db SC Go------------------- -----------using, locating the database SC----------------------------use SC go

2. Create a data table

 --**************************--*** 2. Creating Data Tables * * *--**************************------------------------------1) Creating table Student----------------------------CREATE TABLE Student (Sno char (9) Primary key,/*sno is the main code column level integrity constraint entity integrity */Sname C Har (n) unique,/*sname unique value, do not repeat */Ssex char (2), Sage smallint,/* type is smallint*/sdept char (20)///In the department */); /*; add */------------------------------2) CREATE TABLE Course----------------------------creation Table Course (Cno char (4) Primary key,/* column-level integrity constraints, CNO is the main code */Cname char, Cpno char (4),/*cpno means First class */Ccredit smallint, foreign key (CPNO) refer Ences Course (Cno)/* table-level integrity constraints, CPNO is a foreign code, the reference table is Course, the reference column is cno*/); ----------------------------------------------------------3) CREATE TABLE SC----------------------------CREATE TABLE SC (Sno char (9), Cno char (4), Grade smallint, primary key (SNO,CNO),/* Main code has two attributes, must be defined as table-level integrity */foreign KEY (Sno) references Stud ENT (SNO),/* table-level integrity constraints, Sno is a foreign code, the referenced table is student*/foreign key (Cno) references Course (Cno)/* table-level integrity constraints, Cno is a foreign code, referenced to indicate course*/ ); ----------------------------Go

3. Insert Data Sheet

 --**************************--*** 3. Inserting data * * *--**************************-------------------------------1) Insert Student data----------------------------INSERT into Student values (' 200215121 ', ' Li Yong ', ' Male ', ' CS ')/* insert a tuple into the table Student */INSERT INTO Student values (' 200215122 ', ' Liu Chen ', ' female ', ' is ')/* * Insert a tuple into table Student */INSERT INTO Student values (' 200215123 ' , ' Wang Min ', ' female ', ' MA ')/* insert a tuple into table Student */INSERT INTO Student values (' 200215125 ', ' Zhang Li ', ' Male ', ' is ')/*/insert a tuple into table Student * /-----------------------------------------------------------2) Insert course data------------------------------ Attention Order (pre-class issue) Note add an empty element with null INSERT INTO Course values (' 2 ', ' math ', null,2)/* Insert a tuple into table Course/INSERT INTO Course values (' 6 ', ' Data processing ', null,2)/* Insert a tuple into table Course */INSERT INTO Course values (' 4 ', ' Operating system ', ' 6 ', 3)/* Insert a tuple into table Course */inserts into Course VALUES (' 7 ', ' Pascal language ', ' 6 ', 4)/* Insert a tuple into table Course */INSERT INTO Course values (' 5 ', ' data structure ', ' 7 ', 4)/* Insert a tuple into table Course */ INSERT into Course values (' 1 ', ' Database ', ' 5 ', 4)/* Inserts a tuple into table Course */INSERT INTO Course VALUES (' 3 ', ' Information system ', ' 1 ', 4)/* Insert a tuple into table course */-----------------------------------------------------------3) Insert SC data ----------------------------INSERT INTO SC values (' 200215121 ', ' 1 ', 92)/* Inserts 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)/* Inserts a tuple into the table SC */INSERT INTO SC values (' 200215122 ', ' 2 ', 90)/* Inserts a tuple into the table SC */INSERT INTO SC values (' 200215122 ', ' 3 ', 80)/* Inserts a tuple into the table SC */------------------------ ----

4. Query Data Sheet

--**************************--*** 4. Querying 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 order by course name, result desc----------------------------