SQL Data Retrieval Manipulation Objective type question | SQL Data Retrieval Manipulation Multiple choice question | SQL Data Retrieval Manipulation Wipro Question Dumps | SQL Data Retrieval Manipulation MySkillz | SQL Data Retrieval Manipulation trendnxt
51.The maximum number of components in the DECODE function, including expr,searches, results, and default, is
a) 265
b) 250
c) 255
d) 275
Ans: c
52.Which clause appears only in the last component of a query?
a) WHERE
b) GROUP BY
c) ORDER BY
d) HAVING
Ans: c
53.What is the output of SIGN(a) function? If a is less than ‘0’.
a) 1
b) -1
c) 0
d) 2.414
Ans: b
54.Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which statement inserts a row into the table?
a) INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, ‘John’,’ ‘);
b) INSERT INTO employees( first_name, last_name)VALUES(John,Smith);
c) INSERT INTO employees( first_name, last_name, employee_id) VALUES ( 1000, John, Smith);
d) INSERT INTO employees VALUES ( NULL, John,Smith);
Ans: a
55.What DML operations can perform using Merge Statement
a) Insert
b) Update
c) delete
d) all of these
Ans: d
56.which of the following is data query language?
a) INSERT
b) UPDATE
c) SELECT
d) All the above
Ans: c
57.Which of the following statement is true regarding ORDER BY clause?
a) The sort is in ascending order by default.
b) The sort is in descending order by default.
c) The ORDER BY clause must precede the WHERE clause.
d) The ORDER BY clause is executed on the client side.
Ans: a
58.Which is character manipulation function?
a) TRIM
b) TRUNC
c) TO_DATE
d) CASE
Ans: a
59.which one of following commands does not belongs to DML?
a) truncate
b) delete
c) update
d) merge
Ans: a
60.Which SQL statement returns a numeric value?
a) SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
b) SELECT ROUND(hire_date) FROM EMP;
c) SELECT sysdate-hire_date FROM EMP;
d) SELECT TO_NUMBER(hire_date + 7) FROM EMP;
Ans: d
61.Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2)FROM dual;
What will be displayed?
a) 0
b) 1
c) 2
d) An error statement
Ans: a
62.What would be the output of the below query?
select decode(substr(‘TECHNOLOGY’,2,3),’CH’,’PASS’,’FAIL’) from dual;
a) PASS
b) FAIL
c) No output
d) Error
Ans: b
63.If a table (table_name) contains the values 10,20,30,null,null for a column col1,What will the output of the below select statement.
Select count(*) from table_name;
a) 5
b) 3
c) 2
d) None
Ans: a
64.Which operator is used to return all distinct rows that are selected by the first query but not by the second?
a) MINUS
b) intersect
c) union
d) None of the above
Ans: a
65.Which operator is used to comment a single line within the query?
a) %%
b) —
c) $$
d) COMMENT
Ans: b
66.Which of the following is the right statement to get maximum salary from employees table?
a) SELECT MAX(salary) FROM employees;
b) SELECT salary FROM employees;
c) SELECT salary (MAX) “Maximum” FROM employees;
d) SELECT salary “Maximum” FROM employees;
Ans: a
67.Unique Constraint violation error occurs during ?
a) INSERTS
b) SELECT
c) DELETE
d) All of the above
Ans: a
68.Which of the following syntax enables an existing constraint ?
a) ALTER TABLE table_name ENABLE constraint_name;
b) ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constraint_name;
c) ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
d) ALTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name;
Ans: c
69.State TRUE or FALSE: Oracle SQL Function “MOD(n2,n1)” returns the remainder of n2 divided by n1
a) TRUE
b) FALSE
Ans: a
70.Which of the following SELECT statement will display the result ‘World’ from the string ‘Hello World’?
a) SELECT SUBSTR( ‘Hello World’,7) FROM DUAL;
b) SELECT LEFT(‘Hello World’,5) FROM DUAL;
c) SELECT RIGHT(‘Hello World’,5) FROM DUAL;
d) All of the above
Ans: a
71.SELECT * FROM employees WHERE hire_date < TO_DATE (’01-JAN-1999′, ‘DD-MON-YYYY’) AND salary > 3500; The above SQL statement retrieves ?
a) Only those hired before 1999 and earning less than $3500 a month are returned
b) Only those hired after 1999 and earning more than $3500 a month are returned
c) Only those hired after 1999 and earning more than $3500 a month are returned
d) Only those hired before 1999 and earning more than $3500 a month are returned
Ans: d
72.Which of the following SQL statements can calculate and return the absolute value of -33?
a) SELECT ABS(“-33”) Absolute FROM DUAL;
b) SELECT ABS(‘-33’) “Absolute” FROM DUAL;
c) SELECT ABS(-33) “Absolute” FROM DUAL;
d) None of the above
Ans: c
73.Which of the following SQL statement will display system date in the format “Monday, 01 June, 2001” ?
a) SELECT TO_DATE (SYSDATE, ‘FMDAY, DD Month, YYYY’) FROM dual;
b) SELECT TO_CHAR (SYSDATE, ‘FMDD, DY Month, YYYY’) FROM dual;
c) SELECT TO_CHAR (SYSDATE, ‘FMDY, DDD Month, YYYY’) FROM dual;
d) SELECT TO_CHAR (SYSDATE, ‘FMDay, DD Month, YYYY’) FROM dual;
Ans: d
74.Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?
a) DELETE VIEW emp_dept_vu;
b) DROP VIEW emp_dept_vu;
c) DROP emp_dept_vu;
d) DELETE emp_dept_vu;
Ans:b
75.Which SELECT statement should you use to extract the year from the system date and display it in the format “1998”?
a) SELECT TO_DATE(SYSDATE, ‘yyyy’) FROM dual;
b) SELECT TO_CHAR(SYSDATE, ‘yyyy’) FROM dual;
c) SELECT DECODE(SUBSTR(SYSDATE, 8), ‘YYYY’) FROM dual;
Ans:b
76.Evaluate this SQL statement: SELECT ename, sal FROM EMP; The SAL column stores the monthly salary of the employee. Which of the following query calculates the annual compensation as “monthly salary plus a monthly bonus of $100, multiplied by 12”?
a) SELECT ename, sal, (12sal)+100 FROM emp; b) SELECT ename, sal+(10012) FROM emp;
c) SELECT ename, sal, 12*(sal+100) FROM emp;
d) None of the above
Ans: c
77.Which of the following transaction control statement prevents more than one user from updating data in a table ?
a) LOCK
b) COMMIT
c) ROLLBACK
d) SAVEPOINT
Ans: a
78.You are granted the CREATE VIEW privilege. What does this privilege allow you to do?
a) Create a table view
b) Create a view in ANY schema
c) Create a view in your schema (Create any view, drop any view only)
d) Create a view only of it is based on tables that you created.
Ans: c
79.An inline view is a SELECT statement that is given an alias and is embedded in the _______________ clause of another SELECT statement.
a) FROM
b) WHERE
c) SELECT
d) CASE
Ans: a
80. Which SELECT statement will display the next value of the PARTS_ID_SEQ sequence by actually retrieving the value from the sequence?
a) SELECT NEXTVAL(parts_id_seq) FROM DUAL;
b) SELECT parts_id_seq.NEXTVAL FROM inventory;
c) SELECT parts_id_seq.NEXTVAL FROM DUAL;
d) SELECT NEXTVAL(parts_id_seq) FROM inventory;
Ans: c
81. The EMP table contains these columns: EMPLOYEE_ID NUMBER(4) LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2(10) You want to search for strings that contain ‘SA’ in the JOB_ID column. Which SQL statement do you use?
a) SELECT employee_id, last_name, job_id FROM EMP WHERE job_id LIKE ‘%SA%’;
b) SELECT employee_id, last_name, job_id FROM EMP WHERE job_id LIKE ‘%SA’;
c) SELECT employee_id, last_name, job_id FROM EMP WHERE job_id LIKE ‘SA%’;
d) None of the above
Ans: a
82.Which of the following Oracle Function retrieves maximum salary from EMP table ?
a) MAX
b) MIN
c) AVG
d) None of the above
Ans: a
83.Which of the following Oracle Function retrieves average salary from EMP table ?
a) MAX
b) MIN
c) AVG
d) None of the above
Ans: c
84.Which of the following UPDATE statement is TRUE
a) UPDATE emp SET empno=12 , empname = ‘JOHN’ WHERE empno=1;
b) UPDATE SET empno=12 , EMPNAME = ‘JOHN’ ON emp WHERE EMPNO=1;
c) UPDATE SET empno=12 , EMPNAME = ‘JOHN’ FROM emp WHERE EMPNO=1
d) None of the above
Ans: a
85.Which of the following operation will not make an entry in the log file
a) TRUNCATE
b) DELETE
c) INSERT
d) UPDATE
Ans: a
86.Which command is used to delete the table information from RECYCLEBIN
a) Drop table from recyclebin
b) Purge recyclebin
c) Delete recyclebin
d) None of the above
Ans: b
87.Which of the following clause allows Oracle to perform similar operations on dependant objects of a table
a) ALL
b) CACHE
c) CASCADE
d) COALESCE
Ans: c
88.which of the following statement will help to delete the child record when a parent is getting deleted.
a) ALTER TABLE DEPT ADD FOREIGN KEY (empno) REFERENCES emp(empno) ON DELETE CASCADE;
b) ALTER TABLE DEPT ADD FOREIGN KEY (empno) REFERENCES emp(empno) ON DELETE ALL;
c) ALTER TABLE DEPT ADD FOREIGN KEY (empno) REFERENCES emp(empno) DELETE ALL;
d) ALTER TABLE DEPT ADD FOREIGN KEY (empno) REFERENCES emp(empno) AFTER DELETE CASCADE ;
Ans: a
89.Which of the following is NOT TRUE about the PURGE clause in delete statement
a) Deletes the data from child tables
b) Drops the table and releases the space associated with it in a single step
c) Database does not place the table and its dependent objects into the recycle bin
d) All the above
Ans: b
90.In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?
a) Immediately after the SELECT clause
b) Before the WHERE clause
c) After the WHERE clause
d) After the ORDER BY clause
Ans: c
91.The result of a ————- for tables A and B always contains all records of the “left” table (A), even if the join-condition does not find any matching record in the “right” table (B).
a) left outer join
b) RIGHT OUTER JOIN
c) FULL OUTER JOIN
d) None of the above
Ans: a
92.A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table that did not have a match in the second table
a) TRUE
b) FALSE
Ans: a
93.In —————– join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables.
a) Equi Join
b) Inner Join
c) Outer Join
d) Natural Join
Ans:d
94.Evaluate the following SQL statement:
SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMP e, DEPARTMENT d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
a) Selection, projection, join
b) Difference, projection, join
c) Selection, intersection, join
d) Difference, projection, product
Ans: a
95.An inner join essentially combines the records from two tables (A and B) based on a given join-predicate.
a) TRUE
b) FALSE
Ans: a
96.Which statement about subqueries is true?
a) A single row subquery can retrieve data from more than one table.
b) A single row subquery can retrieve data from only one table.
c) A single row single column subquery cannot be used in a condition where the LIKE operator is used for comparison.
d) None of the above
Ans: a
97.A subquery in the FROM clause of a SELECT statement is called :
a) Nested subquery
b) Inline view
Ans: b
98.what is the difference between Inner and equi join
a) An inner join is a type of join where we use = and <> in the where condition joining the tables. In an equi join we join tables with = operator only
b) An inner join is a type of join where we use =+ in the where condition joining the tables. In an equi join we join tables with + = operator only
c) An equi join is a type of join where we use = and <> in the where condition joining the tables. In an inner join we join tables with = operator only
d) inner join and equi join both are same
Ans: a
99.Which operator can be used with a multiple-row subquery?
a) <>
b) LIKE
c) BETWEEN
d) NOT IN
Ans: d
100.Table A has 5 rows and table B has 0 rows Cartesian join on A,B will have—-rows.
a) 1
b) 0
c) 5
d) None of the above
Ans: b
101.Consider the following query :
select e.name, d.depid
from emp e, dept d
where e.depid(+) = d.depid;
The above query indicates :
a) Right Outer Join
b) Left Outer Join
c) Natural Join
d) Invalid Query
Ans: d
102.Which of the following is true regarding subqueries?
a) No limit on subquery in WHERE clause and 256 level can be nested in FROM clause
b) No limit on subquery in FROM clause and 255 level can be nested in WHERE clause
c) No limit on subquery in WHERE clause and 255 levels can be nested in FROM clause
d) No limit on subquery in WHERE clause and 32 level can be nested in FROM clause
Ans: b
103.The SQL——————- operator takes the results of two queries and returns only rows that appear in both result sets.
a) INTERSECT
b) UNION
c) EXCEPT
d) None of the above
Ans: a
104.What is wrong with the following query?
select col1, col2, col3
from TableA, TableB, TableC
where TableA.col1 = TableB.col1
group by col3
a) col1 is ambiguously named but is not properly prefixed in the select clause.
b) There is no order by clause.
c) Group by clauses are not permitted in a join.
d) Identically named columns in two separate tables are not permitted in a join clause.
Ans: a
105.In which case would you use a FULL OUTER JOIN?
a) Both tables have NULL values.
b) You want all unmatched data from one table.
c) You want all unmatched data from both tables.
d) One of the tables has more data than the other.
Ans: d
106.A subquery can be used to _.
a) Sort data in a specific order
b) Convert data to a different format
c) Retrieve data based on an unknown condition
d) All of the above
Ans: d
107.select 1 ,2 from dual
union
select 1,2 from dual; What will be the output of the above query.
a) 1 2
b) 2 1
c) null null
d) Throws an error
Ans: a
108.1 select ‘A’,’B’ from dual
2 union
3 select 1,2 from dual ;
The above statement throws an error at line number _?
a) error at line 1
b) error at line 2
c) error at line 3
d) None of the above
Ans: a
109.A full outer join combines the results of both left and right outer joins.
Ans:TRUE
110.What are the different types of OUTER JOINS?
a) LEFT OUTER JOIN
b) RIGHT OUTER JOIN
c) FULL OUTER JOIN
d) All of the above
Ans: d
111.Which statements about subqueries are true?
a) A single row subquery can retrieve only one column and one row.
b) A single row subquery can retrieve only one row but many columns.
c) A multiple row multiple columns subquery can be compared by using the “>” operator.
d) A single row single column subquery can not use the IN operator.
Ans: b
112.The order in which the system joins tables changes the final result-set of the query.
Ans:TRUE
113.select 1 ,2 from dual
union all
select 1,2 from dual;
How many rows will be selected by the above query?
a) 1
b) 2
c) 0
d) Throws an error
Ans: b
114.A join is a query that combines rows from two or more tables, views, or materialized views
a) TRUE
b) FALSE
Ans:TRUE
115.A join is a query that combines rows from only one table
a) TRUE
b) FALSE
Ans:b
116.During Joins, if any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity
Ans:TRUE
117.An equijoin is a join with a join condition containing an equality operator
Ans:TRUE
118.An equijoin is a join with a join condition containing an inequality operator
a) TRUE
b) FALSE
Ans:b
119.A self join is a join of a table to itself
Ans:TRUE
120.A self join is a join of two different tables
a) TRUE
b) FALSE
Ans:b
121.A cartesian product join is a join between two tables with no join condition
Ans:TRUE
122.A cartesian product join is a join between two tables with atleast one join condition
a) TRUE
b) FALSE
Ans:b
123.An inner join is also called a simple join
Ans:TRUE
124.An inner join is also called as a self join
a) TRUE
b) FALSE
Ans:b
125.A natural join is a join statement that compares the common columns of both tables with each other
Ans:TRUE
126.A natural join is same as equi join
Ans:TRUE
127.A natural join is NOT same as equi join
a) TRUE
b) FALSE
Ans:b
128.In Oracle, a sub query can be used to ______
a) Create groups of data
b) Sort data in a specific order
c) Convert data to a different format
d) Retrieve data based on an unknown condition
Ans: d
129.Full outer join can be achieved by using which of the following option?
a) Left Outer Join intersect Right Outer Join
b) Left Outer Join minus Right Outer Join
c) Left Outer Join union Right Outer Join
d) None of the above
Ans: c
130.Which of the below statement could be accurate in describing Cartesian product?
a) select * from a, b where 1=2;
b) select * from a, b;
c) Both A & B
d) None of the above
Ans: b
131.In Oracle, Table A has 5 rows and table B has 1 row.
How many rows will be returned by the below query?
Select * from A, B;
a) 1
b) 5
c) 6
d) None of the above
Ans: b
132.In Oracle, Table A has 2 rows and table B has 3 rows.
How many rows will be returned by the below query?
Select * from A, B;
a) 2
b) 3
c) 5
d) 6
Ans: d