Oracle Database Build table + Add data exercise

Tag:arc   key   ima   ons    name     student    operation    har   null   

1--Build Table 2--student table + Note 3 CREATE TABLE student (4 sno VARCHAR2 (3) NOT NULL, 5 sname VARCHAR2 (9) NOT NULL, 6 Ssex VARCHAR2 (3) NOT NULL, 7 Sbirthday date, 8 Sclass varchar2 (5), 9 constraint pk_student p Rimary key (SNO); Comment on column student.sno is ' study number (primary key) '; Comment on column student.sname ' student name '; Comment O  N Column student.ssex is ' student sex '; Comment on column student.sbirthday is ' Student birth date '; Comment on column Student.sclass is ' Student's class ';--course table + Note: Create TABLE course (CNO varchar2 (5) Not null,19 CNAME varchar2 (15)  Not null,20 TNO VARCHAR2 (3) Not null,21 constraint Pk_course primary KEY (CNO), Comment on column Course.cno is ' Course number (primary key) '; Comment on column course.cname is ' course name '; Comment on column Course.tno is ' faculty number (foreign key) ';--s Core table + comments CREATE TABLE score (Sno VARCHAR2 (3) Not null,29 CNO VARCHAR2 (5) Not null,30 Degre      E number (4,1), 31   Constraint Pk_score primary KEY (SNO,CNO), comment on column score.sno is ' study number (primary key) '; Comment on column score.cno Is ' Course number (primary key) '; Comment on column score.degree is ' score ';--teacher table + Annotated PNS CREATE TABLE teacher (3 TNO varchar2 ) Not null,39 tname varchar2 (9) Not null,40 tsex varchar2 (3) not null,41 tbirthday date,42 PR of VARCHAR2 (9), Depart VARCHAR2 (not null,44 constraint Pk_teacher primary KEY (TNO)); Column Teacher.tno is ' staff number (primary key) '; Comment on column teacher.tname is ' faculty name '; Comment on column teacher.tsex is ' Faculty gender '; Comment on column Teacher.tbirthday was ' faculty born '; Comment on column teacher.prof is ' title '; Comment on column teacher. Depart is ' staff unit '; 52--add foreign key to ALTER TABLE course add constraint Fk_tno foreign key (TNO) references teacher (TNO); Table score Add constraint Fk_sno foreign key (SNO) references student (SNO); ALTER TABLE score add constraint Fk_cno fore IGN Key (CNO) referencesCourse (CNO); 56--Add data to--student table student insert into sno,sname,ssex,sbirthday,sclass values (108, ' Zeng Hua ', ' Male ', to_date (' 1977-09-01 ', ' yyyy-mm-dd '), 95033); INSERT into student (Sno,sname,ssex,sbirthday,sclass) VALUES (105, ' Kuanming ', ' Male ', to_ Date (' 1975-10-02 ', ' yyyy-mm-dd '), 95031), insert into student (Sno,sname,ssex,sbirthday,sclass) VALUES (107, ' Wang Li ', ' female ' , To_date (' 1976-01-23 ', ' yyyy-mm-dd '), 95033); INSERT into student (Sno,sname,ssex,sbirthday,sclass) VALUES (101, ' Li June ' , ' Male ', to_date (' 1976-02-20 ', ' yyyy-mm-dd '), 95033); INSERT into student (Sno,sname,ssex,sbirthday,sclass) VALUES (109, ' Wang Fang ', ' Female ', to_date (' 1975-02-10 ', ' yyyy-mm-dd '), 95031); 103, ' contacts ', ' Male ', to_date (' 1974-06-03 ', ' yyyy-mm-dd '), 95031),--teacher table, insert into teacher (Tno,tname,tsex, Tbirthday,prof,depart) VALUES (804, ' sung ', ' Male ', to_date (' 1958/12/02 ', ' yyyy-mm-dd '), ' associate Professor ', ' computer Department '); (Tno,tname,tsex,tbirthday,prof,depart) VALUES (856, ' Zhang Xu ', ' Male ', to_date (' 1969/03/12 ', ' yyyy-mm-dd ')), ' Lecturer ', ' Electronic Engineering Department '; teacher insert into tno,tname,tsex,tbirthday,prof,depart values (825, ' Wang Ping ', ' female ', to_date (' 1972/05/ ', ' yyyy-mm-dd '), ' ta ', ' computer Department '); insert into teacher (Tno,tname,tsex,tbirthday,prof,depart) VALUES (831, ' Liu Bing ', ' female ', to _date (' 1977/08/14 ', ' yyyy-mm-dd '), ' teaching assistant ', ' Electronic Engineering Department ');--course table (Add foreign key to fill in the data in the table to meet the foreign KEY constraints). Insert INTO teacher (course, CNAME,TNO) VALUES (' 3-105 ', ' Introduction to Computers ', 825); INSERT into course (CNO,CNAME,TNO) VALUES (' 3-245 ', ' operating system ', 804); Course (Cno,cname,tno) VALUES (' 6-166 ', ' digital circuit ', 856), course (' 9-888 ', ' advanced mathematics ', 831); 74 --score table (after adding the foreign key to fill in the data in the Student,course table to meet the foreign key constraints), the INSERT INTO score (Sno,cno,degree) VALUES (103, ' 3-245 ', the first); Into score (Sno,cno,degree) VALUES (109, ' 3-245 ', 68), and the insert INTO score (Sno,cno,degree) values (3-245); 78 Insert INTO score (Sno,cno,degree) VALUES (103, ' 3-105 ', (); "INSERT into score (Sno,cno,degree) VALUES (105, ' 3-105 ', 88); Insert INTO score (Sno,cno,degree) VALUES (109, ' 3-105 ', ")," Bayi insert into score (sNo,cno,degree) VALUES (101, ' 3-105 ', ")," the "Sno,cno,degree insert into the score (107 (Sno,cno,degree) VALUES (108, ' 3-105 ', +); INSERT into score (Sno,cno,degree) VALUES (101, ' 6-166 ', +); Score (Sno,cno,degree) VALUES (107, ' 6-166 ', "); INSERT into score (Sno,cno,degree) VALUES (108, ' 6-166 ', 81);

Oracle Database Build table + Add data exercise