amcat test

Choose the correct option

Question Ans 1 Ans 2 Ans 3 Ans 4 Ans 5CorrectAnswer

Which of the following can bea valid column name?

AccountType_#12 #Serial Number 2_Address Column All of theabove

1

Which of the followingcommands will not write tothe rollback segment afterdeleting all the data from atable?

DROP DELETE CASCADE TRUNCATE 4

Which of the following SQLfunctions can operate on anydatatype?

MIN FLOOR LPAD TO_CHAR LOWER 1

What is the minimum numberof joins required after WHEREclause for avoiding a cartesianproduct of 5 tables?

6 5 3 4 4

Which of the following queriesoutput names of all theemployees from a table"Employees", whose "JobType" includes either of thesubstrings "MG" or "DG"?

SELECT Emp_name FROMEmployees WHEREJobType='%MG%' orJobType='%DG%'

SELECT Emp_name FROMEmployees WHERE JobTypeLike '%MG%' or JobType Like'%DG%'

SELECT Emp_nameFROM Employees WHEREJobType= ='%MG%' orJobType= ='%DG%'

SELECT Emp_name FROMEmployees WHERE JobTypeLike '%MG%' or '%DG%'

2

Which of the following queriesselect all the records from atable named "Companies",where the "CompanyName" isalphabetically between"Aspiring" and "Minds"?

SELECT * FROMCompanies WHERECompanyName BETWEEN'Aspiring' AND 'Minds'

SELECTCompanyName>'Aspiring'AND CompanyName<'Minds'FROM Companies

SELECT * FROMCompanies WHERECompanyName>'Aspiring'ANDCompanyName<'Minds'

SELECT * FROM CompaniesWHERECompanyName>='Aspiring'AND CompanyName=<'Minds'

3

Which of the following SQLstatements will generate anerror:

SELECT Workers, SUM(Days)FROM WorkingDaysGROUP BY WorkersHAVING SUM (Days) > 60

SELECT Workers, SUM (Days)FROM WorkingDaysGROUP BY WorkersWHERE SUM (Days) > 60

SELECT Workers, SUM(Days) AS TotalDaysFROM WorkingDaysGROUP BY WorkersHAVING TotalDays > 60

SELECT Workers, SUM (Days)AS TotalDaysFROM WorkingDaysGROUP BY WorkersWHERE TotalDays > 60

2

The table "Sales" is used tomaintain the records by thesales department of acompany. The column"OrderNo" is the primary keyof the table. Which of thefollowing SQL statements willhave the index on "OrderNo"ignored?

Select OrderNo, ProductName from Sales whereOrderNo = '1024'

Select * from Sales whereOrderNo = '1024'

Select * from Sales wherenvl(OrderNo, '00000') ='1024'

Select 1 from Sales whereOrderNo = '1024'

3

What does the followingstatement return: (Assumingtoday is Tuesday, 5 October2010)

SELECTto_char(NEXT_DAY(sysdate,'TUESDAY'), 'DD-MON-RR')FROM dual;

4-Oct-10 10-Oct-10 12-Oct-10 6-Oct-10 17-Oct-10 3

Which of the following querieswill print the name of thedegrees from column"DegreeName", which are notunique, along with theirnumber of occurences in thetable "Degrees"?

SELECT DegreeName,COUNT(DegreeName)FROM DegreesGROUP BY DegreeNameHAVING(COUNT(DegreeName)>1);

SELECT DegreeName,COUNT(DegreeName)FROM DegreesGROUP BY DegreeNameHAVING ( DegreeName NOTUNIQUE);

SELECT NOTDISTINCT(DegreeName),COUNT(DegreeName)FROM DegreesGROUP BY DegreeName;

SELECT NOTDISTINCT(DegreeName),COUNT(DegreeName)FROM Degrees;

1

Which of the followingstatements is/are true?a) Updating a table withindexes takes more time thanupdating a table without them.b) By default the ORDER BYclause sorts the query result indescending order.

Only a Only b Both None of the above 1

If the total number of rows ina table "Product" is 14, thenwhat is the output of thefollowing query?Select * from Productwhere rownum >= 6;

7 8 9 10 This querywill generatean error.

3

Which of the followingconstraints ensure that a tablehas at least one indexedattribute:

CHECK NOT NULL PRIMARY KEY FOREIGN KEY 3

INTERVIEW QUESTIONS - SET 1

1 of 13



Which of the following querieswill find the highest rating ineach city in "Country" tableand output it in the form:The highest rating for the city(name of the city), is: (Rating)

Select 'The highest ratingfor the city ', city, 'is:', MAX(rating)from CountryGroup by city

Select 'The highest rating forthe city ' || city || 'is:' || MAX(rating)from CountryGroup by city

Select 'The highest ratingfor the city ' || city || 'is:'|| MAX(rating)from Country

Select 'The highest rating forthe city ', city, 'is:', MAX(rating)from Country

1

Which of the following criteriamust be true to perform aninner join on two tables?

The columns common inthe joining table may ormay not have commonvalues.

In order to perform inner joinon two tables, they must havecommon columns.

The columns common inthe joining table musthave common values.

None of the above. 1

On the issue of followingcommand, what task could Edperform on the student table?GRANT updateON studentTo edWITH GRANT OPTION;

View data. Delete data. Modify constraints. Give update access to otherusers.

None of theabove.

4

The SQL command used totake off all the privileges froma user is:

REVOKE ALL PRIVILEGESFROM

REMOVE ALL PRIVILEGESFROM

REVOKE ALL ON REMOVE ALL IN REVOKE ALLIN

3

Which of the followingautomatic conversion ofdatatype is not possible?

Date gets converted intocharacter string.

Number gets converted intodate.

Number gets convertedinto character string.

All of the above. 2

Which of the followingstatements is correct?

Foreign key cannot bedefined on a combinationof columns.

The foreign key andreferenced key can exist in thesame table.

Referential integrity isdefined in the child table.


Which of the followingstatements is not correct?

COMMIT and ROLL BACKare TCL commands.

ROLLBACK removes allchanges made to data, to thebeginning or to the savepointof the transaction.

A transaction cannot berolled back after it iscommitted.

All of the above statementsare correct.

4

The following query outputs:Select NULL from DUAL;

Blank value 0 "0" None of the above 4

A user wants to minimize theaccess time. Which of thefollowing SQL commands canbe used to get the desiredresult?

REDUCE TIME INDEX MINIMIZE MIN TIME 2

Which of the following optionsis not a valid set operator inSQL?

UNION UNION ALL INTERSECT MINUS All of theabove arevalid

4

A query within a query wherethe inner query is evaluatedfor each row in the outerquery is called:

Join View Correlated subquery None of the above 3

Complete the followingstatement:A transaction ends _______.

Only when it is committed. Only when it is rolled back. When it is committed orrolled back.

When it is neither committednor rolled back.

3

Which of the following queriesoutput only those rows whichsatisfy match condition?

SELECT b.BookName,b.Author, b.Price, s.QtyFROM Books as b, Stock assON b.BookID = s.BookID

SELECT b.BookName,b.Author, b.Price, s.QtyFROM Books as b LEFT JOINStock as sON b.BookID = s.BookID

SELECT b.BookName,b.Author, b.Price, s.QtyFROM Books as b JOINStock as sON b.BookID = s.BookID

SELECT b.BookName,b.Author, b.Price, s.QtyFROM Books as b RIGHTJOIN Stock as sON b.BookID = s.BookID

3

The DUAL table has: One rows and one column. One row with many columns. One column with manyrows.

Many rows and manycolumns.

1

Which of the following SQLqueries displays the randomrows from the tableEmployee?

SELECT RAND(*)FROM Employee;

SELECT *FROM EmployeeORDER BY RAND();

SELECT RAND()FROM Employee;

SELECT RAND(N)FROM Employee;

2

Which of the following optionscannot be extracted usingDESCRIBE command on atable?

Attribute names Data types Indexes Size of the attributes 3

Which of the following is not atype of data dictionary view?

USER SYS DBA ALL 2

The DUAL table is owned bySYS and can be accessed by:

Only SYS Users. Only Super Users. All Users. Only SYS Users and DBA. 3

Which of the following SQLfunctions do not ignore NULLvalues?

Sum(n) Count (*) Min(n) Avg(n) 2

What is the difference betweenTRUNCATE and DELETEcommand?

TRUNCATE commanddeletes the data as well astable structure, while

TRUNCATE commandperforms automatic commit,while DELETE command does

TRUNCATE commanddeletes only table data,while DELETE command

DELETE command performsautomatic commit, whileTRUNCATE command does

2


2 of 13



DELETE command deletesonly the data of the table.

not perform automaticcommit.

deletes the table data aswell as table structure.

not perform automaticcommit.

Which of the following SQLstatements output sameresults?a) Select * from tbl_hotelswhere hotelName Like '%';b) Select * from tbl_hotelswhere hotelName Like '_ %';c) Select * from tbl_hotelswhere hotelName Like ' %_';d) Select * from tbl_hotelswhere hotelName Like '_ _ ';

b, d b, c a, b, c c, d 2

What is the output of followingquery:Select 25/NULLfrom DUAL;

TRUE FALSE 25 Null Error 4

Which of the followingstatements is correct aboutcollation?a) Collation is used to defineset of rules for the sorting ofdata.b) Collation is used to criticallyexamine the results of thequery.

a b Both a and b None of the above 1

A query has OR, AND, NOTlogical operators with noparenthesis. Find the order inwhich these logical operatorswill be evaluated.

NOT, OR, AND NOT, AND, OR AND, OR, NOT The order of evaluation willdepend on the order of theiroccurrence in the query.

2

A user wants to output all thenames of the employees froma table Employee, such that allthe fields with NULL aredisplayed in the start. Choosethe correct option to fill instatement 3 in the querybelow to get the desiredresult:1. Select emp_names2. from Employee3. Order By ___________

emp_name NULL; emp _name desc; emp_name asc; None of the above 2

If the output of the query:

SELECT COUNT * FROMSales;

on a table "Sales" with anon-Null UNIQUE column"Revenue" is 12, then what isthe output of the followingquery:

SELECT COUNT *FROM SalesWHERE Revenue > ALL(SELECT Revenue FROMSales);

0 5 9 10 1

Choose the correct statement: Column alias cannot beused in the ORDER BYclause.

Column alias can be used inthe ORDER BY clause.

Column alias can be usedin the WHERE clause.

Both A and C. 2

Which of the following optionscannot be a result of thequery:Select city from tbl_city wherecity LIKE '%a_%b%'

Hyderabad Ahmedabad Chaibasa Mahabaleshwar 1

Which of the followingstatements is not correct?

FALSE AND NULL isFALSE.

TRUE AND NULL is TRUE. NOT NULL is NULL. FALSE OR NULL is NULL. 2

What is the output of thequery:Select sum(1)from candidate;

0 7 500 Error 3

Sonia wants to retrieve thedomain name from the emailids listed in column "email_id"of the table "UserRecords".

SELECT SUBSTR(email_id,INSTR(email_id, " '@', 1 )+ 1 ) ") FROM UserRecords;

SELECT SUBSTR(email_id,INSTR(email_id," '@', 1)) ")FROM UserRecords ;

SELECTSUBSTR(email_id,INSTR(email_id," '@', 1)-1) ") FROM UserRecords



3 of 13



Which of the following queryoutputs the required result? In"[email protected]",domain name is"aspiringminds.in".

;

Which of the followingstatements is not correct abouttriggers?

A trigger is a databaseobject.

Triggers cannot be updated. A table can have morethan one trigger.

All of the above statementsare correct.

2

Which of the following optionsis the correct syntax of ALTERTABLE command?

ALTER TABLE<table_name>ADD <column_name><datatype>

ALTER TABLE<table_name>ADD <column_name>

ALTER TABLE<table_name>ADD COLUMN<column_name><datatype>

ALTER TABLE<table_name>ADD COLUMN<column_name>

1

SELECT REPLACE(Unit, 'cent','centile')FROM Score;Which of the followingstatements is true with respectto the above query?

The query updates thefields of the column Unitonly if their value is equalto 'cent'.

If the value of the field in thecolumn Unit is 'Percent', thequery updates it to'Percentile'.

REPLACE is not a SQLfunction.


Which of the followingstatements is true with respectto query:SELECT *INTO Client_ReceiptFROM Receipt;

Output all the data of thetable Receipt.

Copy all the data of tableReceipt to tableClient_Receipt.

This query generate anerror.


Which of the followingstatements are true withrespect to Foreign KeyConstraint?a) Records in the master tablecannot be deleted until thecorresponding records in thereferenced table exist.b) Records in the referencedtable can be inserted even ifthe corresponding record inthe master table does notexist.

Only a Only b Both a and b None of the above 3

Which type of data integrity isensured by the use of aprimary key?

Entity Integrity Domain Integrity Referential Integrity Relational Integrity 1

An HR executive wants toknow the third largest salarydrawn by employees of hiscompany. Which query willretrieve the results desired byhim?

select max(salary)from employee e1where 3<= (select count(*)from employee e2where e1.salary <=e2.salary);

select max(salary)from employee e1where (select count(*)from employee e2where e1.salary <= e2.salary)== 3;

select max(salary)from employee e1where (selectdistinct(salary)from employee e2where e1.salary <=e2.salary)=3 ;

select max(salary)from employee e1where 3 <= (selectdistinct(salary)from employee e2where e1.salary <=e2.salary);

1

Find the odd one out: SELECT UPDATE INSERT INTO ALTER 4

Which of the following querieswill delete the duplicate rowsfrom the table Employee withcolumn empID as its primarykey?

DELETE employeewhere rowid NOT IN(SELECT min(rowid)from employee);

DELETE employeewhere rowid NOT IN (SELECTmin(rowid)from employeegroup by empID);

DELETE employeewhere rowid IN (SELECTmin(rowid)from employeegroup by empID);

DELETE employeewhere rowid IN (SELECTmin(rowid)from employee);

2

Which of the following is atype of trigger?i) Row Triggerii) Column Triggeriii) Statement Trigger

(i) and (ii) (i) and (iii) (ii) and (iii) (i), (ii) and (iii) 2

Which of these is used toreturn the number of rows in atable Automobile?

Select * from Automobile; Select Count(*) fromAutomobile;

Select RowCount fromAutomobile;

Select Count All fromAutomobile;

2

Table Employee andDepartment are joined withoutspecifiying any join conditions.What are the total number ofrows in the resulted table iftable Employee has 150 rowsand table Department has 30rows?

4500 180 450 None of the above 1

Which of the following is agroup function?

MIN SQRT ABS Both a and b 1

A column of a table can beassigned unique numbersusing:

Views Synonym Sequences Clusters 3


4 of 13



Which of the followingoperators is used to denoteouter join?

+ (+) -> * 2

A DBA wants to get entries fora specific attribute of a table tobe validated. Which type ofdata integrity will he enforceto fulfill this:

Entity Integrity Domain Integrity Referential Integrity None of the above 2

Which of the followingstatements is false:a) In a table, there can bemany unique constraints butonly one primary keyconstraint.b) An attribute with uniqueconstraint can have NULLvalues.

a b Both a and b None of the above 4

Which of the following optionsis true regarding indexes?

An indexing operation is anaccess strategy to searchrecords in the table.

The data in the index issorted.

Indexes can be defined asunique or duplicate.

All of the above. 4

Which of the followingstatements are correct aboutdata dictionary?

It contains the definition ofall the tables and view ofthe database.

It contains information aboutthe integrity constraint.

It contains informationabout the storage spaceoccupied by schemaobjects.

All of the above. 4

Which of the followingcommands is used to find thelength of the string?

LEN STRLEN STRLENGTH STR_LEN 1

Which line in the followingquery will generate an error:Line 1: SELECT Job, MAX(Salary) 'Max.Salary'Line 2: FROM StaffLine 3: WHERE Job='Manager'Line 4: GROUP BY Job;

Line 1 Line 3 Line 4 This query will executesuccessfully.

4

Which of the following queryoutputs the mean salary andnumber of employees workingin the department 10.

Select avg(Salary)'Avg.Salary', count(*) 'No.of Emp In Dept 10' fromstaff;

Select mean(Salary) ,count(*) as 'No. ofEmployees' from staff;

Select avg(Salary)'Avg.Salary', count(*) 'No.of Emp In Dept 10'from staffwhere Department =10;

Select mean(Salary) ,count(*) as 'No. ofEmployees' from staffwhere Department =10;

3

What is the output of thefollowing query:Select Count (*) from Staff s1,Staff s2, Staff s3

25 100 125 150 3

What is the output of thefollowing SQL query:Select Count (DistinctDepartment) as "No. ofDepartments"from Staff;

3 4 5 2 1

Which of the following SQLcommands delete theconstraint named"Check_EmpID" from table"Employee"?

Drop ConstraintCheck_EmpIDfrom Employee;

Alter table EmployeeDrop ConstraintCheck_EmpID;

Delete ConstraintCheck_EmpIDfrom Employee;

Delete from EmployeeConstraint Check_EmpID;

2

Which of the following SQLcommands select employeewith post starting from "m"with length of five characters?

Select * from tbl_employeewhere Post like ('m_ _ __');

Select * from tbl_employeewhere Post = 'm_ _ _ _';

Select * fromtbl_employee where Post= 'm 5%';

Select * from tbl_employeewhere Post Like 'm 5%';

Select * fromtbl_employeewhere Post ='m%%%%%';

1

Choose the incorrectstatement:While defining the data typeCHAR (n), _____.

The default value of "n", ifnot defined, is 1.

It is mandatory to define thevalue of "n" in the declaration.

"n" defines the number ofcharacters the cell canhold.


Constraint on tables can bedefined in which of thesestatements:

CREATE TABLE ALTER TABLE UPDATE TABLE Both a and b None of theabove

4

Which of these is not a type ofLOCK?

Read Lock Write Lock Update Lock All of the above 3

The granting of a right orprivilege which enables asubject to legitimately haveaccess to a system or asystem's object is known as:

Authorization Authentication Empowerment Dictum 1


5 of 13



What does the following queryresult into:REVOKE SelectON LoanDataFrom Neha;

Take all the privileges fromthe user Neha.

Take the privilege to viewrecords of the table LoanDatafrom Neha.

Take the privilege to useany SQL statements fromNeha.


What is the output of thefollowing query:Select Length (' ') from Dual

0 1 Null None of the above 3

What is the output of thefollowing SQL query:Select PhoneNo fromtbl_directorywhere NULL= NULL;

All the fields from columnPhoneNo.

Only those fields in thecolumn PhoneNo whose valueis NULL.

Only those fields in thecolumn PhoneNo whosevalue is NULL.

Nothing. 4

The output of the followingSQL query:Select Name, Agefrom tbl_studentwhere Age NOT BETWEEN 12AND 15;

Displays NULL values. Does not display NULL values. May display NULL values. None of the above. 2

Which of these statements isnot correct with respect toVIEW?

Columns of a view can berenamed.

A view can be created frommultiple tables.

Insert and Updateoperations are notallowed in a view.

Both b and c. 3

Which of the following optionsdoes not hold true withrespect to table CLIENT withdefinition:CREATE TABLE client( client_id varchar(6) UNIQUE,name varchar(20),address varchar(30),phoneno int(12) );

Client_id is the PRIMARYKEY of the table Client.

The contents of the columnclient_id is unique across theentire column.

The fields of client_id canhave NULL values.

All the statements are true. 1

ALTER SEQUENCEshipment_invoiceINCREMENT BY 4,START WITH 5;Which of the followingstatements is true with respectto the above query?

The start value of thesequence is reset to 5 andinterval between twonumbers will be 4.

This query will generate anerror.

The interval between twonumbers will be 4 startingfrom the fifth record.


An index created on morethan one column is called__________.

Composite Index Compound Index Multiple Index Complex Index 1

_________ are masks placedupon tables.

Views Sequences Tables Indexes 1

SELECT *' statement is used toretrieve all the attributes froma table while 'SELECT NOT *BUT FIRST' statement_______.

Retrieves no attributesfrom a table.

Retrieves partial attributesfrom a table.

Retrieves first attributefrom a table.

Generates an error. 4

The arguments of WHERESELECT FROM are:

A. Attribute, B. Table, C.Condition

A. Table, B. Condition, C.Attribute

A. Condition, B.Attributes, C. Table

A. Condition, B. Attribute, C.Server

3

Which command can be usedto drop a table?

DROP TABLE DROP SCHEMA DELETE TABLE None of the above 1

CREATE ASSERTION is a: DML statement DDL statement DCL statement TCL statement 2

SQL uses terms relation, tupleand attributes respectively for:

Column, Row, Table Row, Table, Column Table, Row, Column None of the above 3

Referential integrity constraintscan be set by:

UPDATE TABLE ALTER TABLE INSERT INTO All of the above 2

Formatted numbers can bedeclared by:

DECIMAL (i, j) NUMERIC (i,j) INT(i,j) Both 1 and 2 Both 2 and 3 4

DEC (i,j) is used to declareformatted numbers where iand j represent:

Precision and scale Scale and mode Mode and permission Permission and precision 1

Default length of a characterstring is:

1 2 4 8 1

Which of the followingoperations can violatereferential integrity constraint?

Insertion Deletion Updation All of the above 4

CASCADE and RESTRICT aretwo behavior options of _____command.

ALTER DROP JOIN VIEW 2

When are SELECT and SELECTDISTINCT same?

While viewing keyattributes.

While viewing non keyattributes.

They are always different. None of the above. 1


6 of 13



Which SQL function is used tocheck whether the result of anested query is empty or not?

FULL IS EMPTY IS NULL EXISTS 4

The default type of JOINoperation in a joined table is:

NATURAL JOIN CROSS JOIN RIGHT OUTER JOIN INNER JOIN 4

ASSERTIONS are: Operations Clauses Constraints Views 3

Which of the statements arenecessarily false about a VIEWin SQL context:a) A view does not necessarilyexist in physical form.b) A view supports updatequery.c) A view is derived fromsome base tables.d) A view can be storedphysically in memory.

a, b, c b, c a, d None of the above 4

View materialization is relatedwith:

Memory allocation Data abstraction Memory recovery View updation 1

Incremental update is relatedwith:

Updating user log. Updating database engine. Updating a view. None of the above. 3

GRANT and REVOKE are usedfor:

Database recovery Security and authorization Concurrency control Operation monitoring 3

Which of the following holdstrue with respect to CROSSJOIN?

It represents one to onerelation.

It is a default join. It is a cartesian product oftables.

Only 1 and 2. 3

When the nested query isevaluated once for each tuplein the outer query, the queriesare known as:

Dependent queries Linked queries Friendly queries Co-related queries 4

NULL is interpreted as: Value unknown. Value does not exist. Value not applicable. All of the above. 4

LIKE can be used as: An aggregate function toadd attributes of same datatype.

A grouping function to groupattributes of same data type.

A comparison operatingfunction used for stringpattern matching.

An ordering function to sortattributes of same length.

3

If an underscore "_" orpercentile "%" is needed as aliteral character, the charactershould be:

Included within doublequotes.

Succeeded by ESCAPEcharacter.

Included within singlequotes.

Preceded by an ESCAPEcharacter.

4

CREATE TABLE EMP (EMPID INT PRIMARY KEY,EMPNAME VARCHAR(15),DOB DATE,DNUM INT,FOREIGN KEY(DNUM)REFERENCES DEPT(DNO),PRIMARY KEY(EMPID) );If the above SQL command isexecuted, the output will be:

Table will be createdsuccessfully.

Error. Entity integrity constraintviolated.

Not null constraint violated. 2

Which of the following optionscannot be performed usingALTER TABLE command?

Dropping a column of thetable.

Adding new columns. Changing the data type ofexisting column.

All of the above can beperformed by this command.

2

SELECT DISTINCT EMPID,EMPNAMEFROM EMPLOYEEWHERE DEPT='CSE' ANDSAL>10000;If the above query is executed,the output will be:

All distinct employee id'sand employee nameswhose department=cse andsalary>10000 will beretrieved.

All employee id's andemployee names whosedepartment=cse andsalary>10000 will not beretrieved.

Only one employee idand employee namewhose department=cseand salary>10000 willretrieved.


SELECT EMPID,EMPNAMEFROM EMPLOYEEWHERE SALARY>=10000GROUP BY DNOHAVING DNO>=3;What is the output on theabove query if executed on thefollowing table Employee.

0 records 5 records 7 records 6 records 1

SELECT EMPIDFROM EMPLOYEEWHERE SALARY>10000UNIONSELECT STUDENTIDFROM STUDENTWHERE MARKS>=70;If the query is executed, the

EMPID and STUDENTIDwhose SALARY>10000and MARKS>70 will bedisplayed.

UNION needs a condition tobe specified.

Constraint error. Syntax error. 1


7 of 13



output will be:

SELECT EMPIDFROM EMPLOYEEWHERE POSITION IN(SELECT POSITION FROMEMPLOYEE WHERE SALARY >5000);What is the output on theabove query if executed on thefollowing table Employee.

5 records 6 records 3 records 4 records 3

SELECT EMPIDFROM EMPLOYEEWHERE SALARY NOTBETWEEN 10000 AND 15000;What is the output on theabove query if executed on thefollowing table Employee.

4 records 1 records 2 records none of the above 1

SELECT EMPIDFROM EMPLOYEEWHERE EXISTS (SELECT *FROM DEPARTMENT WHEREDEPT='SQL');What is the output on theabove query if executed on thefollowing table Employee.

1 record 0 records 2 records 1 or 2 records 2

Grouping in SQL: Groups the recordstogether.

Groups the columns together. Groups the tablestogether.

All of the above. 1

SELECT BOOKID,AVG(PRICE)FROM CATALOGWHERE PRICE IN (SELECTPRICE FROM CATALOGWHERE BOOKID=12);What is the output on theabove query if executed on thefollowing table Catalog.

0 records 1 record 3 records 2 records 1

SELECTD.NAME,E.FNAME,A.CITYFROM EMP E,DEPARTMENT D,ADDRESS AWHERE D.DNUM=E.DNUMANDA.ADDR_ID=D.ADDR_IDORDER BY D.NAME,E.FNAME;The output of the query is:

List of departments withcorresponding employeesand city in which thedepartment is located.

List of departments withcorresponding employees anddepartment names.

Error List of department names,employee names and the cityin which the employeeresides.

1

SELECTD.NAME,E.FNAME,A.CITYFROM EMP E,DEPARTMENTD,ADDRESS AWHERE D.DNUM(+)=E.DNUMANDA.ADDR_ID=D.ADDR_IDORDER BY D.NAME,E.FNAME;The above type of query isknown as:

equi join self join inner join outer join 4

Which of the following bestdescribes a correlatedsubquery?

A subquery executed oncefor the parent query.

A subquery executed morethan once for the parentquery.

A subquery executed foreach row of the parentquery.

A subquery executed for eachcolumn of the parent query.

3

SELECT D.NAME,E.FIRST_NAME,E.LAST_NAME, E.JOB_TITLEFROM EMP E, DEPT DWHERE (E.DEPT_NO =D.DEPT_NOAND D.ADRS_ID = (SELECTADRS_ID FROM ADDRESSESWHERE ADRS_ID =D.ADRS_ID));The type of subquery in theabove query is:

Nested subquery Correlated subquery Non correlated subquery Conditional subquery 2

Pick the odd one out of thefollowing operators:

' -, X, U, ? ' ' -, +, / ' ' -, ?, X, / ' ' -, X, U, + ' None of theabove

1

In SQL, the operator used inINSERT command to entermultiple input data at one timeis:

$ & / All are correct 2 1


8 of 13



Which of the following SQLstatements is/are used to copyone table to another table?

CREATE TABLEEMPLOYEE11(SELECT *FROM EMP12)

CREATE TABLEEMPLOYEE11(INSERT INTOEMPLOYEE11 '1',XYZ)

SELECT EID FROMEMPLOYEE12 WHERE IN(CREATE TABLEEMPLOYEE11(EMPIDNUMBER(5),ENAMEVARCHAR2(10));

Both 1 and 2 1

Which of the following iscorrect regarding UPDATEcommand?

UPDATE BRANCH='C++'FROM EMP WHERESAL>1000

UPDATE EMP SETBRANCH='C++' WHERESAL>1000

UPDATE SETBRANCH='C++' FROMEMP AND SAL>1000

UPDATE BRANCH='C++'FROM EMP AND SAL>1000

2

What is the keyword used forcolumn check?

verify validate check checking 3

Which of the followingstatements hold true for EntityIntegrity constraint?

Primary key and foreignkey must be same.

Primary key is not null. Primary key is not nulland foreign key refers toprimary key.

All of the above. 2

The correct syntax ofTRUNCATE command is:

TRUNCATE TABLE<TABLE_NAME>

TRUNCATE<TABLE_NAME>

TRUNCATE TABLES TRUNCATE TABLE<FIELD_NAME>

1

CREATE TABLE STUDENT(SID VARCHAR2(15),BRANCH VARCHAR2(5),SEM INT,CONSTRAINT CSEMCHECK(SEM>0 AND SEM<8));In the above query, the CSEMis:

Domain constraint Assertion Check constraint oncolumn

Check constraint on row 3

Updating a VIEW is: Simple and not ambiguous. Complicated but notambiguous.

Complicated andambiguous.

Simple and ambiguous. 3

A transaction is said to becommitted if:

All operations are recordedpermanently for thesession.

All operations are recordedtemporarily for the session.

All operations arerecorded permanently forthe database.

All operations are recordedtemporarily for the database.

3

Which one of the followingoptions is invalid with respectto assigning values tovariables in PL/SQL?

By using the operator := By using the SELECT INTOcommand for assigning thedatabase returned values intothe variable.

By passing the variable asan OUT or IN OUTparameter to asubprogram, and doingthe assignment inside thesubprogram.

By using equality operator(=).

4

Jeff is working as a DatabaseAdministrator in ExcellSolutions. He wants to declarea variable named top_rankthat has the same datatype ascolumn rank, in the tablenamed students. Identify thedeclarations can help himaccomplish his task.

top_rankstudents.rank%TYPE;

top_rank rank%Type; top_rank %Typestudents.rank;

top_rank %Type rank; 1

Which of the following is not atype of array defined inPL/SQL?1) Varray (variable-size array)2) Nested Table3) Associative arrays4) Sarray (fixed-length array)

Both 1 and 2 Both 2 and 3 Both 3 and 4 Only 4 Only 1 4

Which of the following is avalid variable name?

roll-number roll&numbers roll$$number roll number 3

Which of the following is not areserved keyword?

ORGANIZATION HOURLY SERIALIZABLE LENGTH 2

Which of the followingstatements are true?1) Comparisons involving nullsalways yield NULL.2) Applying the logicaloperator NOT to a null yieldsNULL.3) In conditional controlstatements, if the conditionyields NULL, its associatedsequence of statements is notexecuted.

Only 1 Only 3 Both 2 and 3 All of the above 4

What is the result of thefollowing statement?'PL'||NULL||NULL||'SQL'

PL SQL PLSQL NULL Error 2

What is the value of a, afterexecution of the followingcode snippet?Declare

NULL 50 5 Code will generate an error 3


9 of 13



c number := 10; a number :=5; b number := null;Beginif a > b AND a < c thena := c * a;end if;End;

Which of the followingstatements is/are true?1) A GOTO statement cannotbranch from one IF statementclause to another, or from oneCASE statement of WHENclause to another.2) A GOTO statement cannotbranch from an outer blockinto a sub-block (that is, aninner BEGIN-END block).3) A GOTO statement canbranch out of a subprogram.

Both 1 and 2 Both 2 and 3 Only 3 Only 1 1

Identify the position where theuse of record variables isprohibited?

On the right side of theSET clause in an UPDATEstatement.

In the VALUES clause of anINSERT statement.

In the INTO subclause ofa RETURNING clause.

In a SELECT list, WHEREclause, GROUP BY clause, orORDER BY clause.

4

Which of the following is notan attribute of an implicitcursor?

%ROWCOUNT %ISOPEN %BULK_ROWCOUNT %NOTFOUND 3

Which of the followingstatements is true aboutcursors?

We can pass cursorvariables to a procedurethat is called through adatabase link.

We cannot use comparisonoperators to test cursorvariables for equality,inequality, or nullity.

We can assign nulls to acursor variable.

We can store cursor variablesin an associative array.

2

Which of the followingcomment style is notsupported by PL/SQL ?

-- /* */ // All of them are supported byPL/SQL

3

Which of the followingdeclarations is invalid inPL/SQL?

num1 number(3,2) :=321.56;

num1 number(3) := 12; num1 number(3); num1 number(3,2) :=321.567;

4

What will be the output of thefollowing code fragment forn= 5?Declarei number:= 2;f number:= 2;n number:= &n;Beginfor i in 1..n loopf:= f*i+2;dbms_output.put_line(f||' '||i);end loop;End;

4 110 232 3130 4652 5

6 220 382 4412 5

6 220 382 4412 52474 6

None of the above 1

Which command is used toencrypt PL/SQL application?

ENCRYPT WRAP DB_ENCRYPT CLOSE 2

How many different types oftriggers a table can have inPL/SQL?

12 7 3 9 1

In PL/SQL, which of thefollowing will declare a recordwith all of its fields based onthe columns of a table?

table_namerecord_name%ROWTYPE;

record_nametable_name%ROWTYPE;

record_nameROWTYPE%table_name;

table_nameROWTYPE%record_name;

2

Which of the following is not acursor attribute?

%FOUND %NOTFOUND %CHANGE %ISOPEN 3

Which of the following line(s)in the code will generate anerror?1. DECLARE2. var1 CONSTANT NUMBER:= 50;3. var2 NUMBER := 0;4. BEGIN5. SELECT acctno INTO var26. FROM bank_acct7. WHERE (name = ='JORDAN');8. var1 :=var2 + 2000;9. END;

2 5 7 3 3


10 of 13



Which of the following is acorrect syntax of a cursor forloop?

DECLARECURSOR my_employees ISSELECT * FROM employee;my_name VARCHAR2(30);my_title VARCHAR2(30);BEGINOPEN my_employees;FOR csr_rec INmy_employeesLOOPINSERT INTO my_emps(my_empname,my_emptitle)VALUES(my_name,my_title);END LOOP;CLOSE my_employees;END;

DECLARECURSOR my_employees ISSELECT * FROM employee;csr_rec VARCHAR2(30);BEGINFOR csr_rec INmy_employeesLOOPEXIT WHENmy_employees%NOTFOUND;INSERT INTO my_emps(my_empname, my_emptitle)VALUES(csr_rec.name,csr_rec.title);END LOOP;END;

DECLARECURSOR my_employeesISSELECT name, title FROMemployee;BEGINFOR csr_rec INmy_employeesLOOPINSERT INTO my_emps(my_empname,my_emptitle)VALUES(csr_rec.name,csr_rec.title);END LOOP;END;

DECLARECURSOR my_employees ISSELECT name, title FROMemployee;my_name VARCHAR2(30);my_title VARCHAR2(30);BEGINOPEN my_employees;LOOPFETCH my_employees INTOmy_name,my_title;EXIT WHENmy_employees%NOTFOUND;INSERT INTO my_emps(my_empname, my_emptitle)VALUES(my_name, my_title);END LOOP;CLOSE my_employees;END;

2

What will be the value of var2after execution of thefollowing code?Declarevar1 number := null;var2 number := 5;Begingoto << myLabel>>var2 := var2 * var2;if var1 is null then<< myLabel>>var2 := var2 + 5;end if;End;

10 5 Error 25 3

Which of the following is notan event in triggers?

Insert Update Drop Delete 3

Which of the following optionsis/are correct with resect togiven statements?1. DDL can be used in trigger.2. A trigger name can havethe same name as that of thebase class.3. Triggers have to beexecuted explicitly wheneverthe triggering events happens.

Only 1 Both 2 and 3 Both 1 and 2 Only 3 3

Which of the following lineswill generate an error?1. CREATE OR REPLACETRIGGER check_age2. BEFORE INSERT ORUPDATE ON employee FOREACH ROW3. IS4. years_old NUMBER;5. BEGIN6. NULL;7. END;

4 6 2 1 3

Which of the followingstatements is/are correct?1. %ROWTYPE allows you toassociate a variable with anentire table row.2. %TYPE associates avariable with a single columntype.3. NOT NULL constraint mustbe followed by an initializationclause.

Only 1 Both 2 and 3 Both 1 and 2 All 1,2 and 3 4

Which of the followingstatements is not true?a) "IN OUT" parameters canbe used to input and returnsame variable by a function.b) A procedure must alwaysreturn a value.c) DROP command is used todelete a procedure or function.

Both a and b Both a and c Only b Only c all thestatementsare false

1


11 of 13



Complete the following codesuch that it will update thesalaries of all the employees inthe table to 1000?DECLAREPROCEDURE set_column(column_in IN VARCHAR2)ISBEGIN__________________END set_column;

BEGINset_column ('salary');END;

EXECUTE IMMEDIATE'UPDATE employees SETcolumn_in'USING 1000;

EXECUTE IMMEDIATE'UPDATE employees SET:column_name = :newsal'USING column_in, 1000;

UPDATE employees SETcolumn_in = 1000

EXECUTE IMMEDIATE'UPDATE employees SET ' ||column_in || ' = :newsal'USING 1000;

4

Which of the following optionsis correct with respect to thegiven statement?CREATE OR REPLACEPROCEDURE updateStatus(cID IN NUMBER, status OUTVARCHAR(5))

Parameter status is passedby reference and ParametercID is passed by reference

Parameter status is passed byvalue and Parameter cID ispassed by reference

Parameter status ispassed by value andParameter cID is passedby value.

Parameter status is passed byreference and Parameter cIDis passed by value

2

In PL/SQL, a variable declaredinside a _________ has aglobal scope.

Procedure Function Block Package 4

What is a POST-BLOCKtrigger?

Transactional trigger Key trigger Navigational trigger None of the above 3

Identify the event when a setof Dictionary tables arecreated.

Once for the entiredatabase

Ever time a user is created Every time a tablespace iscreated.

None of the above 1

Which of the following is true,with respect to the size oftablespace?

It can be increased byincreasing the size of oneof the Datafiles.

It can be increased by addingone or more Datafiles.

It cannot be increased None of the above 2

Which of the following queriescannot be executed on tablesin PL/SQL?

Create Select Into Update Delete 1

Which of the following is not aproperty of stored Procedure?

Sequence of SQL orPL/SQL statements toperform specific function.

It can be called from all clientenvironments.

Stored in compiled formin the database.

It should always return avalue.

4

Which of the following is notcorrect about Cursor?

Cursor holds temporaryresults.

Cursor is used for retrievingmultiple rows.

Cursor is a named PrivateSQL area.

SQL uses implicit Cursors toretrieve rows.

1

Which of the following is not aproperty of a cursor for loop?

Opening and parsing ofSQL statements.

Requires exit condition to bedefined.

Fetches records fromcursor.

Record type declaration. 4

Which of the followingcommands can be used tocheck if a cursor is open ornot?

%ISOPEN %ISFOUND %ISCURSOR %ISCURSOROPEN 1

Which of the followingstatements holds true after theexecution of the followingcode segment?1. CREATE FUNCTIONmyCompanyDetails2. IS3. phNumber INT(20);4. BEGIN5. SELECT contactNo INTOphNumber6. FROM companyDetails7. WHERE companyID = 50;8. UPDATE contactDetails9 SET phNo := phNumber10. WHERE cID = 100;11. END;

It will update the Phonenumber in the tablecontactDetails with respectto company id 100.

It will generate an errorbecause function is notreturning any value.

It will generate an errordue to wrong variableassignment in line 5.

It will generate an error inline number 1.

2

What value will be assigned toissue_cheque andblank_cheque, if bonus =TRUE and raise = NULL forthe following procedure?

TRUE and FALSE FALSE AND TRUE NULL AND FALSE TRUE and NULL 1

Which of the following isexecuted automatically?

Function Procedure Anonymous PL/SQL block Trigger 4

What will be the value of resultin the following PL/SQL block?

TRUE FALSE NULL 1 2


12 of 13



Which of the followingfunction is used to convert adate value into text string?

CONVERT TO_NUMBER TO_CHAR TO_STRINGS 3

Identify the option which isused to rewrite the declarationblock to reduce the number ofexplicit cursors in thefollowing PL/SQL block?DECLARECURSOR EMP_1 IS SELECT *FROM EMP WHERE EMPID ='40593';CURSOR EMP_2 IS SELECT *FROM EMP WHERE EMPID ='50694';BEGIN...

Using cursor for loops Using %rowtype Using %notfound Passing EMPID values asparameters to the cursor

4

Identify the error in thePL/SQL code block.

VAR1 := VAR2 + 3049; VAR2 NUMBER := 0 SELECT ACCTNO INTOVAR2 FROM BANK_ACCTWHERE NAME = 'LEWIS';

NO ERROR 1

A programmer has written aPL/SQL code block which willfetch the CARTON values fromtable MILK and insert theminto table MY_MILK_CRATE.What will happen when he willexecute the given code block?

It will generate an error asthere should be cursor forloop instead of LOOPstatement.

It will generate an errorbecause of invalidMY_CARTON declaration.

The code will go intoinfinite loop.

It will perform the desiredoperation successfully.

4

Which of the following is notstored within the Oracledatabase for reusability?

Function Anonymous blocks Procedure Package Specs 2

Identify the error in thefollowing PL/SQL block?

LINE 1 LINE 2 LINE 3 LINE 4 1

Five rows of the table vehicleare updated using an updatequery. What is the number ofrows inserted into tablemodify_log?

5 1 A value equal to thenumber of rows in theVEHICLE table.

None 2

;


13 of 13

amcat test

Documents