Category: sql Certificate

SQL – Using DDL Statements Questions ”; Previous Next 1.What is the full form of DDL in Oracle DB? Data Deleting Language Data Definition Language Data Delegating Language Dummy Data Language Answer: B. DDL is one of the categories of SQL which stands for Data Definition Language. Other SQL types are DML, DCL, and TCL. 2.DDL statements are used for which of the following Oracle database objects? Tables Sub-queries Rows Columns Answer: A. DDL contains commands like CREATE, ALTER and ANALYZE which are used to CREATE TABLEs, view stored subprograms and packages in a database schema. 3.What is the basic unit of storage in Oracle Database that contains data? View Column Query Table Answer: D. Table is the basic unit of physical storage of data in Oracle database. 4.Which of the below options best define a View? It is the shorter form of a table It is the logical representation of the subsets from one or more tables It has only one row and one column None of the above Answer: B. View is a query which behaves like a window to format the data contained in one or more tables. Views do not contain any physical data but just a query which are created during runtime. 5. Which of the following are database objects? Table Sequence Synonym All of the above Answer: D. Objects which are physically stored in database schema are database objects. 6. Which of the following database objects generate numeric values? Table View Index Sequence Answer: D. Sequence are used to generate unique values starting with a definite value and incremented by a specified factor. A sequence can be created to generate a series of integers. The values generated by a sequence can be stored in any table. A sequence is created with the CREATE SEQUENCE command. 7.Which of the following database objects gives an alternative name to an object? Synonym Sequence View Index Answer: A. A synonym provides a permanent alias for a database object. A public synonym is available to any database user. A private synonym is available only to the user who created it. A synonym is created by using the CREATE SYNONYM command. A synonym is deleted by using the DROP SYNONYM command. Only a user with DBA privileges can drop a public synonym. 8.Which of the following database objects improves the performance of some queries? Table Synonym View Index Answer: D. 9. When a table can be created? When the database is not being used by any user When the database is newly created It can be created any time, even when a user is using the database None of the above Answer: C. An index can be created to speed up the query process. DML operations are always slower when indexes exist. Oracle 11g creates an index for PRIMARY KEY and UNIQUE constraints automatically. An explicit index is created with the CREATE INDEX command. An index can be used by Oracle 11g automatically if a query criterion or sort operation is based on a column or an expression used to create the index. 10. What is true about a table? It is not mandatory to specify the size of a table The size of each table is the same A table can be modified online None of the above Answer: A, C. 11. A table named 123_A is created for storing the number of employees in an organization. What is wrong in the name of the table? The name of a table cannot start with a digit Nothing is wrong in this name. You cannot use an underscore while naming a table None of the above Answer: A. As per the object naming conventions, table name must start with an alphabet. 12. What is the range of number of letters a table name can have? 1-20 characters 1-10 characters 1-30 characters 1-50 characters Answer: C. A table name cannot exceed more than 30 characters. 13 Which of the following characters can be used to name a table? A to Z a to z 0 to 9 All of the above Answer: D. As per the standard naming convention in Oracle, object”s name can contain alphabets in any case. Mandatorily, first place is for letters while the rest can be mix of letters and digits. 14. Which of the following special characters can be used to name a table? @ # $ _ (underscore) Answer: B, C, D. No other special character, except (#, $, _), are allowed while naming a table. Use of special characters in the table name is discouraged. 15. What is true about the name of a table? A table can have a name which is used by some other object owned by the same user A sequence and a table can have same names A view and a table can have the same name A table name must not duplicate the name of another object owned by the same user Answer: D. By virtue of namespace, a table name cannot be same as any other schema objects. Schema objects which share the same namespace include tables, views, sequences, private synonyms, stored procedures, stored functions, packages, materialized views, and user-defined types. 16.You create a table and name it as COUNT. What will be the outcome of CREATE TABLE script? The table will not be created The table will be created and an underscore will be added automatically to the name COUNT_ An ORA error will be thrown The table COUNT will be created without any errors Answer: A, C. You cannot create a table with the name same as an Oracle Server reserved word. 17. You create a table using quoted identifiers ” ”. How will you refer this table? ”table_name” “table_name” Either of A or B None of the above Answer: B. If the table is created with the name having a quoted identifier, it must be addressed using double quotes. Using quoted identifiers is not recommended. Quoted identifiers are case-sensitive 18.

SQL Certificate Mock Exams ”; Previous Next 1. What will be the outcome of the following query? SELECT ROUND(144.23,-1) FROM dual; 140 144 150 100 2.In which of the following cases, parenthesis should be specified? When INTERSECT is used with other set operators When UNION is used with UNION ALL When MINUS is used for the queries None of the above 3. Which of the following are DML commands in Oracle Database? SELECT GROUP BY INTERSECT INSERT 4. Write a query to display employee details (Name, Department, Salary and Job) from EMP table. SELECT ename, deptno, sal, job FROM emp; SELECT * FROM emp; SELECT DISTINCT ename, deptno, sal, job FROM emp; SELECT ename, deptno, sal FROM emp; 5.What among the following are different types of Views? Simple views Complex views Both A and B None of the above 6.What is true about the SET operators? The SELECT clause should have the same number of columns, data types can be different The SET operators can be used only for combining two queries The data type of each column in the 2nd query must match the data type of its corresponding column in the first query. None of the above 7.Which of the following multi-row operators can be used with a sub-query? IN ANY ALL All of the above 8. When a table can be created? When the database is not being used by any user When the database is newly created It can be created any time, even when a user is using the database None of the above 9. Which among the following is a common technique for inserting rows into a table? (Choose the most sensible and appropriate answer) Using SELECT clause Manually typing each value into the INSERT clause Using SET operators None of the above 10. What among the following is true about a View? Sub-queries can be embedded in a CREATE VIEW statement A sub-query used in the CREATE VIEW statement has to have a simple SELECT syntax You cannot use a WHERE clause in a sub-query when it is used in the CREATE VIEW statement None of the above 11. Predict the output when below statement is executed in SQL* Plus? DESC emp Raises error “SP2-0042: unknown command “desc emp” – rest of line ignored.” Lists the columns of EMP table Lists the EMP table columns, their data type and nullity Lists the columns of EMP table along with their data types 12. What will be the outcome of the query given below? SELECT 100+NULL+999 FROM dual; 100 999 NULL 1099 13. With respect to the given query, if the JOIN used is replaced with NATURAL JOIN, it throws an error. What is the reason for this error? When the NATURAL JOIN is used, a WHERE clause is mandatory, omitting which gives an error The ON clause should be replaced with the USING clause The words NATURAL, JOIN and USING are mutually exclusively in the context of the same join clause A query can”t combine the NATURAL JOIN and ON (or USING) clauses while joining. 14.Which of the following syntax models is used in extensively in the software systems worldwide? ANSI SQL: 1999 Both traditional Oracle syntax and the ANSI SQL: 1999 syntax Traditional Oracle syntax All of the options 15.What is true about co-related sub-queries? The tables used in the main query are also used in a co-related sub-query The sub-queries which reference a column used in the main query are called co-related sub-queries The sub-queries which are written without parenthesis are called co-related sub-queries The sub-queries which mandatorily use different tables than those used in the main query are called co-related sub-queries 16. You issue an UPDATE statement as follows: UPDATE employees SET employee_id = NULL; WHERE job_id = ”CLERK”; What will be the outcome of the above statement? (Here the column EMPLOYEE_ID is marked as mandatory by putting a constraint) The first column of the data set will get updated to NULL The 3rd column of the first row will get updated to NULL The 3rd column of all the rows will get updated to NULL And ORA error will be thrown 17.What is true with respect to the query given above? It gives an ORA error as the mandatory WHERE clause is not present The JOIN..ON clause can”t contain more than one condition The query ignores the last condition and executes without an ORA error The JOIN..ON clause can be written in the form given above for putting more conditions. 18. Consider the following query. SELECT e.job_id , e.first_name, d.department_id FROM departments D JOIN employees e JOIN BONUS b USING (job_id ); This query results in an error. What is the reason of the error? A JOINOUSING can happen only between two tables at a time USING clause in the query doesn”t have any column from the department There is no WHERE clause in the query None of the above 19. Predict the output of the below query SELECT 50 || 0001 FROM dual 500001 51 501 5001 20. You create a table and name it as COUNT. What will be the outcome of CREATE TABLE script? The table will not be created The table will be created and an underscore will be added automatically to the name COUNT_ An ORA error will be thrown The table COUNT will be created without any errors 21. What will be the outcome of the following query? SELECT * FROM employees WHERE salary BETWEEN (SELECT max(salary) FROM employees WHERE department_id = 100) AND (SELECT min(salary) FROM employees where department_id = 100); This query returns an error. What is the reason for the error? A GROUP BY clause should be used as the function MAX is used Both the sub-queries cannot use the same department ID in the same outer query BETWEEN operator cannot be used with a sub-query SELECT clause should have columns mentioned and not a asterix (*) 22. Which of the following is not a property of

Discuss SQL Certificate ”; Previous Next Oracle”s Oracle Database 11g: SQL Fundamentals exam is part of the Oracle Certified Oracle Database 11g Administrator track, combining training, experience, and testing to endorse candidates with a strong foundation and expertise in the industry’s most advanced database management system. This certification is to put you on the short list for winning Oracle SQL-Based projects. An Oracle Technical Certification is a valuable, industry-recognized credential that signifies a proven level of knowledge and skill. Print Page Previous Next Advertisements ”;

SQL – Creating Other Schema Objects Questions ”; Previous Next 1. Which database object among the following provides a layer of abstraction between the users and the data? Table Rows Views Synonyms Answer: C, D. Views and Synonyms do not store data themselves. A view is a temporary or virtual table used to retrieve data stored in underlying database tables. 2. Which of the following data base objects can generate serial numbers? Synonyms Views Tables Sequences Answer: D. A sequence can be created to generate a series of integers. The values generated by a sequence can be stored in any table. A sequence is created with the CREATE SEQUENCE command. 3. What is true about views? They are equal to tables They store data from one or many tables We can execute SELECT and other DMLs on Simple views Views share the same namespace as tables and hence a table and a view cannot have the same name Answer: C, D. DML operations aren”t permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword. 4. Why are views useful? (Choose the most appropriate answer) Because they have shorter names than tables To prevent users from accessing the columns of tables To simplify user SQL All of the above Answer: B, C. A view is a temporary or virtual table used to retrieve data stored in underlying database tables. The view query must be executed each time the view is used. A view can be used to simplify queries or restrict access to sensitive data. 5. In which of the below scenarios, DML operations on a view are not possible? View contains GROUP BY clause Base tables contain NOT NULL columns but not selected in the view query View query uses ROWNUM pseudocolumn All of the above Answer: D. DML operations aren”t permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword. 6. Where can views get their data from? Tables from the same schema Tables from different schema Both A and B None of the above Answer: C. Consider the given table structure and the following statement and answer the questions 7 to 9 that follow: SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) CREATE VIEW emp_details AS SELECT hire_date, job, salary, department_id FROM employees; 7. You issue the below query. How many columns will the user see as a result of the below query? SELECT * FROM emp_details WHERE department_id= 100; 0 1 2 4 Answer: D. Since the view definition is based on four columns from the EMPLOYEES table, a query on a view with all column will show those four columns only. 8. You need to get the department name in addition to the above 4 columns. Which of the following query will give you the required results? SELECT E.*, dept_name FROM departments D join emp_details E ON (E.department_id= D.dept_id); SELECT hire_date, job, salary, dept_name FROM emp_details This is not possible a view cannot be joined to other tables None of the above Answer: A. A view can be joined with other tables or views in a SELECT query. 9. You need to find the maximum salary along with the department name in addition to the 4 columns selected in the view. Which of the following query will give you the required results? Select dept_name, e.salary FROM departments D join emp_details E On (E.department_id= D.dept_id); Select dept_name, max(salary) FROM departments D join emp_details E On (E.department_id= D.dept_id) Group by dept_name; View cannot appear in queries using group functions Select dept_name, max(salary) FROM departments D join emp_details E On (E.department_id= D.dept_id); Answer: B. 10. What among the following is true about synonyms? PUBLIC and PRIVATE synonyms can have the same name for the same table DROP SYNONYM will remove a synonym DROP PUBLIC SYNONYM can be executed only by a SYSDBA None of the above Answer: A, C. A synonym can be a private synonym, which users use to reference objects they own,or a public synonym, which users use to access another user”s database objects. Only SYSDBA or a user with DBA privileges can create a public synonym. 11. What is true about creating a view? (Choose the most appropriate answer) A view can only be created from a table A view can only be created from one table A view can be created from one or many tables or views None of the above Answer: C. A view containing expressions or functions or joining multiple tables is considered a complex view. A complex view can be used to update only one table. 12. Which of the following privileges are required to create views in one”s own schema? CREATE TABLE system privilege CREATE VIEW system privilege ALTER VIEW system privilege CREATE ANY VIEW system privilege Answer: B. CREATE VIEW privilege is required by a user to create a view in its own schema. 13. Which of the following privileges are required to create views in someone else”s schema? CREATE ANY VIEW CREATE VIEW Both A and B None of the above Answer: A. CREATE ANY VIEW privilege is required by a user to create a view in other user”s schema. 14.Which of the following are supported for an object view or relational view? LOBs Object types REF data types All of the above Answer: D. 15. What among the following are different types of Views? Simple views Complex views Both A and B None of the above Answer: C. Simple and Complex views are two types of views. Simple views are based on a subquery that references only one table and doesn”t include group functions, expressions, or GROUP BY clauses. Complex views are based on a subquery that retrieves or derives data from one or

SQL – Manipulating Data Questions ”; Previous Next 1.What does ACID mean with respect to relational database? Accuracy, Consistency, Isolation, Database Accuracy, Concurrency, Isolation, Durability Atomicity, Consistency, Isolation, Durability Atomicity, Concurrency, Isolation, Durability Answer: C. All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. Atomicity states that all tasks of a transaction are performed or none of them are. There are no partial transactions. Consistency implies the transaction takes the database from one consistent state to another consistent state. Isolation means the effect of a transaction is not visible to other transactions until the transaction is committed. Durability means that changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost. 2. What does the word DML stands for in Oracle SQL? Durability Management Language Database Management Language Database Manipulation Language None of the above Answer: C. DML stands for Data Manipulation Language. 3. Which of the following are DML commands in Oracle Database? SELECT GROUP BY INTERSECT INSERT Answer: A, D. On strict grounds, SELECT is a DML command as it is one of the mandatory clauses for manipulation of data present in tables. 4.Which of the following DML commands can be considered to be a hybrid of INSERT and UPDATE in a single statement? INTERSECT INSERT SELECT MERGE Answer: D. MERGE can perform INSERT and UPDATE actions in a single statement in Oracle. 5. What all operations can MERGE statement perform in SQL? INSERT DELETE GROUP BY None of the above Answer: A, B. In some conditions MERGE can perform the DELETE operation too, along with INSERT and UPDATE. 6.Which of following commands is a DDL (Data Definition Language) command but is often considered along with DML commands? DELETE INSERT TRUNCATE None of the above Answer: C. TRUNCATE is a DDL command. It removes the records from the table without any condition. It is not the part of any ongoing transaction and an uncommitted transaction in the session is committed after TRUNCATE is executed. 7.Which of the following commands manipulate data basically? MINUS UPDATE TRUNCATE All of the above Answer: B, C. UPDATE is a DML statement to modify a column value in a table. TRUNCATE manipulates the data by removing them unconditionally from a table. 8. Which of the following commands is used to populate table rows with data? DELETE INSERT SELECT UPDATE Answer: B. INSERT command is used to insert rows in a table. 9. What is true about the INSERT statement? (Choose the most appropriate answer) It can insert data in one row of one table at a time It can insert data in many rows of one table at a time It can insert data in many rows of many tables at a time All of the above Answer: C. The INSERT statement is capable of inserting a row or set of rows in a single table at a time. 10.What is true about the insertion of rows in tables? The rows can be inserted randomly Any number of rows can be inserted in a table without any restrictions Generally the rows are inserted in a table based on certain rules known as constraints All of the above Answer: C. Constraints are business rules imposed on the columns so as to ensure the behavior of the data coming in the column. These constraints are validated for the data during the INSERT process. 11. What is true about the INSERT statement in Oracle SQL? (Choose the most appropriate answer) An INSERT statement can override any constraint put on the table An INSERT statement cannot be used on a table if a constraint is already placed on the table An INSERT statement can be used on a table only if a constraint is already placed on the table An INSERT statement can never insert a row that violates a constraint. Answer: D. Oracle raises exception if any of the data contained in the insert statement violates the constraint. Consider the following data set from the EMPLOYEES table along with its structure and answer the questions 12, 13 and 14: SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER (6) FIRST_NAME VARCHAR2 (20) LAST_NAME NOT NULL VARCHAR2 (25) EMAIL NOT NULL VARCHAR2 (25) PHONE_NUMBER VARCHAR2 (20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2 (10) SALARY NUMBER (8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) EMPLOYEE_ID FIRST_NAME JOB_ID ——————- —————— ——– 5100 BRUCE CLERK 5101 JESSICA SALESMAN 5102 DEBBY SALESMAN 12. Examine the structure of the EMPLOYEES table. You issue the following command: INSERT INTO EMPLOYEES (employee_id , first_name , job_id) VALUES (5100, ”BRUCE”, ”CLERK”); Assuming that there is a duplicate value check constraint on the EMPLOYEE_ID column, what will be the outcome of the above statement? It will insert another row with 5100 BRUCE CLERK values It will insert another row with 51002 BRUCE CLERK values It will throw a ”Constraint violated” ORA error None of the above Answer: C. As the row with values “5100, BRUCE, CLERK” already exists in the table, the insert statement with same data set is not possible. 13.You issue the following command to the data set shown above: INSERT INTO EMPLOYEES (employee_id , first_name , job_id) VALUES (51003,”BRUCE”,”CLERK”); What will be the output of this statement? It will insert a new row with 51003 BRUCE CLERK values It will throw an ORA error as there cannot be another BRUCE who is a CLERK It will throw an ”Constraint violated” ORA error None of the above Answer: A. As there is no constraint on the columns FIRST_NAME and job_id, the INSERT will work without any error 14. You issue the following command to the data set shown above: INSERT INTO EMPLOYEES (employee_id , first_name , job_id ) VALUES (51003,”BRUCE”, NULL); What will be the output of this statement? It will insert a new row with 51003 BRUCE CLERK values It will throw

SQL – Subqueries to Solve Queries Questions ”; Previous Next 1. Which of the following are the types of sub-queries? Ordered sub-queries Grouped sub-queries Single row sub-queries None of the above Answer: C. A subquery is a complete query nested in the SELECT,FROM, HAVING, or WHERE clause of another query.The subquery must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum. Single row sub-queries and multi-row sub-queries are the main types of sub-queries 2.Which of the following is true about sub-queries? They execute after the main query executes They execute in parallel to the main query The user can execute the main query and then, if wanted, execute the sub-query They execute before the main query executes. Answer: D. The sub-query always executes before the execution of the main query.Subqueries are completed first.The result of the subquery is used as input for the outer query. 3.Which of the following is true about the result of a sub-query? The result of a sub-query is generally ignored when executed. The result of a sub-query doesn”t give a result, it is just helpful in speeding up the main query execution The result of a sub-query is used by the main query. The result of a sub-query is always NULL Answer: C. Subqueries are completed first.The result of the subquery is used as input for the outer query. 4.Which of the following clause is mandatorily used in a sub-query? SELECT WHERE ORDER BY GROUP BY Answer: A. A sub-query is just like any other query which has to start with a SELECT clause. They are contained within an outer query. 5. Which of the following is a method for writing a sub-query in a main query? By using JOINS By using WHERE clause By using the GROUP BY clause By writing a SELECT statement embedded in the clause of another SELECT statement Answer: D. A subquery is a complete query nested in the SELECT, FROM, HAVING, or WHERE clause of another query.The subquery must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum. 6.In the given scenarios, which one would appropriately justify the usage of sub-query? When we need to sum up values When we need to convert character values into date or number values When we need to select rows from a table with a condition that depends on the data from the same or different table. None of the above Answer: C. 7.In which of the following clauses can a sub-query be used? HAVING WHERE FROM All of the above Answer: D. A sub-query is not different from a normal query. It can make use of all the primary clauses of a SELECT statement. 8.Which of the following single-row operators can be used for writing a sub-query? >= < = All of the above Answer: D. Single-row operators include =, >, <, >=, <=, and <>. 9.Which of the following multi-row operators can be used with a sub-query? IN ANY ALL All of the above Answer: D. Multiple-row subqueries return more than one row of results.Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS. 10.What is true about the output obtained from a sub-query? It remains in the buffer cache It remains inside the sub-query and can be used later when needed It is used to complete the outer (main) query Both A and C Answer: C. Subqueries are completed first.The result of the subquery is used as input for the outer query. 11.You need to find the salaries for all the employees who have a higher salary than the Vice President of a company ”ABC”.Which of the following queries will give you the required result? (Consider the table structure as given) SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE job_id = ”VICE-PRESIDENT”); SELECT first_name, last_name, salary FROM employees WHERE salary = (SELECT salary FROM employees WHERE job_id = ”VICE-PRESIDENT”); SELECT first_name, last_name, salary FROM employees WHERE job_id = ”VICE-PRESIDENT”); None of the above Answer: A. In the option ”A”, the inner sub-query gives the VP”s salary as a result to the outer query. 12.What among the following is true about sub-queries? Sub-queries can be written on either side of a comparison operator Parenthesis is not mandatory for sub-queries Single-row sub-queries can use multi-row operators but vice versa is not possible All of the above Answer: A. Sub queries can be placed on left or right hand side of the comparison operator depending on the query indentation and usability. 13. What will be the outcome of the following query? (Consider the given table structure) SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SELECT first_name, last_name, salary FROM employees WHERE salary ANY (SELECT salary FROM employees); It executes successfully giving the desired results It executes successfully but does not give the desired results It throws an ORA error It executes successfully and gives two values for each row obtained in the result set Answer: C. Multi-row operators cannot be used in single-row sub-queries and vice versa. 14.Which of the following is true about single-row sub-queries? They give one result from the main query They give only one row in the result set They return only one row from the inner SELECT statement They give multiple rows from the main (outer) query Answer: C. A single-row subquery can return a maximum of one value. 15.What is true about multi-row sub-queries? They can return more than one column as the result of the inner

SQL – Creating Other Schema Objects ”; Previous Next Apart from tables, other essential schema objects are view, sequences,indexes and synonyms.A view is a logical or virtual table. Synonyms are simply alias names for database objects.Synonyms also simplify query writing and provide an element of system security by disguising the actual name of a database object.Sequences are special database objects that support the automatic generation of integer values,and are often used to generate primary key values for tables.Indexes are created on table columns to facilitate the rapid retrieval of information from tables. Views A database view is a logical or virtual table based on a query.Views are queried just like tables.This means that from your perspective as a developer or from a database system user”s perspective, a view looks like a table.The definition of a view as an object is stored within a database”s data dictionary; however,a view stores no data itself.A database also stores the execution plan for creating a view-this means that data can be retrieved rapidly through use of a view even though the actual data presented by a SELECT query of a view is not stored as part of a view.Rather,the data is “gathered together” each time that a view is queried from the database tables for which a view is defined-these are termed base tables. The general syntax is given below. CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [ViewName] [(Column Alias Name…)] AS [Query] [WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]]; From the syntax, The FORCE option allows a view to be created even if a base table that the view references does not already exist.This option is used to create a view prior to the actual creation of the base tables and accompanying data. The NOFORCE option is the opposite of FORCE and allows a system user to create a view if they have the required privileges to create a view, and if the tables from which the view is created already exist. This is the default option. The WITH READ ONLY option allows creation of a view that is read-only.You cannot use the DELETE,INSERT,or UPDATE commands to modify data for a read-only view. The WITH CHECK OPTION clause allows the update of rows that can be selected through the view.It also enables you to specify constraints on values.The CONSTRAINT clause works in conjunction with the WITH CHECK OPTION clause to enable a database administrator to assign a unique name to the CHECK OPTION.If a database administrator omits the CONSTRAINT clause,Oracle will automatically assign the constraint a system-generated name that will not be very meaningful. Types of Views A Simple view is created on top of one table only.It is a simple SELECT query with no functions or group clause,but just selection of columns from the table without any transformation.If a DML is performed on the view, it is straightaway reflected in the base table. A Complex view is created on multiple tables using joins.It can contain SQL functions,Group by functions.But since the view is on multiple data and selection of columns is also not simple, it does not allow DML operation on it. Illustration Simple View: The below simple view select employee name, department id and salary for the employees with JOB ID as DEV. CREATE OR REPLACE VIEW v_emp_dev AS SELECT first_name, department_id, salary FROM employees WHERE job_id = ”DEV”; Complex view: The below example shows the department name, average salary drawn in the department and the count of employees working in it. CREATE OR REPLACE VIEW EMP_VU AS SELECT department_name, AVG (salary) avg_sal, COUNT (first_name) count FROM employees E, departments D WHERE E.department_id = D.department_id GROUP BY department_name; DESCRIBE [view name] describes the view structure. Columns are listed in the same sequence as in the view definition. DML operations on a View DML operations can be easily exercised on simple views.As stated earlier, the insert, update and delete operations actually happen on the base table. When you execute an UPDATE, DELETE, or INSERT DML statement on a view, you are actually manipulating the data rows for the base table or tables on which the view is defined.There are restrictions on the use of UPDATE, DELETE, and INSERT statements with views.First, to use the UPDATE, DELETE, or INSERT statement with a view, the view must be updateable.A view is updateable if the SELECT clause does not specify any aggregate function in the SELECT listing.Additionally, the view could not have been created through use of a GROUP BY, DISTINCT, or UNION clause or clauses.It is permissible for aggregate functions to be used in a SELECT subquery in a FROM clause. Also, the view cannot have any derived columns in the SELECT list. Next, if a view is created as the result of a JOIN operation (a join view), the UPDATE and INSERT statements can only modify or insert rows into one of the base tables at a time. You cannot modify rows from two or more tables with a single data manipulation language (DML) statement.Finally, a DELETE statement can only execute against a view if a table is referenced in a FROM clause. This simply means that you cannot delete rows from a table that has not been specified. WITH CHECK OPTION clause WITH CHECK OPTION is an optional clause that specifies the level of checking to be done when inserting or updating data through a view.If a view is created using WITH CHECK OPTION clause, every row which gets inserted or updated in the base table through the view must comply with the view definition. Note that the option cannot be specified if the view is created as read-only. For example, a view V_EMP_DEV is created for employees who are developers (JOB_ID=DEV). CREATE OR REPLACE VIEW v_emp_dev AS SELECT first_name, department_id, salary, FROM employees WHERE job_id = ”DEV” WITH CHECK OPTION empvu_dev; A user attempts to update salary of an HR employee through the view but encounters an exception. Its because the view was created WITH CHECK OPTION. UPDATE v_emp_dev SET

SQL – Get Data from Multiple Tables Questions ”; Previous Next 1.Which of the following is not related to a Relational Database? Selection Projection Joining None of the above Answer: D. The options A, B and C are the major capabilities of the Oracle Relational Database. 2.Which of the following methods is used for writing a query with columns from multiple tables? SELECT GROUP BY ORDER BY JOINS Answer: D. Joins are used to connect multiple tables and project column data from multiple tables in Oracle. 3.Which of the following is one of the most common methods to join multiple tables? Hash Join Equijoin Self Join Cross Join Answer: B. Equijoin is one of the types of joins which is the most common and simple technique for joining more than one tables. Equijoins are also called simple joins or inner joins.Equijoin involve primary key and foreign key. 4.Which of following will be used to join rows with other tables if the column values fall in a range defined by inequality operators? Equijoin Simple join Non-equijoin None of the above Answer: C. Equijoins use equality operators to join rows, non-equijoins use inequality operators. 5.Which of the following statements is true about Oracle joins? NULL values are included in the result set Only the rows which have matching conditions are fetched All the rows that are present in any of the one tables are fetched None of the above Answer: B. NULL values and differing entries in common join columns are excluded when joins are used. 6.Which of the following can be used to join the rows of a table with other rows of the same table? Equijoin Non-equijoin Outer join Self-join Answer: D. The association is based on columns with logical and usually hierarchical relationships with each other. 7.What is true about a cartesian join of two tables in Oracle DB? It must be avoided as it is costly and non optimized It is formed when every row from one table is joined with all rows in the second table Both A and B None of the above Answer: B. Cartesian join is often the result of missing or inadequate join conditions.It is simply the cross product of two tables. 8.Which of the following is one of the basic types of joins in Oracle DB ? Outer join Self-join Equi-join All of the above Answer: C. Equi-join and non-equijoin are the two basic types of joins in Oracle DB. 9.What is the main condition for using joins between a source table and a target table in Oracle DB for getting a non-cartesian product result? There is no condition At least one of the columns in both the tables should be common. The names of the columns in both the joining tables should be the same for using joins None of the above Answer: B. The tables must be connected through a common column relating two entities.The table joined on a common column produce non Cartesian product. 10. Which of the following can be used to fetch rows from multiple tables in a single SQL query? SELECT WHERE FROM Equi-joins Answer: D. Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key. 11.What is true about the source table and the target table in terms of Oracle Joins? They must have atleast one column of same name All the columns should be of the same name and same data type for joining the two tables The source and the target tables cannot be swapped and are position specific None of the above Answer: D. The source and the target tables can be swapped and are not fixed at their positions.Depending of the type of join used in the query, the result may differ or remain same. 12.What is true about Natural joins in Oracle DB? The column names of the source and the target tables should be identical If the column names of the source and the target tables are not same, Oracle implicitly does the needful NATURAL JOINS, USING and ON are the keywords associated with Natural Joins All of the above Answer: C. The keyword NATURAL JOIN instruct Oracle to identify columns with identical names between source and target tables. Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. 13.Assume the tables EMPLOYEES and DEPARTMENT have to be joined using NATURAL JOIN. What is the difference between the following two queries which follow? (Consider the table structures as given) SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> DESC departments Name Null? Type ———————– ——– —————- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SELECT department_id FROM employees NATURAL JOIN department WHERE first_name = ”John” AND last_name = ”Bacon”; SELECT department_id FROM department NATURAL JOIN employees WHERE first_name = ”John” AND last_name = ”Bacon”; There is no difference The result is different in both the cases Both the queries will give an ORA error on execution None of the above Answer: B. The source and target tables can be swapped while using NATURAL JOIN giving relationally different result sets. 14.Which of the following options is true regarding the NATURAL JOIN in Oracle DB? While using NATURAL JOIN mentioning the names of all the columns from both the tables is mandatory NATURAL JOIN can be used only if the names of all the columns of both the tables are identical The join in NATURAL JOIN happens only when the user specifies the columns of the source and the target tables. There is no need to mention the columns when using NATURAL JOINS. Answer: D. There”s an implicit joining of the

SQL – Using Group functions ”; Previous Next Reporting Aggregate data using the Group functions SQL has numerous predefined aggregate functions that can be used to write queries to produce exactly this kind of information.The GROUP BY clause specifies how to group rows from a data table when aggregating information, while the HAVING clause filters out rows that do not belong in specified groups. Aggregate functions perform a variety of actions such as counting all the rows in a table, averaging a column”s data, and summing numeric data. Aggregates can also search a table to find the highest “MAX” or lowest “MIN” values in a column. As with other types of queries, you can restrict, or filter out the rows these functions act on with the WHERE clause. For example, if a manager needs to know how many employees work in an organization, the aggregate function named COUNT(*) can be used to produce this information.The COUNT(*) function shown in the below SELECT statement counts all rows in a table. SELECT COUNT(*) FROM employees; COUNT(*) ———- 24 The result table for the COUNT(*) function is a single column from a single row known as a scalar result or value. Notice that the result table has a column heading that corresponds to the name of the aggregate function specified in the SELECT clause. Some of the commonly used aggregate functions are as below – SUM( [ALL | DISTINCT] expression ) AVG( [ALL | DISTINCT] expression ) COUNT( [ALL | DISTINCT] expression ) COUNT(*) MAX(expression) MIN(expression) The ALL and DISTINCT keywords are optional, and perform as they do with the SELECT clauses that you have learned to write.The ALL keyword is the default where the option is allowed.The expression listed in the syntax can be a constant,a function, or any combination of column names, constants, and functions connected by arithmetic operators.However, aggregate functions are most often used with a column name. Except COUNT function,all the aggregate functions do not consider NULL values. There are two rules that you must understand and follow when using aggregates: Aggregate functions can be used in both the SELECT and HAVING clauses (the HAVING clause is covered later in this chapter). Aggregate functions cannot be used in a WHERE clause. Its violation will produce the Oracle ORA-00934 group function is not allowed here error message. Illustrations The below SELECT query counts the number of employees in the organization. SELECT COUNT(*) Count FROM employees; COUNT —– 24 The below SELECT query returns the average of the salaries of employees in the organization. SELECT AVG(Salary) average_sal FROM employees; AVERAGE_SAL ———– 15694 The below SELECT query returns the sum of the salaries of employees in the organization. SELECT SUM(Salary) total_sal FROM employees; TOTAL_SAL ——— 87472 The below SELECT query returns the oldest and latest hired dates of employees in the organization. SELECT MIN (hire_date) oldest, MAX (hire_date) latest FROM employees; OLDEST LATEST ——— ———– 16-JAN-83 01-JUL-2012 GROUP BY Aggregate functions are normally used in conjunction with a GROUP BY clause. The GROUP BY clause enables you to use aggregate functions to answer more complex managerial questions such as: What is the average salary of employees in each department? How many employees work in each department? How many employees are working on a particular project? Group by function establishes data groups based on columns and aggregates the information within a group only. The grouping criterion is defined by the columns specified in GROUP BY clause. Following this hierarchy, data is first organized in the groups and then WHERE clause restricts the rows in each group. Guidelines of using GROUP BY clause (1) All the dependent columns or columns used in GROUP BY function must form the basis of grouping, hence must be included in GROUP BY clause also. SELECT DEPARTMENT_ID, SUM(SALARY) FROM employees; DEPARTMENT_ID, * ERROR at line 2: ORA-00937: not a single-group group function (2) GROUP BY clause does not support the use of column alias, but the actual names. (3) GROUP BY clause can only be used with aggregate functions like SUM, AVG, COUNT, MAX, and MIN.If it is used with single row functions,Oracle throws an exception as “ORA-00979: not a GROUP BY expression”. (4) Aggregate functions cannot be used in a GROUP BY clause. Oracle will return the “ORA-00934: group function not allowed” here error message. Below query lists the count of employees working in each department. SELECT DEPARTMENT_ID, COUNT (*) FROM employees GROUP BY DEPARTMENT_ID; Similarly, below query to find sum of salaries for respective job ids in each department. Note the group is established based on Department and Job id. So they appear in GROUP BY clause. SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL) FROM employees GROUP BY DEPARTMENT_ID, JOB_ID; The below query also produces the same result. Please note that grouping is based on the department id and job id columns but not used for display purpose. SELECT SUM (SALARY) FROM employees GROUP BY DEPARTMENT_ID, JOB_ID; Use of DISTINCT, ALL keywords with Aggregate functions By specifying DISTINCT keyword with the input parameter, group by function considers only the unique value of the column for aggregation. By specifying ALL keyword with the input parameter, group by function considers all the values of the column for aggregation, including nulls and duplicates. ALL is the default specification. The HAVING clause The HAVING clause is used for aggregate functions in the same way that a WHERE clause is used for column names and expressions.Essentially,the HAVING and WHERE clauses do the same thing, that is filter rows from inclusion in a result table based on a condition. While it may appear that a HAVING clause filters out groups, it does not.Rather,a HAVING clause filters rows. When all rows for a group are eliminated so is the group.To summarize, the important differences between the WHERE and HAVING clauses are: A WHERE clause is used to filter rows BEFORE the GROUPING action (i.e., before the calculation of the aggregate functions). A HAVING clause filters rows AFTER the GROUPING action (i.e., after

SQL – Restricting and Sorting Data Questions ”; Previous Next 1. Which of the following clause is used to limit the number of rows retrieved from a SELECT query? LIMIT WHERE AND FROM Answer: B. The WHERE clause is used to restrict the number of rows returned from a SELECT query. 2. Choose the database elements whose values can be compared in a WHERE clause of a SELECT query. Column Sequence Procedure Literal Answer: A, D. The WHERE clause can be used to compare the values from columns, literals, arithmetic functions and functions. 3. What are the elements NOT contained in the WHERE clause predicate of the SELECT query? Comparison operator Comparison condition Column Name Table Name Answer: D. Table Name is not required in the WHERE clause predicate. 4. Which of the following values can NOT be returned after evaluation of WHERE clause condition? UNKNOWN TRUE FALSE NULL Answer: A. If the result of the condition in WHERE clause is not known, NULL is returned. In all other scenarios, either TRUE or FALSE is returned. 5. What is the minimum number of WHERE clauses that must be present in a SELECT query? 1 2 0 3 Answer: C. The WHERE clause is an optional clause in the SELECT query which is only used to restrict the number of rows. 6. What is the maximum number of WHERE clauses that can be included in a SELECT query? 1 2 0 3 Answer: A. The WHERE clause is an optional clause in the SELECT query which can be used only once to restrict the number of rows. 7. Which of the following statements are correct about the WHERE clause? Column Alias can be used in WHERE clause to refer a column Comparison operator is an optional element in WHERE clause condition Functions can be used as operands in the WHERE clause There can be multiple WHERE clause in a SELECT query Answer: C. The WHERE clause must have comparison operator to evaluate the condition. It can use function as one of the operand. Only one WHERE clause is allowed in a SELECT query. 8. Write a SELECT query to list down unique departments from EMP table? SELECT deptno FROM emp; SELECT DISTINCT deptno FROM emp; SELECT DISTINCT (deptno) FROM emp; SELECT empno, DISTINCT deptno FROM emp; Answer: B & C. The keyword DISTINCT is used to filter out the duplicate rows from the SELECT query. 9. Which of the following operations are permitted for date and timestamp columns? Division Addition Subtraction Concatenation Answer: B, C, and D. Addition, subtraction and Concatenation are the operations permitted for date and timestamp columns. 10. From the below operators, which one of them holds the highest precedence level? Division (/) Multiplication (*) Brackets ( () ) Subtraction Answer: C. Expressions within the brackets hold the highest precedence level. 11. Interpret the output returned by the below SELECT query SELECT ename, (sysdate – hiredate) FROM emp; Number of days in the current year Number of days in the year when an employee was hired Number of days spent by employee with the company The query raises exception “ORA-00932: inconsistent datatypes: expected NUMBER got DATE” Answer: C. The expression (sysdate-hiredate) returns the number of employment days of an employee with the company. 12. Which of the below statements correctly describle the DUAL table in Oracle? DUAL table is a temporary table in Oracle database DUAL table contains only one character type column known as DUMMY DUAL table owned by SYS cannot be dropped A table with the name DUAL can be created by a user in its own schema Answer: B, C, D. The DUAL table in Oracle is owned by SYS and contains one column DUMMY of type VARCHAR2(1). 13. Determine the type of output returned by the below query SELECT sysdate – hiredate FROM emp WHERE empno=7369; DATE data type NUMBER data type VARCHAR2 data type The query raises error since arithmetic operations cannot be performed on date columns Answer: B. Subtraction between two dates results in numeric difference of days between the two dates 14. Which expressions do NOT return NULL values? SELECT ((10 + 20) * 50) + null from dual; SELECT ”this is a ”||null||”test with nulls” from dual; SELECT null/0 from dual; SELECT null||”test”||null as “Test” from dual; Answer: B, D. Any arithmetic operation with NULL results in NULL. 15. Determine the output of the below query SELECT ”Tutorial””s Point compiles technical tutorials” FROM DUAL; Tutorial””s Point compiles technical tutorials Tutorial”s Point compiles technical tutorials ”Tutorial””s Point compiles technical tutorials” Raises exception “ORA-01756: quoted string not properly terminated” Answer: B. 16. Examine the TRAINING table as given below: Name Null? Type —————————————– ——– ————- TRAINING_ID NOT NULL NUMBER(5) TRAINING_LOCATION NUMBER(7,2) START_DATE DATE END_DATE DATE Which two SQL would execute successfully? (Choose two) SELECT NVL (ADD_MONTHS (END_DATE,1),SYSDATE) FROM training; SELECT TO_DATE (NVL(SYSDATE-END_DATE,SYSDATE)) FROM training; SELECT NVL(MONTHS_BETWEEN(START_DATE,END_DATE),’In Progress’) FROM training; SELECT NVL(TO_CHAR(MONTHS_BETWEEN(START_DATE,END_DATE)),’In Progress’) FROM training; Answer: A, D. Use NVL function to provide an alternate value to a column when NULL. 17. What does the selection of columns in a SELECT statement known as? Retrieval Selection Projection Limiting Answer: C. Projection is the ability to select only the required columns in SELECT statement. 18. What does the restriction of rows returned by a SELECT statement known as Retrieval Projection Restricting Selection Answer: C. Restricting is the ability to limit the number of rows by putting certain conditions. 19. Which of the following is true about the query given below? SELECT col1, col2 FROM tab1 ORDER BY col1; All the rows for the column COL1 will be sorted in the Descending order. All the rows for the column COL1 will be sorted in the Ascending order. The query will give an error as there is no WHERE clause in the query. The query will give an error as the ORDER BY clause should include all the columns in the SELECT clause. Answer: B. By default, the ORDER BY clause sorts