Chapter 1+2
Chapter 1+2
Chapter 1+2
1. Database Objects
2. SQL statements
3. Connect to database
- Cài đặt oracle client
\\file-server\Setup01\Database
\
o Cấu hình tnsnames
4. SQL Navigator
- Log in
- Open file
- Tool Bar
5. Table in course
- Name
- Statements
o Insert
o Update
o Delete
o Insert & Update
CHAPTER 2
1. Dual table
DUAL is a dummy table that you can use to view results from functions and calculations.
2. Data types
Guidelines
A LONG column is not copied when a table is created using a subquery.
A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
Only one LONG column can be used per table.
No constraints can be defined on a LONG column.
You might want to use a CLOB column rather than a LONG column.
3. Create / Alter table
- Create:
Syntax:
Syntax:
alter table
table_name
add
(
column1_name column1_datatype column1_constraint,
column2_name column2_datatype column2_constraint,
column3_name column3_datatype column3_constraint
);
Example:
alter table
author
add
(author_last_published date,
author_item_published varchar2(40));
- Drop table
The DROP TABLE statement removes the definition of an Oracle table. When you
drop a table, the database loses all the data in the table and all the indexes associated
with it.
Syntax
DROP TABLE table
In the syntax, table is the name of the table.
- Confirm
Syntax:
Describe table
Example
describe job_grades
4. Constraints
Constraints enforce rules at the table level.
Constraints prevent the deletion of a table if there are dependencies.
The following constraint types are valid:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
In the syntax:
schema is the same as the owner’s name
table is the name of the table
DEFAULT expr specifies a default value to use if a value is omitted in the
INSERT statement
column is the name of the column
datatype is the column’s data type and length
column_constraint is an integrity constraint as part of the column definition
table_constraint is an integrity constraint as part of the table definition
Example
6. Practices
a. Create the Employee, Department, Job_Grades table based on the following
table and confirm table is created
b. Create table Dept table based on the following table and confirm table is
created
c. Create table Emp table based on the following table and confirm table is
created.
d. Alter table Emp table add “Email” column. Confirm table is altered
e. Create table Emp2 table based on the structure of the Employees table. Include
only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and
DEPARTMENT_ID columns. Name the columns in your new table ID,
FIRST_NAME, LAST_NAME, SALARY , and DEPT_ID
CREATE TABLE employees2 AS
SELECT employee_id id, first_name, last_name, salary,
department_id dept_id
FROM employees;
f. Drop the Emp table.