Oracle Basic Build Table statement

--Create User
Create user Han identified by Han default tablespace
Users temporary tablespace Temp;
Grant Connect,resource,dba to Han; Grant users the right to Han developers

--------------------the operation of the table--------------------------
--Create a table
CREATE TABLE Classes (
ID Number (9) NOT null primary key,
ClassName VARCHAR2 (+) NOT NULL
)    
--Enquiry Form
SELECT * from Classes;

--Delete Table
drop table students;

--Modify the name of the table
Rename Alist_table_copy to alist_table;

--Display table structure
Describe test--no, not found.

-----------------------The operation of the field-----------------------------------
--Add columns
ALTER TABLE test add address varchar2 (40);

--Delete Columns
ALTER TABLE test drop column address;

--Modify the name of the column
ALTER TABLE test Modify address addresses varchar (40);

--Modifying the properties of a column
ALTER TABLE Test Modi

CREATE TABLE Test1 (
ID Number (9) primary key NOT NULL,
Name Varchar2 (34)
)
Rename test2 to test;

--Create an auto-increment sequence
Create sequence Class_seq increment by 1 start with 1 MAXVALUE 999999 nocycle NOCACHE;

Select Class_seq.currval from dual

--Inserting data
Insert into classes values (Class_seq.nextval, ' software class ')
Commit

--Update data
Update Stu_account set username= ' AAA ' where count_id=2;
Commit

--Create a unique index
Create unique index username on stu_account (username); --The unique index cannot insert the same data

--row locks cannot operate on this row in a newly opened conversation
SELECT * from Stu_account t where t.count_id=2 for update; --Line Lock

--alter Table Stuinfo Modify sty_id to stu_id;

ALTER TABLE students drop constraint CLASS_FK;
ALTER TABLE students add constraint CLASS_FK foreign key (class_id) references classes (ID);--FOREIGN KEY constraint
ALTER TABLE STUINFO add constraint STU_FK foreign key (stu_id) references students (ID) on delete cascade;--foreign KEY constraint, cascade delete

ALTER TABLE Stuinfo drop constant STU_FK;

INSERT into students values (Stu_seq.nextval, ' Zhang San ', 1,sysdate);

INSERT into stuinfo values (stu_seq.currval, ' Weihai ');

SELECT * from Stuinfo;

CREATE TABLE Zhuce (
ZC_ID Number (9) NOT null primary key,
STU_ID Number (9) is not NULL,
Zhucetime Date Default Sysdate

)

CREATE TABLE Feiyong (
FY_ID Number (9) NOT null primary key,
STU_ID Number (9) is not NULL,
MX_ID Number (9) is not NULL,
Yijiao Number (7,2) is not null default 0,
Qianfei number (7,2) NOT NULL

)

Create Talbe Fymingxi (
MX_ID Number (9) NOT null primary key,
Feiyong number (7,2) not NULL,//A total of 7 digits, two digits after decimal
CLASS_ID Number (9) NOT NULL
}

CREATE TABLE Card (
CARD_ID Number (9) primary key,
STU_ID Number (9) is not NULL,
Money Number (7,2) is not NULL for default 0,
Status number (1) NOT NULL default 0--0 table available, 1 table lost
)

--linked list query

Select C.classname| | ' _ ' | | S.stu_name as Class _ name, si.address from classes c,students s, Stuinfo si where c.id=s.class_id and s.id=si.stu_id;
INSERT into students values (Stu_seq.nextval, ' John Doe ', 1,sysdate);
INSERT into stuinfo values (stu_seq.currval, ' Nanjing ');

--function
Select Rownum,id,stu_name from students T order by ID ASC;

--Intermediate tables for many-to-many associations
--(1 1, 1 n,n 1,n N)

--1 N Description 1 table without processing n table with 1 table field
--1 1 describes the primary foreign Key association
--n n describes the intermediate table for many-to-many associations

CREATE TABLE Course (
COURSE_ID Number (9) is not NULL,
Couser_name VARCHAR2 (+) NOT NULL
)
ALTER TABLE course to couse;
CREATE TABLE Stu_couse (
STU_COUSE_ID Number (9) primary key,
STU_ID Number (9) is not NULL,
COUSE_ID Number (9) NOT NULL

)

Create unique index stu_couse_unq on stu_couse (stu_id,couse_id); --The only student
Create sequence Stu_couse_seq increment by 1 start with 1 MAXVALUE 999999 nocycle NOCACHE;

Create sequence Couses_seq increment by 1 start with 1 MAXVALUE 999999 nocycle NOCACHE;
Insert into course values (couses_seq.nextval, ' Principles of computer ');
Insert into course values (couses_seq.nextval, ' compiling principle ');
Insert into course values (couses_seq.nextval, ' database principle ');
Insert into course values (couses_seq.nextval, ' data structure ');
Insert into course values (couses_seq.nextval, ' Computer Basics ');
Insert into course values (Couses_seq.nextval, ' C-language preliminary ');
Commit

INSERT into stu_couse values (stu_couse_seq.nextval,1,1);
INSERT into stu_couse values (stu_couse_seq.nextval,1,3);
INSERT into stu_couse values (stu_couse_seq.nextval,1,5);
INSERT into stu_couse values (stu_couse_seq.nextval,1,5);

INSERT into stu_couse values (stu_couse_seq.nextval,2,1);
Commit
SELECT * from Stu_couse;
SELECT * from course;

--select s.stu_name,sc.couse_id, c.couser_name from students S,course C,stu_couse SC where stu_id=1

--select couse_id from Stu_couse where stu_id=1

Select Cl.classname,s.stu_name,c.couser_name from Stu_couse SC, students s,course c,classes CL where s.id=sc.stu_id and SC . couse_id=c.course_id and S.class_id=cl.id and s.id=1;

--Class--Name
Select C.classname,s.stu_name from Students s,classes C where s.class_id=c.id and s.id=2;

SELECT * from students s where s.id=2
--Class--name--course

Select Cl.classname,s.stu_name,c.couse_name from Stu_couse sc,students s,classes cl,couse C where sc.stu_id=s.id and SC.C ouse_id=c.couse_id and s.id=26;

--sql statement, now write out the associated table, then write out the field to find, third write out the association condition, remember to write the table associated to the first data table, which helps to improve the efficiency of SQL

Select C.couser_name,s.stu_name from Stu_couse sc,students s,course C where c.course_id=1 and c.course_id=sc.couse_id and Sc.stu_id=s.id;

Select S.stu_name from Students s,stu_couse SC where s.id=sc.stu_id GROUP by S.id,s.stu_name;

Select C.classname,count (sc.couse_id) from Stu_couse sc,students s,classes C where s.class_id=c.id and s.id=sc.stu_id Gro Up by C.classname;

Select S.stu_name, COUNT (sc.couse_id) from Stu_couse sc,students s,classes cl where s.id=sc.stu_id GROUP by S.id,s.stu_nam E having count (sc.stu_couse_id) >3;
Number of classes selected by class students
Select Cl.classname,count (sc.stu_couse_id) from Stu_couse sc,students s,classes cl where s.id=sc.stu_id and s.class_id= Cl.id GROUP BY Cl.classname;

--Number of classes selected by class students
Select Cl.classname,s.stu_name,count (sc.stu_couse_id) from Stu_couse sc,students s,classes CL where s.id=sc.stu_id and S . class_id=cl.id GROUP BY S.stu_name;

Select Cl.classname,s.stu_name,count (sc.stu_couse_id) from Stu_couse SC, students s,classes CL where sc.stu_id=s.id and S . class_id=cl.id GROUP BY S.id;

Select Cl.classname,s.stu_name,count (sc.stu_couse_id) from Stu_couse sc,students s,classes CL where sc.stu_id=s.id and S . class_id=cl.id GROUP BY S.stu_name;
--Class student selected Course ID selected course Name

--Create an attempt to federate a table and then view it as a table, in conjunction with other queries
CREATE View xsxk as Select Cl.classname, s.stu_name,c.couse_id, c.couse_name from Stu_couse sc,students s,classes cl,couse C where Sc.stu_id=s.id and sc.couse_id=c.couse_id and s.class_id=cl.id;

SELECT * FROM Xsxk

CREATE View Classstu as select S.id,c.classname,s.stu_name from students s,classes C where c.id=s.class_id;
Drop View Classstu; --Delete View
SELECT * from Classstu;
CREATE View Stu_couse_view as select S.id, c.couse_name from Stu_couse sc,students s,couse C where s.id=sc.stu_id and SC.C ouse_id=c.couse_id;
SELECT * from Stu_couse_view;
CREATE View CSC as select Cs.classname,cs.stu_name,scv.couse_name from Classstu cs,stu_couse_view SCV where cs.id=scv.id;
SELECT * from CSC;

SELECT * FROM classes cross join students; --full connection, equivalent to select * from Classes,students;

SELECT * FROM classes CL left joins students s on cl.id=s.class_id; --left connection whether or not the left table is displayed
SELECT * FROM classes CL Right joins students s on cl.id=s.class_id; --Right connection
SELECT * FROM classes CL full join students s on cl.id=s.class_id; --Full connection

Insert into classes values (Class_seq.nextval, ' software Class four ');

CREATE TABLE Sales (
Nian VARCHAR2 (4),
Yeji Number (5)

);
INSERT into sales values (' 2001 ', 200);
INSERT into sales values (' 2002 ', 300);
INSERT into sales values (' 2003 ', 400);
INSERT into sales values (' 2004 ', 500);
Commit
SELECT * from sales;
drop table Sale;

Select S1.nian,sum (S2.yeji) from the sales s1,sales S2 where S1.nian>=s2.nian GROUP by S1.nian ORDER by S1.nian Desc;

Select S1.nian,sum (S2.yeji) from the sales s1,sales S2 where S1.nian>=s2.nian Group by S1.nian;

S
Total annual results
2001 200
2002 500
2003 900
2004 1400

CREATE TABLE Test1 (
T_ID Number (4)
);

CREATE TABLE Org (
ORG_ID Number (9) NOT null primary key,
Org_name VARCHAR2 (+) NOT NULL,
PARENT_ID Number (9)
);

Create sequence Org_seq increment by 1 start with 1 MAXVALUE 999999 nocycle NOCACHE;
Drop sequence org_seq;
INSERT into org values (1, ' Huajian Group ', 0);
INSERT into org values (2, ' Huajian Group One branch ', 1);
INSERT into org values (3, ' Huajian Group Two branch ', 1);
INSERT into org values (4, ' Hua Jian Group Finance Department ', 1);
INSERT into org values (5, ' Huajian Group Engineering Department ', 1);
INSERT into org values (6, ' Huajian Group, a corporate finance office ', 2);
INSERT into org values (7, ' Huajian Group Branch Engineering Office ', 2);

select * from org;
--incorrect inability to implement loops
Select b.org_id, B.org_name, b.parent_id from org a,org b where a.org_id=7 and a.parent_id=b.org_id;
SELECT * from org connect to prior parent_id=org_id start with org_id=7 order by org_id;
SELECT * from org connect to prior org_id=parent_id start with org_id=1 order by org_id;

CREATE TABLE Chengji (
       cj_id Number (9) NOT null primary key,
        stu_cou_id Number (9) NOT NULL,
       fen number (4,1)
       
);
INSERT into Chengji values (1,1,62);
INSERT into Chengji values (2,2,90);
INSERT into Chengji values (3,3,85);
INSERT into Chengji values (4,4,45);
INSERT into Chengji values (5,5,68);
INSERT into Chengji values (6,6,87);
Commit;
Select * from Chengji;
Select * from Stu_couse;
--does not appear to be applicable in Oracle ALTER TABLE Chengji change stu_cou_id stu_couse_id;alter table shop_jb change Price1 price double;

Average student name Score
Select S.stu_name,avg (Cj.fen) from Stu_couse Sc,chengji cj,students s where s.id=sc.stu_id and sc.stu_couse_id=cj.stu_ COUSE_ID GROUP BY S.id,s.stu_name;
Select S.stu_name from students s,stu_couse Sc,chengji CJ where s.id=sc.stu_id and sc.stu_couse_id=cj.stu_couse_id Group B Y s.id,s.stu_name;
Select S.stu_name,cj.fen from students s,stu_couse Sc,chengji CJ where s.id=sc.stu_id and Sc.stu_couse_id=cj.stu_couse_ ID and cj.fen>60;

Student Name Subject Score
Select S.stu_name,c.couse_name,cj.fen from Stu_couse sc,students s,couse C,chengji CJ where Sc.stu_id=s.id and Sc.couse_i d=c.couse_id and sc.stu_couse_id=cj.stu_couse_id and cj.fen>60 order by=;

SELECT * from Stu_couse;

--Set operation
--The student who chose Course 3 Union selected the student of course 5 and set
--Students who have chosen course 3 or who have chosen course 5
Select S.stu_name from students s,couse C,stu_couse SC where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=3
Union
Select S.stu_name from students s,couse C,stu_couse SC where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=5

--the intersection of students who chose the course 3,5,2 intersect to choose the course 1,2,4
--the intersection of students who have chosen course 2 and selected course 3
Select S.stu_name from students s,couse C,stu_couse SC where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=2
Intersect
Select S.stu_name from students s,couse C,stu_couse SC where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=3;

--The student who chose the course 3,5,8 minus the students who chose the course 1,7,8--difference set
----------for students of all courses with a score greater than 60
SELECT DISTINCT (s.stu_name) from Stu_couse sc,students s,couse C,chengji CJ where Sc.stu_id=s.id and Sc.couse_id=c.couse_ ID and sc.stu_couse_id=cj.stu_couse_id and cj.fen>60
Minus
SELECT DISTINCT (s.stu_name) from Stu_couse sc,students s,couse C,chengji CJ where Sc.stu_id=s.id and Sc.couse_id=c.couse_ ID and sc.stu_couse_id=cj.stu_couse_id and cj.fen<60;

Oracle Basic Build Table statement