Sql questions
Sql questions and answers(IBM,POLARIS,HONEYWELL)
1. What is difference between DBMS and RDBMS?
The function count() executes faster when given a criteria/value which is quicker to resolve in the sql processing mechanism.Thus, an integer or rowid are faster to resolve than an '*'which is a wild card symbol for all the colums in a table and hence it is more time consuming.
19. What are date functions?
1. What is difference between DBMS and RDBMS?
In DBMS normalization process will not be present; there is no concept of primary and foreign key. In RDBMS normalization process will be present to check the database table consistency
Normalization is the process of efficiency organizing data in database
De-normalization is the process of attempting the performance of a database by adding redundant data.
A subquery is a select statement that is embedded in a clause of another select statement. You can build powerful statements out of simple once by using sub queries.
EX: select ename, sal from emp where sal >(select sal from emp where ename ='FORD') /
DIFFERENCE
The inner query executes first and finds a value, the outer query executes once using the value from the inner query(subquery)
Fetch by the outer query, execute the inner query using the value of the outer query, use the values resulting from the inner query to qulify or disqualify the outer query(correlated)
Join is the form of select command that contains information from two or more tables
Equi , inner ,simple join:- based on the equality condition in the where clause tables are joined only matching records are displayed, joining tables must have at least one common column with same data type and same value
Self join: - joins a table to itself as though it were two separate tables. it is used on same table, the table must have at least 2 column with same data type and value
Outer join: - outer joins gives the non-matching records along with matching records
Left outer join: - it will display the all matching records and the records which are in the left table. Even if there is no match in the right side table
Right outer join:- it will display the all matching records and the which are in the right table even if there no match in the left side table
Full outer join:- it will display matching records and non-matching records both table
Each value in oracle is manipulated by a data type
1. Character data type: - They store character data which can alphanumeric data
2. Number data types: - Stores numeric data
3. Date time data types:- stores date time information likes dd-mon-yy 12:00:00
4. Rowid data types: - each row in the database has as address,
The nvl function is used to convert a null value to an actual value.
Syntax: nvl(expr1 , expr2)
Expr1 : is the source value or expr may contain null
Expr2: is the target value for converting null
Nvl2 function contain 3 expressions , null if function contain 2 expressions
Nvl2 function: syntax: nvl2(expr1 , expr2 , expr3 )
If expr 1 is not null nvl2 returns expr2. If expr1 is null nvl2 returns expr3.
Null if function: syntax null if (expr1 ,expr2)
Compares two expressions and returns null if they are equal or the first if they are not null.
Union is the sql key word used to merge the results of two or more tables using a select statement, contain same fields with removed duplicate values.
Union all does same, however it persists duplicates
10. What is data dictionary?
Data dictionary is a collection of database object it’s user , it stores privileges of oracle users.
Single row function
Single row functions can be categorized into five. These will be applied for each row and produces individual output for each row. Accept character data as input and can return both char and numeric values
1. Numeric functions
2. String functions
3. Date functions
4. Miscellaneous functions
5. Conversion functions
Multi row function
Multi row functions will be applied on all the rows but produce single row output
Ex: Sum, avg, max, min, count.
View is a logical table, through which a selective portion of the data from one or more tables can be seen, view is stored as a select statement in the database. Views are used to restrict access to the database table
Materialized view is a physical table selective portion of the data from table’s periodically can be seen, it is used to improve the performance, useful in distributed environment
Difference
1. View is a logical table
2. View can hold the query
3. We can’t create indexes on view
4. View will create security purpose
1. Mv is a physical tale
2. Mv can hold the query with refresh data
3. We can create indexes on mv
4. Mv will create performance issues
Oracle supports several special-purpose data elements that are not actually contained in a table, but are available for use in SQL statements as if they were part of the table.
Here is a partial list of pseudo-columns in Oracle. For a complete list refer to Oracle's reference guide.
ROWNUM
For each row of data returned by a SQL query, ROWNUM will contain a number indicating the order in which the row was retrieved. For example, the first row retrieved will have a ROWNUM of 1, the second row will have a ROWNUM of 2, and so on. This approach can be useful for limiting the number of rows returned by a query. To display only ten rows of the emp table, the following SQL statement makes use of the ROWNUM pseudo-column:
For each row of data returned by a SQL query, ROWNUM will contain a number indicating the order in which the row was retrieved. For example, the first row retrieved will have a ROWNUM of 1, the second row will have a ROWNUM of 2, and so on. This approach can be useful for limiting the number of rows returned by a query. To display only ten rows of the emp table, the following SQL statement makes use of the ROWNUM pseudo-column:
SELECT *
FROM emp
WHERE ROWNUM < 11
FROM emp
WHERE ROWNUM < 11
NOTE:
ROWNUM returns a number indicating the order in which the row was retrieved from the table, but this is not always the order in which a row is displayed. For example, if a SQL statement includes an ORDER BY clause, rows will not be displayed in ROWNUM sequence, since ROWNUM is assigned before the sort operation.
ROWNUM returns a number indicating the order in which the row was retrieved from the table, but this is not always the order in which a row is displayed. For example, if a SQL statement includes an ORDER BY clause, rows will not be displayed in ROWNUM sequence, since ROWNUM is assigned before the sort operation.
CURRVAL
When using Oracle SEQUENCE values, the pseudo-column CURRVAL returns the current value of the sequence. To reference CURRVAL, it must be associated with a sequence, for example:
When using Oracle SEQUENCE values, the pseudo-column CURRVAL returns the current value of the sequence. To reference CURRVAL, it must be associated with a sequence, for example:
schema.sequence_name.CURRVAL
NEXTVAL
When using Oracle SEQUENCE values, the pseudo-column NEXTVAL returns the next value of the sequence and causes the sequence to increment by one. You can only reference NEXTVAL if it is associated with a sequence. For example:
When using Oracle SEQUENCE values, the pseudo-column NEXTVAL returns the next value of the sequence and causes the sequence to increment by one. You can only reference NEXTVAL if it is associated with a sequence. For example:
schema.sequence_name.NEXTVAL
USER
This pseudo-column will always contain the Oracle username under which you are connected to the database.
This pseudo-column will always contain the Oracle username under which you are connected to the database.
SYSDATE
This pseudo-column will contain the current date and time. This column is a standard Oracle DATE datatype. The value represents the current time on the server not the client.
This pseudo-column will contain the current date and time. This column is a standard Oracle DATE datatype. The value represents the current time on the server not the client.
Or
A pseudo-column is an Oracle assigned value (pseudo-field) used in the same context as an Oracle Database column, but not stored on disk. SQL and PL/SQL recognizes the following SQL pseudocolumns, which return specific data items: SYSDATE, SYSTIMESTAMP, ROWID,ROWNUM, UID, USER, LEVEL, CURRVAL, NEXTVAL, ORA_ROWSCN, etc.
Pseudo columns are not actual columns in a table but they behave like columns. For example, you can select values from a pseudo column. However, you cannot insert into, update, or delete from a pseudo column. Also note that pseudo columns are allowed in SQL statements, but not in procedural statements.
14. What is decode function
Decode will act as value by value substitution. For every value of field, it will checks for a match in a series of if/then tests.
Ex:
SQL> select sal, decode(sal,500,'Low',5000,'High','Medium') from emp;
15. What is case?
Case is similar to decode but easier to understand while going through coding
Ex:
SQL> Select sal,
Case sal
When 500 then ‘low’
When 5000 then ‘high’
Else ‘medium’
End case
From emp;
16. What is sequence and advantages?
Oracle sequence generates a series of unique numbers for numeric column of a database’s tables
17. What is inline function?
18. Difference b/w count (1) and count (*)
19. What are date functions?
Sysdate
Current_date
Current_timestamp
Systimestamp
Localtimestamp
Dbtimezone
Sessiontimezone
To_char
To_date
Add_months
Months_between
Next_day
Last_day
Extract
Greatest
Least
Round
Trunc
New_time
Coalesce
20. Lpad function and rpad functions
Lpad function to pad the left side of a column with any set of characters.
Ex: select lpad('computer',15,'*');
Rpad function to pad the right side of a column with any set of characters.
Ex: select rpad('computer',15,'*');
21. String in function
22. What is table space?
A data base divided into logically storage unit called table spaces .a table spaces is used to grouped related logical structures together. A table space consists of at least on data file.
23. Difference b/w having clause and where clause
The where clause select rows before grouping. The having clause select rows after grouping
------------ by lakshmi
