Oracle learning note excerpt 4-subquery and associated Query

Create a table as follows:

Student basic info table
Create student (
[Studentid] [int] identity (1, 1) not null primary key, -- primary key
[Studentname] [char] not null

)
Course info table
Create subject (
[Subjectid] [char] not null primary key, -- primary key
[Subjectname] [char] not null
)
Orders table
Create grade (
[Studentid] [int] not null, -- Union primary key
[Subjectid] [char] not null, -- Joint primary key
[Grade] [int] not null,
Primary Key (studentid, subjectid)
)

1. Change the table creation command to an oracle statement to create a table in Oracle.
Create Table student (-- Student table
Studentid number (3) primary key, -- Student ID
Studentname varchar2 (20) -- Student name
);

Create Table subject (-- Curriculum
Subjectid char (3) primary key, -- course number
Subjectname varchar2 (20) -- Course name
);

Create Table grade (-- score table
Studentid number (3) references student (studentid), -- Student ID
Subjectid char (3) references subject (subjectid), -- course ID
Mark Number (3), -- score
Primary Key (studentid, subjectid) -- Union primary key
);

Insert into student values (101, 'zhang san ');
Insert into student values (102, 'Li yun ');
Insert into student values (103, 'UN ');

Insert into subject values ('a01', 'c ++ ');
Insert into subject values ('a02', 'asp ');
Insert into subject values ('a03', 'java ');

Insert into grade values (101, 'a01', 59 );
Insert into grade values (101, 'a02', 72 );
Insert into grade values (101, 'a03', 90 );

Insert into grade values (102, 'a01', 75 );
Insert into grade values (102, 'a02', 91 );

Insert into grade values (103, 'a01', 71 );

2. Make the following 4 questions:

Question 1: The following information is displayed:

Student ID name Course name score (all student information is required)

Join query (for multiple tables)
Alias

Select a. studentid as "student ID", studentname "Student name ",
Subjectname "Course name", mark "achievements"
From student A, Subject B, Grade C
Where a. studentid = C. studentid
And B. subjectid = C. subjectid;

[Select a. studentid "student ID", studentname "Student name ",
Subjectname "Course name", mark "achievements"
From student A, Subject B, Grade C] Cartesian Product

3*3*6 = 54;

Question 2: The following information is displayed:

Student ID name Course name score (only displays the highest score per subject)

Select a. studentid "student ID", studentname "Student name ",
Subjectname "Course name", mark "achievements"
From student A, Subject B, Grade C
Where a. studentid = C. studentid
And B. subjectid = C. subjectid
And (subjectname, mark)
In (select subjectname "Course name", max (Mark) "achievements"
From student A, Subject B, Grade C
Where a. studentid = C. studentid
And B. subjectid = C. subjectid
Group by subjectname)

(Highest score-Number of students with higher scores = 0)
Select a. studentid "student ID", studentname "Student name ",
Subjectname "Course name", mark "achievements"
From student A, Subject B, Grade C
Where a. studentid = C. studentid
And B. subjectid = C. subjectid
And (select count (*) from Grade
Where subjectid = B. subjectid and
Mark> C. Mark) = 0

Question 3: The following information is displayed:

Student name Course name score (show pass when score is greater than 60, show fail when score is less than 60)

Select a. studentid "student ID", studentname "Student name ",
Subjectname "Course name ",
Decode (sign (mark-60),-1, 'failed', 'failed') "achievements"
From student A, Subject B, Grade C
Where a. studentid = C. studentid
And B. subjectid = C. subjectid

Question 4: The following information is displayed:

Student ID (Query Information of more than 1 student in the course)

Select a. studentid "student ID", studentname "Student name ",
Count (subjectname)
From student A, Subject B, Grade C
Where a. studentid = C. studentid
And B. subjectid = C. subjectid
Group by A. studentid, studentname
Having count (subjectname)> = 2

2 copy the structure of a table
-- Data Replication
Create Table mydept as select * from Dept;
-- Only copy the structure
Create Table mydept1 as select * from Dept where 1 = 2;
-- Copy data from one table to another table with the same structure
Insert into mydept1 select * from Dept;
-- Only copy part of the data
Insert into mydept1 select * from Dept where deptno> 20;
Insert into mydept1 (deptno, Loc) Select deptno, Loc
From dept;

-- The table constraints cannot be copied. You must add them by yourself.

-- Column alias
Select ename "employee name" from EMP;

A. How to cancel duplicate records for tables without primary keys

Create Table Test (
ID number (2 ),
Name varchar2 (10 ));

Insert into test values (1, 'A ');
Insert into test values (1, 'A ');
Insert into test values (1, 'A ');
Insert into test values (2, 'bb ');
Insert into test values (3, 'cc ');

Create Table test1 as select distinct * from
Test;
Or
Create Table test1 as select ID, name from test
Group by ID, name;

Rename test to Test2;
Rename test1 to test;

B. Primary Key deduplication

Create Table Test (
ID number (2) primary key,
Name varchar2 (10 ));

Insert into test values (1, 'A ');
Insert into test values (2, 'A ');
Insert into test values (3, 'A ');
Insert into test values (4, 'bb ');
Insert into test values (5, 'cc ');

Create Table test1 as select name from test
Group by name;

Alter table test1 add (ID number (2 ));

Update test1 Set ID = (select max (ID) from test
Where name = test1.name );

Create Table Test2 as select ID, name from test1;

Rename test to testb;
Rename Test2 to test;

Alter table test add primary key (ID );

<2> environment setting commands in SQL * Plus
Run the SQL * Plus Environment command without a semicolon.
SQL commands that can be executed only after the end of a semicolon (same as SQL Server)

Connect system/Manager
Show user
Spool C:/aa.txt
Spool off
Clear Screen
Set escape on enable the escape Function

Set linesize 1000 set the number of characters displayed in a row
Set pagesize 20: set the number of rows displayed on a page
-------
Define AA = 10 define a variable AA = 10
Define BB = 'manager' defines a character BB = 'manager'

Prompt Hello world is the string after the prompt is output as is

Set feedback off the system feedback
Set heading off Column Title closed
Set pagesize 0 No Paging
Set autocommit on Set automatic submission to open
Set timing on opening time statistics
Set Time on enable a clock

------
A) automatically generate a script
Use SQL * Plus environment commands to generate script files
Set heading off -- disable the column title
Set feedback off -- disable feedback
 
Spool C:/aa.txt -- buffer Write File C:/aa.txt

Select 'insert into dept values ('| deptno |
',' | ''' | Dname | ''' | ', '| ''' | loc | ''' |'); 'from Dept; -- execute the SELECT statement.

Spool off -- Disable buffering

Usage: It is useful in backup and recovery database management.

<4> create database objects in Oracle
Table
Constraints

Sequence sequence --- automatic number ---- identity (sqlserver)
<1> Create a sequence
Create sequence seq1; automatically increases from 1 to 1 without a maximum value.
<2> How to Use
Select seq1.nextval from dual;
Next value of the nextval pseudo Column
Select seq1.currval from dual;
Current Value of the currval pseudo Column

Create sequence seq2
Start with 1000 -- start value 1000
Increment by 2 -- step 2
Maxvalue 9000 -- maximum 9000
Minvalue 900 -- Minimum value 900
Cycle -- cyclic Sequence

1000 start
Increase by 2 to 9000 each time
Return to 900 and start again

<3> associate with a table as the table Field Value
A) Create Table student (
XH number (4) primary key, -- Student ID
XM varchar2 (20) not null -- name
);

Requirement: the student ID field is increased from 1000 to 9999 at a time.
-- Create Sequence
Create sequence xh_seq
Start with 1000 -- Starting from 1000
Increment by 4 -- increase by 4 each time
Maxvalue 9999 -- maximum 9999
;
-- Associate with SQL statements
Insert into student values
(Xh_seq.nextval, 'Mike ');
Insert into student values
(Xh_seq.nextval, 'john ');
Insert into student values
(Xh_seq.nextval, 'Rose ');

-- Feature: unique values can be generated, but the continuity of values in the table cannot be guaranteed.
 
B) Create Table teacher (
Teacherxh varchar2 (10) primary key,
Teachername varchar2 (20)
);
Requirement: the instructor ID is in the format of th00001.
Th00002
....

-- Create Sequence
Create sequence teacher_seq
Maxvalue 99999;
--
Insert into teacher values ('th' |
Ltrim (to_char (teacher_seq.nextval, '000000 ')),
'Zhang san ');
Insert into teacher values ('th' |
Ltrim (to_char (teacher_seq.nextval, '000000 ')),
'Lil ');
Insert into teacher values ('th' |
Ltrim (to_char (teacher_seq.nextval, '000000 ')),
'King ');

View)
-- Create a view (use the query statement select empno, ename, Sal from emp to create an emp_v1 view)
Create or replace view emp_v1 as -- SELECT statement
Select empno, ename, Sal from EMP;

Role of a view: hiding data and increasing System Security
Does the view store data ??? Store only queries without data
Ensures that the queried data is consistent with the table
The data in is consistent
Select * From emp_v1;
<1> check whether emp_v1 is a table.
<2> whether the table is a view or not
<3> select * from (select empno, ename, Sal from EMP );
<4> execution result

Can I change the data in a table through a view ???
Conditional:
<1> the SELECT statement for creating a view must be a simple SELECT statement.
Simple: Multi-table queries are not allowed.
Grouping functions are not allowed.
<2> A view cannot contain the with readonly keyword.
Create or replace view emp_v2 as -- SELECT statement
Select empno, ename, Sal from EMP
With read only;

Possible changes:
1) Create or replace view emp_v1 as -- SELECT statement
Select empno, ename, Sal, comm from EMP;

Update emp_v1 set comm = 1000
Where e-mapreduce = 7934;
 
Select * from EMP; -- data modified

2) create or replace view emp_v3
As
Select empno, ename, hiredate, Sal, deptno
From EMP
Where deptno = 10;

Updated emp_v3 set deptno = 20 where empno = 7782;
 
Select * From emp_v3; -- 7782 of data is missing