Very Common Questions on Oracle SQL


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- Is it possible to update another table with 2 concatenated columns?
A- Yes, the following update statement will work:
UPDATE tablename SET colname = columnX || columnY
WHERE colname = ¤t_value_for_this_column ;
NOTE: The result is truncated to 255 characters.

Q- How can a new view be created from two old views that have common field names?
A- Use aliases in the fields. For example:
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 allowed to have % in the field name?
A- Allowed, 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- No, view columns themselves cannot be indexed.

 

Q- Is it possible to prompt a user for data when running a SQL script?

A- Yes, prefix the column name with & or &&.

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- How do you select the first 10 rows of a table?
A- Use system variable rownum in where clause. For example:
SELECT *
FROM table1
WHERE ROWNUM < 11;

Q- How do I return the first 10 values that occur most 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;

Advertisements

5 thoughts on “Very Common Questions on Oracle SQL

  1. You can have a table with column names “TO” or “FROM”. Try:

    CREATE TABLE bogus as
    select dummy “TO”, dummy “FROM” from dual;

    Like

  2. Trigonometric functions like SIN and COS are available in SQL, and thus SQL*Plus

      1* select sin(3.14159/2), cos(3.14159/2) from dual
    SQL> /
    
    SIN(3.14159/2) COS(3.14159/2)
    -------------- --------------
                 1     1.3268E-06
    

    The query

    SELECT *
    FROM table1
    WHERE ROWNUM < 11

    does not return the first 10 rows for any reasonable definition of “first”. It returns an arbitrary 10 rows from the table. Since heap-organized tables are essentially unordered, you would need to supply an ORDER BY clause to identify what rows are “first”. And since ROWNUM is applied before ORDER BY, you’ll need a nested query

    SELECT *
    FROM (
    SELECT *
    FROM your_table
    ORDER BY some_column )
    WHERE rownum <= 10;

    Additionally, it will be more efficient to get the most common values by scanning the table once, rather than twice, i.e.

    SELECT col, cnt
    FROM (
    SELECT col, COUNT(*) cnt
    FROM your_table
    GROUP BY col
    ORDER BY COUNT(*)
    )
    WHERE rownum <= 10

    Like

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s