Oracle / SQL
Which of the following is the appropriate syntax used to create a Table? 1) CREATE TABLE [schema.] table (column datatype [DEFAULT expr] [,..] ); 2) CREATE TABLE INTO [schema.] table (column datatype [DEFAULT expr] [,..] ); 3) CREATE TABLE VALUES [schema.] table (column datatype [DEFAULT expr] [,..] ); 4) None of the mentioned options
1) CREATE TABLE [schema.] table (column datatype [DEFAULT expr] [,..] );
Which of the following is the Database Language that allows you to access or maintain Data in a Database 1) DCL 2) DML 3) DDL 4) All of the Mentioned
1) DCL
Which of the following is true about the result of a sub-query? 1) The result of a sub-query is generally ignored when executed. 2) The result of a sub-query doesn't give a result, it is just helpful in speeding up the main query execution 3) The result of a sub-query is used by the main query. 4) The result of a sub-query is always NULL
3) The result of a sub-query is used by the main query.
Which of the following statements are true about the evaluation of clauses in a SELECT statement? Check all that apply. 1) The Oracle Server will evaluate a HAVING clause before a WHERE clause. 2) The Oracle Server will evaluate a WHERE clause before a GROUP BY clause 3) The Oracle Server will evaluate a GROUP BY clause before a HAVING clause. 4) The Oracle Server will evaluate an ORDER BY clause before a WHERE clause.
2) The Oracle Server will evaluate a WHERE clause before a GROUP BY clause 3) The Oracle Server will evaluate a GROUP BY clause before a HAVING clause.
When must a subquery be placed in the outer query's HAVING clause? 1) The inner query needs to reference the value returned to the outer query. 2) The value returned by the inner query is to be compared to grouped data in the outer query. 3) The subquery returns more than one value to the outer query. 4) None of the mentioned options
2) The value returned by the inner query is to be compared to grouped data in the outer query.
Which of the following can be used to make a new Database table? 1) alter 2) create 3) add 4) update
2) create
Insert' is a DDL statement. State True or False. 1) true 2) false
2) false
Which of the following is not true about SQL joins? 1) A left or right outer join returns the results of the inner join as well as the unmatched rows in the left or right table respectively. 2) An inner join is a join of two tables returning only matching rows. 3) None of the mentioned options 4) A full outer join returns results of an inner join as well as the results of a left and right join.
3) None of the mentioned options
Which join refers to join records from the write table that have no matching key in the left table, included in the result set? 1) Left outer join 2) Half outer join 3) Right outer join 4) Full outer join
3) Right outer join
Which is the statement equivalent to the statement select * from R, S in SQL? 1) Select * from R union join S 2) Select * from R natural join S 3) Select * from R cross join S 4) Select * from R inner join S
3) Select * from R cross join S
EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW_EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which of the following is a valid UPDATE statement? 1) UPDATE new_employees SET name = (SELECT last_name || first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new_employees); 2) UPDATE new_employees SET name = (SELECT last_name||first_name FROM employees ) WHERE employee_id =180; 3) UPDATE new_employees SET name = (SELECT last_name|| first_name FROM employees WHERE employee_id =180) WHERE employee_id =180; 4) UPDATE new_employees SET name = (SELECT last_name|| first_name FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees)) WHERE employee_id =180;
3) UPDATE new_employees SET name = (SELECT last_name|| first_name FROM employees WHERE employee_id =180) WHERE employee_id =180;
What is the difference between the DELETE and TRUNCATE SQL clauses? 1) The TRUNCATE clause is identical to the DELETE clause 2) The DELETE clause deletes all rows in a database table, while the TRUNCATE clause can have a WHERE condition and might or might not delete all rows in a table 3) The TRUNCATE clause deletes all rows in a database table, while the DELETE clause can have a WHERE condition and might or might not delete all rows in a table.
3) The TRUNCATE clause deletes all rows in a database table, while the DELETE clause can have a WHERE condition and might or might not delete all rows in a table.
What is the capacity of a BLOB Data Type column to hold values? 1) 1 KB 2) 2 GB 3) 4 GB 4) 3 KB
3) 4 GB
What is the significance of the below statement "HAVING COUNT (emp_id)>2" in the below given query? SELECT d.name, count (emp_id) emp_no FROM department d INNER JOIN Employee e ON d.dept_id=e.emp_id GROUP BY d.name HAVING COUNT (emp_id)>2 (A) Filter out all rows whose total emp_id below 2 (B) Selecting those rows whose total emp_id>2 1) Filter out all rows whose total emp_id below 2 2) Selecting those rows whose total emp_id>2 3) Both (A) and (B) 4) Neither (A) nor (B)
3) Both (A) and (B)
Which is the correct format to create a Table? 1) CREATE EMP.SCOTT TABLE ( EmpNo NUMBER, EName VarChar(25), Job Char(10) , Hiredate Date, ) 2) CREATE EMP TABLE ( EmpNo NUMBER, EName VarChar(25), Job Char(10) , Hiredate Date, ) 3) CREATE TABLE EMP ( EmpNo NUMBER, EName VarChar(25), Job Char(10) , Hiredate Date, ) 4) All the mentioned options
3) CREATE TABLE EMP ( EmpNo NUMBER, EName VarChar(25), Job Char(10) , Hiredate Date, )
What happens when a table which is marked as Read Only is attempted for drop? 1) It will throw an error 2) It will no longer remain Read Only but cannot be dropped either 3) It will be dropped without errors 4) It will remain un-touched
3) It will be dropped without errors
Which of the following Data Types are chosen for large objects? Select all that apply. 1) CLOB 2) BLOB 3) RAW 1) Only option 1 2) Only option 2 3) Options 1 and 2 4) Options 2 and 3
3) Options 1 and 2
Which of the following helps to add Emp_Address column to emp table? 1) ALTER TABLE EMP add ( EMP_Address Char(24)); 2) ALTER TABLE EMP add EMP_Address Char(24); 3) ALTER EMP add ( EMP_Address Char(24)); 1) Only option 1 2) Only option 2 3) Options 1 and 2 4) Options 2 and 3
3) Options 1 and 2
Which query is used to find the temperature in increasing order of all cities. 1) SELECT city FROM weather ORDER BY temperature; 2) SELECT city, temperature FROM weather; 3) SELECT city, temperature FROM weather ORDER BY temperature; 4) SELECT city, temperature FROM weather ORDER BY city;
3) SELECT city, temperature FROM weather ORDER BY temperature;
Which of the following query is used to Select all the Students from City = "Pune". 1) Select * from STUDENT where city is "Pune"; 2) Select * from STUDENT where city == "Pune"; 3) Select * from STUDENT where city="Pune"; 4) None of the mentioned options
3) Select * from STUDENT where city="Pune";
Which of the following are the types of sub-queries? 1) Ordered sub-queries 2) Grouped sub-queries 3) Single row sub-queries 4) None of the mentioned options
3) Single row sub-queries
Which of following commands is a DDL (Data Definition Language) command that is often considered along with the DML commands? 1) DELETE 2) INSERT 3) TRUNCATE 4) None of the mentioned options
3) TRUNCATE
Which of the following statements is true about column-level constraints? 1) They can be created before the creation of a table 2) They can be created before the defining of a column 3) They are included when the column is defined 4) None of the mentioned options
3) They are included when the column is defined
What is the functional difference between a column-level constraint and a table-level constraint? 1) Column-level constraint applies to all the columns of a table 2) Table-level constraint applies to all the columns of a table 3) They both are functionally the same, only the syntax is different 4) None of the mentioned options
3) They both are functionally the same, only the syntax is different
Which of the following are valid Constraints in Oracle? Check all that apply. 1) INDEX 2) GENERAL 3) UNIQUE 4) PRIMARY KEY
3) UNIQUE 4) PRIMARY KEY
Which of the following SQL statements returns only different values? 1) select unique 2) select diff 3) select distinct 4) select once
3) select distinct
To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join? 1) 8 2) 2 3) 4 4) 3
4) 3
If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a Cartesian join on those two tables? 1) 15 2) 10 3) 5 4) 50
4) 50
Which type of join is needed when you wish to return rows that have matching values? 1) Equi-join 2) Natural join 3) Outer join 4) All the mentioned options
4) All the mentioned options
Which of the following is used to retain the non-matching information by including non-matching rows in the results of a join? 1) full outer join 2) left outer join 3) right outer join 4) Any of above
4) Any of above
The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data? 1) Self join 2) Non-Equijoin 3) Equijoin 4) Outer join
4) Outer join
The JOIN column must be included in the select statement when you use the NATURAL JOIN clause. State True or False. 1) FALSE 2) TRUE
1) FALSE
A subquery can be used anywhere an expression is allowed. State True or False. 1) True 2) False
1) True
Which of the following operators is typically used in a nonequijoin? 1) NOT 2) >=, <= 3) IN 4) OR
2) >=, <=
Which of the following should be used to find all the courses taught in the Fall 2009 semester but not in the Spring 2010 semester? 1) Select distinct course id from section where semester = 'Fall' and year= 2009 and course id not in (select course id from section where semester = 'Spring' and year= 2010); 2) Select distinct course_id from instructor where name not in ('Fall', 'Spring'); 3) (Select course id from section where semester = 'Spring' and year= 2010) 4) Select count (distinct I from takes where (course id, sec id, semester, year) in (select course id, sec id, semester, year from teaches where teaches.ID= 10101);
1) Select distinct course id from section where semester = 'Fall' and year= 2009 and course id not in (select course id from section where semester = 'Spring' and year= 2010);
Evaluate the set of SQL statements: CREATE TABLE dept (dept_id NUMBER (2) dname VARCHAR2 (14), Loc VARCHAR2 (13)); ROLLBACK; DESCRIBE DEPT . Which of the following statements is true about the above mentioned set? 1) The DESCRIBE DEPT statement displays the structure of the DEPT table 2) The ROLLBACK statement frees the storage space occupied by the DEPT table. 3) The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist 4) The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
1) The DESCRIBE DEPT statement displays the structure of the DEPT table
Which of the following is true about correlated query? 1) Use the result of an inner query to determine the processing of an outer query 2) Use the result of an outer query to determine the processing of an inner query 3) Use the result of an outer query to determine the processing of an outer query 4) Use the result of an inner query to determine the processing of an inner query
1) Use the result of an inner query to determine the processing of an outer query
Which of the following is a SQL aggregate function? 1) LEFT 2) AVG 3) JOIN 4) LEN
2) AVG
If a table appears only in a subquery and not in the outer query, then columns from that table can be included in the output. State True or False. 1) True 2) False
2) False
Which of the following SQL keyword(s) is used with wildcards? 1) NOT IN only 2) LIKE only 3) IN only 4) IN and NOT IN
2) LIKE only
Using which construct can we can test for the nonexistence of tuples in a subquery? 1) Not exist 2) Not exists 3) Exists 4) Exist
2) Not exists
Which of the following return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions? 1) Outer joins 2) Inner Join 3) Self Join 4) Cartesian Product
1) Outer joins
Which of the following options fits in the blank to select the dept_name that has Computer Science as its ending string? Select emp_name from department, where dept_name like ' _____ Computer Science'; 1) % 2) _ 3) || 4) $
1) %
Which of the following statements is true about a Table and an Index? 1) An index and a table can have the same name in a schema 2) An index and a table within a schema cannot have the same name 3) Neither of A nor B 4) None of the mentioned options
1) An index and a table can have the same name in a schema
Which of the following DML operations consider constraints on a column? 1) INSERT 2) UNION 3) DELETE 4) UPDATE
1) INSERT 3) DELETE 4) UPDATE
Which of the following statements is true about PRIMARY KEY constraint? 1) It applies a NOT NULL constraint implicitly to the column on which it is defined 2) It applies a UNIQUE KEY constraint implicitly to the column on which it is defined 3) It applies a CHECK constraint implicitly to the column on which it is defined 4) It applies a DEFAULT constraint implicitly to the column on which it is defined
1) It applies a NOT NULL constraint implicitly to the column on which it is defined
What does NUMBER (8, 2) in Oracle indicate? 1) It means there are 8 digits in total, 6 digits before the decimal and 2 after the decimal 2) It means there are 10 digits in total with 8 digits before the decimal and 2 after decimal 3) It means there are 2 digits before the decimal and 8 after the decimal point 4) None of the mentioned options
1) It means there are 8 digits in total, 6 digits before the decimal and 2 after the decimal
INSERT INTO departments (department_id ,DEPARTMENT_NAME ,MANAGER_ID, LOCATION_ID) VALUES (100, 'Human Resources', 121, 1000); Suppose the above given statement is modified as below: INSERT INTO departments VALUES (100, 'Human Resources', 121, 1000); What will be the outcome of this modification? Assume that the DEPARTMENTS table has four columns namely, department_id ,DEPARTMENT_NAME ,MANAGER_ID and LOCATION_ID . 1) It will insert values into all the columns of the departments table assuming that column values are provided in the same sequence as the column in the table 2) It will throw an ORA error because column names are not explicitly mentioned 3) It will throw an ORA error because VALUES clause is wrongly used in the INSERT 4) None of the mentioned options
1) It will insert values into all the columns of the departments table assuming that column values are provided in the same sequence as the column in the table
The EXISTS keyword will be true if: (A) Any row in the subquery meets the condition only. (B) All rows in the subquery fail the condition only. Which of the above statements is true? 1) Only (A) 2) Only (B) 3) Both (A) and (B) 4) Neither (A) nor (B)
1) Only (A)
Which two SELECT statements display 2000 in the format "$2,000.00" ? (Choose two) 1) SELECT TO_CHAR (2000, '$#,###.##') FROM dual; 2) SELECT TO_CHAR (2000, '$0,000.00') FROM dual; 3) SELECT TO_CHAR (2000, '$9,999.00') FROM dual; 4) SELECT TO_CHAR (2000, '$N,NNN.NN') FROM dual;
2) SELECT TO_CHAR (2000, '$0,000.00') FROM dual; 3) SELECT TO_CHAR (2000, '$9,999.00') FROM dual;
Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employee's possible minimum and maximum salaries based on their job title? EMPLOYEES Table: 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) JOBS Table: JOB_ID NOT NULL VARCHAR2 (10) JOB_TITLE NOT NULL VARCHAR2 (35) MIN_SALARY NUMBER (6) MAX_SALARY NUMBER (6) 1) SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary FROM employees e NATURAL JOIN jobs j USING (job_id); 2) SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary FROM employees e NATURAL JOIN jobs j; 3) SELECT first_name, last_name, job_id, min_salary, max_salary FROM employees e FULL JOIN jobs j (job_id); 4) SELECT first_name, last_name, job_id, min_salary, max_salary FROM employees NATURAL JOIN jobs;
4) SELECT first_name, last_name, job_id, min_salary, max_salary FROM employees NATURAL JOIN jobs;
In which of the following scenarios would you use a FULL OUTER JOIN? 1) You want all matched data from both tables. 2) Both tables have NULL values. 3) You want all unmatched data from one table. 4) You want all unmatched data from both tables.
4) You want all unmatched data from both tables.
Select the correct WHERE clause for the following select statement to perform a valid outer join between DEPARTMENTS and EMPLOYEES, to list departments without employees: SELECT d.department_name, e.last_name FROM employees e, departments d WHERE 1) e.department_id = d.department_id(+) 2) e.department_id(+) = d.department_id(+) 3) e.department_id = d.department_id 4) e.department_id(+) = d.department_id
4) e.department_id(+) = d.department_id
Which of the following multi-row operators can be used with a sub-query? 1) IN 2) ANY 3) ALL 4) All the mentioned options
4) All the mentioned options
Which of the following SQL statements is used to delete date from a Database? 1) COLLAPSE 2) REMOVE 3) ALTER 4) DELETE
4) DELETE
What is the meaning of LIKE '%0%0%' ? 1) Feature begins with two 0's 2) Feature ends with two 0's 3) Feature has more than two 0's 4) Feature has two 0's in it, at any position
4) Feature has two 0's in it, at any position
Which of the following keywords is used with Data Control Language (DCL) statements? 1) SELECT 2) INSERT 3) DELETE 4) GRANT
4) GRANT
The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this? 1) GRANT select ON dept TO ALL_USERS; 2) GRANT select ON dept TO ALL; 3) GRANT QUERY ON dept TO ALL_USERS 4) GRANT select ON dept TO PUBLIC;
4) GRANT select ON dept TO PUBLIC;
You need to give the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this? 1) GRANT select, insert, update ON student_grades TO manager; 2) GRANT select, insert, update ON student_grades TO ROLE manager 3) GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION; 4) GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION
4) GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION
Which of the following SQL commands can be used to add data to a database table? 1) ADD 2) UPDATE 3) APPEND 4) INSERT
4) INSERT
Which of the following queries is illegal? 1) SELECT SYSDATE - SYSDATE FROM DUAL; 2) SELECT SYSDATE - (SYSDATE - 2) FROM DUAL; 3) SELECT SYSDATE - (SYSDATE + 2) FROM DUAL; 4) None of the mentioned options
4) None of the mentioned options
Which of the following statements gets the total value of the column 'Price' in the 'Sales' table? 1) SELECT ADD(Price) FROM Sales 2) SELECT TOTAL(Price) FROM Sales 3) SELECT SUM(Price) WHERE Sales 4) SELECT SUM(Price) FROM Sales
4) SELECT SUM(Price) FROM Sales
SELECT c.customer_id, o.order_id, o.order_date, p.product_name FROM customer c, curr_order o, product p WHERE customer.customer_id = curr_order.customer_id AND o.product_id = p.product_id ORDER BY o.order_amount; This statement fails when executed. Which change will correct the problem? 1) Include the ORDER_AMOUNT column in the SELECT list 2) Use the table name in the ORDER BY clause. 3) Remove the table aliases from the WHERE clause. 4) Use the table aliases instead of the table names in the WHERE clause.
4) Use the table aliases instead of the table names in the WHERE clause.
What is the output of the SQL statement SELECT ROUND(45.926, -1) FROM DUAL? 1) is illegal 2) prints garbage 3) prints 045.926 4) prints 50
4) prints 50
