1. What will be the outcome of the following query?
SELECT ROUND(144.23,-1) FROM dual;
2.In which of the following cases, parenthesis should be specified?
3. Which of the following are DML commands in Oracle Database?
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?
6.What is true about the SET operators?
7.Which of the following multi-row operators can be used with a sub-query?
8. When a table can be created?
9. Which among the following is a common technique for inserting rows into a table? (Choose the most sensible and appropriate answer)
10. What among the following is true about a View?
11. Predict the output when below statement is executed in SQL* Plus?
12. What will be the outcome of the query given below?
SELECT 100+NULL+999 FROM dual;
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?
14.Which of the following syntax models is used in extensively in the software systems worldwide?
15.What is true about 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)
17.What is true with respect to the query given above?
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?
19. Predict the output of the below query
SELECT 50 || 0001 FROM dual
20. You create a table and name it as COUNT. What will be the outcome of CREATE TABLE script?
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?
22. Which of the following is not a property of functions?
23.What is true with respect to INNER JOINS and OUTER JOINS in Oracle DB?
24. Which of the following can create a view even if the base table(s) does not exist?
25. Which of the following ANSI SQL: 1999 join syntax joins are supported by Oracle?
26. What among the following are the pre-requisites for creating a table?
27. What is the syntax for creating a table?
28.You need to display all the non-matching rows from the EMPLOYEES table and the non-matching rows from the DEPARTMENT table without giving a Cartesian product of rows between them. Which of the following queries will give the desired output?
SELECT * FROM employees e, department d WHERE e.department_id = d.department_id ;
SELECT * FROM employees e NATURAL JOIN department d;
SELECT * FROM employees e FULL OUTER JOIN department d ON e.department_id = d.department_id ;
SELECT * FROM employees e JOIN department d ON ( e.department_id > d.department_id ) ;
29. Which of the below alphanumeric characters are used to signify concatenation operator in SQL?
30.What is the best way to change the precedence of SET operators given the fact that they have equal precedence?
31.What will be displayed in the result of this query?
32. Which of the following commands ensures that no DML operations can be performed on a view?
33. What is true about the NOFORCE option in CREATE VIEW statement?
34. What is true about the OR REPLACE keyword?
35. What among the following is a type of Oracle SQL functions?
36. What among the following is a type of single-row function?
37. What is the most appropriate about Multiple Row Functions?
38. Which of the following are also called Group functions?
39. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (*) FROM t_count;
40. Pick the element which you must specify while creating a table.
41. What can be said about the statement given above?
42. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (num) FROM t_count;
43. You need to find the results obtained by the above query only for the departments 100 and 101. Which of the following clauses should be added / modified to the above query?
44. Which of the following is NOT a GROUP BY extensions in SQL?
45. What will happen if the above statement is modified as below?
CREATE OR REPLACE VIEW dept_sum_vu(name, maxsal, minsal, avgsal) AS SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary) FROM employees e JOIN departments d ON (e.department_id= d.dept_id) GROUP BY d.dept_name;
46. What among the following is true about the DELETE statement?
47. Assuming the last names of the employees are in a proper case in the table employees, what will be the outcome of the following query?
SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'smith';
48.What among the following happens when we issue a DELETE statement on a table? (Choose the most appropriate answer)
49.What is true about the query given above?
50.What will happen if a value is provided to the &N variable in the above query (option C in question 76) does not match with any row? (Choose the best answer)
51.What is the default sorting order of the results when UNION ALL operator is used?
52. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (ALL num) FROM t_count;
53.What is the maximum level up to which Sub-queries can be nested?
54. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (DISTINCT num) FROM t_count;
55. Here are few statements about VARIANCE function in SQL.
i. The function accepts multiple numeric inputs and returns variance of all the values
ii. The function accepts a number column and returns variance of all column values including NULLs
iii. The function accepts a number column and returns variance of all column values excluding NULLs
Chose the correct combination from the below options.
56. Which clause is used to filter the query output based on aggregated results using a group by function?
57. A user named "Kevin" wants to access a table which is owned by another user named "Jonathan". Which of the following will work for Kevin?
58.What is true about the ALL operator used for sub-queries? (Choose the most appropriate answer.)
59. Suppose you select DISTINCT departments and employee salaries in the view query used in above question. What will be the outcome if you try to remove rows from the view dept_sum_vu?
60.What will happen if the SELECT list of the compound queries returns both a VARCHAR2 and a NUMBER data type result?
61. What is true about a schema?
62. In which order the values will get inserted with respect to the above INSERT statement?
63. What among the following is true about tables?
65. Which of the below SQL query will display employee names, department, and annual salary?
SELECT ename, deptno, sal FROM emp;
SELECT ename, deptno, sal + comm FROM emp;
SELECT ename, deptno, (sal * 12) Annual_Sal FROM emp;
66. What is true about the SUBSTR function in Oracle DB?
67. Which of the following SELECT statements lists the highest retail price of all books in the Family category?
SELECT MAX(retail) FROM books WHERE category = 'FAMILY';
SELECT MAX(retail) FROM books HAVING category = 'FAMILY';
SELECT retail FROM books WHERE category = 'FAMILY' HAVING MAX(retail);
68. Which of the following functions can be used to include NULL values in calculations?
69.Which statements best describes the inference drawn from the questions 34 and 35?
70. What will be the outcome of the following query?
SELECT length('hi') FROM dual;
Answer(1): A. The ROUND function will round off the value 144.23 according to the specified precision -1 and returns 140.
Examine the structure of the EMPLOYEES table as given and answer the questions 2 and 3 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)
Answer(2): A. Using parenthesis will explicitly change the order of evaluation when INTERSECT is used with other operators.
Answer(3): 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.
Answer(4): A.Select the required from the tables each separated by a comma.
Answer(5): 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 more tables and can contain functions or grouped data.
Answer(6): C. All the combined should have the same no. of columns when using SET operators. The corresponding columns in the queries that make up a compound query must be of the same data type group.
Answer:(7) 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.
Answer(8): 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.
Answer(9): A. Using the SELECT clause is the most common technique for inserting rows into tables. It reduces the effort of manually keying in values for each column.
Answer(10): A. View definition can make use of sub-queries.
Answer(11): C. DESCRIBE is used to show the table structure along with table columns, their data type and nullity
Answer(12): C. Any arithmetic operation with NULL results in a NULL.
Answer()13: C, D.
Answer(14): C. The ANSI SQL: 1999 syntax though not used as much as the traditional Oracle syntax, it still is one of the syntaxes that may be used in Oracle SQL
Answer(15): B. Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query while Uncorrelated subquery executes the subquery first and passes the value to the outer query.
Answer(16): D. The constraints on the column must be obeyed while updating its value. In the given UPDATE statement, error will be thrown because the EMPLOYEE_ID column is a primary key in the EMPLOYEES table which means it cannot be NULL.
Answer(17): D. The WHERE clause can be omitted and the relevant conditions can be accommodated in the JOIN..ON clause itself as shown in the given query
Answer(18): A. Table1 JOIN table2 JOIN table3 is not allowed without the ON clauses for between each JOIN
Answer(19): C. The leading zeroes in the right operand of expression are ignored by Oracle.
Answer(20): A, C. You cannot create a table with the name same as an Oracle Server reserved word.
Answer(21): C. The BETWEEN operator can be used within a sub-query but not with a sub-query.
Answer(22): D. Functions can perform calculations, perform case conversions and type conversions.
Answer(23): A, C. A join can be an inner join,in which the only records returned have a matching record in all tables,or an outer join, in which records can be returned regardless of whether there's a matching record in the join.An outer join is created when records need to be included in the results without having corresponding records in the join tables. These records are matched with NULL records so that they're included in the output.
Answer(24): B. Ff you include the FORCE keyword in the CREATE clause, Oracle 11g creates the view in spite of the absence of any referenced tables. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn't created.
Answer(26): A, B. A user must possess the CREATE TABLE privilege and must have sufficient space to allocate the initial extent to the table segment.
Answer(28): C. The FULL OUTER JOIN returns the non-matched rows from both the tables. A full outer join includes all records from both tables, even if no corresponding record in the other table is found.
Answer(29): B.In SQL, concatenation operator is represented by two vertical bars (||).
Answer(30): C. Parenthesis can be used to group the specific queries in order to change the precedence explicitly. Parentheses are preferred over other SET operators during execution.
Answer(31): A. UNION Returns the combined rows from two queries, sorting them and removing duplicates.
Answer(32): C. The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it's important that users can only query data, not make any changes to it.
Answer(33): B, C. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn't created.
Answer(34): B. The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view's previous version should be replaced with the one defined in the new command.
Answer(35): A. There are basically two types of functions - Single row and Multiple row functions.
Answer(36): B. Character, Date, Conversion, General, Number are the types of Single row functions.
Answer(37): B. Multiple Row functions always work on a group of rows and return one value per group of rows.
Answer(38): C. Group functions are same as Multi row functions and aggregate functions.
Answer(39): A. The COUNT(*) counts the number of rows including duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate and NULL values.
Answer(40): D. A table must have atleasr one column, its data type specification, and precision (if required).
Answer(41): C. Specifying alias name is good practice to improve the readability of the code and the view queries.
Answer(42): C. COUNT (column) ignores the NULL values but counts the duplicates.
Answer(43): C. The NATURAL JOIN clause implicitly matches all the identical named columns. To add additional conditions the WHERE clause can be used.
Answer(44): A. GROUPING SETS operations can be used to perform multiple GROUP BY aggregations with a single query.
Answer(45): B. The sequence of the column alias not matters much as they don't carry any behavioral attribute.
Answer(46): B. The WHERE clause predicate is optional in DELETE statement. If the WHERE clause is omitted, all the rows of the table will be deleted.
Answer(47): B. Provided the last names in the employees table are in a proper case, the condition WHERE last_name = 'smith' will not be satistified and hence no results will be displayed.
Answer(48): C. As a part of the active or a new transaction, the rows in the table will be deleted.
Answer(49): D. A compound query is one query made up of several queries using different tables.
Answer(51): B. A compound query will by default return rows sorted across all the columns,from left to right in ascending order.The only exception is UNION ALL, where the rows will not be sorted. The only place where an ORDER BY clause is permitted is at the end of the compound query.
Answer(52): C. COUNT(ALL column) ignores the NULL values but counts the duplicates.
Answer(54): B. COUNT (DISTINCT column) counts the distinct not null values.
Answer(55): C. The VARIANCE function accepts single numeric argument as the column name and returns variance of all the column values considering NULLs.
Answer(56): D. HAVING Clause is used for restricting group results. You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.
Answer(58): C. '> ALL' More than the highest value returned by the subquery. '< ALL' Less than the lowest value returned by the subquery. '< ANY' Less than the highest value returned by the subquery. '> ANY' More than the lowest value returned by the subquery. '= ANY' Equal to any value returned by the subquery (same as IN). '[NOT] EXISTS' Row must match a value in the subquery.
Answer(59): C. The view DEPT_SUM_VU is still a complex view as it uses DISTINCT keyword. Hence, DML operations are not possible on it.
Answer(60): C. Oracle does not convert data types implicitly.
Answer(61): D. The user space in a database is known as schema. A schema contains the objects which are owned or accessed by the user. Each user can have single schema of its own.
Answer(62): B. If the columns are mentioned in the INSERT clause, the VALUES keyword should contain values in the same order
Answer(63): B. A default value can be specified for a column during the definition using the keyword DEFAULT.
Answer(65): C. Use numeric expressions in SELECT statement to perform basic arithmetic calculations.
Answer(66): A. The SUBSTR(string, x, y) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position (x). When position is positive, then the function counts from the beginning of string to find the first character. When position is negative, then the function counts backward from the end of string.
Answer(67): A. Since the category FAMILY has to be restricted before grouping, table rows must be filtered using WHERE clause and not HAVING clause.
Answer(68): B. NVL is a general function to provide alternate values to the NULL values. It can really make a difference in arithmetic calculations using AVG, STDDEV and VARIANCE group functions.
Answer(69): C. As the combination of the job codes and departments is unique, there are no duplicates obtained.
Answer(70): A. the LENGTH function simply gives the length of the string.