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
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 :
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:
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
GROUP BY name;
FROM v1 a
WHERE 10 > (SELECT COUNT(*)
WHERE a.num < num)
ORDER BY num;