Document ID:         2180.1
Subject:            COMMON SQL AND SQL*PLUS Q&A
Last Revision Date: 31 July      1996
Author:		    Michael Kennedy


Q- Can two different users own tables with the same name?
A- Yes, tables are unique by username.tablename.
   NOTE Problems will occur if synonyms are used to make
        table names the same name; Private synonyms
        override public synonyms.

Q- How do you produce a report with totals ontop of each
   other.
A-    SET FEEDBACK OFF
      SELECT col1,col2
      FROM tab1,tab2
      WHERE x=x;
      SET HEADING OFF
      SELECT '                       TOTAL 1:'A,SUM(X),
             '                       TOTAL 2:'B,SUM(Y)
      FROM tab1,tab2;

Q-How do you skip a page in a SQL*Plus report?
A-
    BREAK ON X SKIP PAGE;
    COMPUTE SUM OF Y ON X;
    SELECT fld1,fld2
    FROM tab1,tab2
    WHERE x=y
    GROUP BY fld1,fld2 ORDER BY fld1;

Q-Is it possible update another table with 2 concatenated
  columns?
A-Yes, the following update statement will work.
  UPDATE tablename SET colname = columnx || columny
  WHERE colname = xxx
  NOTE The result is truncated to 255 characters.

Q- How can I put formatted fields into TTITLE?
A- The following SQL*Plus commands will work :
   COLUMN deptno NEW_VALUE mikevar
   BREAK ON deptno
   TTITLE CENTER 'Department Number : ',FORMAT 9999.999
                 mikevar
   SELECT deptno,ename
   FROM emp
   WHERE deptno=10;

Q- How can a new view be created from two old views which
   have common field names?
A- Use aliases in the fields.
   EG. CREATE VIEW x AS
       SELECT tab1.fld1 afld1, tab2.fld1 bfld1
       FROM tab1,tab2
       WHERE tab1.fld1=tab2.fld1;

Q- Can columns be called TO and FROM?
A- These are reserved words and cannot be used to name
   database objects.

Q- Is it ok to have % in the field name
A- Yes! Recommended No! It can be confused with the wildcard
   symbol '%' used in the LIKE clause.

Q- Can you index columns defined in views?
A- View columns themselves cannot be indexed however the
   view is dynamically generated each time it is used and
   in this creation indices will be if the query to set up
   the view requires it.

Q- How do I get all the data from tableA plus all the data
   in tableB which matches the key in tableA. For the
   records in tableB selected get from table C some data
   corresponding to a key in table B. Sort the output by
   fld1 and fld2.
A-
     SELECT a.fld1,a.fld2,b.fld3,c.fld4
     FROM   taba,tabb,tabc
     WHERE  KEY IN A = KEY IN B AND
                   KEY IN B = KEY IN C
UNION
     SELECT a.fld1,a.fld2,to_number(null),null {nEED TO
     FROM   taba                                CAST NUMBER
     WHERE  NOT EXISTS                          NULLS}
            (SELECT null
             FROM   taba
             WHERE  KEY IN A = KEY IN B)
ORDER BY 1,2; {Numbers not columns name}

Q- Is it possible to prompt a user for data when running a
   SQL script?
A- The following method can be used :
   SELECT fld1,fld2
   FROM tab1,tab2
   WHERE key1='&key1';
   Prompts for Enter value from key1: when data is entered
   this produces an old/new information listing which may
   be turned off with
   SET VERIFY OFF
   If && is used the value is prompted for once and then
   used automatically if that value is used again during
   that SQL*Plus session.

Q- Why when writing data to a spool file with headings off
   are columns of numbers (defined as default) taking up
   more than the default 10 spaces.
A- The values of the fields are between 0-10 characters
   (over 10 char numbers are shown as 123456+E10)
   the extra spaces are caused when the column title
   header is greater than 10 characters.

Q- Why is it when the column format command is used the
   resulting output for numbers (ie 999) is always 1 char
   wider than specified?
A- This is because a space is left for the sign.

Q- How do I join a number and character field together
   without a sign space between them?
A- Concatenate the number and character field using column
   format :
   COLUMN fld1||fld2 FORMAT A5;
   SELECT fld1||fld2,fld3
   FROM table;

Q- Are SIN/COSIN avaliable in SQL*Plus?
A- No, These functions are avaliable through Pro*C or
   SQL*ReportWriter.

Q- How do you select the first 10 rows of a "table.
A- Use system variable rownum in where clause
   eg. SELECT *
       FROM table1
       WHERE ROWNUM < 11;

Q- How do I return the first 10 values which occur must
   frequently?
A- CREATE VIEW v1 AS
   SELECT name, count(*) num
   FROM table
   GROUP BY name;

   SELECT name,num
   FROM v1 a
   WHERE 10 > (SELECT COUNT(*)
       FROM v1
       WHERE a.num < num)
   ORDER BY num;