1. Create a table named student in the oracle database, requiring: (sno char(6) not null, sname char(10) not null, ssex char(2) not null, birthday date not null, polity char(20), sdept char(20)), where the fields in the table satisfy: sno is set as the primary key, and the sname field is set as a unique constraint

create table student
(sno char(6) primary key not null,
sname char(10) unique not null, 
ssex char(2) not null, 
birthday date not null, 
polity char(20),
sdept char(20)
);

2. Set the check constraint on the ssex of the student table, requiring that the ssex can only be set for 'M' or 'F', and the default constraint is set for the polity field, and the value is 'mass'

alter table student add constraint
ssex_check check(ssex='M' or ssex='F');

alter table student modify polity default 'the masses';

3. Create a table named course2 in oracle database (cno, cname, teacher, class)

create table course2
(cno char(6),
cname varchar(10),
teacher varchar (10),
class varchar(10)
);

4. Create a student selection table sc (sno char(6) not null,cno char(10) not null,grade real). Create a foreign key constraint on the sc table, and associate the sno of the sc table with the sno of the student table. Here Create a constraint relationship between the two tables.

create table sc(
sno char(6) not null,
cno char(10) not null,
grade real,
constraint sc_sno_student_sno foreign key (sno) references student(sno)
);

5. Use the insert statement to insert a new record into the student table: ('0007', 'Zhang San', 'M', to_date('1982-3-21', 'yyyy-mm-dd'), 'member ','computer science')

insert into student values
('0007','Zhang San','M',to_date('1982-3-21','yyyy-mm-dd'), 'member','computer science');

6. Use the update statement to change the student polity numbered '0007' to 'party member'

update student
set polity = 'party member'
where sno = '0007';

7. Use the delete statement to delete the records of students whose English scores are greater than 90

delete from sc where cno in
(select cno from course2
where cname ='English') and grade>90;

9. Create a view with data for exam scores of software engineering students

conn sys ;

create view se_grade
as
select sname,grade from scott.student,scott.sc
where sdept='software engineering';

12. Create a Users table with the following table, design the structure of the table, and the UserType field must be greater than 0 and less than 3, the default value of the UserPwd field is 111111

conn scott ;

create table Users
(UserId number,
UserName varchar2(40),
UserType number(1),
UserPwd varchar2(40) default(111111)
);

comment on column Users.UserId is 'user ID';

comment on column Users.UserName is 'username';

comment on column Users.UserType is 'User type (1 for administrative users, 2 for ordinary users)';

comment on column Users.UserPwd is 'password';

13. Create a sequence USER_S, which is an integer between 1 and 1000, automatically incremented by 1. Use this sequence to insert 2 new records into table USERS

create sequence USER_S
increment by 1
start with 1
minvalue 1
maxvalue 1000;

insert into Users values(USER_S.nextval,USER_S.nextval,USER_S.nextval,USER_S.nextval);
insert into Users values(USER_S.nextval,USER_S.nextval,USER_S.nextval,USER_S.nextval);
select * from Users;

#The table is not displayed in one row, the following two parameters can be modified
#set linesize 200;
#set pagesize 200;

14. Example of this experiment guide: student achievement management project.
Instance requirements: In this project, regardless of the requirements analysis process, the following relationships are required:
Department information: department number, department name, department manager, address, number of people in the department;
Basic student information: department number, student number, name, gender, student type, enrollment date, place of origin, ID number, detailed address, date of birth, major, nationality, phone number (home), total payment, remarks;
Course information: course number, course name, opening grade, number of hours;
Transcript: student number, course number, semester, grades, remarks.

create table Department Information
(Department Number varchar(50),
Department name varchar(50),
Department manager varchar(50),
address varchar(50),
Number of Departments varchar(50)
);

create table Basic information for students
(Department number varchar(50),
student ID varchar(50),
Name varchar(50),
gender varchar (50),
student type varchar(50),
Admission date date,
Hometown varchar(50),
identity number varchar(50),
Address varchar(50),
date of birth date,
Major varchar(50),
nationality varchar(50),
phone home varchar(50),
total payment varchar(50),
Remark varchar(50)
);

create table course
(Course number varchar(50),
Course Title varchar(50),
open grade varchar(50),
number of hours varchar(50)
);

create table Transcript
(student ID varchar(50),
Course number varchar(50),
Semester grades varchar(50),
Remark varchar(50)
);