ITEC 4200 Study guide

What is the result of: SELECT TRUNC(ROUND(-15.749), 2) FROM dual; 16 -1 -16 15

-16

Consider the following statement: SELECT customer, state_id, DECODE(state_id, 'CA', .08, 'FL', .07, .05) "Sales Tax Rate" FROM STATE_REGISTRY WHERE state_id IN ('CA', 'FL', 'GA', 'TX'); What will be the statement shown as the sales tax rate in Georgia? .07 .05 null .08

.05

The following lists data rows in a table called SHIP_CABINS (Note:The empty values are NULL). ROOM_NUMBER STYLE WINDOW -------------------- -------- ----------- 102 Suite Ocean 103 Ocean 104 For the following SELECT statement: SELECT room_number FROM ship_cabins WHERE (style = NULL) OR (window = NULL); How many rows will this statement retrieve? 1 0 2 It will not execute due to a syntax error in the WHERE clause.

0

Review the follow row data for a table named VENDORS: VENDOR_ID CATEGORY ------------------ ------------- 1 Supplier 2 Teaming Partner Given the following SELECT statement: SELECT vendor_id FROM vendors WHERE category IN ('Supplier', 'Subcontractor', '%Partner'); How many rows will the SELECT statement return? 1 2 Compile Error. 0

1

What is returned when the following statement is executed? SELECT ROUND(15607.329, -1) FROM DUAL; 15607.3 15607 Syntax error because a negative number is not permitted as the second parameter of the ROUND function. 15610

15610

What would be the result of the following query: SELECT TO_NUMBER('17,940.50', '99999D99') FROM DUAL; 17,940.50 17940.50 17940.5 no rows return

17940.5

What is the result set value returned from the following SQL: select LENGTH('This is a good day!') from dual; 19 syntax error 0 20

19

Consider the following data rows for the table CRUISES: ACCT_ID CRUISE_NAME START_DATE END_DATE ----------- ------------------- ----------------- -------------- 168 Hawaii 11-JUL-19 24-JUL-19 257 Hawaii 10-OCT-19 23-OCT-19 325 Mexico 04-OCT-19 17-OCT-19 412 Mexico 06-DEC-19 19-DEC-19 Given the following SQL: SELECT ACCOUNT_ID FROM CRUISES ORDER BY cruise_name DESC, start_date; What will be the value of the Account_id for the first row displayed? 325 168 412 257

325

Consider the following table named "ports": PORT_ID PORT_NAME CAPACITY ----------- -------------- ------------ 1 Galveston 4 2 San Diego 4 3 San Francisco 3 4 Los Angeles 4 5 San Juan 3 6 Grand Cayman 3 Now consider the following SELECT statement: SELECT * FROM ports WHERE port_name LIKE 'San%' OR port_name LIKE 'Grand_Cayman' OR capacity = 3; How many rows from the data in the table will be returned? 1 none or 0 rows 3 4

4

Review the following data for a table called ONLINE_ORDERS: UNIT_PRICE SHIPPING TAX_MULT ------------- -------------- ------------ 3.00 4.00 1.10 What is the result of the following SELECT statment: SELECT UNIT_PRICE + SHIPPING * TAX_MULT FROM ONLINE_ORDERS; 7.40 .70 12.12 7.70

7.40

Consider the list of data from the PUBLISHERS table: CONTACT_NAME -------------- Broussard Gautier Smith Lindon West Abel Worthington What will be returned by the following statement: SELECT MIN(CONTACT_NAME) FROM PUBLISHERS ORDER BY contact_name; Broussard abel Worthington Abel

Abel

Which of the following functions can produce different results, depending on the value of a specified column? CASE UPPER SUBSTR LTRIM

CASE

The following code has a compile error. What will be result of executing the following statement (note all table names and columns are correct): SELECT category, AVG(retail-cost) "Profit" FROM books WHERE AVG(cost) > 15 GROUP BY category HAVING AVG(retail-cost) < 10; Compile Error-- there cannot be an aggregate function in the WHERE clause. Compile Error-- zero rows returned, because the output is limited to average cost > 15 and the other average < 10. Compile Error-- the GROUP BY clause should group the profit, not the category. Compile Error-- you can't have a WHERE clause and a HAVING clause in the same SELECT statement.

Compile Error-- there cannot be an aggregate function in the WHERE clause.

The relational algebra operator projection, is implemented in the SELECT statement by use of the WHERE clause. True False

FALSE

The return value of the SQL statement "SELECT MOD(16, 3) FROM DUAL;" is 2; TRUE FALSE

FALSE

Will the following SQL, return any rows from a table if some of the CRUISE_DATE values are August 21, 2019 at exactly 12:44:33? SELECT TRUNC(CRUISE_DATE, 'MONTH') FROM SHIPS where to_char(CRUISE_DATE,'dd-mon-yy hh24:mi:ss') = '21-AUG-12 12:44:33'; True False

FALSE

Which of the following functions returns a number? SUBSTR UPPER INSTR LTRIM

INSTR

If you are using an ORDER BY to sort values in descending order, in which order will they appear? If the datatype is date, the value 25-JUN-2012 will first appear before the value 28-AUG-2012. If the datatype is character, the value 'Michael' will first appear before the value 'Jackson. If the datatype is numeric, the value 400 will first appear before the value 800. If the datatype is character, the value '130' will first appear before the value '75'.

If the datatype is numeric, the value 400 will first appear before the value 800.

Consider the following SQL: SELECT port_id FROM ports WHERE 1 = 2; Which one of the following is true of the SELECT statement: It will execute and return the port_id for all of the rows in the table. It will produce a syntax error because the WHERE condition does not refer to any columns in the table It will produce a syntax error because the WHERE condition returns a FALSE value. It will execute and return the port_id for none of the rows in the table.

It will execute and return the port_id for none of the rows in the table.

Consider the following SELECT statement: SELECT ship_id FROM ships WHERE 10 = 5 + 5; Which of the following is true of this statement? It will produce a syntax error because the WHERE statement does not refer to any columns in the table. It will execute and return the ship_id for all of the rows in the table because the WHERE condition is TRUE. It will produce a syntax error because the WHERE condition returns a TRUE value. It will execute but return no rows found.

It will execute and return the ship_id for all of the rows in the table because the WHERE condition is TRUE.

Assume that the table and column names are correct for the SHIPS table. Consider the following SELECT statement. Line 01: SELECT ship_id FROM ships Line 02: WHERE ship_name LIKE 'Codd %' Line 03: OR lifeboats >= 80 Line 04: AND lifeboats <= 100; Which of the following statements is true about this SELECT statement? It will return all ships beginning with 'Codd' along with all ships that have between 80 and 100 lifeboats (inclusive) It will return all ships beginning with 'Codd' that have between 80 and 100 lifeboats (inclusive) Line 3 and 4 need parenthesis to compile. The syntax on lines 3 and 4 is incorrect.

It will return all ships beginning with 'Codd' along with all ships that have between 80 and 100 lifeboats (inclusive)

You are writing a SELECT statement which will be used to print a check. The amount of the check will be displayed as dollars and cents, however, the bank wants the amount to have asterisks (*) filled in to the left of the amount. Which function would you use to accomplish this? LFILL LEFT_FILL LPAD LTRIM

LPAD

Which of the following would be used to display how many months a book was available before the customer placed the order? NOTE: (orderdate is the date that a book was ordered, and pubdate is the date that the book is available.) MONTHS_BETWEEN(pubdate, orderdate) MONTHS_BETWEEN(orderdate, pubdate) NEXT_DAY(orderdate, pubdate) NEXT_DAY(pubdate, orderdate)

MONTHS_BETWEEN(orderdate, pubdate)

A bookstore has a policy that when a book is ordered, it is shipped on the first Monday after the date that the book was ordered. Which of the following will determine the correct ship date? NEXT_DAY(orderdate, 'Monday') NEXT_DAY('Monday', SYSDATE) LAST_DAY(orderdate, 'Monday') NEXT_DAY(SYSDATE, 'MONDAY')

NEXT_DAY(orderdate, 'Monday')

Which of the following statements is true of Boolean operators? For this question, ignore the role of parentheses. AND is evaluated before NOT NOT is evaluated after OR OR is evaluated before AND NOT is evaluated first

NOT is evaluated first

Which of the following statement(s) will NOT return a compile error? SELECT DUAL FROM SYSDATE; none of the answers SELECT SYSDATE; SELECT Upper(hello) E FROM DUAL;

None of these

Which clause of the SELECT statement always occurs last? ORDER BY clause the order of the clauses does not matter. WHERE clause FROM clause

ORDER BY clause

Examine the following data for the SALES table: SALES_PROD SALES_COST CATEGORY ------------------- --------------- -------------- Saffron Rice 1.24 <null> Looseleaf paper 3.40 <null> Picture frame <null> <null> What will be returned by the following statement: SELECT COUNT(sales_prod) "Product", AVG(Sales_Cost) "Cost", COUNT(Category) "Cat" FROM SALES; Product Cost Cat ---------- ------- ------ 3 2 0 Product Cost Cat ---------- ------- ------ 3 2.32 0 Product Cost Cat ---------- ------- ------ 3 1.54 0 Product Cost Cat ---------- ------- ------ 3 <null> <null>

Product Cost Cat ---------- ------- ------ 3 2.32 0

or more? (Assume table and column names are legitimate names in the database.) SELECT * FROM book WHERE pubid = 2, retail < 35; SELECT * FROM book WHERE pubid IN (1, 2, 3) AND retail NOT BETWEEN 1 and 29.99; SELECT * FROM book WHERE pubid IN (1, 2, 3) AND retail >= 35; SELECT * FROM book WHERE pubid IN (2) AND NOT retail < 35;

SELECT * FROM book WHERE pubid IN (2) AND NOT retail < 35;

GIVEN THE FOLLOWING TABLE DESCRIPTIONS: PATIENT PATIENT_ID NUMBER NOT NULL, Primary Key LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(25) NOT NULL DOB DATE INS_CODE NUMBER PHYSICIAN PHYSICIAN ID NUMBER NOT NULL, Primary Kev LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(25) NOT NULL LICENSE_NO NUMBER(?) NOT NULL HIRE_DATE DATE ADMISSION ADMISSION_ID NUMBER NOT NULL, Primary Key PATIENT_ID NUMBER NOT NULL, References PATIENT_ID column of PATIENT table PHYSICIAN_ID NUMBER NOT NULL, References PHYSICIAN_ID column of PHYSICIAN ADMIT_DATE DATE DISCHG_DATE DATE ROOM_ID NUMBER Foreign key to ROOM ID of the ROOM table Which SQL statement will produce a list of all patients who have more than one physician? SELECT p.patient_id FROM patient p WHERE p.patient_id IN (SELECT patient_id FROM admission GROUP BY patient_id HAVING COUNT(*) > 1); SELECT DISTINCT a.patient_id FROM admission a, admission a2 WHERE a.patient_id = a2.patient_ id AND a. physician_id <> a2.physician_id; SELECT patient_id FROM admission WHERE COUNT(physician_id) > 1; SELECT patient_id FROM patient FULL OUTER JOIN physician;

SELECT DISTINCT a.patient_id FROM admission a, admission a2 WHERE a.patient_id = a2.patient_ id AND a. physician_id <> a2.physician_id;

Which of the following will produce "Hello World" as the results (with the capitalization as shown.) SELECT LOWER('HELLO WORLD') FROM DUAL; SELECT INITCAP('HELLO WORLD') FROM dual;

SELECT INITCAP('HELLO WORLD') FROM dual;

Which of the following SELECT statements will return 30 as the result? SELECT TRUNC(29.99, 0) FROM DUAL; SELECT TRUNC(29.99, -1); SELECT ROUND(29.01, -1) FROM DUAL; SELECT ROUND(24.37, 2) FROM DUAL;

SELECT ROUND(29.01, -1) FROM DUAL;

Which of the following will display the current time, in hours, minutes, and seconds? SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL; SELECT TO_CHAR(SYSDATE) FROM DUAL; SELECT TO_CHAR(SYSDATE, 'HR:MI:SS') FROM DUAL; SELECT TO_CHAR(SYSDATE, 'HH:MM:SS') FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;

Which of the following is a valid SQL statement? SELECT LTRIM(RPAD(state, 5, '*'), 4, -3, "*") FROM DUAL; SELECT TRUNC(ROUND(124.67, 1) , 0) FROM DUAL; SELECT ROUND(TRUNC(125.38, 0) FROM DUAL; SELECT SUBSTR(ROUND(14.87, 2, 1), -4, 1) FROM DUAL;

SELECT TRUNC(ROUND(124.67, 1) , 0) FROM DUAL;

Which of the following SQL statements is NOT valid? (Assume that all of the table and column names are valid.) SELECT address, city, state, zip FROM customers WHERE Lastname LIKE "Smith"; SELECT * FROM publisher WHERE Lastname = 'Smith' ORDER BY contact DESC; SELECT address || city || state || zip "Address" FROM Customers WHERE Lastname = 'Smith'; SELECT * FROM publisher ORDER BY contact;

SELECT address, city, state, zip FROM customers WHERE Lastname LIKE "Smith";

Given the following EMPLOYEE table Description: EMPLOYEE_ID NUMBER Primary Key NOT NULL EMP_LNAME VARCHAR2 (25) EMP FNAME VARCHAR2 (25 DEPT_ID NUMBER Foreign key to DEPT ID column of the DEPARTMENT table JOB_ID NUMBER Foreign key to JOB IO column of the JOB table MGR_ ID NUMBER References EMPLOYEE ID column SALARY NUMBER(9 , 2) HIRE_DATE DATE You want to generate a list of employees are in department 30, that have been promoted from clerk to associate by querying the EMPLOYEE and EMPLOYEE_HIST tables. The EMPLOYEE HIST table has the same structure as the EMPLOYEE table. The JOB ID value for clerks is 1 and the JOB ID value for associates is 6. Which query should you use? SELECT employee_id, emp_lname , emp_fname , dept_id FROM employee WHERE (employee_id, dept_id) IN (SELECT employee_id, dept_id FROM employee_hist WHERE dept_id = 30 AND job_id = 1) AND job_id = 6; SELECT employee_id, emp_lname , emp_fname , dept_id FROM employee WHERE (employee_id, dept_id) (SELECT employee_id, dept_id FROM employee_hist WHERE dept_id = 30 AND job_id = 6); SELECT employee_id, emp_lname , emp_fname , dept_id FROM employee WHERE (employee_id, dept_id) IN (SELECT employee_id, dept_id FROM employee WHERE dept_id = 30) AND job_id = 6; SELECT employee_id, emp_lname , emp_fname , dept_id FROM employee_hist WHERE (employee_id, dept_id) = (SELECT employee_id, dept_id FROM employee_hist WHERE dept_id = 30 AND job_id = 1) AND job_id = 6;

SELECT employee_id, emp_lname , emp_fname , dept_id FROM employee WHERE (employee_id, dept_id) IN (SELECT employee_id, dept_id FROM employee_hist WHERE dept_id = 30 AND job_id = 1) AND job_id = 6;

Examine the data from the PO_HEADER and PO_DETAIL tables. PO_HEADER PO_NUM PO_DATE SUPPLIER_ID PO_TERMS PO_TOTAL -------- ---------------- -------------- ------------- ----------- 10052 03-JUL-2001 2 Net30 2030.00 10053 03-JUL-2001 2 Net30 84.5 10054 03-JUL-2001 1 Net60 3805.OO 10055 03-JUL-2001 1 Net60 125.OO 10056 03-JUL-2001 1 Net60 85.72 PO_DETAIL PO_NUM PO_LINE_ID PRODUCT_ID QUANTITY UNIT PRICE -------- ------------- --------------- ------------ ------------- 10052 1 1 100 10.30 10052 2 2 100 10.00 10054 1 1 80 72.10 10054 2 1 10 10.00 10054 3 3 10 10.00 10057 1 1 75 54.30 You need to produce a report to identify any PO_HEADER rows that have no matching PO_DETAIL rows and any PO_DETAIL rows that have no matching PO_HEADER record. Which SELECT statement should you execute? SELECT h.po_num , d.po_num, d.po_line_id FROM po_header h FULL OUTER JOIN po_detail d ON (h.po_num = d.po_num) WHERE h.po_num IS NULL OR d.po_line_id IS NULL; SELECT h.po_nurn, d.po_num, d.po_line_id FROM po_header h LEFT OUTER JOIN po_detail d ON (h.po_num = d. po_num ) WHERE d.po_num IS NULL; SELECT h.po_nurn, d.po_ num, d.po_line_id FROM po_header h LEFT OUTER JOIN po_detail d ON (h.po_num = d.po_num ) WHERE h.po_num IS NULL AND d.po_line_id IS NULL; SELECT h.po_nurn, d.po_num, d.po_line_id FROM po_header h RIGHT OUTER JOIN po_detail d ON (h.po_num = d.po_num) - WHERE h.po_num IS NULL;

SELECT h.po_num , d.po_num, d.po_line_id

Which of the following does NOT return a customer with the last name THOMPSON in the query results? Assume that all table and column names are valid and there is at least 1 row with a last name equal to THOMPSON (all caps). SELECT lastname FROM customers WHERE lastname LIKE '%T%'; SELECT * FROM customers; SELECT lastname FROM customers WHERE lastname = '_SON'; SELECT lastname FROM customers WHERE lastname = 'THOMPSON';

SELECT lastname FROM customers WHERE lastname = '_SON';

Which of the following statements returns a list of all customers' names sorted by state using the default, and by city descending (within a state )? SELECT name FROM Customers ORDER BY state desc, city desc; SELECT name FROM Customers ORDER BY desc state, city; SELECT name FROM Customers ORDER BY state, desc city; SELECT name FROM Customers ORDER BY state, city desc;

SELECT name FROM Customers ORDER BY state, city desc;

Review the schema for the PRODUCTS table: Prod_id NUMBER (Primary key) Purpose VARCHAR2(50) Prod_Name VARCHAR2(50) Prod_cost NUMBER Days NUMBER Which of the following SELECT statements queries the PRODUCTS table, to show the average cost for each PURPOSE, restricting output to those rows where DAYS are greater than 3. SELECT purpose, AVG(prod_cost) FROM PRODUCTS WHERE days > 3 GROUP BY purpose; SELECT purpose, AVG(prod_cost) FROM PRODUCTS GROUP BY purpose HAVING days > 3; SELECT purpose, AVG(prod_cost) FROM PRODUCTS GROUP BY purpose, (days > 3); SELECT purpose, AVG(prod_cost) FROM PRODUCTS WHERE days > 3 GROUP BY purpose HAVING days > 3;

SELECT purpose, AVG(prod_cost) FROM PRODUCTS WHERE days > 3 GROUP BY purpose;

Review the schema for the STUDENTS table: Proj_id NUMBER (Primary key) Purpose VARCHAR2(50) Proj_Name VARCHAR2(50) Proj_cost NUMBER Days NUMBER Pick the following SELECT statement that queries the STUDENTS table, to show the average cost for each PURPOSE, restricting output to those rows where DAYS are greater than 3. SELECT purpose, AVG(proj_cost) FROM students WHERE days > 3 GROUP BY purpose; SELECT purpose, AVG(proj_cost) FROM students GROUP BY purpose HAVING days > 3; SELECT purpose, AVG(proj_cost) FROM students GROUP BY purpose, (days > 3); SELECT purpose, AVG(proj_cost) FROM students WHERE days > 3 GROUP BY purpose, days HAVING days > 3;

SELECT purpose, AVG(proj_cost) FROM students WHERE days > 3 GROUP BY purpose;

Which of the following lists each book having a profit of $10.00 or more in descending order by profit? SELECT title, profit "Profit" FROM books WHERE profit >= 10 ORDER BY 2 DESC; SELECT title, profit FROM books WHERE profit >= 10 ORDER BY 1 DESC; SELECT title, profit "Profit" FROM books WHERE "Profit" >= 10 GROUP BY "Profit" DESC; SELECT * FROM books WHERE profit > 10 ORDER BY "Profit" DESC;

SELECT title, profit "Profit" FROM books WHERE profit >= 10 ORDER BY 2 DESC;

How would you write an expression that would display only the last four digits of a person's phone number? SUBSTR(phone_number, -4, 4) SUBSTR(phone_number, -1, 4) SUBSTR(phone_number, -4, 1) It cannot be determined because it is dependent upon how the phone number is stored in the phone number field.

SUBSTR(phone_number, -4, 4)

Are the following 2 statements equivalent to each other? Select * from student WHERE age >= 13 AND age <= 20; Select * from student WHERE age BETWEEN 13 AND 20; True False

TRUE

Is the following is a legal SQL statement? SELECT MONTHS_BETWEEN(SYSDATE, '02-MAR-10') FROM DUAL; True False

TRUE

Given the following CUSTOMER and ORDER table description (data structure). CUSTOMER Column Name Data Type Constraints custid Number(6) Primary Key custname Varchar2(30) Not Null custemail Varchar2(25l custcreditlimit Number(9,2) custlocation Varchar2(20) ORDER Column Name Data Type Constraints ordid Number(6) Primary Key orddate Varchar2(30) Not Null ordsalesrep Varchar2(25) ordcustid Number(9.2) Foreign Kev to Custld in the CUSTOMER table custlocation Varchar2(20) ordamount Number(5.2) You want to create a report showing each customer and all orders placed by that customer. Specifically, you want your report to contain the columns custid, custname , and custcreditlimit from the CUSTOMER table as well as the columns ordid, orddate, and ordamount from the ORDER table. The output should be limited to customers who are located in Dallas, and should omit customers who have never placed an order. You issue the following SELECT statement to accomplish this: SELECT c.custid ,c.custname , c.custcreditlimit ,o.ordid , o.orddate ,o.ordamount FROM CUSTOMER c, ORDER o WHERE UPPER (c.custlocation ) = 'DALLAS'; Which of the following statements is true regarding the results of this statement? The SELECT statement will return the results required by the scenario. The SELECT statement will only return customer rows if the data in the custlocation column of the CUSTOMER table is stored in all caps. The SELECT statement will fail because the location from the CUSTOMER table is referenced in the WHERE clause but it never appears in the SELECT clause. The SELECT statement will return each customer in Dallas from the CUSTOMER table matched with every order in the ORDER table, as well as each order in the ORDER table matched with every Dallas-based customer in the CUSTOMER table.

The SELECT statement will return each customer in Dallas from the CUSTOMER table matched with every order in the ORDER table, as well as each order in the ORDER table matched with every Dallas-based customer in the CUSTOMER table.

If you wish to display a numeric value along with dollar signs and commas, which of the following is the best approach to take? The TO_NUMBER function with a format mask The TO_CHAR function with a format mask A combination of string literals that contain commas and dollar signs, concatenated to the numeric value the MONEY datatype

The TO_CHAR function with a format mask

Given the following SELECT statement (Note the subquery is in the FROM - which is legal) SELECT s.student_name, s.grade_point_avg, s.major_id, m.gpa_avg FROM student s, (SELECT major_id, AVG(grade_point_avg) gpa_avg FROM student GROUP BY major_id) m WHERE s.major_id = m.major_id AND s.grade_ point_ avg > m.gpa_avg ; What will be the result of executing this SELECT statement? The names of all students with grade point average that is higher than the average grade point average in their major will be displayed. The names of all students with a grade point average that is higher than the average grade point average of all student s will be displayed. The names of all students, grouped by each major, with a grade point average that is higher than the average grade point average of all student s in each major will be displayed. The names of all students with grade point average that is lower than the average grade point average in their major will be displayed.

The names of all students with grade point average that is higher than the average grade point average in their major will be displayed.

In which situation would you use a natural join (self-join, join on, using)? The tables being joined do not have primary and foreign keys defined. The tables being joined have matching columns with different names but compatible datatypes. The tables being joined each have two columns with the same name and compatible datatypes, and you want to join on one of the columns. The tables being joined each have two columns with the same name and compatible datatypes, and you want to join on both of the columns.

The tables being joined each have two columns with the same name and compatible datatypes, and you want to join on both of the columns.

Which of the following is true of an SQL function in Oracle? They never return a value. They often return a value. There is no consistent answer to whether they return a value or not. They always return a value.

They always return a value.

*** EMP table *** employee_id NUMBER(10) (PK), department_id NUMBER (10), hire_date DATE *** EMP_HIST *** employee_id NUMBER(10) (pk), department_id NUMBER (10), termination_date DATE Which set operator would you use to display the employee ID's of employees' hired after January 10, 2007 in the EMP table and employee IDs of employees who have held more than one position in the EMP_HIST table, eliminating any duplicate IDs? INTERSECT UNION ALL UNION MINUS

UNION

Evaluate this SQL statement: SELECT c.customer_id, o.order_id, o.order_date, p.product_name FROM customer c, currorder o, product p WHERE customer.customer id = currorder.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? Use the table name in the ORDER BY clause. Remove the table aliases from the WHERE clause. Use the table aliases instead of the table names in the WHERE clause. Remove the table alias from the ORDER BY clause and use only the column name.

Use the table aliases instead of the table names in the WHERE clause.

Which of the following WHERE clauses will returns rows for customers with the first name BONITA (all caps) in the results? WHERE firstname LIKE '%n%' WHERE TO_LOWER(firstname) = 'B%' WHERE firstname LIKE '%N%' WHERE TO_UPPER(firstname) LIKE 'bon%'

WHERE firstname LIKE '%N%'

If an integer is multiplied by a NULL value, the result is: 1.a syntax error 2.an integer 3.a whole number 4.a NULL value

a NULL value

Examine the Description (data structures) of the PLAYER and TEAM table: PLAYER PLAYER_ID NUMBER PK LAST_NAME VARCHAR2(30) FIRST_NAME VARCHAR2(25) TEAM_ID NUMBER MGR_ID NUMBER SIGNINGBONUS NUMBER(9,2) TEAM TEAM ID NUMBER TEAM_NAME VARCHAR2(30) Which situation would require a subquery to return the desired result? a list of all players who are also managers a list of all teams that have more than 11 players a list of all players who have a larger signing bonus than their manager a list of all players who received a signing bonus that was lower than the average bonus

a list of all players who received a signing bonus that was lower than the average bonus

Which construct can be used to return data based on an unknown condition? a subquery a GROUP BY clause an ORDER BY clause a WHERE clause with an OR condition

a subquery

Given that the columns and table name are all correct, which of the following lines of the SELECT statement contain an error? Line 01: SELECT name, contact, Line 02: "Person to call", phone Line 03: FROM publisher; there are no errors line 01 line 02 line 03

line 2

Which of the following functions can be used to determine how many months a book is available? none of the answers MONTH MONTH_BETWEEN MON

none of the answers

Which of the following SQL statements is valid? SELECT Order_num FROM orders WHERE shipdate = 'NULL'; none of the statements are valid SELECT Order_num FROM orders WHERE shipdate = NULL; SELECT Order_num FROM orders WHERE shipdate LIKE NULL;

none of the statements are valid

Complete the following SQL statement so that it returns all books published after March 20, 2015: SELECT * FROM books WHERE pubdate > 03-20-2015; pubdate > 'MAR-20-15'; pubdate > '03-20-15'; pubdate > '20-MAR-15';

pubdate > '20-MAR-15';

Evaluate this SQL statement: SELECT product _id, product _name, price FROM product WHERE supplier_id IN (SELECT supplier_id FROM product WHERE price > 120 OR qty_in_stock > 100 ); Which values will be displayed? the PRODUCT_ID, PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 and have a QTY_ IN_STOCK value greater than 100 the PRODUCT_ID, PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 or that have a QTY_IN_STOCK value greater than 100 the PRODUCT_ID, PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 or that have a QTY_IN_STOCK value greater than 100, and that have a supplier the PRODUCT_ID, PRODUCT_NAME, and PRICE of products supplied by a supplier with products that are priced greater than $120.00 or with products that have a QTY_IN_STOCK value greater than 100

the PRODUCT_ID, PRODUCT_NAME, and PRICE of products supplied by a supplier with products that are priced greater than $120.00 or with products that have a QTY_IN_STOCK value greater than 100