A.2.19___A.2.20.docx

Data Definition Language (DDL) Statements

Data definition language (DDL) statements enable you to perform these tasks:

Create, alter, and drop schema objects

To create a table, you can give the following statement

create table emp (empno number(5) primary key,

                   name varchar2(20),

                   sal number(10,2),

                   job varchar2(20),

                   mgr  number(5),

                   Hiredate  date,

                   comm number(10,2));

Now Suppose you have emp table now you want to create a TAX table with the following structure and also insert rows of those employees whose salary is above 5000.

create table tax (empno number(5), tax number(10,2));

You can also use CREATE TABLE AS statement to create copies of tables. Like to create a copy EMP table as EMP2 you can give the following statement.

create table emp2 as select * from emp;

To copy tables without rows i.e. to just copy the structure give the following statement

create table emp2 as select * from emp where empno>=5;

Alter

Use the ALTER TABLE statement to alter the structure of a table.

Examples:

To add  new columns addr, city, pin, ph, fax to employee table you can give the following statement

alter table emp add (addr varchar2(20), city varchar2(20),

      pin varchar2(10),ph varchar2(20));

To modify the datatype and width of a column. For example we you want to increase the length of the column ename from varchar2(20) to varchar2(30) then give the following command.

alter table emp modify (ename varchar2(30))

To decrease the width of a column the column can be decreased up to largest value it holds.

alter table emp modify (ename varchar2(15));

To drop columns.

From Oracle Ver. 8i you can drop columns directly it was not possible in previous versions.

For example to drop PIN, CITY  columns from emp table.

alter table emp drop column (pin, city);

Rename

Use the RENAME statement to rename a table, view, sequence, or private synonym for a table, view, or sequence.

• Oracle automatically transfers integrity constraints, indexes, and grants on the old object to the new object.
• Oracle invalidates all objects that depend on the renamed object, such as views, synonyms, and stored procedures and functions that refer to a renamed table.

Example

To rename table emp2 to employee2 you can give the following command.

rename emp2 to employee2

Drop

Use the drop statement to drop tables, functions, procedures, packages, views, synonym, sequences, tablespaces etc.

Example

The following command drops table emp2

drop table emp2;

If emp2 table is having primary key constraint, to which other tables refer to, then you have to first drop referential integrity constraint and then drop the table. Or if you want to drop table by dropping the referential constraints then give the following command

drop table emp2 cascade constraints;

The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands:

The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands: 

INSERT 

The INSERT command in SQL is used to add records to an existing table. Returning to the personal_info example from the previous section, let's imagine that our HR department needs to add a new employee to their database. They could use a command similar to the one shown below: 

INSERT INTO personal_info 
values('bart','simpson',12345,$45000) 

Note that there are four values specified for the record. These correspond to the table attributes in the order they were defined: first_name, last_name, employee_id, and salary. 

SELECT 

The SELECT command is the most commonly used command in SQL. It allows database users to retrieve the specific information they desire from an operational database. Let's take a look at a few examples, again using the personal_info table from our employees database. 

The command shown below retrieves all of the information contained within the personal_info table. Note that the asterisk is used as a wildcard in SQL. This literally means "Select everything from the personal_info table." 

SELECT * 
FROM personal_info 

Alternatively, users may want to limit the attributes that are retrieved from the database. For example, the Human Resources department may require a list of the last names of all employees in the company. The following SQL command would retrieve only that information: 

SELECT last_name 
FROM personal_info

Finally, the WHERE clause can be used to limit the records that are retrieved to those that meet specified criteria. The CEO might be interested in reviewing the personnel records of all highly paid employees. The following command retrieves all of the data contained within personal_info for records that have a salary value greater than $50,000: 

SELECT * 
FROM personal_info 
WHERE salary > $50000 

UPDATE 

The UPDATE command can be used to modify information contained within a table, either in bulk or individually. Each year, our company gives all employees a 3% cost-of-living increase in their salary. The following SQL command could be used to quickly apply this to all of the employees stored in the database: 

UPDATE personal_info 
SET salary = salary * 1.03 

On the other hand, our new employee Bart Simpson has demonstrated performance above and beyond the call of duty. Management wishes to recognize his stellar accomplishments with a $5,000 raise. The WHERE clause could be used to single out Bart for this raise: 

UPDATE personal_info 
SET salary = salary + $5000 
WHERE employee_id = 12345 

DELETE 

Finally, let's take a look at the DELETE command. You'll find that the syntax of this command is similar to that of the other DML commands. Unfortunately, our latest corporate earnings report didn't quite meet expectations and poor Bart has been laid off. The DELETE command with a WHERE clause can be used to remove his record from the personal_info table: 

DELETE FROM personal_info 
WHERE employee_id = 12345