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;
Just a quick note, in 11g, you can create Virtual Columns that CAN be indexed. So technically, you can index a column in a view.
chet
LikeLike
You can have a table with column names “TO” or “FROM”. Try:
CREATE TABLE bogus as
select dummy “TO”, dummy “FROM” from dual;
LikeLike
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-06The 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
LikeLike
i got your point, i remove that question as that is not relevant in article. thanks for highlighting.
LikeLike