41⟩ Why does the following command give a compilation error?
DROP TABLE &TABLE_NAME; Variable names should start with an alphabet. Here the table name starts with an '&' symbol.
SQL
Home Databases Programming SQL
“SQL Interview Questions and Answers will guide us now that SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon Relational Algebra. So learn SQL or get preparation for the job of SQL (Structured Query Language) with the help of this SQL Interview Questions with Answers guide”
172 SQL Questions And Answers
42⟩ Operators used in SELECT statements.
= Equal
<> or != Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
43⟩ What is SQL Mutating Table?
Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Constraining Table is a table that a triggering statement might need to read either directly for a SQL statement or indirectly for a declarative Referential Integrity constraints. Pseudo Columns behaves like a column in a table but are not actually stored in the table. E.g. Currval, Nextval, Rowid, Rownum, Level etc.
44⟩ What is SQL Rollback?
Rollback causes work in the current transaction to be undone.
45⟩ What are SQL Data types?
Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits.
Cannot Query on a long column.
Char, Varchar2 Max. size is 2000 & default is 1 byte.
Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.
Long Character data of variable length upto 2GB.
Date Range from Jan 4712 BC to Dec 4712 AD.
Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes.
Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.
46⟩ What is sql Intersect?
Intersect is the product of two tables listing only the matching rows.
47⟩ Under which circumstance must you recompile the package body after recompiling the package specification?
1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the package constructs
48⟩ What is SQL Indexes?
Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.
49⟩ Examine this function
61. CREATE OR REPLACE FUNCTION set_budget
62. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
63. BEGIN
64. UPDATE studio
65. SET yearly_budget = v_new_budget
WHERE id = v_studio_id;
IF SQL%FOUND THEN
RETURN TRUEl;
ELSE
RETURN FALSE;
END IF;
COMMIT;
END;
Which code must be added to successfully compile this function?
1. Add RETURN right before the IS keyword.
2. Add RETURN number right before the IS keyword.
3. Add RETURN boolean right after the IS keyword.
4. Add RETURN boolean right before the IS keyword.
50⟩ What is the difference between Truncate and Delete interms of Referential Integrity?
DELETE removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers. Also, it only checks for the existence (and status) of another foreign key Pointing to the table. If one exists and is enabled, then you will get The following error. This is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before issuing the TRUNCATE command, then re-enable them afterwards.
51⟩ What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL;?
1200.
52⟩ What is the parameter substitution symbol used with INSERT INTO command?
&
53⟩ What is referential integrity constraint?
Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.
54⟩ What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.
55⟩ What is SQL ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.
56⟩ Explain CONNECT BY PRIOR?
Retrieves rows in hierarchical order eg.
select empno, ename from emp where.
57⟩ What is ON DELETE CASCADE?
When ON DELETE CASCADE is specified Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.
58⟩ For which trigger timing can you reference the NEW and OLD qualifiers?
1. Statement and Row
2. Statement only
3. Row only
4. Oracle Forms trigger
59⟩ What is difference between TRUNCATE & DELETE?
TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.
60⟩ If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE?
It won't, Because SYSDATE format contains time attached with it.
