SQL operations and queries

1. Introduction to SQL

  SQL is a Structured Query Language (Structured Query Language), which is specially used for operations such as data access, data update, and database management.

  In Oracle development, the client sends the SQL statement to the server, the server compiles and executes the SQL statement, and returns the execution result to the client. An Oracle SQL statement consists of the following commands:

  The current mainstream database products (such as: SQL Server, Oracle,MySQL) all support standard SQL statements. Data definition language, table addition, deletion and modification operations, simple data query, transaction submission and rollback, permission authorization and revocation, etc., Oracle and MySQL are basically the same in operation.

2. Explanation of data types

  The core of the Oracle database is the table, and the common data types used by the columns in the table are as follows:

type	meaning
CHAR(length)	Stores a fixed-length string. The parameter length specifies the length, if the length of the stored string is less than length, it will be filled with spaces. The default length is 1, and the maximum length does not exceed 2000 bytes.
VARCHAR2(length)	Store variable-length strings. length specifies the maximum length of the string. The default length is 1, and the maximum length cannot exceed 4000 characters.
NUMBER(p，s)	It can store both floating-point numbers and integers. p represents the maximum number of digits (if it is a decimal, it includes the integer part, decimal part and decimal point, and p defaults to 38), and s refers to the number of decimal places.
DATE	Store date and time, store era, 4-digit year, month, day, hour, minute, second, and store time from January 1, 4712 BC to December 31, 4712 AD.
TIMESTAMP	Not only the year, month, day, hour, minute, second, and 6 digits after the second are stored, but also the time zone is stored.
CLOB	Store large text, such as storing unstructured XML documents
BLOB	Stores binary objects such as graphics, video, sound, etc.

Example corresponding to the NUMBER type:

Format	entered number	actual storage
NUMBER	1234.567	1234.567
NUMBER(6，2)	123.4567	123.46
NUMBER(4，2)	12345.67	The entered number exceeds the specified precision and cannot be stored in the database

For the date type, you can use the sysdate built-in function to obtain the current system date and time, return the DATE type, and use the systimestamp function to return the current date, time and time zone.

In Oracle query, the complete syntax of "select column... from table" must be used. When querying a single-row function, the DUAL table is used after from. The dual table has only one row and one column in the system. When outputting a single-row function, the table is for select... from for grammatical integrity.

3. Create tables and constraints

1. Table structure

The syntax of creating a table in Oracle

CREATE TABLE table name (

field name type constraints,

field name type constraints,

Field name Type Constraint

)

Case insensitivity in the database

Related cases of creating tables and comments

-- Create a user table. Used to store user information
CREATE TABLE t_student(
   id  number(5) , -- student number
   name varchar2(20) , -- student's name
   age number(2) , -- student's age
   address varchar2(100) -- student's address
); -- SQL At the end of the statement, we add a ;

-- Give table add notes:
COMMENT ON TABLE t_student is 'student table'; -- Add comments to the table
COMMENT ON COLUMN t_student.id is 'student number' ; -- Add comments to fields in the table
COMMENT ON COLUMN t_student.name is 'student name' ; 
COMMENT ON COLUMN t_student.age is 'age' ;
COMMENT ON COLUMN t_student.address is 'student address';

2. Update table structure

  We may need to modify and adjust the table after we create a table structure

-- Operations on table structures
-- 1.delete table
DROP TABLE t_student;
CREATE TABLE t_student(
   id  number(5) , -- student number
   name varchar2(20) , -- student's name
   age number(2) , -- student's age
   address varchar2(100)  -- student's address
); -- SQL At the end of the statement, we add a ;
-- add field
ALTER TABLE t_student ADD gender char(3);
-- Modify field type
ALTER TABLE t_student MODIFY gender varchar2(3);
-- Modify field name
ALTER TABLE t_student RENAME COLUMN gender to sex;
-- delete field
ALTER TABLE t_student DROP COLUMN sex;

3. Constraints

1. Non-null constraint The content of this field is not allowed to be empty
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) , – student ID
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;

2. Default value: If the value of the column is null, it will be filled with the default value
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) , – student ID
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;

3. Unique constraint: when there is data. Duplicate records cannot appear in this column. null does not include
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) unique , – student number
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;
– not null + unique constraint
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) unique not null , – student number
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;

4. Primary key constraint: a constraint that can uniquely identify the field. The field modified by the primary key cannot be repeated and cannot have null values
– And only one annotation can appear in a table [joint primary key: a primary key contains multiple fields]
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) primary key , – student number
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;

– Create a table and then add a primary key
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) , – student ID
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;
alter table t_student add constraints pk_student_id primary key(id);

5. Foreign key: the relationship between tables and tables
– Foreign key: It can appear repeatedly in the main table, but its value is the primary key of another table, and the foreign key associates the two tables
drop table t_class;
create table t_class(
id number(3) primary key,
name varchar2(30)
);

drop table t_student ;
create table t_student(
id number(3) primary key,
name varchar2(30) ,
classid number(3)
);

– Maintain foreign key associations
alter table t_student add constraints fk_student_classid foreign key(classid) references t_class(id);

6.check constraints: based on the field type. Further improve the accuracy of the data
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) unique not null , – student number
name varchar2(20) not null, – student's name
–age number(2) check(age > 1 and age < 25), – age of the student
age number(2) check(age between 1 and 25), – the age of the student
address varchar2(100), – the address of the student
gender char(3) check (gender in ('male','female'))
); – the end of the SQL statement, we add an English status ;

Four, DML statement

  DML data manipulation language: implement data insertion, modification and deletion operations through SQL, and the commonly used data manipulation voices in Oracle are

• INSERT
• UPDATE
• DELETE
• SELECT ... FOR UPDATE

1.INSERT

  Data insertion statement

INSERT INTO Table Name(fieldName1,fieldName1,...fieldNameN)values(value1,value2,...,valueN)
-- the case
insert into t_class(id,name)values(1,'Computer Class 1');

Short syntax: If the records we insert need to add information to each field in the table. Then we can omit the field list, but the subsequent value list must be consistent with the order of the fields in the table structure

INSERT INTO Table Name values(value1,value2,...,valueN)

INSERT  INTO t_class values(2,'Computer Class 2');
-- The following is an example of the error
INSERT  INTO t_class values('English class one',3);

2. Serial number

Primary key: When we insert data, we need to ensure its uniqueness

• The serial number solution provided in Orlace to solve
• MySQL provides a primary key auto-increment scheme
• In a distributed environment. We can solve it by distributed ID

Syntax for serial number:

CREATE SEQUENCE sequence name
[INCREMENT BY] -- The number of increments each time
[START WITH 1] -- counting from 1
[NOMAXVALUE]   -- Do not set a maximum
[NOCYCLE]     -- Always accumulate, no loop
CACHE 10;     -- Cache 10

case application

create sequence s_class; -- Start from 1 and increase by 1 each time
-- currval is executing nextval will take effect after
select s_class.currval from dual;
select s_class.nextval from dual;

Application of insert statement

INSERT  INTO t_class values(s_class.nextval,'Computer Class 2');

3.UPDATE

   Need to make adjustments to the data that has been inserted into the table structure. corresponding grammatical structure

UPDATE Table Name SET field1=value1,field2=value2 ... [where condition]
update t_class set name = 'software class' ;

update t_class set name = 'Computer Class 1' where id = 15;
-- renew id The record for 18 19 21 is 'Computer Class 2'
update t_class set name = 'Computer Class 2' where id = 18 or id=19 or id=21 ;
update t_class set name = 'Computer Class 3' where id in (14,16,17) ;
update t_class set name = 'English Class 1' where id >= 100 and id <=200 ;
update t_class set name = 'English Class 2' where id between 100 and 130 ;
update t_class set name = 'test' where id != 24 ;
update t_class set name = 'test666' where id <> 22 ;

4. Delete statement

 DELETE FROM Table Name [where condition]
 delete from t_class where id = 17;
 -- for null The query we use is is null to match
 delete from t_class where name is null;
 
 -- delete Delete the cache that will do data
delete from t_class ; -- All data in the table will be deleted.  Delete the library and run away, use it carefully
-- Delete the data of the whole table, delete the data directly. Do not cache.  efficient. high risk
truncate table t_class ;

5. Insert multiple rows

-- multiline insert
insert into t_class_copy(id,name)
select id,name from t_class

select * from t_class_copy1

-- copy table
create table t_class_copy as select * from t_class where 1 != 1;

-- Copy table with data
create table t_class_copy1 as select * from t_class ;

Five, DQL statement

  DQL: Data Query Language.

Syntax Structure of Query Statement

--Grammatical structures
SELECT <column name>
FROM <Table Name>
[WHERE <Query conditions> ]
[ORDER BY <sorted column name>[ASC or DESC] ]
[GROUP BY <grouping field> ]

1. Simple query statement

1.1 Explanation of knowledge points

Contents in a full table query:

create sequence s_student;
insert into t_student(id,name,age,address,gender)values(s_student.nextval,'Wang Wu',18,'Changsha, Hunan','female') ;

-- 1.Query all the information of the student table * Represents all columns in the query table
select * from t_student;

-- 2.query specific columns
select id,name,age from t_student;

-- 3.Query information is identified by an alias,alias cannot be used' we have to use "
select 
    id as "student number",
    name as "student name",
    age as "student age" 
from t_student;
-- we can omit ""
select 
    id as student number,
    name as student name,
    age as student age 
from t_student;
-- We can also omit as keywords
select 
    id  student number,
    name student name,
    age  student age 
from t_student;
-- If there are special symbols in the alias. then you can't omit""

select 
    id  "[Student ID]",
    name as student name,
    age as student age 
from t_student;

-- 4.constant handling
select id,name,age ,29 weight from t_student

-- 5.Query student information. id and name stitched together ||
select id,name,age , '['||id||'-'||'name'||']' combination from t_student

Conditional query:

drop table t_student ;
create table t_student(
id number(3) primary key,
name varchar2(30) ,
gender char(3) ,
age number(2),
class_id number(5)
)

Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (1,'Zhang San','male ',18,1001);
Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (2,'Li Si','female ',22,1002);
Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (3,'Xiao Ming','male ',35,1003);
Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (4,'little flower','female ',16,1001);
Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (5,'Zhang Sanfeng','male ',18,1001);
Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (6,'Wang Zhangni','female ',25,1002);

-- 6.Find students between the ages of 18 and 25
select *
from t_student 
where age >= 18 and age <= 25

select * from t_student where age between 18 and 25;
-- 7.Query the student information with the class number 1001 or 1002
select * from t_student  where t_student.class_id = 1001 or t_student.class_id = 1002 
select * from t_student t  where t.class_id = 1001 or t.class_id = 1002 
select * from t_student t  where t.class_id in (1001,1002)
-- 8.Query the information of students who are not in the class number 1001 and 1002
select * from t_student where class_id not in (1001,1002) or  class_id is null

-- 9.Query the information of all students surnamed Zhang Fuzzy query: like
select * from t_student where name like 'open%'
select * from t_student where name like '%open%'
-- 10.Query information about all students with the surname Zhang. And the name is composed of two characters
select * from t_student where name like 'open_'

-- 11.Find all student information. Sort by age ascending  asc Ascending can be omitted
select * from t_student;
select * from t_student order by age ;
select * from t_student order by age desc;

-- 11.Find all student information. Sort by class in ascending order. If the class is the same then sort by age descending
select * from t_student order by class_id , age desc 


-- 12.Query all class numbers in the student table distinct Keywords can help us remove duplicate records.
-- If there are multiple columns behind. What is removed is the same record in multiple column combinations
select distinct class_id,age from t_student

1.2 Case explanation

SQL practice explanation:

Table Structure

Emp----employee information form
Ename varchar2(30), --Name
Empno number(5),  --serial number
Deptno number(5), --department
Job varchar2(20), --type of work(Personnel category)，like: manager manager, clerk clerk
Hiredate Date, --date of employment
Comm number(6,2),  --commission
Sal  number(6,2)  --salary
Mgr number(5)  --employee supervisor number


Dept-----department table
Dname varchar2(30),  --department name
Deptno number(5), --department number
Loc varchar2(50)  --Location

Prepare data:

create table emp --–Create employee information form
(
Ename varchar2(30), --Name
Empno number(5),  --serial number
Deptno number(5), --department
Job varchar2(20), --type of work(Personnel category)，like: manager manager, clerk clerk
Hiredate Date, --date of employment
Comm number(7,2),  --commission
Sal  number(7,2) , --salary
Mgr number(5)  --serial number
);
insert into EMP (ENAME, EMPNO, DEPTNO, JOB, HIREDATE, COMM, SAL, MGR)
values ('Zhang San', 1, 10, 'clerk', null, 500, 2000, null);
insert into EMP (ENAME, EMPNO, DEPTNO, JOB, HIREDATE, COMM, SAL, MGR)
values ('Li Si', 2, 10, 'clerk', null, 650, 2333, null);
insert into EMP (ENAME, EMPNO, DEPTNO, JOB, HIREDATE, COMM, SAL, MGR)
values ('Wang Wu', 3, 20, 'clerk', null, 1650, 1221, null);
insert into EMP (ENAME, EMPNO, DEPTNO, JOB, HIREDATE, COMM, SAL, MGR)
values ('Xiao Zhang', 4, 20, 'manager', null, 980, 3200, null);
insert into EMP (ENAME, EMPNO, DEPTNO, JOB, HIREDATE, COMM, SAL, MGR)
values ('Xiao Liu', 5, 10, 'clerk', null, 500, 2000, null);
commit;
create table dept --department table
(
Dname varchar2(30),  --department name
Deptno number(5), --department number
Loc varchar2(50)  -- department location
);
insert into DEPT (DNAME, DEPTNO, LOC)
values ('Marketing Department', 10, 'Dalian');
insert into DEPT (DNAME, DEPTNO, LOC)
values ('R & D department', 20, 'Shenyang');
insert into DEPT (DNAME, DEPTNO, LOC)
values ('Personnel Department', 30, 'Shenzhen');
insert into DEPT (DNAME, DEPTNO, LOC)
values ('Personnel Department', 40, 'Guangzhou');
commit;

topic:

single table query

1.Select employees in department 30 
select * from emp where emp.deptno = 30

2.List all clerks by name, number and department
select ename as Name,empno "serial number",deptno department
from emp
where emp.job = 'clerk'

select ename as Name,empno "serial number",deptno department ,(select dname from dept where dept.deptno = emp.deptno) Department name
from emp
where emp.job = 'clerk'

3.Find employees whose commission is higher than their salary
select * from emp where comm > sal

4.Find out if the commission is higher than the salary 60%employees
select emp.*,sal*0.6 from emp where comm > sal*0.6

5.Find the details of all managers in department 10 and all clerks in department 20
select * 
from emp
where (deptno = 10 and job = 'manager')
or (deptno = 20 and job = 'clerk')

6.Find out the different jobs of employees on commission
select distinct job from emp where comm > 0

7.Find employees who take no commission or take less than 100 commission
select * from emp where (comm <= 0 or comm is null ) or comm < 100
select * from emp where comm <= 100 or comm is null 
8.Find all employees who were hired on the last day of each month
select emp.*,last_day(emp.hiredate)
from emp
where emp.hiredate = last_day(emp.hiredate)


9.Find employees who were hired more than 25 years ago
select emp.*,sysdate 
from emp
where add_months(emp.hiredate,25*12) < sysdate

10.Display names of all employees with only the first letter capitalized
select emp.*,ename,substr(ename,1,1)
from emp
where substr(ename,1,1) >= 'A' and substr(ename,1,1) <= 'Z'

select emp.*,ename,substr(ename,1,1)
from emp
where substr(ename,1,1) between 'A' and 'Z'

11.Displays the employee name with exactly 6 characters
select emp.*,emp.ename,length(emp.ename)
from emp 
where length(emp.ename) = 6

12.show without'R'employee name of
select *
from emp
where emp.ename not like '%R%'

13.Display the first three characters of all employees' names
select emp.*,substr(emp.ename,1,2) from emp 
14.To display the names of all employees, use a replace all'A'
select ename,replace(ename,'A','a')
from emp
15.Show the names of all employees and the date they completed their 10 years of service
select ename,hiredate,add_months(hiredate,10*12)
from emp
16.Display employee details, sorted by name
select *
from emp
order by ename desc

17.Display employee names, with the oldest employee first, based on their years of service
select ename Name
from emp
order by hiredate asc 
18.Displays the names, jobs, and salaries of all employees, sorted in descending order by job, and ascending by salary if the job is the same
select ename Name, job Work ,sal salary
from emp
order by job desc ,sal asc

19.Displays the names of all employees and the year and month they joined the company, sorted by the month the employee was hired, with the oldest year first
select emp.ename,
        emp.hiredate,
        extract(YEAR from emp.hiredate) year,
        extract(MONTH from emp.hiredate) month
from emp
order by extract(MONTH from emp.hiredate) ,extract(YEAR from emp.hiredate) ;

20.Display the daily salaries of all employees in a month with 30 days
select emp.*,nvl(sal,0),round(nvl(sal,0)/30,2) Daily salary
from emp

21.Find all employees hired in February (of any year)
select *
from emp
where extract(MONTH from hiredate) = 2

22.For each employee, show the number of days they have been with the company
select emp.*,round(sysdate-hiredate ) number of days
from emp
23.Anywhere the name field is displayed, including "A" The names of all employees of
select ename Name
from emp
where ename like '%A%'
24.Displays the years of service of all employees in years, months and days
select
    trunc((sysdate-hiredate)/365) year,
    trunc((sysdate-hiredate)/12) month,
    trunc((sysdate-hiredate)) days
from emp

2. Aggregate function

  The role of aggregate functions: to solve our statistical needs for data

-- Aggregate and group functions
-- 1.Count the number of students count(Field Name) Count the number of records whose data in this column is not empty
select count(*),count(name),count(gender),count(age),count(1)
from t_student ;
-- count(*) and count(1) the difference
select t_student.*,1
from t_student ;

-- 2.Other commonly used statistical functions
select count(1),max(age),min(age),sum(age),round(avg(age))
from t_student ;

3. Group query

grouping function  group by
--  1> Not used together with the aggregate function, the effect is the same as distinct The same one. Duplicate records can be removed
    select * from emp ;
    select deptno from emp group by deptno ;
  
    select distinct deptno from emp;
  
    select deptno,job 
    from emp 
    group by deptno , job 
    order by deptno;
  
-- 2>Scenarios for use with aggregate functions,The data counted by the aggregation function is not all the data queried. but the grouped data
   -- We cannot directly appear non-grouped fields in the grouped data
   -- a.Count the number of boys and girls in the student table
   select gender,count(1) from t_student group by gender;
   
   -- b.Count the number of people in each class in the student table
   select class_id,count(1)  from t_student t group by t.class_id;
   
   -- c.Count the number of boys and girls in each class in the student table. The aggregation function counts the data of the smallest unit after grouping
   select class_id,gender,count(1) from t_student group by class_id ,gender ;
   
   -- d.Count the number of boys and girls who are older than 18 in the student table
   select gender,count(1)
   from t_student
   where age > 18 -- where The location must be in the group by Before. The function is to filter the data to be grouped
   group by gender
   
   -- e.Count the records in the learning table where the class size is greater than 1 and the age is greater than 18
   select class_id,count(1)
   from t_student
   where age > 18
   group by class_id
   having count(1) > 1 -- exist group by after. and group by With the use of. The function is to filter the grouped data

4. Multi-table query

  Oracle and MySQL are both relational databases. [Relationship] refers to the relationship between tables and data between tables.

multi-table query
-- 1.cross connect:Get the Cartesian product of two tables
select * from t_class ;
select * from t_student;

-- Query student information and corresponding class information
select t1.*,t2.*
from t_student t1,t_class t2

-- Equivalent connection: add filter conditions on the basis of cross connection
select t1.*,t2.*
from t_student t1,t_class t2 -- 1000 *  1000 = 100W
where t1.class_id = t2.id -- where The key is the conditional filter after the result set

-- Inner join: When the records in the left table structure and the records in the right table structure are connected, they will be based on on Conditional judgment in . Get it if you are satisfied. otherwise all lost
select t1.*,t2.*
from t_student t1 
inner join t_class t2 
on t1.class_id = t2.id

-- Query all the records in the student table. At the same time, the class name corresponding to the student is displayed
select t1.*,t2.*
from t_student t1 
inner join t_class t2 
on t1.class_id = t2.id
-- left join:based on inner joins. Keep the data that does not meet the condition on the left
select t1.*,t2.*
from t_student t1 left outer join t_class t2
on t1.class_id = t2.id

-- right join:based on inner joins. Keep the data that does not meet the conditions on the right
-- Query all class information. Query relevant student information at the same time
select t2.*,t1.*
from t_student t1 right outer join t_class t2
on t1.class_id = t2.id

-- Full connection: Based on the inner connection, the data on the left and right sides that do not meet the conditions are retained
select t1.*,t2.*
from t_student t1 full join t_class t2
on t1.class_id = t2.id


-- union  union all merge result set

select t1.*,t2.*
from t_student t1 left outer join t_class t2
on t1.class_id = t2.id
union -- Merge result sets. Duplicate records will be removed, similar to full connections
select t1.*,t2.*
from t_student t1 right outer join t_class t2
on t1.class_id = t2.id

select t1.*,t2.*
from t_student t1 left outer join t_class t2
on t1.class_id = t2.id
union all -- Merge result sets. Duplicate records will not be removed  
select t1.*,t2.*
from t_student t1 right outer join t_class t2
on t1.class_id = t2.id

5. Case explanation

drop table student;
create table student (
id number(3) PRIMARY key,
name VARCHAR2(20) not null,
sex varchar2(4),
birth number(4),
department varchar2(20),
address VARCHAR2(50));


create score surface. SQL code show as below:
create table score(
   id number(3)   PRIMARY key,
   stu_id number(3)  not null,
   c_name VARCHAR(20) ,
   grade number(3)
);

-- Towards student table insert record INSERT The statement is as follows:
insert into student values(901,'Boss Zhang','male',1985,'computer science','Haiding district, Beijing');
insert into student values(902,'Zhang Laoer','male',1986,'Department of Chinese','Changping district, Beijing');
insert into student values(903,'Zhang San','female',1990,'Department of Chinese','Yongzhou City, Hunan Province');
insert into student values(904,'Li Si','male',1990,'English major','Fuxin City, Liaoning Province');
insert into student values(905,'Wang Wu','female',1991,'English major','Xiamen City, Fujian Province');
insert into student values(906,'Wang Liu','male',1988,'computer science','Hengyang City, Hunan Province');
-- Towards score table insert record INSERT The statement is as follows:
insert into score values(1,901,'computer',98);
insert into score values(2,901,'English',80);
insert into score values(3,902,'computer',65);
insert into score values(4,902,'Chinese',88);
insert into score values(5,903,'Chinese',95);
insert into score values(6,904,'computer',70);
insert into score values(7,904,'English',92);
insert into score values(8,905,'English',94);
insert into score values(9,906,'computer',90);
insert into score values(10,906,'English',85);

SELECT * from student;
select * from score;

1,Inquire student The 2nd to 4th records of the table 
   Steps for paging query:
-- rownum:The system is automatically maintained from 1
select t.* ,rownum
from (
select s.*,rownum num
from student s
where rownum <=4 ) t
where t.num >=2
   
2,from student The table queries the student ID numbers of all students ( id),
           Name( name)and departments ( department)Information

select id student ID, name Name, department Faculty
from student 
   
3,from student Query the information of students in the Department of Computer Science and Department of English in the table
   select *
   from student 
   -- where department = 'computer science' or department = 'English major'
      where department in ('English major','computer science' )  
4,from student Query age in table 25~30 Year-old Student Information
   select s.*,(extract(year from sysdate) - birth) age
   from student s
   where (extract(year from sysdate) - birth)  between 30 and 35
   
5,from student Query the number of people in each department in the table?
select department,count(1)
from student
group by department
   
6,from score Query the highest score of each subject in the table
select c_name,max(grade)
from score
group by c_name
 
7,Query Li Si's test subjects ( c_name)and test scores ( grade)
     Notice: '=' It is only used when the result is determined to be one. If it is not determined, use 'in'
   
     select * from student for update;
   
     select c_name,grade
     from score
     where stu_id in (
        select id from student where name = 'Li Si'
     )
   
     select c_name,grade
     from score
     where exists ( -- Whether the condition of the following subquery is satisfied
        select id from student where name = 'Li Si' and score.stu_id = student.id
     )
   
     select s1.c_name,s1.grade,s2.name
     from score s1 , student s2
     where s2.name = 'Li Si' and s1.stu_id = s2.id
   
     select s1.name,s2.c_name,s2.grade
     from student s1 left join score s2  on s1.id = s2.stu_id -- and s1.name = 'Li Si' The conditions for the connection to match
     where s1.name = 'Li Si'
   
     select s1.name,s2.c_name,s2.grade
     from (
        select * from student where name = 'Li Si'
     ) s1 left join score s2  on s1.id = s2.stu_id -- and s1.name = 'Li Si' The conditions for the connection to match
     -- where s1.name = 'Li Si'
  
8,Query all student information and test information by inner connection
    select s1.*,s2.*
    from student s1 inner join score s2 on s1.id = s2.stu_id
  
9,Compute the total grade for each student
  select stu_id,(select name from student where id = stu_id) name,sum(grade)
  from score
  group by stu_id 
   
10,Calculate the average grade for each exam subject
    select c_name,round(avg(grade),2) average score
    from score
    group by c_name

11,Query the information of students whose computer scores are lower than 95
  select s1.*,s2.grade
  from student s1 left join score s2 on s1.id = s2.stu_id
  where s2.grade < 95 and s1.department = 'computer science'
   
12,Inquire about the information of students who take computer and English exams at the same time
    select *
    from student 
    where id in (
        select stu_id from score where c_name = 'computer' and c_name = 'English'
    )
  
    select t1.stu_id
    from (
    select * from score where c_name = 'computer' ) t1,
    (select * from score where c_name = 'English' ) t2
    where t1.stu_id = t2.stu_id

13,Sort computerized test scores from high to low
    select *
    from score
    where c_name = 'computer'
    order by grade desc
  
14,from student table and score Query the student's student number in the table,
     Then combine query results UNION and union all
select  id
from student
union all
select stu_id
from score
      
15,Query the name, department, examination subjects and results of students surnamed Zhang or Wang
  select s1.name ,s1.department,s2.c_name,s2.grade
  from (select * from student where name like 'open%' or name like 'king%') s1
  left join score s2  on s1.id = s2.stu_id
    
16,Query the names, ages, faculties, exam subjects and grades of students from Hunan
  
  select s1.name ,s1.department,s2.c_name,s2.grade
  from (select * from student where address like 'Hunan%' ) s1
  left join score s2  on s1.id = s2.stu_id