Wipro Training Materials
Q1> Using which of the following the strucutre of a database can be manipulated
1>Data Definition Language (DDL)
2>Data Control Language (DCL)
3>Data Manipulation Language (DML)
4>Transaction Control Language (TCL)
------------------------------------------------------------------------------------------------------------
Q2> The host language contains which of the following SQL strucutre
1>SQL-92
2>Processor command
3>Embedded SQL
4>Host-language declaration2
------------------------------------------------------------------------------------------------------------
Q3> Which of the following GROUP BY Query results in an error when executed
1>select deptno , job, sum(HRA) from emp group by job, deptno ;
2>select sum(HRA) , deptno , job from emp group by job ,deptno ;
3>select deptno , job, sum(HRA ) from emp group by job;
4>select deptno , sum(HRA) , job from group by job , deptno
---------------------------------------------------------------------------------------------------------
Q4> Which pseudo-column name is often utilized for obtaining information for top-N queries ?
1>row id
2>rownum
3>a & b
4>None of the above
------------------------------------------------------------------------------------------------------------
Q5> ______ and _______ are valid wildcard characters
1>& and *
2>% and _
3>|| and #
4>None of the above
------------------------------------------------------------------------------------------------------------
Q6> Which of the following SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
1>SELECT ename, salary*12 'Annual Salary' FROM employees;
2>SELECT ename, salary*12 "Annual Salary" FROM employees;
3>SELECT ename, salary*12 AS Annual Salary FROM employees;
4>SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees
Q7> For which two constraints does the Oracle Server implicitly create a unique index? (Choose two)
1>NOT NULL
2>PRIMARY KEY
3>FOREIGN KEY
4>CHECK
5>UNIQUE
----------------------------------------------------------------------------------------------------------
Q8> Which clause allows the deletion of child records if the parent records are deleted
1>on delete cascade
2>drop constraint constraint_name cascade
3>cascade constraints
4>None of the above
----------------------------------------------------------------------------------------------------------
Q9> Which of the following is not a conversion function
1>To_char
2>To_date
3>To_Initstring
4>To_number
----------------------------------------------------------------------------------------------------------
Q10> SELECT id_number, (product_quantity+100*19-19-1) FROM Inventory; What is evaluated 1st in the above query?
1>19-19
2>100*19
3>19-1
4>product_quantity+100
----------------------------------------------------------------------------------------------------------
Q11> A primary key made up of more than one attribute is known as ___________ key
1>Foreign
2>Composite
3>Unique
4>Candidate
---------------------------------------------------------------------------------------------------------
Q12> Which of the following is not a valid SQL command ?
1>select * from dual ;
2>set define empname
3>update emp set empno = 6543 where ename = 'SMITHERS';
4>create table employees (empid varchar2(10) primary key) ;
----------------------------------------------------------------------------------------------------------
Q13> You are setting up a join operation between tables EMPLOYEE and DEPT. There are some employees in the EMPLOYEE table that you wants return by the query,but the employees are not assigned to department yet. Which of the following
select statement is most appropriate?
1>select e.empid,d.dname from employee e,dept d where e.deptno=d.deptno (+);
2>select e.empid,d.dname from employee e,dept d where e.deptno=d.deptno ;
3>select e.empid,d.dname from employee e,dept d ;
4>select e.empid,d.dname from employee e, dept d where e.deptno (+)=d.deptno ;
----------------------------------------------------------------------------------------------------------
Q14> Which of the following statement(s) are not true about order by clauses?
1>the ascending or descending order can be defined with the asc or desc keyword.
2>columns can be represented by numbers indicating their listed order in the
select clause with in order by
3>multiple columns can be used to define sort order in an order by clause.
4>only one column can be used to define the sort order in an order by clause
---------------------------------------------------------------------------------------------------------
Q15> select student_id,topic,marks where topic='Oracle'; Which one of the
following statements when included in the above statement makes it valid query
1>a where clause
2>a select clause
3>a from clause
4>an order by clause
----------------------------------------------------------------------------------------------------------
Q16> You want to join the data from two tables, X and Y, into one result set and display. Table X and Y have a common column, called Z in both tables. Which of the following choices correctly displays the where clause you should use if you want to see the table in table A where the value in column Z equals 10, even when no corresponding value appears in table Y?
1>where X.Z=10 AND X.Z (+)=Y.Z(+)
2>where A.Z=10 AND A.C=B.C
3>where X.Z=10 AND X.Z=Y.Z(+)
4>where X.Z=10
----------------------------------------------------------------------------------------------------------
Q17> You are formulating a SQL statement to retrieve data from oracle database. Which of the following SQL statement is invalid?
1>select name,topic from course
2>select name,topic from course where empno=101
3>select * course where empno=101
4>select empno from course
---------------------------------------------------------------------------------------------------------- Q18> Table BOOKS NAME QTYt;-----------;JAVA 800 ;ORACLE 1600 ;C++) SELECT * FROM BOOKS WHERE NAME = NULL)
SELECT * FROM BOOKS WHERE NAME IS NULL)
SELECT * FROM BOOKS WHERE NAME != NULL)
SELECT * FROM BOOKS WHERE NAME IS NOT NULL
;Which one of the above queries will return data associated with 'C++'?
1>a
2>b
3>c
4>d
5>a and b
-----------------------------------------------------------------------------------------------------------
Q19> The database administrator of your company created a public synonym Projects for Onshore_Clients table, because many users frequently use this table. As a user of the database, you created a table called Projects, what happens when you execute this query? select * from Projects;
1>you obtain the results retrieved from the public synonym projects created by the database administrators
2>you obtain the results retrieved from the projects table created by you
3>you get an error message because you cannot retrieve from a table that has the same name as a public synonym
-----------------------------------------------------------------------------------------------------------
Q 20) Use the following Data values to answer
STUDENTNO STUDENTNAME PROJECTID
1001 ANU 101
1002 GUPTA 102
1003 BALIGA 101
1004 DESILVA
1005 AGARKAR 103
1006 SHIVANI 104
select studentno,studentname,nvl(projectid,'none') from emp ;which of the following choices describes what oracle will return as the output in the projectid column for DESILVA record from this query?
1>oracle returns NULL in the projectid column for DESILVA record
2>oracle returns projectid in the projectid column for DESILVA record
3>oracle returns NONE in the projectid column for DESILVA record
4>oracle returns and error
----------------------------------------------------------------------------------------------------------
Q 21> Output from a table called projects with two columns, project_desc and Project_manager, is shown next. Which of the following SQL statements produce the following output;PROJECT DETAILS MUMBAI STOCK EXCHANGE SYSTEM BASU COCHIN PORT SYSTEM SHANKARAN NAIR ASIAN SPORTS MANAGEMENT SYSTEM VANI GANAPATI
1>SELECT PROJECT_DESC||PROJECT_MANAGER FROM PROJECTS;
2>SELECT PROJECT_DESC,PROJECT_MANAGER FROM PROJECTS;
3>SEL ECT PROJECT_DESC||', '||PROJECT_MANAGER FROM PROJECTS;
4>SELECT PROJECT_DESC||', '||PROJECT_MANAGER PROJECT_DETAILS FROM PROJECTS;
---------------------------------------------------------------------------------------------------------
Q 22> You want to obtain data from the COURSE table, which contains three columns: TRAINER,COURSEDATE, AND NODAYS. Which of the following choices identifies how you would formulate the where clause in a query against the COURSE table when you want to see number of days of training delivery for trainer SHENTANU that exceeds 8?
1>select TRAINER,COURSEDATE, NODAYS from course where trainer='SHENTANU';
2>select TRAINER,COURSEDATE, NODAYS from course where trainer='SHENTANU' AND nodays>8;
3>select TRAINER,COURSEDATE, NODAYS from course where trainer='SHENTANU' OR nodays>8;
4>select TRAINER,COURSEDATE, NODAYS from course where trainer='SHENTANU' OR nodays<8;
<CORRECT_ANSWER>2
----------------------------------------------------------------------------------------------------------------
Q 23> You want to find the number of characters present in the NAME column of EMP table, Examine the below given data and answer the following: NAME VALUE --------- ------------ AMAR 4 JOHN 4 MADHU 5 ANU 3 SINGH 5 RANJAN 6 Which of the following statement produce the above result?
1>select name,len(name) as value from emp;
2>select name,length(name) as value from emp;
3>select name,length(name) from emp;
4>select name,len(name) from emp;
<CORRECT_ANSWER>2
----------------------------------------------------------------------------------------------------------------
<Q 24> Examine the below given data and answer: You want three characters, beginning from the second character to extract from a longer text string: PROJECT_DETAILS SUB --------------- ------- FINANCIAL ACCOUNTING INA HUMAN RESOURCE UMA ESTIMATION STI LIBRARY MANAGEMENT IBR Which of the following statement will give the above result?</QUESTION_DESC>
1>select project_details,str(project_details,2,3) from projects;
2>select project_details,substring(project_details,2,3) from projects;
3>select project_details,substr(project_details,2,3) from projects;
4>select project_details,mid(project_details,2,3) from projects;
<CORRECT_ANSWER>3
----------------------------------------------------------------------------------------------------------------
<Q 25> Examine the Data and Answer: JOINDATE REVIEWDATE ---------- --------------------- 23-MAY-2006 21-AUG-2006 22-MAY-2006 20-AUG-2006 18-JUL-2006 16-OCT-2006 26-FEB-2007 24-JUN-2007 Oracle stores date information internally as a number, so you can perform arithmetic on dates. You can add numbers to or subtract numbers from a date in order to determine a new date value. You want to determine the 90 day performance evaluation should take place based on the employee join date. Which of the following select statement is most appropriate?
1>SELECT SYSDATE+90 AS REVIEWDATE FROM EMP;
2>SELECT JOINDATE+90 AS REVIEWDATE FROM EMP;
3>SELECT JOINDATE+90 FROM EMP;
4>SELECT SYSDATE+90 FROM EMP;
<CORRECT_ANSWER>2
----------------------------------------------------------------------------------------------------------------
<Q 26>Examine the given data and answer: JOINDATE Weeks at Work -------------- ---------------------- 23-MAY-2006 -42.57 22-MAY-2006 -42.71 18-JUL-2006 -34.57 26-MAR-2007 1.28 17-MAR-2007 0 You want to determine how many weeks on the job a particular employee had with the company. Which of following statement is most appropriate?
1>SELECT JOINDATE,(JOINDATE+SYSDATE)/7 AS "Weeks at Work" from emp;
2>SELECT JOINDATE,(JOINDATE-SYSDATE)/7 AS "Weeks at Work" from emp;
3>SELECT JOINDATE,(JOINDATE+SYSDATE)/7 from emp;
4>SELECT JOINDATE,(JOINDATE+7) AS "Weeks at Work" from emp;
<CORRECT_ANSWER>2
----------------------------------------------------------------------------------------------------------------
<Q 27> You added a PAN number column of VARCHAR2(10) data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 50 employees. Now, you want to enter the PAN numbers of each of the 50 employees into the tables. Some of the employees may not have a PAN number available. Which data manipulation operation do you perform?
1>MERGE
2>INSERT
3>UPDATE
4>You cannot enter the PAN numbers for the existing employee records
<CORRECT_ANSWER>3
----------------------------------------------------------------------------------------------------------------
<Q 28> Which of the following SELECT statement will produce the result 'ello World' from the string 'Hello World'?
1>SELECT SUBSTR('Hello World') FROM dual;
2>SELECT LOWER(TRIM('H' FROM 'Hello World')) FROM dual;
3>SELECT LOWER(SUBSTR('Hello World',2,1) FROM dual;
4>SELECT INITCAP(TRIM('Hello World',1,1)) FROM dual;
<CORRECT_ANSWER>2
----------------------------------------------------------------------------------------------------------------
<Q 29>Examine the data and answer: NAME LOCATION_NO SAL --------- ----------- ------------ MADHU 30 12500 AMALA 30 16000 AKBAR 20 11000 JOHN 20 29750 FAROOQ 20 30000 SACHIN 20 30000 MILAN 10 13000 CHETAN 10 24500 KOMAL 10 50000 What will be the result of the query SELECT MAX(SAL),MIN(SAL) FROM EMP;
1>50000 AND 11000
2>12500 AND 50000
3>30 AND 10
<CORRECT_ANSWER>1
----------------------------------------------------------------------------------------------------------------
<Q 30> You issue a select statement on the customer_acct table containing the order by clause. Which of the following uses of the order by clause would produce an error?
1>order by acctno desc;
2>order by 1;
3>order by sqrt(1);
4>order by custno asc;
<CORRECT_ANSWER>3
----------------------------------------------------------------------------------------------------------------
<Q 31> You issue the following statement: select round(45.256,2) from dual; what result will this statement display?
1>45
2>45.25
3>45.256
4>45.26
<CORRECT_ANSWER>4
----------------------------------------------------------------------------------------------------------------
<Q 32>You create a table but then subsequently realize you need a few additional columns. To add those new columns later, you should issue which of the following statements?
1>create or replace table
2>alter table
3>create table
4>truncate table
<CORRECT_ANSWER>2
----------------------------------------------------------------------------------------------------------------
<Q 33>The Student_Marks table has four columns:StudentNo,TestNo,Marks and TestDate. The primary key is studentno. You want to add new records daily through view. The view will be created using following Code block: CREATE VIEW MARKS_VIEW AS SELECT STUDENTNO,TESTNO,MARKS,TESTDATE FROM STUDENT_MARKS WHERE TESTDATE=SYSDATE ORDER BY STUDENTNO; What happens when you try to create the previous view?
1>oracle returns error stating that the order by clause is not permitted on views
2>Oracle returns error check option required
3>oracle creates the view successfully
<CORRECT_ANSWER>3
----------------------------------------------------------------------------------------------------------------
<Q 34>In a claims application, you are searching for employee information in the emp table corresponding to a claim number you have. The claim table contains empno, the primary key for emp table. Which of the following options is appropriate for obtaining data from EMP using claim number?
1>select * from emp where empno=&empno;
2>select * from emp where empno=1001;
3>select * from emp where empno=(select empno from claims where claimno=1456);
4>select * from emp;
<CORRECT_ANSWER>3
----------------------------------------------------------------------------------------------------------------
<Q 35> select name,age,location from employee where (location,age) in(select location,max(age)) from employee group by location); Which of the following choices identifies both the type of query and the expected result from database?
1>single row subquery, the eldest employee from a location
2>Multiple row subquery, the eldest employee from all locations
3>multiple column subquery, the eldest employee from all locations
4>multiple column subquery, oracle will return an error because = should replace IN
<CORRECT_ANSWER>3
----------------------------------------------------------------------------------------------------------------
<Q 36>The contents of the EMPLOYEE table are listed as follows: empno name age location ------ ---------- -------- ------------- 1001 ANU 25 BANGALORE 1002 GUPTA 26 MUMBAI 1003 BALIGA 24 CHENNAI 1004 DESILVA 24 DELHI 1005 AGARKAR 28 PUNE 1006 SHIVANI 25 You issue the following query against this table data: select name from employee where (location,age) in (select location,min(age) from employee group by location); Which of the following contestants will not be listed among the output?
1>ANU
2>BALIGA
3>SHIVANI
4>AGARKAR
<CORRECT_ANSWER>3
----------------------------------------------------------------------------------------------------------------
<Q 37> Examine the STUDENT table data and answer: STUDENTID STUDENTNAME SAL ----------------- ------------------------ -------------- 1001 AMAR 2000 1002 AKBAR 3000 1003 VARGISH 2800 1005 KULKARNI 5000 1006 KALYAN 3000 Which of the following choices identifies the value that would be returned from the following query : select count(studentname) from student where SAL=5000-2000;?</QUESTION_DESC>
1>Four
2>Three
3>One
4>Two
<CORRECT_ANSWER>4
----------------------------------------------------------------------------------------------------------------
<Q 38>Examine the STUDENT table data and answer: STUDENTID STUDENTNAME DESIGNATION --------- ----------- ------------ 1001 AMAR CLERK 1002 AKBAR IT-ENGINEER 1003 VARGISH IT-ENGINEER 1005 KULKARNI MANAGER 1006 KALYAN IT-ENGINEER Which of the following choices identifies the third employee listed from the top of the output from the following SQL command: select studentid,studentname from student where designation='IT-ENGINEER' order by 2 asc?
1>AMAR
2>AKBAR
3>VARGISH
4>KALYAN
<CORRECT_ANSWER>3
----------------------------------------------------------------------------------------------------------------
<Q 39> Examine the STUDENT table data and answer: STUDENTID STUDENTNAME DESIGNATION ----------------- ------------------------ -------------- 1001 AMAR CLERK 1002 AKBAR IT-ENGINEER 1003 VARGISH IT-ENGINEER 1005 KULKARNI MANAGER 1006 KALYAN IT-ENGINEER Which of the following choices identifies the value returned by oracle when you issue the following query: select * from student where studentid>=1005? (choose two)
1>AMAR
2>KULKARNI
3>VARGISH
4>KALYAN
<CORRECT_ANSWER>2,4
----------------------------------------------------------------------------------------------------------------
<Q 40>A table called Course contains two columns: CourseNo, defined as a number(10) datatype, and coursetopic , defined as varchar2(20) datatype. You issue the following statement on oracle: insert into course(courseno,courseopic) values(null,'ORACLE'). You then issue the following query against that table: select nvl(courseno,'EMPTY') as courseno from course where coursetopic='ORACLE';. Which of the following choices correctly identifies the result?
1>oracle returns zero
2>oracle returns EMPTY
3>oracle returns NULL
4>oracle returns an error
<CORRECT_ANSWER>4
----------------------------------------------------------------------------------------------------------------
0 comments:
Post a Comment