Information Systems

this is about databases questions , maybe i miss copy some option D, if ABC there are all incorrecct please type D after that question thank you

Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a INTERSECT query based on these two tables?

[removed] a. The query output will be: 125,124,123,126
[removed] b. The query output will be: 123
[removed] c. The query output will be: 125,124,124,123,123,124,125,126
[removed] d. The query output will be: 123,124,125

 

What is the difference between UNION and UNION ALL?

[removed] a. A UNION ALL operator will yield all rows of both relations, including duplicates
[removed] b. UNION yields unique rows
[removed] c. UNION eliminates duplicates rows
[removed] d. All of these choices are correct.

A(n) ______________ is a block of PL/SQL code that is automatically invoked by the DBMS upon the occurrence of a data manipulation event (INSERT, UPDATE or DELETE.)

[removed] a. stored procedure
[removed] b. trigger
[removed] c. view
[removed] d. function

__________________ means that the relations yield attributes with identical names and compatible data types.

[removed] a. duplicated
[removed] b. Set comparable
[removed] c. Union compatible
[removed] d. compatible-oriented

 

Which of the following a parts of the definition of a trigger?

[removed] a. The triggering level
[removed] b. The triggering action
[removed] c. The triggering timing
[removed] d. All of these choices are correct.

Which of the following relational set operators does NOT require that the relations are union-compatible?

[removed] a. INTERSECT
[removed] b. PROJECT
[removed] c. MINUS
[removed] d. UNION

Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the INTERSECT query?

[removed] a. The query output will be: John Cretchakov and Mary Chen
[removed] b. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen
[removed] c. The query output will be: John Cretchakov
[removed] d. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen

A _____________________ is a join that performs a relational product (or Cartesian product) of two tables.

[removed] a. CROSS JOIN
[removed] b. DUPLICATE JOIN
[removed] c. OUTER JOIN
[removed] d. INNER JOIN

What Oracle function should you use to calculate the number of days between the current date and January 25, 1999?

[removed] a. SELECT SYSDATE()-#25-JAN-1999#;
[removed] b. SELECT SYSDATE – TO_DATE(’25-JAN-1999′, ‘DD-MON-YYYY’) FROM DUAL;
[removed] c. SELECT SYSDATE FROM DUAL;
[removed] d. SELECT SYSDATE()-JAN-#25-1999#;

Using tables named T1 and T2, write a query example for a LEFT OUTER JOIN, assuming that T1 and T2 share a common column named C1.

[removed] a. SELECT * FROM T1 OUTER JOIN T2 ON T1.C1 = T2.C2;
[removed] b. SELECT * FROM T1 JOIN T2 ON T2.C1 = T2.C1;
[removed] c. SELECT * FROM T1 LEFT JOIN T2 ON T1.C1 = T2.C1;
[removed] d. SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1;

Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the MINUS query (specifically, SELECT * FROM EMPLOYEE MINUS SELECT * FROM EMPLOYEE_1)?

[removed] a. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen
[removed] b. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen
[removed] c. The query output will be: Mary Chen
[removed] d. The query output will be: Alice Cordoza and Anne McDonald

What Oracle function should you use to return the current date?

[removed] a. TODAY
[removed] b. SYSDATE
[removed] c. NOW

Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a UNION ALL query based on these two tables?

[removed] a. The query output will be: 125,124,124,123,123,124,125,126
[removed] b. The query output will be: 125,124,124,123
[removed] c. The query output will be: 125,124,123,126
[removed] d. The query output will be: 123

_________________is a term used to refer to SQL statements that are contained within an application programming language such as COBOL, C++, ASP, Java, or ColdFusion.

[removed] a. NoSQL
[removed] b. Embedded SQL
[removed] c. Dynamic SQL

The order of the operands (tables) matter in a _______ query.

[removed] a. PROJECT
[removed] b. MINUS
[removed] c. UNION
[removed] d. INTERSECT

Which of the following is true of Oracle sequences?

[removed] a. You can have as many sequences as you want and they are not tied to any particular table.
[removed] b. A sequence is a completely independent object.
[removed] c. The sequence-generated value is not tied to any field in any table and can, therefore, be used on any attribute in any table.

Which of the following is true of Oracle sequences?

[removed] a. You can have as many sequences as you want and they are not tied to any particular table.
[removed] b. A sequence is a completely independent object.
[removed] c. The sequence-generated value is not tied to any field in any table and can, therefore, be used on any attribute in any table.
[removed] d. All of these choices are correct.

A(n) ______________ is a named collection of procedural and SQL statements that are stored in the database and that can be used to encapsulate and represent business transactions.

[removed] a. trigger
[removed] b. stored procedure
[removed] c. view

Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION query?

[removed] a. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen
[removed] b. The query output will be: John Cretchakov and Mary Chen
[removed] c. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen
[removed] d. The query output will be: John Cretchakov

 

A subquery can appear in which of the following places in a SQL statement?

[removed] a. as part of a FROM clause,
[removed] b. to the right of a HAVING clause conditional operator,
[removed] c. in a EXISTS operator,
[removed] d. All of these choices are correct.

A __________ is a query (expressed as a SELECT statement) that is located inside another query and is normally executed first.

[removed] a. multi-query
[removed] b. subquery
[removed] c. outside query
[removed] d. special query

What string function (in Oracle) should you use to list the first three characters of a company’s EMP_LNAME values using a table named EMPLOYEE?

[removed] a. SELECT STR(EMP_LNAME, 1, 3) FROM EMPLOYEE;
[removed] b. SELECT STRING(EMP_LNAME, 1, 3) FROM EMPLOYEE;
[removed] c. SELECT SUBSTRING(EMP_LNAME, 1, 3) FROM EMPLOYEE;
[removed] d. SELECT SUBSTR(EMP_LNAME, 1, 3) FROM EMPLOYEE;

_____________ is a term used to describe an environment in which the SQL statement is not known in advance; instead, the SQL statement is generated at run time.

[removed] a. PL/SQL
[removed] b. Embedded SQL
[removed] c. Dynamic SQL
[removed] d. NoSQL

Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a MINUS query (VENDOR MINUS PRODUCT) based on these two tables?

[removed] a. The query output will be: 124
[removed] b. The query output will be: 125,124,123,126
[removed] c. The query output will be: NULL
[removed] d. The query output will be: 126

 

In the relational model, SQL operators are ________________ because they operate over entire sets of rows and columns (or tables) at once.

[removed] a. set-oriented
[removed] b. table-oriented
[removed] c. completeness-oriented

Which of the followings explains the difference between a regular subquery and a correlated subquery?

[removed] a. A correlated subquery will execute once for each row evaluated by the outer query
[removed] b. A regular subquery executes only once and the result is held for use by the outer query
[removed] c. A regular subquery executes before the outer query
[removed] d. All of these choices are correct.

If you do not specify a join condition when joining tables, the result will be a ______________ or PRODUCT operation.

[removed] a. CROSS JOIN
[removed] b. DUPLICATE JOIN
[removed] c. INNER JOIN
[removed] d. OUTER JOIN

A ______________ is a subquery that executes once for each row in the outer query; it will run the outer query first, and then it will run the inner subquery once for each row returned in the outer subquery.

[removed] a. outside subquery
[removed] b. uncorrelated subquery
[removed] c. correlated subquery

A relational view has which of the following characteristics?

[removed] a. views are dynamically updated
[removed] b. views may be used as the basis for reports or queries, just like tables
[removed] c. views provide a level of security in the database

A(n) ______________ is a special type of object that generates unique numeric values in ascending or descending order; it can be used to assign values to a primary key field in a table and it provides functionality similar to the Autonumber data type in MS Access.

[removed] a. sequence
[removed] b. key
[removed] c. instance
[removed] d. view

 

which of the following is NOT considered an advantage of a stored procedure?

[removed] a. Increase performance
[removed] b. Help reduce code duplication through code isolation and code sharing
[removed] c. Reduce network traffic
[removed] d. None of these choices are correct.

What are the types of results a subquery can return?

[removed] a. a list of values only
[removed] b. a single value, a list of values, or a virtual table
[removed] c. a single value only
[removed] d. a virtual table only

 

The SQL standard prescribes three different types of __________ operations: LEFT, RIGHT, and FULL.

[removed] a. OUTER join
[removed] b. INNER join
[removed] c. DUPLICATE join

A(n) __________________ is a type of JOIN operation that yields all rows with matching values in the join columns as well as all unmatched rows ( those without matching values in the join columns).

[removed] a. INNER JOIN
[removed] b. OUTER JOIN
[removed] c. CROSS JOIN
[removed] d. DUPLICATE JOIN

Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a UNION query based on these two tables?

[removed] a. The query output will be: 123,124,125,126
[removed] b. The query output will be: 125,124,124,123
[removed] c. The query output will be: 125,124,123,126
[removed] d. The query output will be: 123

Triggers are critical to proper database operation and management in which of the following ways?

[removed] a. add functionality by automating critical actions and providing appropriate warnings for remedial action.
[removed] b. used to enforce constraints that cannot be enforced at the DBMS design and implementation levels.
[removed] c. can be used to enforce referential integrity, to update table values, insert records in tables, and call other stored procedures.
[removed] d. All of these choices are correct.

 

A(n) ________ is a virtual table based on a SELECT query.

[removed] a. view
[removed] b. copy
[removed] c. schema

Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION ALL query?

[removed] a. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen
[removed] b. The query output will be: John Cretchakov and Mary Chen
[removed] c. The query output will be: John Cretchakov
[removed] d. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen

The ______________________ will yield all rows with matching values in the join columns, plus all of the unmatched rows from the right table.

[removed] a. CENTER OUTER JOIN
[removed] b. RIGHT OUTER JOIN
[removed] c. LEFT OUTER JOIN
[removed] d. FULL OUTER JOIN

 

Use the following scenario for questions from this chapter:

You have been given a database for a small charity used to track donations made to it. It has the following structure:

http://cnow.apps.ng.cengage.com/ilrn/books/cmdsin12h/images/activities/CH07_Donations_erd.png

and the following sample data in the tables:

Donor

Donor_Id Donor_
FirstName
Donor_
LastName
Donor_Address Donor_City Donor_State Donor_ZipCode Donor_Phone Donor_Email
101 James James 123 Mockingbird Place Peoria IL 55556 555-555-2342 jj343434@somewhere.com
175 Joseph Mays 54321 7th St Atlantic City NJ 15678 555-555-9877 jojo9@somewhere.com
207 Susan Ames 777 Main St Burlington KY 41098 555-555-3478 amess@elsewhere.com
303 Nancy Zornes P.O. Box 88776 Peoria IL 55578-8776 555-555-1255 zornes98@nowhere.com

 

ReceiptType

ReceiptType_Id ReceiptType_Description
C Cash
CK Check/Money Order
CC Credit Card
PD Payroll Deduction
A Art / Collectible
I In-kind

 

Fund

Fund_Id Fund_Name
G General Operation
S Scholarship
B Building Maintenance
C Capital Campaign

 

Receipt

Receipt_Id Donor_Id Receipt_Date ReceiptType_Id Fund_Id Receipt_Amount Receipt_Description
1001 101 2015-01-05 CK G 100
1002 207 2015-01-05 C S 250 For: Virginia Wolfe Wilde
1003 207 2015-01-05 C B 100
1004 175 2015-01-06 CC G 137.5 In Memory of Bob
1005 101 2015-02-14 CK G 100
1006 175 2015-02-20 A C 15000000 Picasso Painting

 

For reporting purposes the client would like you to create a temporary table called “FundSummary” that contains the fund id, donor id, the number of receipts (donations) made to the fund by a donor, and the total of the receipts and the donor. The new table from the sample data would look like:

B 207 1 100
C 175 1 15000000
G 101 2 200
G 175 1 137.5
S 207 1 250

 

Fill in the blanks of the SQL Statements:

Fund_Id VARCHAR(10)                   ,
Donor_id VARCHAR(10)                   ,
Count_Of_Receipts INTEGER,
Total_Receipts DECIMAL(10,2),
(Fund_Id, Donor_Id),
FOREIGN KEY (Donor_Id) REFERENCES          (         ),
FOREIGN KEY (         ) REFERENCES          (Fund_Id) );

INSERT INTO FundSummary
Fund_Id, Donor_Id,          (Receipt_Id),          (         )
FROM Receipt
Fund_Id,          ;

 

Fill in the blanks with words that would best complete the passage.

donor_id

FundSummary

receipt_amount

(

not

null

select

sum

donor_id

primary

by

group

CREATE

key

donor

not

fund_id

fund

count

null

TABLE

Use the following scenario for questions from this chapter:

You have been given a database for a small charity used to track donations made to it. It has the following structure:

http://cnow.apps.ng.cengage.com/ilrn/books/cmdsin12h/images/activities/CH07_Donations_erd.png

and the following sample data in the tables:

Donor

Donor_Id Donor_
FirstName
Donor_
LastName
Donor_Address Donor_City Donor_State Donor_ZipCode Donor_Phone Donor_Email
101 James James 123 Mockingbird Place Peoria IL 55556 555-555-2342 jj343434@somewhere.com
175 Joseph Mays 54321 7th St Atlantic City NJ 15678 555-555-9877 jojo9@somewhere.com
207 Susan Ames 777 Main St Burlington KY 41098 555-555-3478 amess@elsewhere.com
303 Nancy Zornes P.O. Box 88776 Peoria IL 55578-8776 555-555-1255 zornes98@nowhere.com

 

ReceiptType

ReceiptType_Id ReceiptType_Description
C Cash
CK Check/Money Order
CC Credit Card
PD Payroll Deduction
A Art / Collectible
I In-kind

 

Fund

Fund_Id Fund_Name
G General Operation
S Scholarship
B Building Maintenance
C Capital Campaign

 

Receipt_Id Donor_Id Receipt_Date ReceiptType_Id Fund_Id Receipt_Amount Receipt_Description
1001 101 2015-01-05 CK G 100
1002 207 2015-01-05 C S 250 For: Virginia Wolfe Wilde
1003 207 2015-01-05 C B 100
1004 175 2015-01-06 CC G 137.5 In Memory of Bob
1005 101 2015-02-14 CK G 100
1006 175 2015-02-20 A C 15000000 Picasso Painting

Receipt

 

The client wants a listing of donor id, last name, first name, receipt date, type, and amount for all receipts greater than $100.00. The client wants the result sorted by donor last name, first name, and the donation date. The query result from the sample data would look like:

207 Ames Susan 2015-01-05 C 250
175 Mays Joseph 2015-01-06 CC 137.5
175 Mays Joseph 2015-02-20 A 15000000

 

Fill in the blanks of the SQL Statement:

Donor.          , Donor_LastName, Donor_FirstName, Receipt_Date,
Receipt.ReceiptType_Id, Receipt_Amount
Receipt
Receipt.Donor_Id =
Receipt_Amount          100.00
Donor_LastName, Donor_FirstName, Receipt_Date;

 

Fill in the blanks with words that would best complete the passage.

from

>

donor.donor_id

select

donor

where

and

order

by

donor_id

,

 

The problems for this chapter use a database for a simple department store that sells items to customers and wants to keep track of the invoices, the selling price (if an item is on sale), and the sales tax (7%) to be collected on some items. Every customer and invoice are assigned unique numbers. All items have a Universal Product Code (UPC) number and bar-code assigned to each unique item. Food and non-carbonated beverages are not taxed, but clothing, home goods, and most other items are.

The structure of the tables are described in the following crows foot ERD:

http://cnow.apps.ng.cengage.com/ilrn/books/cmdsin12h/images/activities/CH08_Invoice.png

Sample Data for the tables follow:

Customer

Customer_Id Customer_
FirstName
Customer_
LastName
Customer_
Address
Customer_
City
Customer_
State
Customer_
ZipCode
Customer_
Phone
Customer_Email
342 Linda Spangler 2323 Roanoke Pk Floyd VA 24987 555-555-5646 linda5646@nowhere.com
505 Rodney Ray 12399 27th Ave New York NY 10097 555-555-0909 rayray7@somewhere.com
776 Nancy Reno P.O.Box 98 Carter City KY 41155 555-555-2342 puppylove8@elsewhere.com
987 Gustov Jones 333 East Main St Jamestown VA 23099 555-555-9876 gustov99@somewhere.com

 

ItemType

ItemType_Id ItemType_Description
W Woman’s Clothing
M Men’s Clothing
WA Woman’s Accessories
MA Men’s Accessories
A General Accessories
O Other

 

ItemSize

ItemSize_Id ItemSize_Description
XS Extra Small
S Small
M Medium
L Large
XL Extra Large

 

Item

UPC Item_Description ItemType_Id ItemSize_Id Item_Price Item_Taxable
012345234569 Cream Blouse W S 29.95 1
012345234576 Cream Blouse W M 29.95 1
012345234588 Cream Blouse W L 29.95 1
012345234590 Cream Blouse W XL 29.95 1
012345234468 Blue Blouse W S 29.95 1
012345234475 Blue Blouse W M 29.95 1
012345234491 Blue Blouse W XL 29.95 1
012345224889 12 Inch Pearl Necklace WA 345.95 1
012345224126 10 Inch Pearl Necklace WA 298.95 1
012345334678 Explorer Cargo Shorts M S 33.45 1
012345334734 Explorer Cargo Shorts M M 33.45 1
012345334795 Explorer Cargo Shorts M L 33.45 1
012345334889 Explorer Cargo Shorts M XL 33.45 1
012345335101 Pink Silk Tie MA 67.55 1
012345335303 Pink and Green Silk Tie MA 67.55 1
012345999001 Yummy Bottled Water O 1.29 0

 

Invoice

Invoice_Number Customer_Id Invoice_Date Invoice_Taxable Invoice_NonTaxable Invoice_SalesTax Invoice_Total
10101 987 2015-07-27 29.95 2.58 2.1 34.63
10102 505 2015-07-27 33.45 0 2.34 35.79
10107 505 2015-07-28 59.99 1.29 4.2 65.48
10111 342 2015-07-28 262.89 0 18.4 281.29

 

InvoiceDetail

Invoice_Number UPC Detail_Quantity Detail_RegularPrice Detail_SellingPrice
10101 012345334795 1 33.45 29.95
10101 012345999001 2 1.29 1.29
10102 012345334889 1 33.45 33.45
10107 012345335303 1 67.55 59.99
10107 012345999001 1 1.29 1.29
10111 012345234576 1 29.95 29.95
10111 012345234475 1 29.95 29.95
10111 012345224126 1 298.95 202.99

Suppose that we have a second table with vendor information (sample is below) in it and that we want to create a single telephone directory with both vendor and customer information in it.

Vendor_Id Vendor_CompanyName Vendor_Address Vendor_City Vendor_State Vendor_ZipCode Vendor_Phone Vendor_Email
101 QRS Importers 12345 Dock St San Fransisco CA 97654 555-544-4444 bob@importeverythingsf.com
505 ABC Supply 505 Euclid Ave Lexington KY 40505 555-505-0505 sales@abcsupplylex.com

 

The phone directory should contain the state, a name column with either the customer last name and first name concatenated with a comma or the vendor company name, city, and phone number. Output should be sorted by state then by name. Your results should look like:

State Name City Phone
CA QRS Importers San Fransisco 555-544-4444
KY ABC Supply Lexington 555-505-0505
KY Reno, Nancy Carter City 555-555-2342
NY Ray, Rodney New York 555-555-0909
VA Jones, Gustov Jamestown 555-555-9876
VA Spangler, Linda Floyd 555-555-5646

Fill in the blanks (remember a blank, drop “blank” as answer)

SELECT          ,          ,          ,
FROM ( SELECT          || ‘, ‘ || Customer_FirstName AS Name,
Customer_City                    , Customer_State AS State, Customer_Phone AS Phone
FROM Customer

SELECT Vendor_CompanyName AS Name,
Vendor_City AS City, Vendor_State AS State, Vendor_Phone AS Phone
FROM Vendor          )
State, Name;

Order now and get 10% discount on all orders above $50 now!!The professional are ready and willing handle your assignment.

ORDER NOW »»