Ddl data definition language
Download
1 / 35

DDL (Data Definition Language) - PowerPoint PPT Presentation


  • 75 Views
  • Uploaded on

DDL (Data Definition Language). Data Definition Language is used 1. to Create an object 2. to alter the structure of an object 3. to drop an object created. ankitg1689@gmail.com. How To Create Table?. Syntax : create table tablename (columnname data type(size),

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' DDL (Data Definition Language)' - maryam-oneal


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Ddl data definition language

DDL (Data Definition Language)

Data Definition Language is used

1. to Create an object

2. to alter the structure of an object

3. to drop an object created

ankitg1689@gmail.com


How to create table
How To Create Table?

  • Syntax :

    create table tablename

    (columnname data type(size),

    columnname data type(size), …);

  • We should specify unique column name

  • We should specify proper data type along with its width.

  • If the above command is executed successfully the message

    “table created” is displayed.

ankitg1689@gmail.com


Naming conventions for table name
Naming Conventions For Table Name

The table name should adhere strictly to the following norms

  • First letter should be alphabet

  • Reserve word cannot be used

  • Maximum length for table name is 30 characters

  • Two different tables cannot have same name

  • Underscore, numerals & letters are allowed but not blank space and

    Single quotes.

    Note:

    If the user uses double quotes then upper & lower case are not equivalent.

    e.g. “info”, “INFO”, “Info” are not same.

ankitg1689@gmail.com


Example to create table
Example To Create Table

  • Create table client_master

    (client_no varchar2(6), name varchar2(15),

    addr varchar2(25), bal_due number(10,2));

ankitg1689@gmail.com


How to view table structure
How to view table structure?

Command: desc

If user wants to view the table structure above command will achieve the same.

Syntax:

Desc (tablename);

Example:

desc client_master;

This will display structure of table client_master.

ankitg1689@gmail.com


How to insert data into table
How to Insert Data Into Table?

  • Insert Command:

    Once table is created it remains skeletal structure unless it is populated with data. The insert command is used to add one or more records to a table.

  • Syntax:

    insert into tablename values(expression,expression,…);

    OR

    insert into tablename

    (columnname, coumnname,…)

    values(expression,expression,…);

ankitg1689@gmail.com


Ddl data definition language

Insert Statement :

When inserting a single row of data into a table, the insert operation,

  • creates a new row in the database table

  • loads that row with the values passed into all the columns specified

  • In Insert statement columns & values have one to one relationship. (first value into first column)

  • If there are exactly same no. of values as per no. of columns & values are in accordance with the way the column were created; then no need to specify column name

  • If there are less values than the columns in the table then it is mandatory to indicate both the column name & its corresponding value in the insert statement.

ankitg1689@gmail.com


Ddl data definition language

(….continued)

  • While using ‘insert’ values are separated by commas and values having data types char, varchar/varchar2, raw, long, date should be enclosed in the single quotes.

  • Values must be entered in the same order as they are defined in the table.

ankitg1689@gmail.com


Ddl data definition language

  • Use Of ‘&’ In Insert Statement

    Syntax:

    Insert into tablename values(‘&expr1’, ‘&expr2’,…..);

    Oracle will prompt the user to enter values for specified columns.

  • Discussion of example.

ankitg1689@gmail.com


Ddl data definition language

Viewing Data In The Table

Select Command:

Request for information stored in a table can be done through the select command.

(This is generally referred as ‘querying’ the table.)

We can use ‘Select’ to view

1. All rows and all columns

2. Filtered data

ankitg1689@gmail.com


Ddl data definition language

  • All Rows And All Columns:

    Syntax

    In order to view global table data the syntax is,

    select columnname1, columnname2, ……..n from tablename;

    e.g.

    select cl_no, name, addr, bal_due from client_master;

    OR

    select * from tablename;

    e.g.

    select * from client_master;

    Note: Oracle allows the user to use the meta character

    asterisk(*), which is expanded by the oracle to mean all columns.

ankitg1689@gmail.com


Ddl data definition language

  • Filtering Table Data:

    SQL gives us filtering out data that is not required. The ways of filtering table data are,

    1. selected columns and all rows

    2. selected rows and all columns

    3. selected columns and selected rows

    Note: Oracle engine compiles the sentence, executes it, and retrieves data for all columns/rows from the table.

ankitg1689@gmail.com


Ddl data definition language

  • selected columns and all rows:

    It is retrieval of specific columns of a table.

    Syntax:

    select columnname ,columnname

    from tablename;

    Example:

    select client_no, name

    from client_master;

ankitg1689@gmail.com


Ddl data definition language

  • selected rows and all columns

  • Oracle provides the option of using where clause in combination with select statement to apply filter on rows.

  • Syntax

    Select *

    from tablename

    where search condition;

    Example

    Select *

    from client_master

    where bal_due>0;

ankitg1689@gmail.com


Ddl data definition language

  • Where Clause

  • When where clause added to SQL sentence, the oracle server compares each record from the table with the condition specified in where clause.

  • Oracle displays only those records that satisfy the specified condition.

  • Where clause can appear only after from clause.

  • In search condition all standard operators can be used. (logical, arithmetic, comparison)

ankitg1689@gmail.com


Ddl data definition language

  • Selected Rows And Selected Columns

    It is used to view specific data set from a table .

    Syntax:

    select columnname, columnname

    from tablename

    where search condition;

    Example:

    Select client_no, client_name

    from client_master

    where bal_due>0;

ankitg1689@gmail.com


Ddl data definition language

  • Elimination Of Duplicates:

  • To prevent selection of duplicate rows, we include distinct clause in the select statement.

    Syntax:

    select distinctcolumnname, columnname

    from tablename;

    The above syntax scans through the values of columns specified and displays unique values from amongst them.

    Example:

    select distinct job from employee;

ankitg1689@gmail.com


Ddl data definition language

(….continued)

Syntax:

select distinct *

from tablename;

  • The above syntax scans through entire rows, and eliminates rows that have exactly the same contents in each column.

    Example:

    select distinct * from client_master;

    This will select only unique row from client_master,

ankitg1689@gmail.com


Ddl data definition language

  • Sorting Data In a Table

  • Oracle allows data from a table to be viewed in a sorted order.

  • The rows retrieved from the table will be sorted in either

    ascending or descending order

  • Ascending or descending order of the data is depends on condition specified in the select clause.

    Syntax

    select * from tablename

    order by columnname, columname [sort order];

ankitg1689@gmail.com


Ddl data definition language

  • Ascending Order:

    select * from client_master

    order by client_no;

    The above query will display all records from client_master sorted in ascending order as per column client_no.

  • Descending Order:

    For viewing the data in descending order the word ‘desc’ must mentioned after the column name and before the semi colon in order by clause.

    In case there is no mention of sort order in order by clause Oracle engine sorts in ascending order by default.

    select client_no, name, addr1, city

    from client_master

    order by client_no desc;

ankitg1689@gmail.com


Ddl data definition language

  • Creating a Table From a Table:

    syntax:

    create table tablename

    [(columnname, columnname)]

    as select columnname, columnname from tablename;

  • Source Table: Source table is that identified in the select clause.

    Target Table: The target table is one specified In the create statement.

  • The above SQL statement will populate target table with the data from source table.

ankitg1689@gmail.com


Ddl data definition language

  • (….continued)

  • Example:

    create table course(Coursename, Fees, Stud_admit)

    as select Coursename, Fees, Stud_admit

    from college_info;

    If the source table college_info was populated with records these will be uploaded in course table.

ankitg1689@gmail.com


Ddl data definition language

  • How to create table structure from Source Table?

  • To create a target table without records from source table (only structure creation) , the select statement should contain a ‘where clause’.

  • The condition specified in where clause must not be satisfied.

ankitg1689@gmail.com


Ddl data definition language

  • Inserting Data Into a Table From Another Table:

    Oracle allows to populate a table with data that already exist in another table. (one row at a time into a table).

    Insertion of records from one table to another:

    Syntax:

    insert into tablename

    select columnname, columnname

    from tablename;

    Example:

    insert into Client_master

    select client_name, addr

    from client_info;

ankitg1689@gmail.com


Ddl data definition language

Insertion of data set from one table to another:

Syntax:

insert into tablename

select columnname, columnname

from tablename

where column=expr;

Example:

insert into Client_master

select client_name, addr

from client_info

where client_name=‘Akash’;

ankitg1689@gmail.com


Ddl data definition language

  • Delete Operations:

  • The Delete command is used to remove rows from table.

  • We can use delete command for two operations.

    1. To delete all the rows from table

    2. To delete a set of rows.

  • Removal of All Rows:

    Syntax:

    delete from tablename;

    Example:

    delete from client_master;

ankitg1689@gmail.com


Ddl data definition language

  • (…continued)

    Removal Of Specified Rows:

    Syntax:

    delete from tablename

    where search condition;

    Example:

    delete from client_master

    where bal_due<1000;

ankitg1689@gmail.com


Ddl data definition language

  • Updating Contents Of A Table:

  • Update command is used to change data values in a table.

  • We can use update command

    • To update all rows from a table

    • To update data set of rows from a table

      Updating All Rows:

      Syntax:

      update tablename

      set columnname=‘expr’;

      Example:

      update client_master

      set bal_due=5000;

ankitg1689@gmail.com


Ddl data definition language

  • Updating Records Conditionally:

    Syntax:

    update tablename

    set columnname=‘expr’

    where search condition;

    Example:

    update client_master

    set bal_due=5000

    where client_name=‘Thomus’;

ankitg1689@gmail.com


Ddl data definition language

  • How to Modify Structure Of Table?

    We can modify structure of table by,

    1. Adding new column

    2. Modifying existing column

    To Add New Columns:

    Syntax:

    alter table tablename

    add(newcolumnname datatype(size),…..n);

    alter table client_master

    add(client_tele numer(8));

ankitg1689@gmail.com


Ddl data definition language

  • Modifying Existing Column:

    Syntax:

    alter table tablename

    modify(columnname newdatatype(newsize),…..n);

    Example:

    alter table client_master

    add(client_tele numer(10));

ankitg1689@gmail.com


Ddl data definition language

  • Restrictions on the Alter Table:

    Alter table cannot be performed

    • to change name of the table

    • to change name of the column

    • to drop a column

    • Decrease size of a column if table data exists

ankitg1689@gmail.com


Ddl data definition language

  • To Rename Tables:

    Syntax:

    rename oldname to newname;

    Example:

    rename client_master to client_info;

ankitg1689@gmail.com


Ddl data definition language

ankitg1689@gmail.com


Ddl data definition language

ankitg1689@gmail.com