, , ,

DDL statements

Data definition language (DDL) refers to the subgroup of SQL statements that create, alter, or drop database objects.
This sub-category of SQL statements is of particular interest to database architects or database administrators who must define an original database design and who must respond to requirements and extend a database design. It is also of interest to database application developers, because there are times when the easiest way to meet an application requirement is to extend an existing database object definition.
In general DDL statements begin with one of the following keywords: CREATE, ALTER, or DROP. Examples of DDL statements for creating database objects include: CREATE TABLE, CREATE TRIGGER, CREATE PROCEDURE, and CREATE SEQUENCE. These statements generally contain multiple clauses used to define the characteristics and behavior of the database object. Examples of DDL statements for altering database objects include: ALTER TABLE, and ALTER PROCEDURE. These statements generally are used to alter a characteristic of a database object.
DDL statements can be executed from a variety of interactive and application interfaces although they are most commonly executed in scripts or from integrated development environments that support database and database object design.
Describing Tables
The best way to think of a table for most Oracle beginners is to envision a spreadsheet containing several records of data. Across the top, try to see a horizontal list of column names that label the values in these columns. Each record listed across the table is called a row. In SQL*Plus, the command describe enables you to obtain a basic listing of characteristics about the table.
SQL> DESCRIBE po_headers_all
Name                       Type           Nullable Default                                                                                                Comments
————————– ————– ——– —————————
PO_HEADER_ID               NUMBER                                                                                                                                 
AGENT_ID                   NUMBER(9)                                                                                                                              
TYPE_LOOKUP_CODE           VARCHAR2(25)                                                                                                                           
LAST_UPDATE_DATE           DATE                                                                                                                                   
LAST_UPDATED_BY            NUMBER                                                                                                                                 
SEGMENT1                   VARCHAR2(20)   R2(1)
Commenting Objects
You can also add comments to a table or column using the comment command. This is useful especially for large databases where you want others to understand some specific bits of information about a table, such as the type of information stored in the table. An example of using this command to add comments to a table appears in the following block:
SQL> comment on table employee is
  2  ‘This is a table containing employees’;
Comment created.
You can see how to use the comment command for adding comments on table columns in the following code block:
SQL> comment on column employee.empid is
  2  ‘unique text identifier for employees’;
Comment created.
Tip    
Comment information on tables is stored in an object called USER_TAB_COMMENTS, whereas comment information for columns is stored in a different database object, called USER_COL_COMMENTS. These objects are part of the Oracle data dictionary. You’ll find out more about the Oracle data dictionary later in the book.
/by
, , ,

Inline view : Subqueries in a from Clause

You can also write subqueries that appear in your from clause. Writing subqueries in the from clause of the main query can be a handy way to collect an intermediate set of data that the main query treats as a table for its own query-access purposes. This subquery in the from clause of your main query is called an inline view. You must enclose the query text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later. The subquery can be a select statement that utilizes joins, the group by clause, or the order by clause
Select a.PO_HEADER_ID, a.Segment1, b.unit_price, b.Quantity
From PO_HEADERS_ALL a,
(
Select unit_price, Quantity, po_header_id
From PO_LINES_ALL
) b
Where a.PO_HEADER_ID=b.PO_HEADER_ID
Inline Views and Top-N QueriesTop-N queries use inline views and are handy for displaying a short list of table data, based on “greatest” or “least” criteria. For example, let’s say that profits for our company were exceptionally strong this year, and we want a list of the three lowest-paid employees in our company so that we could give them a raise. A top-N query would be useful for this purpose. Take a look at a top-N query that satisfies this business scenario:
SQL> select ename, job, sal, rownum
  2  from (select ename, job, sal from emp
  3        order by sal)
  4  where rownum <=3;
ENAME      JOB             SAL     ROWNUM
———-     ———         ———   ———
SMITH      CLERK           800         1
JAMES      CLERK           950         2
ADAMS      CLERK          1100       3
You need to know two important things about top-N queries for OCP. The first is their use of the inline view to list all data in the table in sorted order. The second is their use of ROWNUM—a virtual column identifying the row number in the table—to determine the top number of rows to return as output. Conversely, if we have to cut salaries based on poor company performance and want to obtain a listing of the highest-paid employees, whose salaries will be cut, we would reverse the sort order inside the inline view, as shown here:
SQL> select ename, job, sal, rownum
  2  from (select ename, job, sal from emp
  3        order by sal desc)
  4  where rownum <=3;
ENAME      JOB             SAL    ROWNUM
———- ——— ——— ———
KING       PRESIDENT      5000         1
SCOTT      ANALYST        3000         2
FORD       ANALYST        3000         3
/by
, , ,

Multiple-Column Subqueries

Notice that in all the prior examples, regardless of whether one row or multiple rows were returned from the sub query, each of those rows contained only one column’s worth of data to compare at the main query level. The main query can be set up to handle multiple columns in each row returned, too. To evaluate how to use multiple-column sub queries, let’s consider an example
Select *
From PO_LINES_ALL
Where (PO_HEADER_ID, PO_LINE_ID) IN
(
Select PO_HEADER_ID, PO_LINE_ID
From PO_LINE_LOCATIONS_ALL
WHERE QUANTITY_RECEIVED < QUANTITY/2
AND CLOSED_CODE <> ‘CLOSED FOR RECEIVING’
)
The benefit of writing query in above format is that separating the requirements in tables. From PO_LINE_LOCATIONS_ALL we are only taking those data which are relevant for our purpose and our end aim is to view the PO_LINEA_ALL entries corresponding to some required conditions satisfied by entries in PO_LINE_LOCATIONS_ALL.

/by
, , ,

Multi row subquery

A multi row subquery   returns one or more rows.  Since it returns multiple values, the query must use the set comparison operators (IN,ALL,ANY).   If you use a multi row subquery with the equals comparison operators, the database will return an error if more than one row is returned.

Exampe:
select last_name from employees where manager_id in
(select employee_id from employees where department_id in
(select department_id from departments where location_id in
(select location_id from locations where country_id=’UK’)));

with
You can improve the performance of this query by having Oracle9i execute the subquery only once, then simply letting Oracle9i reference it at the appropriate points in the main query. The following code block gives a better logical idea of the work Oracle must perform to give you the result. In it, the bold text represents the common parts of the subquery that are performed only once, and the places where the subquery is referenced:

SQL> with summary as
  2  (select dname, sum(sal) as dept_total
  3   from emp, dept
  4   where emp.deptno = dept.deptno
  5   group by dname)
  6  select dname, dept_total
  7  from summary
  8  where dept_total >
  9  (select sum(dept_total) * 1/3
 10   from summary)
 11  order by dept_total desc;
DNAME                DEPT_TOTAL
——————– ———-
RESEARCH                  10875

/by
, , ,

Single-Row Subqueries

The main query expects the sub query to return only one value.
Check out the following example, which should look familiar:
SQL>  select ename, deptno, sal
  2   from emp
  3   where deptno =
  4    ( select deptno
  5      from dept
  6      where loc = ‘NEW YORK’ );
ENAME         DEPTNO       SAL
———-         ———         ———
CLARK             10            2450
KING               10            5000
MILLER            10            1300
Though the above query results have 3 rows it is a single-row subquery Because, the subquery on the DEPT table to derive the output from EMP returns only one row of data.
/by