Oracle Foundation Training

First, Oracle Foundation SQL Practice

Class table: Class number (primary key, identity column), class name, head teacher, commencement date
Teacher's Table: Teacher number (primary key, identity column), name, gender (default ' male '), Social Security number (must be 18 digits), contact number
Student Table: School Number (primary key, fixed five digits), class number, name, gender, date of birth, contact number, home address
Score Table: Number (primary key, identity column), study number, account name, fraction

1, Form create TABLE ();
Grammar
CREATE TABLE [schema.] Table
(column datatype [DEFAULT expr],
[...]
);
The schema represents the name of the owner of the object, table represents the name of the tables, default represents the Defaults, column represents the names of the columns, and datatype represents the data type and length

2. Insert Data test
3. Query data test
4. Constraints: A. Non-null constraint
B. Primary KEY constraint (PRIMARY key): Not nullable
C. Uniqueness constraint (unique): null value can be inserted
Note: There are two ways to increase the constraint:
Method One: In the construction table statement;
Method Two:
ALTER TABLE T_country ADD constraint Pk_countrycode primary key (CountryCode);

5. Delete Tables: Drop table

6. Insert Data:
A. Full column insert:
B. Partial column insertion:
C. Inserting records from other tables

7. Query data
A. Full-column query:
B. Partial column queries:
C. Conditional query:
Note: Display a different name for the column

8. Delete the data record in the table (the difference from the delete table)
Remove DELETE from: line by row
Delete truncate directly:

9. Update Data updates:

10, data type classification:

A. Text data type
Fixed length type (Char/nchar)
Variable length type (VARCHAR2/NVARCHAR2)
Note: A variable-length string, unlike the char type, does not use spaces to fill the maximum length. VARCHAR2 can store up to 4,000 bytes of information.

B. Date and time
Date
Timestamp: Time can be a fraction of a second

C. Numbers
Number (P, s)--p is precision, refers to the total number of digits-s refers to the number of decimal places, the number of digits to the right of the decimal point

D.lob types (1~4GB, storing unstructured information, such as sound clips, video files)
CLOB: Storing character data, such as unstructured XML documents
BLOBs: Shapes, video clips, sound files
BFILE: Storing binaries in operating system files outside of the database

11. ALTER Table Command
A. User to add a new column:
ALTER TABLE <table name> ADD (column definition ...);
B. Modify the width of the data type or the data type itself:
ALTER TABLE <table name> MODIFY (column definition ...);
eg. ALTER TABLE vendor_master MODIFY (venname VARCHAR2 (25));
Note: You cannot reduce the length of a table when the column is not empty
C. Add or remove integrity constraints:
ALTER TABLE T_country ADD constraint Pk_countrycode primary key (CountryCode);
D. Physically delete the column column name and its corresponding data:
ALTER TABLE table_name DROP COLUMN column_name;

12. DESC View Table Structure command: DESC table_name

13. Transaction Processing Commands
COMMIT
Save point: SavePoint savepoint_id;
Rollback:rollback; ROLLBACK to SavePoint save_pt;

14. Creating a copy of an existing table create table as ....

15. Foreign key (the foreign key of the child table refers to the primary key of the parent table)
Insert child table data: The parent table related records must be inserted first;
Delete parent table data: the child table related records must be deleted first;

Exercise: Join a foreign key and attempt to insert data from a child table that is not in the parent table, deleting the records from the parent table's sub-tables:
CREATE TABLE T_buyerinfo
(
Buyernoint primary Key Auto_increment,
Chnnamevarchar (50),
Countrycodevarchar (10),
Buyeraddrvarchar (300),
Builddatedate,
Yearsaledouble,
CONSTRAINT t_buyer_cc_fk FOREIGN KEY (CountryCode)
REFERENCES T_country (CountryCode)
);

16. Related queries
INNER JOIN (equivalent join) returns only rows that have the same join field in two tables
Left join returns records that include all the records in the left table and the equivalent of the junction fields in the right table
Right join returns records that include all records in the right table and the junction fields in the left table

13, Operator: =,>,<,>=,<=,,<>,!=,between and not between

14. Logical Expression
? And:1 and 1 = 1; 1 and 0 = 0; 0 and 0 = 0;
? Or:1 OR 1 = 1; 1OR 0 = 1; 0 or 0 = 0;
? Not:select * from T_buyerinfo where not yearsale>900000

15. Wildcard Characters
One character: ' _ '
string of any length:%

16. Inquiry details
Sort query: Desc (Descending) ASC
Use as to name the column:
Use constant columns:
Limit number of fixed lines: rownum
Between
Is null
Inch
rowID

17. Functions
A. String functions:
Length (String): Find string Lengths
Lower (String): Turn lowercase
Upper (String): Turn uppercase
REPLACE (str, FROM_STR, TO_STR)

B. Date function
Months_between (D1,D2),: Find the number of months between two dates;
Last_day (d), D represents the date: returns the corresponding date on the last day of the month;
Sysdate:

C. Mathematical functions
ABS (x) returns the absolute value of X
Power (N1,N2): Seeking N1 's N2
Round (N1,N2): Returns the number of the N1 decimal point as the N2 bit, rounding method

D. Aggregation functions (in select queries commonly used in GROUP BY clauses)
Avg (COL) returns the average of the specified column
Count (COL) returns the number of non-null values in the specified column
Min (col) returns the minimum value of the specified column
Max (COL) returns the maximum value of the specified column
SUM (COL) returns the sum of all values of the specified column

E. Conversion functions
To_char (D|N,FMT): Converts a date or number to a string in the specified format
To_char (sysdate, ' yyyy '-"MM"-"DD")
To_char (23457, ' $99,999.00 ')
RR: Returns the century year value for the specified date
To_date (S,[fmt]):
Converting a char or varchar data type to a date data type
To_date (' 2-11-2004 ', ' month-dd-yyyy ')
To_number (s): Converts a string containing numbers into a number type that can perform arithmetic operations

F. Other:
Select Case ' 1 ' if ' 0 ' then ' Male ' when ' 1 ' then ' female ' end case from dual;
Select decode (Gender, ' 0 ', ' Male ', ' 1 ', ' female ') from dual

18. Sequence: Generates a unique, contiguous integer used to generate a primary key value
A.nextval: The initial value of the sequence is returned when you first use Nextval
B.currval: Returns the current value of the sequence, which is the value returned when the last reference to Nextval

CREATE SEQUENCE <sequencename>
INCREMENT by N
START with N
[MAXVALUE N] [MINVALUE N]
[cycle| Nocycle]
[CACHE n| NOCACHE];
Note: INCREMENT by N: Specifies the integer interval between sequence numbers
START with N: Specifies the first sequence number to be generated
MINVALUE N: Specifies the minimum value of the sequence
MAXVALUE N: Specifies the maximum value that a sequence can generate
Cycle: Specifies that the sequence should continue to generate values even if the maximum value is reached, usually the loop produces the sequence value from the beginning
CACHE: Allows faster generation of serial numbers, Oracle assigns serial numbers, and saves them in memory for faster access

C. Deleting a sequence
Grammar:
DROP SEQUENCE <sequencename>;
Example
DROP SEQUENCE Member_seq;
To view a sequence:
select * from SEQ;

19. Views: Customized representations to display data from one or more tables, also known as "virtual tables"
CREATE [OR REPLACE] VIEW viewname[(Alias,alias,...)]
As subquery
With CHECK OPTION [CONSTRAINT ConstraintName]
With READ only [CONSTRAINT ConstraintName]

20, index (speed up the execution of SQL statements, careful, specific circumstances need specific analysis)
A. Unique index: Ensure that there are no duplicate values in the column that defines the index
CREATE UNIQUE index index name on table name (field name);
B. Composite index: An index created on multiple columns of a table that can improve data access speed for queries that contain multiple columns in a WHERE clause
CREATE index index name on table name (column name 1, column name 2);
C. Partitioned indexes: Indexes are stored in different partitions and correspond to different table spaces, as table partitions
CREATE INDEX IndexName on tablename (C1,C2,...) [Global/local]
PARTITION by RANGE (C2)
PARTITION VALUES less THAN (v1) tablespace tb1,
PARTITION VALUES less THAN (v2) tablespace TB2;

Oracle Foundation Training