Posts Tagged ‘syntax’
Learning DDL in Oracle
March 30, 2012 | 
Author the types of DDL in the oracle, namely:
A. CREATE TABLE
Used to create tables. Syntax is generally as follows:
CREATE TABLE [schema], table (column datatype [DEFAULT expr] [, ...]);
Example of a table is:
create table MHS (
nim varchar2 (7),
name varchar2 (15),
CPI number (3.2),
jml_e number (2)
);
Then to see the existing tables, can write
Select * from tab;
Then to see the description of the table, can write
Desc MHS;
2. ALTER TABLE
ALTER TABLE statement is used to:
-> Adding a new column
To add a new column, the following general syntax:
ALTER TABLE table ADD (column datatype [DEFAULT expr] [, column datatype] …);
alter table item add / modify the brand varchar (50);
add to add a field, while the Modify to edit the field;
alter table rename column name> table> name> field>;
to change the name of the field
MHS alter table add the address varchar (20);
-> Delete column
DROP COLUMN clause is used to remove the columns that are not needed anymore in the table. For example, to delete the address in the table above MHS.
MHS alter table drop column address;
-> Modify an existing column
We can modify the column by changing the data type, size and default values.
The syntax of the ALTER TABLE command to modify the columns as follows:
Modify the ALTER TABLE table (column datatype [DEFAULT expr] [, column datatype] …);
Example:
Modify the ALTER TABLE student (jml_e number (5));
Table altered.
3. DROP TABLE
Used to eliminate the table. Perform the deletion table with the DROP command, then do the following:
-> All the data and the structure of the table is deleted
-> All pending transactions will be commit
-> All indexes will be removed
-> The command can not drop this on-rollback
For example, if you want to remove MHS table, then that should be addressed:
DROP TABLE MHS;
4. RENAME
Used to merubaha name of an object (table, view, sequence or synonim). For example, if we want to change the student table, then the command to do are:
RENAME TO MHS students;
Posted in ddl script | 
Tags: column address, column name, constituent, cpi number, data definition language, data types, DDL, default values, drop column, e number, jml, number 3, syntax, table column, table schema | 
Comments Closed
Operation DDL (Data Definition Language)
DDL is a language that is used to manage or define a table.
Statements is:
- CREATE
- ALTER
- RENAME
- DROP
- TRUNCATE
In this article we will discuss about how to make table using the CREATE
In the manufacture of table and column names are the requirements to be met are:
- Must begin with alphabetic
- name length must be 1-30 characters
- Can only use AZ, az, 0-9, _, $, and #
- The table name and the name of His column should not be the same as the names and columns in other tables, the same user
- May not use names that have been reserved by the Oracle Server
such as: FROM, INSERT, WHERE, and others
Syntax is as follows:
CREATE TABLE [table_name] (
[Column_name] [type_kolom] [size column]
)
-Create a table MsEmployee
CREATE TABLE MsEmployee
(
EmployeeID CHAR (5),
EmployeeName VARCHAR (20),Email varchar2 (20),
Address varchar2 (30),
Salary NUMBER (10,2))
-Create a table TrHeaderTransaction
CREATE TABLE TrHeaderTransaction
(
TransactionID CHAR (5),
CustomerlD CHAR (5),
TransactionDate DATE)
Keep in mind, the table above belom interconnected. We can connect the two tables by using Constraint.
Usefulness CONSTRAINT:
- Creating rules in the table
- Avoid the pen-delete-an on the table if the table has a dependence
- Constraint Type is valid is as follows:
- PRIMARY KEY
- FOREIGN KEY
- NOT NULL
- UNIQUE
- CHECKED
Guidelines in using Constraint:
- We can provide the constraint name manually or can be generates by the Oracle Server using the command SYS_cn
- Constraint-making can be conducted simultaneously at the time we create a table or the table is completed by using the Alter Table command
- Defining the constraint can be performed on the column level or table level
CONSTRAINT [nama_constraint] [TYPE_CONSTRAINT]
-Making MsEmployee tables by using column-level constraint
CREATE TABLE MsEmployee
(
EmployeeID CHAR (5) PRIMARY KEY NOT NULL,
Cs002 CONSTRAINT CHECK (LENGTH (EmployeeID) = 5),
EmployeeName VARCHAR2 (20) NOT NULL,
Email varchar2 (20) UNIQUE,
Address varchar2 (30) NOT NULL,
Salary NUMBER (10,2) NOT NULL)
-Making TrHeaderTransactiondengan tables using table-level constraint
CREATE TABLE TrHeaderTransaction
(
TransactionID CHAR (5) NOT NULL,
EmployeeID CHAR (5) NOT NULL,
TransactionDate DATE NOT NULL,
AAB CONSTRAINT PRIMARY KEY (TransactionID),
Cs005 CONSTRAINT CHECK (LENGTH (TransactionID) = 5),
Ngehubungin CONSTRAINT FOREIGN KEY (EmployeeID) REFERENCES MsEmployee (EmployeeID) ON DELETE CASCADE)
Explanation:
- NOT NULL CONSTRAINT is used to ensure the column is filled so that no value is empty
- CONSTRAINT CHECKED used to check whether the data has been in accordance with predefined rules. In the above examples should be 5 characters long TransactionID not be less or more
- UNIQUE CONSTRAINT is used to ensure there are no charging value or the same name on other data so as to prevent duplication of data
- “Ngehubungin CONSTRAINT FOREIGN KEY (EmployeeID) REFERENCES MsEmployee (EmployeeID)” is used to link tables by table TrHeaderTransaction MsEmployee using EmployeeID EmployeeID as the connecting which is PRYMARY MsEmployee KEY of the table. Where Table MsEmployee as a parent and a child TrHeaderTransaction
- CONSTRAINT ON DELETE CASCADE is used to ensure the event’s pen-delete the parent table row, child rows referencing the value of the data is also deleted but not vice versa.
In Oracle we can also use DEFAULT Syntax as initial value data
example:
CREATE TABLE EMPLOYEES2 (
Hire_Date DATE DEFAULT SYSDATE / * hire_date column filled with a date now without doing the INSERT * /
)
Copying or copy a table
In addition to creating the table above we can also make a table of the contents and data types together with other tables
example:
CREATE MsEmp2
U.S.
SELECT * FROM MsEmployees
we also can determine which columns are willing to be copied
CREATE MsEmp2
U.S.
EmloyeeId SELECT, FROM EmployeeName MsEmployees
above query is used to copy the table along with its contents, if we want to copy only the table columns without data content that is by adding WHERE 1 = 2
example:
CREATE MsEmp2
U.S.
MsEmployees SELECT * FROM WHERE 1 = 2
Posted in ddl script | Tags: alter table, column name, column names, constraint name, data definition language, dependence, foreign key, oracle, oracle server, size column, syntax, table statements, truncate, two tables, type constraint | Comments Closed
Mastering The DDL Script Command
DDL is a sub language whose function is to define the SQL database and tables. The commands used are: CREATE, ALTER, and DROP.
Jump ajah well, so the table creation commands you are not experiencing the error, create a database electronicdb and then activate with the following example:
and switch on or connect it with the following command:
After you run the second command has been entered into the database electronikdb, and you can create a table in the database is active.
To understand the command to create a simple table, the condition you should already know what tables you will create, then image data to be stored in it. By using the command:
if it has been completed on the do command as above and there is no error, you can see and check with SHOW TABLES command as shown below:
Then we go back to the command to create a table tipe_barang:
Well, now my friends already have electronikdb database with two tables already contained in it, namely tables and tables jenis_barang tipe_barang.
There again, the SQL language, the command ALTER able to assist us in changing the data and its provisions when we forget to make during the first defines the table. You can change the column names, table names, add column, add primary key, delete the columns that exist there. For example, if we want to add a new column named description of type TEXT on jenis_barang table, the command as follows:
and you can see it with the command:
mysql> desc jenis_barang;
By using the same way, you can add a new column on the first part. For that, use the FIRST parameter, such as the following:
SYNTAX: alter table table_name add kolom_baru type (length) [FIRST | AFTER kolom_lama]
There is another command to change the name column:
SYNTAX: alter table table_name CHANGE kolom_lama kolom_baru type (length)
Image and video hosting by TinyPic
and as usual, can be checked using desc command.
The latter is in the SQL DROP command is used to remove components of an existing table. You can remove the primary key, or delete the table. Let not curious, straight at aja peep like the ways below.
To remove the primary key, you can use the DROP command. With a command like the following:
SYNTAX: alter table table_name DROP primary key
To delete a table column, with the following syntax and command as follows:
SYNTAX: alter table table_name DROP column_name
sources http://haidinda.wordpress.com/2009/01/09/menguasai-perintah-ddl/
Posted in ddl script | Tags: column names, ddl script, desc, image data, lama, provisions, script command, sql database, sql language, syntax, table creation, table names, tinypic, two tables | Comments Closed