Oracle basics Chapter 2 SQL language basics

This chapter introduces the basic SELECT data type of the target data type SQL statement type. data Type a) the data type needs to be defined in the design table structure. selecting an appropriate data type can save storage space and improve computing efficiency. B) Oracle data types mainly include character type, data type, date and time type, and large object type (LOB ).

This chapter introduces the basic SELECT data type of the target data type SQL statement type. data Type a) the data type needs to be defined in the design table structure. selecting an appropriate data type can save storage space and improve computing efficiency. B) Oracle data types mainly include character type, data type, date/time type, and LOB type.

Objectives of this Chapter

Data Type

SQL statement type

SELECT Basics

Data Type

1. Data Type

A) the data type needs to be defined in the design table structure. selecting an appropriate data type can save storage space and improve computing efficiency.

B) Oracle data types mainly include reserved, Data, date/time, And LOB.

2. Balanced type

3. numeric type

4. date/time type

5. Large Object (LOB) Type

SQL statement type

1. SQL statement type

A) Data Manipulation Language (DML)

I. Used to operate data

Ii. Example: insert into, update, delete

B) Data Definition Language (DDL)

I. Used to create objects

Ii. Example: create table, alter table, drop table, create view;

C) Data Control Language (DCL)

I. Used to control access permissions

Ii. For example, revoke, grant, commit, and rollback

2. SELECT statement

A) basic syntax of the SELECT statement

SELECT clause

FROM clause

[WHERE clause]

B) Description

I. SELECT clause: Specifies the composition of the columns in the query result set. The columns in the list can come from one or more tables or views.

Ii. FROM clause: specify one or more tables or views to be queried.

Iii. WHERE clause: Specify the query Conditions

3. Instructions

A) In this course, keywords, clauses, and statements are defined as follows:

B) KEYWORDS

I. refers to a single SQL element.

Ii. For example, both SELECT and FROM are keywords.

C) clause

I. It is part of an SQL statement.

Ii. For example, SELECT employee_id, last_name ,... Is a clause

D) Statement

I. is a combination of two or more clauses.

Ii. For example, SELECT * FROM employees is an SQL statement.

4. related tables

A) Information about tables used in this course

I. customers

Save Customer Details

Ii. product_types

Save the product type sold by the store

Iii. products

Save product details

Iv. purchases

Information of the purchased customer

V. employees

Save employee details

Vi. salary_grades

Save employee salary level information

Vii. promotions

Save promotion information

5. Table details

A) MERs table

I. customer_id INTEGER

Ii. first_name VARCHAR2 (10)

Iii. last_name VARCHAR2 (10)

Iv. dob DATE

V. phone VARCHAR2 (12)

B) product_types table

I. product_type_id INTEGER

Ii. name VARCHAR2 (10)

C) products table

I. product_id INTEGER

Ii. product_type_id INTEGER

Iii. name VARCHAR2 (30)

Iv. description VARCHAR2 (50)

V. price NUMBER (5, 2)

D) purchases table

I. product_id INTEGER

Ii. customer_id INTEGER

Iii. quantity INTEGER

E) employees table

I. employee_id INTEGER

Ii. manager_id INTEGER

Iii. first_name VARCHAR2 (10)

Iv. last_name VARCHAR2 (10)

V. title VARCHAR2 (20 ),

Vi. salary NUMBER (6, 0)

F) salary_grades

I. salary_grade_id INTEGER

Ii. low_salary NUMBER (6, 0 ),

Iii. high_salary NUMBER (6, 0)

G) promotions table

I. promotion_id INTEGER

Ii. name VARCHAR2 (30)

Iii. duration interval day (3) to second (4)

6. Select all columns

A) You can add an asterisk (*) after the SELECT keyword to SELECT all columns or list all columns.

For example

SELECT *

FROM employees;

B. Shows the query result:

7. Select a specific column

A) You can specify the column names separated by commas in the SELECT statement. In this way, you can specify the table to display certain columns or adjust the Column Display sequence.

For example

SELECT employee_id, last_name

FROM employees;

B) the query result is shown in:

8. Write SQL statements

A) in order to construct a valid statement that is easy to read and easy to compile, its rules and guidelines are as follows:

I. SQL statements are case-insensitive.

Ii. the SQL statement can be one or multiple rows.

Iii. Keywords cannot be split into two or abbreviated words between two rows.

Iv. clauses are usually placed in separate rows, which enhances readability and is easy to edit.

V. use indentation to enhance readability

Vi. keywords are generally input in upper case letters; all other words are input in lower case.

9. arithmetic expression

A) Use Arithmetic Operators to create numeric and date data expressions.

B) arithmetic expressions can contain column names, constant values, and arithmetic operators.

10. Use Arithmetic Operators

A) For example:

SELECT employee_id, last_name, salary, salary + 500

FROM employees;

B. Shows the query result:

C) Description

I. The calculated salary + 500 columns are not new columns in the employees table. They are only used for marking.

Ii. By default, the new output column name comes from the formula used to generate the column.

11. operator precedence

A) */+-

B) multiplication and division operations have a higher priority than addition or subtraction.

C) operators with the same priority perform operations from left to right.

D) You can use parentheses to force statements to perform priority operations and make the order of statements clearer.

E) Example

SELECT maid, last_name, salary, salary/12 + 100

FROM employees;

F) shows the query result:

12. use parentheses

A) For example:

SELECT employee_id, last_name, salary, (salary + 10000)/12

FROM employees;

B. Shows the query result:

SELECT Basics

1. The Oracle Database contains multiple user types. Only database administrators (DBAs) can manage the database.

2. Oracle Database User Type

A) Database Administrator

B) Security Officer

C) Network Administrator

D) application developers

E) Application Administrator

F) database users

3. Database Administrator

A) each database must have at least one database administrator. For large databases, a group of database administrators are required. The main task of the database administrator is

I. Evaluate the hardware configuration of the Database Server

Ii. Install Oracle database software and patches

Iii. Plan the logical storage structure, database structure, and backup policy of the database

Iv. Create and enable or disable databases on a daily basis

V. Create and maintain the primary database storage structure and database objects

Vi. Manage Users and maintain system security

Vii. control and monitor user access to the database

Viii. Monitor and optimize database Behavior

Ix. Back up and restore the database

4. Other database user types

A) security officer

It mainly manages users, controls and monitors users' access to databases, and maintains database security.

B) Network Administrator

Manage Oracle network products

C) application developers

Design and develop database applications

D) Application Administrator

Manage specified applications

E) database users

Database users interact with databases through applications, such as adding, modifying, and deleting data.

5. Default database administrator user

A) when installing the Oracle database, the username SYS and SYSTEM are automatically created, the user password is set during installation, and the database administrator (DBA) is automatically assigned.

B) SYS user

This account has all database management functions. The basic tables and views in all database data dictionaries are stored in the user's SYS Schema, these basic tables and views are important for Oracle database operations.

C) SYSTEM user

This account can perform all other daily management tasks except for database backup and recovery and Database Upgrade.

Summary

Data Type

SQL statement type

SELECT Basics