Grand Test SQL

40 Questions | Total Attempts: 994

SettingsSettingsSettings
SQL Quizzes & Trivia

This is a grand SQL test


Questions and Answers
  • 1. 
    ) You issue the following select statement in Oracle: SQL> select e.empno, e.ename, d.loc from emp e, dept d   where e.deptno = d.deptno   and substr(e.ename,1,1) = 'S';Which of the following statements identifies an ANSI-compliant equivalent statement usable on the Oracle database?
    • A. 

      Select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S';

    • B. 

      Select empno, ename, loc from emp, dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S';

    • C. 

      Select empno, ename, loc from emp join dept where emp.deptno = dept.deptno and substr(emp.ename,1,1) = 'S';

    • D. 

      Select empno, ename, loc from emp join dept on emp.deptno = dept.deptno and substr(emp.ename,1,1) = 'S';

  • 2. 
    You are trying to manipulate data on the Oracle database. Which of the following choices identifies a capacity of select statements in Oracle and does not require the use of a subquery?
    • A. 

      You can change data in Oracle using select statements.

    • B. 

      You can remove data from Oracle using select statements.

    • C. 

      You can create a table with the contents of another using select statements.

    • D. 

      You can truncate tables using select statements

  • 3. 
    You issue a query in the Oracle database. Which of the following choices does not identify a component of your query if you want the query to execute a mathematical operation on user-defined static expressions?
    • A. 

      Column clause

    • B. 

      Table clause

    • C. 

      The DUAL table

    • D. 

      The where clause

  • 4. 
    You are manipulating data in Oracle. Which of the following is not a SQL command?
    • A. 

      . select * from dual;

    • B. 

      Update emp set empno = 6543 where ename = 'SMITHERS';

    • C. 

      Set define ?

    • D. 

      Create table employees (empid varchar2(10) primary key);

  • 5. 
    You are defining SQL queries in Oracle. Which of the following database objects cannot be referenced directly from a select statement?
    • A. 

      Tables

    • B. 

      Sequences

    • C. 

      Indexes

    • D. 

      Views

  • 6. 
    You need to filter return data from your query on the PROFITS table according to the PRODUCT_NAME column. Which of the following clauses in your SQL query will contain reference to the appropriate filter criteria?
    • A. 

      Select

    • B. 

      From

    • C. 

      Where

    • D. 

      Having

  • 7. 
    ) A partial listing of output from the PROFITS table is shown in the following code block:PRODUCT_NAME PRODUCT_TYPE QTR_END_DATE        PROFIT------------ ------------ ------------ ------------- BARNEY           DOLL  TOY          31-MAR-2001     6575430.30GAS GRILL       APPLIANCE       31-MAR-2001     1234023.88PENCIL              OFFICE                30-JUN-2001       34039.99 Which of the following choices identifies the proper setup of a where clause for a query that calculates the total profits for all appliances sold in the six-month period from January 1 to June 30, 2001?
    • A. 

      . where product_name = 'GAS GRILL' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001';

    • B. 

      Where product_type = 'APPLIANCE' and product_name = 'GAS GRILL' and qtr_end_date ='31-JAN-2001' or '30-JUN-2001';

    • C. 

      . where product_type = 'APPLIANCE' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001';

    • D. 

      Where product_name = 'GAS GRILL' and qtr_end_date = '01-JAN-2001' or '30-JUN-2001';

  • 8. 
    A table called TEST contains two columns: TESTCOL, defined as a NUMBER(10) datatype; and TESTCOL_2, defined as a VARCHAR2(10) datatype. You issue the following statement on Oracle: insert into test (testcol, testcol_2) values (null, 'FRANCIS'). You then issue the following query against that table: select nvl(testcol,'EMPTY') as testcol from test where testcol_2 = 'FRANCIS'. Which of the following choices correctly identifies the result?
    • A. 

      Oracle returns zero as the result

    • B. 

      Oracle returns EMPTY as the result.

    • C. 

      Oracle returns NULL as the result

    • D. 

      Oracle returns an error as the result

  • 9. 
    You want to obtain data from the ORDERS table, which contains three columns: CUSTOMER, ORDER_DATE, and ORDER_AMT. Which of the following choices identifies how you would formulate the where clause in a query against the ORDERS table when you want to see orders for customer LESLIE that exceed 2700?
    • A. 

      Where customer = 'LESLIE';

    • B. 

      Where customer = 'LESLIE' and order_amt < 2700;

    • C. 

      Where customer = 'LESLIE' or order_amt > 2700;

    • D. 

      Where customer = 'LESLIE' and order_amt > 2700;

  • 10. 
    Use the following output to answer the question ENAME----------SMITH-dog-ALLEN-dog-WARD-dog-dJONES-dog-MARTIN-dogBLAKE-dog-CLARK-dog-SCOTT-dog-KING-dog-dTURNER-dogADAMS-dog-JAMES-dog-FORD-dog-dMILLER-dogWhich of the following choices identifies the SQL statement that produced this output?
    • A. 

      Select trim(trailing '-dog' from ename) as ename from emp;

    • B. 

      Select rpad(ename, 10, '-dog') as ename from emp;

    • C. 

      Select substr(ename, 1, 10) as ename from emp;

    • D. 

      Select lpad(ename, 10, '-dog') as ename from emp;

  • 11. 
    Use the following code block to answer the question: SQL> select _____(-45) as output from dual;OUTPUT------   -45 Which of the following choices identifies a single-row function that could not have produced this output?
    • A. 

      Abs( )

    • B. 

      Ceil( )

    • C. 

      Floor( )

    • D. 

      Round( )

  • 12. 
    For a certain row in a table, a VARCHAR2 column contains the value SMITHY, padded to the right with seven spaces by the application. When the length( ) function processes that column value, what will be the value returned?
    • A. 

      6

    • B. 

      13

    • C. 

      30

    • D. 

      60

  • 13. 
    You issue the following statement in SQL*Plus: SQL> select ceil(-97.342),   floor(-97.342),  round(-97.342,0),   trunc(-97.342)   from dual; Which of the following choices identifies the function that will not return -97 as the result?
    • A. 

      Ceil( )

    • B. 

      Floor( )

    • C. 

      Round( )

    • D. 

      Trunc( )

  • 14. 
    You issue the following statement in SQL*Plus:SQL> select ceil(256.342),   floor(256.342),  round(256.342,0),   trunc(256.342)   from dual; Which of the following choices identifies the function that will not return 256 as the result?
    • A. 

      Ceil( )

    • B. 

      Floor( )

    • C. 

      .round( )

    • D. 

      Trunc( )

  • 15. 
    You issue the following query in Oracle: SQL> select months_between('15-MAR-83', '15-MAR-97') from dual;          What will Oracle return?
    • A. 

      14

    • B. 

      -14

    • C. 

      168

    • D. 

      -168

  • 16. 
    25)Two tables, PRODUCT and STORAGE_BOX, exist in a database. Individual products are listed in the table by unique ID number, product name, and the box a particular product is stored in. Individual storage boxes (identified by number) listed in the other table can contain many products, but each box can be found in only one location. Which of the following statements will correctly display the product ID, name, and box location of all widgets in this database?   
    • A. 

      Select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.prod_id = b.prod_id and prod_name = 'WIDGET';

    • B. 

      Select p.prod_id, p.prod_name, b.box_loc fromproduct p, storage_box b where prod_name = 'WIDGET';

    • C. 

      Select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.stor_box_num = b.stor_box_num and p.prod_name = 'WIDGET';

    • D. 

      Select prod_id, prod_name, box_loc from product, storage_box where stor_box_num = stor_box_num and prod_name = 'WIDGET'

  • 17. 
    You want to join information from three tables as part of developing a report. The tables are EMP, DEPT, and SALGRADE. Only records corresponding to employee, department location, and salary range are required for employees in grades ten and higher for the organization. How many comparison operations are required for this query?  
    • A. 

      .Two

    • B. 

      Three

    • C. 

      Four

    • D. 

      Five

  • 18. 
    You want to join the contents of two tables, PRODUCT and STORAGE, to list the location of all boxes containing widgets. PRODUCT has three columns: ID, NAME, and BOX#. STORAGE has two columns: BOX# and LOC. Which of the following choices will not give the desired result?
    • A. 

      Select product.id, product.name, storage.loc from product, storage where product.box# = storage.box#;

    • B. 

      Select product.id, product.name, storage.loc from product join storage on product.box# = storage.box#;

    • C. 

      Select product.id, product.name, storage.loc from product natural join storage on product.box# = storage.box#;

    • D. 

      Select product.id, product.name, storage.loc from product natural join storage;

  • 19. 
    You are defining an outer join statement. Which of the following choices is true concerning outer join statements?
    • A. 

      Because outer join operations permit NULL values from one of the tables, you do not have to specify equality comparisons to join those tables.

    • B. 

      In outer join statements on tables A and B, you specify the right outer join when you want all of table A's rows, even when no corresponding record exists in table B.

    • C. 

      In outer join statements on tables A and B, you specify the left outer join when you want all of table B's rows, even when no corresponding record exists in table A.

    • D. 

      Even though outer join operations permit NULL values from one of the tables, you still need to specify equality comparisons to join those tables.

  • 20. 
    Two tables, PRODUCT and STORAGE_BOX, exist in a database. Individual products are listed in the table by unique ID number, product name, and the box a particular product is stored in. Individual storage boxes (identified by number) listed in the other table can contain many products, but the box can be found in only one location. Which of the following statements will correctly display the product ID, name, and box location of all widgets in this database that have or have not been assigned to a storage box?
    • A. 

      Select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET'(+);

    • B. 

      Select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET';

    • C. 

      Select p.prod_id, p.prod_name, b.box_loc from product p right outer join storage_box b where b.stor_box_num = p.stor_box_num (+) and p.prod_name = 'WIDGET';

    • D. 

      Select p.prod_id, p.prod_name, b.box_loc from product p full outer join storage_box b on p.stor_box_num = b.stor_box_num where b.stor_box_num is NULL;

  • 21. 
    You issue the following command in Oracle: SQL> select e.ename, a.street_address, a.city, a.state, a.post_code  from emp e, addr a   where e.empno = a.empno (+)   and a.state = 'TEXAS';Which of the following choices shows the ANSI/ISO equivalent statement?
    • A. 

      Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e outer join addr a on e.empno = a.empno where a.state = 'TEXAS';

    • B. 

      . select e.ename, a.street_address, a.city, a.state, a.post_code from emp e left outer join addr a on e.empno = a.empno where a.state = 'TEXAS'

    • C. 

      Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a on e.empno = a.empno where a.state = 'TEXAS';

    • D. 

      Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a where e.empno = a.empno (+) and a.state = 'TEXAS';

  • 22. 
    Examine the following output from SQL*Plus:PRODUCT.ID PRODUCT.NAME BOX.LOCATION---------- ------------ ------------578-X      WIDGET       IDAHO              TENNESSEE456-Y      WIDGETWhich of the following choices identifies the type of query that likely produced this result?
    • A. 

      Full outer join

    • B. 

      Left outer join

    • C. 

      Right outer join

    • D. 

      Equijoin

  • 23. 
    )Which of the following choices identifies a group by query that will not result in an error from Oracle when run against the database?
    • A. 

      Select deptno, job, sum(sal) from emp group by job, deptno;

    • B. 

      Select sum(sal), deptno, job from emp group by job, deptno;

    • C. 

      Select deptno, job, sum(sal) from emp;

    • D. 

      Select deptno, sum(sal), job from emp group by job, deptno;

  • 24. 
    Review the following SQL statement: SQL> select a.deptno, a.job, b.loc, sum(a.sal)               from emp a, dept b               where a.deptno = b.deptno               group by a.deptno, a.job, b.loc         order by sum(a.sal); Which of the following choices identifies the column upon which the order of output from this query will be returned?
    • A. 

      DEPTNO

    • B. 

      JOB

    • C. 

      LOC

    • D. 

      Sum(A.SAL)

  • 25. 
    You are developing a query on the PROFITS table, which stores profit information by company region, product type, and quarterly time period.Which of the following choices identifies a query that will obtain the average profits greater than $100,000 by product type, region, and time period?
    • A. 

      Select region, prod_type, period, avg(profit) from profits where avg(profit) > 100000 group by region, prod_type, period;

    • B. 

      Select region, prod_type, period, avg(profit) from profits where avg(profit) > 100000 order by region, prod_type, period;

    • C. 

      Select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) > 100000;

    • D. 

      Select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) < 100000;

Related Topics
Back to Top Back to top