Explain UNION, MINUS, UNION ALL and INTERSECT?

PL/SQL Interview Questions and Answers

(Continued from previous question...)

Explain UNION, MINUS, UNION ALL and INTERSECT?

INTERSECT - returns all distinct rows selected by both queries. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION ALL - returns all rows selected by either query, including all duplicates.

(Continued on next question...)

Other Interview Questions

• What's SQL
• What's SQL*Plus
• What's NVL
• What's Date Functions
• What's Character Functions
• What's TTITLE & BTITLE
• What's COLUMN
• What's BREAK
• What's COMPUTE
• What's SET
• What's SPOOL
• What's JOIN
• What's Union
• What's Intersect
• What's Minus
• What's Correlated Subquery
• What's Multiple columns
• What's Sequences
• What's Synonyms
• What's Indexes
• What's Data types
• Order of SQL statement execution
• What's Transaction
• What's Commit
• What's Posting
• What's Rollback
• What's Savepoint
• Set Transaction
• What's Locking
• What's Consistency
• What's Integrity
• What's Deadlock
• What's Mutating Table
• What's SQL*Loader
• The most important DDL statements in SQL are:
• Operators used in SELECT statements.
• SELECT statements:
• The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records.
• The INSERT INTO Statements:
• The Update Statement:
• The Delete Statements:
• Sort the Rows:
• The IN operator may be used if you know the exact value you want to return for at least one of the columns.
• BETWEEN ... AND
• What is the use of CASCADE CONSTRAINTS?
• Why does the following command give a compilation error?
• Which system tables contain information on privileges granted and privileges obtained?
• Which system table contains information on constraints on all the tables created?obtained?
• What is the difference between TRUNCATE and DELETE commands?
• State true or false. !=, <>, ^= all denote the same operation?
• State true or false. EXISTS, SOME, ANY are operators in SQL?
• What will be the output of the following query?
• What does the following query do?
• What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
• Which command executes the contents of a specified file?
• What is the value of comm and sal after executing the following query if the initial value of ‘sal’ is 10000 UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;?
• Which command displays the SQL command in the SQL buffer, and then executes it?
• What command is used to get back the privileges offered by the GRANT command?
• What will be the output of the following query?
• Which date function is used to find the difference between two dates?
• What operator performs pattern matching?
• What is the use of the DROP option in the ALTER TABLE command?
• What operator tests column for the absence of data?
• What are the privileges that can be granted on a table by a user to others?
• Which function is used to find the largest integer less than or equal to a specific value?
• Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
• What is the use of DESC in SQL?
• What command is used to create a table by copying the structure of another table?
• TRUNCATE TABLE EMP; DELETE FROM EMP; Will the outputs of the above two commands differ?
• What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL;?
• What are the wildcards used for pattern matching.?
• What is the parameter substitution symbol used with INSERT INTO command?
• What's an SQL injection?
• What is difference between TRUNCATE & DELETE
• What is a join? Explain the different types of joins?
• What is the sub-query?
• What is correlated sub-query?
• Explain CONNECT BY PRIOR?
• Difference between SUBSTR and INSTR?
• Explain UNION, MINUS, UNION ALL and INTERSECT?
• What is ROWID?
• What is the fastest way of accessing a row in a table?
• What is an integrity constraint?
• What is referential integrity constraint?
• What is the usage of SAVEPOINTS?
• What is ON DELETE CASCADE?
• What are the data types allowed in a table?
• What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
• How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?
• What are the pre-requisites to modify datatype of a column and to add a column with NOT NULL constraint?
• Where the integrity constraints are stored in data dictionary?
• How will you activate/deactivate integrity constraints?
• If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE?
• What is a database link?
• How to access the current value and next value from a sequence? Is it possible to access the current value in a session before accessing next value?
• What is CYCLE/NO CYCLE in a Sequence?
• What are the advantages of VIEW?
• Can a view be updated/inserted/deleted? If Yes - under what conditions?
• If a view on a single base table is manipulated will the changes be reflected on the base table?
• Which of the following statements is true about implicit cursors?
• Which of the following is not a feature of a cursor FOR loop?
• How would the developer define this variable using referential datatypes?
• Which three of the following are implicit cursor attributes?
• If left out, which of the following would cause an infinite loop to occur in a simple loop?
• Which line in the following statement will produce an error?
• The command used to open a CURSOR FOR loop is
• What happens when rows are found using a FETCH statement
• Read the following code:
• Read the following code:
• What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
• 77. For which trigger timing can you reference the NEW and OLD qualifiers?
• Read the following code 2:
• When invoking this procedure, you encounter the error
• Read the following code 3
• Which procedure can be used to create a customized error message?
• The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
• Examine this database trigger
• Examine this function:
• Under which circumstance must you recompile the package body after recompiling the package specification?
• Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
• Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
• What occurs if a procedure or function terminates with failure without being handled?
• Examine this code
• Which Oracle supplied package will enable this feature?
• Which Oracle supplied package will enable this feature?
• How to implement ISNUMERIC function in SQL *Plus ?
• How to Select last N records from a Table?
• What is the difference between Truncate and Delete interms of Referential Integrity?
• CLIENT/SERVER
• What does the OLTP stands for ?
• What is the most important requirement for OLTP ?
• In a client server environment, what would be the major work that the client deals with ?
• Why is the most of the processing done at the sever ?
• What does teh term upsizing refer to ?
• What does one do when one is rightsizing ?
• What does the term downsizing refer to ?
• What is event trigger ?
• Why do stored procedures reduce network traffic ?
• What are the types of processes that a server runs ?
• What is a event handler ?
• What is an integrity constraint ?
• What are the various uses of database triggers ?
• What is a transaction ?
• Why are the integrity constraints preferred to database triggers ?
• Why is it better to use an integrity constraint to validate data in a table than to use a stored procedure ?
• What are the three components of a client server model ?
• What are the advantages of client/server model ?
• What are the disadvantages of the client/server model ?
• What are the different topologies available for network ?
• What is the first work of Client process ?
• What are the responsibilities of a Server ?
• In a Client/Server context, what does API (Application Programming Interface) refer to ?
• Give some examples of standard API??s ?
• What is the main advantage of developing an application using an API ?
• What is the main disadvantage of developing an application using an API ?
• Why is an event driven program referred to a passive program ?
• What are the four types of events ?
• What is the difference between file server and a database server ?
• What is inheritance ?
• What are the two components of ODBC ?
• What is the function of a ODBC manager ?
• What is the function of a ODBC Driver ?
• What description of a data source is required for ODBC ?
• How is a connection establised by ODBC ?
• PL/SQL Questions:
• SQL/ SQLPlus