2018.5.19 Comprehensive practice of Oracle data operations and Management tables

--Job one, log in with your own user, complete the following operations, and create 5 test data
--Create a student table (STU) with the following fields:
--School Number (STUID)
--Name (Stuname)
--Gender (Stusex)
--Admission Time (Studate)

CREATE TABLE Stu (
Stuid Number (12),
Stuname varchar (5),
Stusex varchar (2) Check (Stusex in (' Male ', ' female ')),
Studate Date
);

--second, please create a table, the table name is phone, the table structure is as follows
Phone Number (Phonenum VARCHAR2 (8))
Telephone Fee (pay number (8,2))
Number level (Numlevel VARCHAR2 (4))
Cost Date (PayDate varchar2 (12)

CREATE TABLE Phone (
Phonenum VARCHAR2 (8),
Pay number (8,2),
Numlevel VARCHAR2 (4),
PayDate VARCHAR2 (12)
);

1, insert the following two data.
123456, pt04, 20051220
888888, pt05, 20051019

INSERT into phone (phonenum,pay,numlevel,paydate) VALUES (123456,600, ' pt04 ', 20051220);
INSERT into phone (phonenum,pay,numlevel,paydate) VALUES (888888,900, ' pt05 ', 20051019);

2. Create a backup table structure named Phone_bak and insert the data from the phone into the phone_bak.
CREATE TABLE Phone_bak as SELECT * from phone;

3. Change the phone charge of telephone number 123456 to 1000, perform rollback operation
Update phone set paydate=1000 where phonenum=123456;
Rollback

4, in the phone table, if the data in the Phonenum field is ' 123456 ', then return ' Y ' otherwise return ' N '
Select Decode (phonenum, ' 123456 ', ' y ', ' n ') from the phone;

5. Convert the date of the cost date (paydate) field in the phone number 123456 record in the phone table to the YYYY/MM/DD format
Select To_date (paydate, ' yyyy/mm/dd ') from phone where phonenum=123456;
Update phone set paydate=to_date (paydate, ' Yyyy/mm/dd ') where phonenum=123456;
6. Change the number level of phone number 888888 to pt04 and submit
Update phone set numlevel= ' pt04 ' where phonenum=888888;
Commit

--Three, create three database tables that meet the following conditions

--Table name: Card
Function: Store the information of the Internet card
-------------------------------------------------------------------
Column name data type length is empty field description
ID varchar 10 No primary key, no same value allowed
PassWord varchar 50 No password
Balance int 4 is the balance on the card
UserName varchar 50 is the cardholder's name.
-------------------------------------------------------------------
CREATE TABLE Card (
ID varchar (primary key),
PassWord varchar () NOT NULL,
Balance Number (4),
UserName varchar (50)
);

--Add Comment content
Comment on column card.password is ' password ';
Comment on column card.balance is ' card balance ';

--Table name: computer
Function: Store computer and status information
-------------------------------------------------------------------
Column name data type length is empty field description
ID varchar 10 No primary key, no same value allowed
CardID varchar 1 No whether you are using
ComputerId varchar 100 is a note and description information
-------------------------------------------------------------------

CREATE TABLE computer (
ID varchar (primary key),
CardID varchar (1) NOT NULL,
ComputerId varchar (+) NOT NULL
);

Table Name: Record
Function: Store information on a per-machine
-------------------------------------------------------------------
Column name data type length is empty field description
ID Numeric 8 No primary key, no same value allowed
CardID varchar 10 No foreign key, Reference Card table ID field
ComputerId varchar 10 No foreign key, referencing the ID field of the computer table
BeginTime Data 4 is the beginning of the machine between
EndTime Data 4 is the time of the machine
Fee Data 9 is the cost of this machine.
-------------------------------------------------------------------

CREATE TABLE Record (
ID Number (8) is not NULL,
CardID varchar (TEN) is not NULL,
ComputerId varchar (TEN) is not NULL,
BeginTime date,
EndTime date,
Fee Date
);

Foreign KEY (CardID) REFERences Card (ID)
--View constraints
Select Constraint_name,table_name from user_constraints where table_name =upper (' computer ');
--Comment content
Comment on column Record.fee is ' This time machine fee ';
Comment on column record.begintime is ' Start on machine time ';
Comment on column Record.fee is ' This time machine fee ';
Comment on column record.endtime is ' down machine time ';

After the table is created, the constraints are written and enforced, with the following requirements:
1. Establish the primary foreign key relationship (referential integrity constraint) for the Cardid and ComputerId fields of the record table, respectively, with the card table and the computer table.

ALTER TABLE Record add constraint fk_cardid foreign key (CardID) references computer (ID);

2, card table, the balance of cards can not exceed 1000
ALTER TABLE Card add constraint ck_balance check (balance<=1000);

3, Computer table, Onuse can only be 0 or 1
ALTER TABLE computer Add (onuse number (2));
ALTER TABLE computer add constraint ck_onuse check (onuse in (0,1));
4, the record table, Endtime can not be earlier than begintime (wrong)
ALTER TABLE computer add constraint ck_time check (to_date (EndTime, ' yyyy-mm-dd hh24:mi:ss ') >to_date (BeginTime, ' Yyyy-mm-dd hh24:mi:ss '));

--Job 02 modifying fields
1. Add an Age field (Stuage) for the Stu table, type (number (8)), and a uniform assignment of 30.
ALTER TABLE Stu Add (stuage number (8) default (30));

2. Modify the Stuname field (VARCHAR2 (20))
ALTER TABLE Stu Modify (stuname varchar (20));

3. Delete studate field
ALTER TABLE Stu drop studate;

--Job 04-1 adding constraints
To create a class table (clsses), the fields contain:
"Class number (CID), class name (CNAME)"

CREATE TABLE Classes (
CID Number (8),
CNAME VARCHAR2 (20)
);

The requirements are as follows:
1, for "class number Add" and the primary KEY constraints
ALTER TABLE CLASSES ADD constraint Pk_cid primary key (CID);

2. Add a non-empty constraint for class name
ALTER TABLE classes modify (CNAME not NULL);

3. Insert 3 test data
Insert into classes VALUES (1001, ' Blue Bridge 1 classes ');
Insert into classes VALUES (1002, ' Blue Bridge 2 classes ');
Insert into classes values (1003, ' Blue Bridge 3 classes ');

To create a student table (Stus), the fields contain:
"Student number (STUID), name (Stuname), Gender (Stusex), age (stuage), Enrollment Time (studate), class number (CID)"

CREATE TABLE Stus (
Stuid Number (8),--Study No.
Stuname varchar2 (20),--Name
Stusex char (2),--gender
Stuage Number (8),--age
Studate date,--Admission time
CID Number (8)--Class No.
);

The requirements are as follows:
1. Add a PRIMARY KEY constraint for Stuid
ALTER TABLE Stus ADD constraint Py_stuid primary key (STUID);

2. Add non-null constraints for Stuname
ALTER TABLE Stus Modify (stuname not NULL);

3. Add default values for Stusex
ALTER TABLE Stus Modify (Stusex char (4));--Modified length
ALTER TABLE Stus Modify (stusex default ' male ');

4. Add check constraint for Stuage 0-100
ALTER TABLE Stus add constraints ck_stuage Check (stuage>=0 and stuage<=100);

5. Add uniqueness Constraints for Studate
ALTER TABLE Stus add constraint un_studate unique (studate);

6. Adding FOREIGN KEY constraints for CID
ALTER TABLE Stus add constraint fk_cid foreign key (CID) references classes (CID) on DELETE cascade; --Add foreign keys
Select Constraint_name,table_name from user_constraints where table_name =upper (' Stus ');--View constraints
ALTER TABLE Stus drop CONSTRAINT fk_cid; --delete foreign KEY constraint

7. Add 5 test data
INSERT into Stus values (1801, ' Ben ', ' Male ', 21,to_date (' 2015/5/19 ', ' Yyyy-mm-dd '), 1001);
INSERT into Stus values (1800, ' BenQ ', ' Female ', 19,to_date (' 2017/2/29 ', ' yyyy-mm-dd '), 1003);
INSERT into Stus values (1802, ' Chen ', ' Male ', 22,to_date (' 2018/6/22 ', ' yyyy-mm-dd '), 1002);
INSERT into Stus values (1803, ' Legend ', ' Male ', 25,to_date (' 2014/8/19 ', ' yyyy-mm-dd '), 1003);
INSERT into Stus values (1804, ' Wave ', ' Female ', 21,to_date (' 2016/5/19 ', ' yyyy-mm-dd '), 1002);
INSERT into Stus values (1805, ' QQ ', ' Female ', 20,to_date (' 2016/7/21 ', ' yyyy-mm-dd '), 1003);

--04-2 Restraint Exercises
1, create the table Emp1, the fields are as follows
Eno char (3),
Ename char (6),
Sex char (2),
Age Number (2),
DNO char (3)

CREATE TABLE EMP1 (
Eno char (3),
Ename char (6),
Sex char (2),
Age number,
DNO char (3)
);

2, and insert the following data
1, TOM, Male, 21, ' 001 '
2, JERRY, Male, 21, ' 002 '
3, KATE, Female, 21, ' 003 '
4, MARY, Female, 21, ' 004 '
5, JACK, Male, 21, ' 005 '

INSERT into EMP1 values (1, ' TOM ', ' Male ', 21,001);
INSERT into EMP1 values (2, ' JERRY ', ' Male ', 21, ' 002 ');
INSERT into EMP1 values (3, ' KATE ', ' female ', 21,003);
INSERT into EMP1 values (4, ' MARY ', ' female ', 21, ' 004 ');
INSERT into EMP1 values (5, ' JACK ', ' Male ', 21, ' 005 ');

3. Create a PRIMARY KEY constraint on the ENO field
ALTER TABLE EMP1 add constraint pk_ Eno primary key (ENO);

4. Create a non-null constraint on the ename field
ALTER TABLE EMP1 Modify (ename not NULL);

5. Create a CHECK constraint to determine the age of 18-60-year-old male or female aged between 18-55 years
ALTER TABLE EMP1 add constraint ck_age check ((sex= ' Male ') and (age between) or (sex= ' woman ' and age between and 55) );

INSERT into EMP1 values (666, ' Legend ', ' Male ', 20,100);--Test data
INSERT into EMP1 values ("Lily", ' female ', 120,100);

6. Create a Uniqueness constraint on the DNO field
ALTER TABLE EMP1 add constraint Un_dno unique (DNO);

7. Create a Emp_bak table with the same field as the Emp1 table, and create a foreign key constraint for the ENO fields of Emp_bak tables and the ENO fields of the EMP table
CREATE TABLE Emp_bak AD select * from EMP1;
ALTER TABLE EMP1 ADD constraint Fk_emo foreign key Emp_bak (ENO) references Emp1 (ENO);

--Job 04-3 adding constraints
To create a clsses table:
CREATE TABLE Classes
(
CID Number (8),
CNAME VARCHAR2 (20)
);
Add a PRIMARY KEY constraint to the CID

ALTER TABLE CLASSES ADD constraint Pk_cid primary key (CID);

To create a stus table:
CREATE TABLE Stus (
Stuid Number (8),--Study No.
Stuname varchar2 (20),--Name
Stusex char (2),--gender
Stuage Number (8),--age
Studate date,--Admission time
CID Number (8)
);

Add a PRIMARY KEY constraint for Stuid
ALTER TABLE Stus ADD constraint Pk_stuid primary key (STUID);

Add a non-null constraint for Stuname
ALTER TABLE Stus Modify (stuname not NULL);

Add a default value for Stusex
ALTER TABLE Stus Modify (stusex dafault ' female ');

To add a check constraint for stuage 0-100
ALTER TABLE Stus add constraint ck_stuage check (stuage between 0 and 100);

Add a Uniqueness constraint for studate
ALTER TABLE Stus add constraint un_studate unique (studate);

Add a FOREIGN KEY constraint to the CID
ALTER TABLE Stus add constraint fk_cid foreign key (CID) reference classes (CID);

2018.5.19 Comprehensive practice of Oracle data operations and Management tables