Oracle databases include Oracle database servers and clients

Oracle Database Server:
Oracle Server is an object-1 relational database management system. It provides open, comprehensive, and integrated information management methods. Each server is composed of an Oracle DB and an Oracle server instance. It provides site autonomy and transparent data storage mechanisms to achieve data storage transparency. Each Oracle database corresponds to a unique instance name Sid. After the Oracle database server is started, there are usually at least the following users: internal, which is not a real user name, it is the alias of the Sys user with the sysdba priority. It is used by the DBA user to complete database management tasks, including starting and shutting down the database; sys, which is
DBA user name, with the maximum database operation permissions; system, which is also a DBA user name, the permission is second only to SYS users.
Client
The database user operation end is composed of applications, tools, and SQL * net. A user must connect to a server when operating a database. The database is called a local database ). Databases on other servers in the network environment are called remote databases ). To access data in a remote database, you must establish a database chain.

The architecture of the Oracle database includes the physical and logical storage structures. Because they are separated, the access to the logical storage structure is not affected when you manage the physical storage structure of data.
Logical Storage Structure
It consists of at least one tablespace and a database schema object. Here, the pattern is a collection of objects, while the pattern object directly references the logical structure of database data. Schema objects include the following structures: tables, views, sequences, stored procedures, same words, indexes, clusters, and database chains. The logical storage structure includes tablespaces, segments, and ranges to describe how to use the physical space of the database. The schema objects and Relationships form the relational Design of databases.

Data Block ):
Is the smallest unit for database to perform the uo operation. It is not a concept with the block of the operating system. The Oracle database does not request data in units of the operating system blocks, but is measured in multiple Oracle Database blocks.
Segment (segment ):
It is a logical storage structure of a specified type in a tablespace. It consists of one or more ranges. segments occupy and increase the storage space.
Including:
Data Segment: used to store table data ;.
Index segment: used to store table indexes;
Temporary segment: used to store intermediate results;
Rollback segment: used to restore the transaction when an exception occurs.
Range (extent): it is the logical unit for database storage space allocation. A range consists of many continuous data blocks, and the range is allocated by segments accordingly, the first range of allocation is called the initial range, and the scope of future allocation is called the incremental range.
Advantages
■ High Availability
■ High scalability
■ Strong data security
■ Strong stability
Example:
Write an SQL script
Question 1: How many record rows in the deptno field are obtained from the EMP table?
SQL> select count (distinct deptno) from EMP;
Question 2: How can I obtain the average and total salaries of employees in each department from the EMP table?
SQL> select deptno, AVG (SAL), sum (SAL) from EMP group by deptno;
Question 3: What is the average salary of a department whose average salary is higher than 2000 in the EMP table?
SQL> select deptno, AVG (SAL) from EMP group by deptno having AVG (SAL)> 2000;
Advanced SQL connection Query
Question 1: Please query the EMP and dept tables and display the Department name with Department Number 10. What are the employees of this department?
SQL> select e. ename, D. dname from EMP E, DEPT where E. deptno = D. deptno and E. deptno = 10;
Question 2: Can I query the EMP and dept tables and display the Department name with Department number 10, the employees of the Department, and the names of employees of other departments?
SQL> select e. ename, D. dname from EMP e left join dept don e. deptno = D. deptno and D. Dept = 10;
You can also write it as follows:
SQL> select e. ename, D. dname from EMP E, DEPT d Where E. deptno = D. deptno (+) and D. Dept = 10;
Advanced SQL subquery
Question 1: What is the total number of sales employees?
SQL> select count (empno) from EMP where deptno = (select deptno from Dept where dname = 'sales ');
Question 2: What is the name, position, salary, and department Number of the employee matching the job title and department 30?
SQL> select ename, job, Sal, deptno from EMP where job in (select job from EMO where deptno = 30 );
Question 3: What is the employee name, salary, and department Number of all employees whose salaries are higher than Department 30?
SQL> select ename, Sal, deptno from EMP where SAL> All (select Sal from EMP where deptno = 30 );