Customer Transaction Table – CUSTOMER_TXNS Column Name Description Type Account_id Account identifier Integer Txn_timestamp Time of...

Customer Transaction Table – CUSTOMER_TXNS

Column Name	Description	Type
Account_id	Account identifier	Integer
Txn_timestamp	Time of transaction (UTC)	Timestamp
Product_id	The id of the product purchased	Integer
Txn_Amt	The revenue amount of the transaction	Float
Txn_Qty	The number of items purchased	Integer

NOTE: Customer Transaction table has multiple records per account_id.

Customer Master Table – CUSTOMER_MSTR

Column Name	Description	Type
Account_id	Account identifier	Integer
Country	Country Code	Character(3)
Address	Address of the customer	Character(64)
Registerd_Dt	Date the account id was first used	Date
Tier	Account Tier	Integer

NOTE: Customer Master table has one record per account_id.

Table CUSTOMER_MSTR has 100 records and table CUSTOMER_TXNS has 500 records. What is the maximum number of records you can get in a result set if you did the following types of joins? • INNER JOIN on account_id • LEFT JOIN CUSTOMER_MSTR with CUSTOMER_TXNS on account_id • UNION • UNION ALL Feel free to provide a more detailed explanation with assumptions if you wish.

Solutions

Expert Solution

The maximum number of records in the record set on performing the following type of joins are:

INNER JOIN on account_id

Inner join selects only records that have matching values in both tables, to get the maximum number of records lets assume that all the records in the Customer Transaction table (containing 500 records) have a account_id value that is present in the Customer Master table (containing 100 records). Therefore the maximum number of records obtained will be 500.

LEFT JOIN CUSTOMER_MSTR with CUSTOMER_TXNS on account_id

Left join selects all the records from the left table (in this case the CUSTOMER_MSTR table containing 100 records) and the matched records from the right table (in this case the CUSTOMER_TXNS table containing 500 records). To get the maximum number of records lets assume that all the records in the CUSTOMER_TXNS table have a account_id value present in the CUSTOMER_MSTR table. Therefore the maximum number of records will be 500 as all the account id's from the customer taxation table successfully match with the customer master.

UNION

Union operator is used to combine the result set of two or more SQL queries and display only the distinct values. But all the queries must have the same number of columns and the datatype of the columns in both queries must be similar. In this case, as the datatype of the columns in both the queries are different the query will result in a error. Therefore the maximum number of records returned will be 0.

UNION ALL

Similar to the Union operator, union all operator can also be used to combine the result set of two or more SQL queries but it displays the duplicate values also. Similar to the Union operator all the queries must have the same number of columns and the datatype of the columns in both queries must be similar. In this case, as the datatype of the columns in both the queries are different this query will too result in a error. Therefore the maximum number of records returned will be 0.

venereology answered 11 months ago

Next > < Previous

Related Solutions

February 1st transaction is completed as an example: Only include the letter of the account not the account name.

ASSETS LIABILITIES & STOCKHOLDERS’ EQUITY INCOME STATEMENT ACCOUNTS A. Cash F. Accounts Payable K. Service Revenue B. Accounts Receivable G. Salaries Payable L. Advertising expense C. Supplies H. Deferred revenue M. Rent Expense D. Prepaid advertising I. Notes Payable N. Salaries/Wage expense E. Equipment J. Retained Earnings O. Utilities expense February 1st transaction is completed as an example: Only include the letter of the account not the account name. Date Transaction AMOUNT February 01 (ex) Paid an Accounts...

For each transaction described below, write the name of the account that would be debited and...

For each transaction described below, write the name of the account that would be debited and the name of the account that would be credited when recording the transaction. No dollar amounts or explanations are required. Transaction To Record the Transaction in a Journal Entry, Name the Accounts that you would debit and credit Account to DEBIT Account to CREDIT Company purchased inventory on account Company sold/provided services for cash Company recorded depreciation on equipment for the period Company paid...

For each transaction described below, write the name of the account that would be debited and...

For each transaction described below, write the name of the account that would be debited and the name of the account that would be credited when recording the transaction. Company purchased inventory on account Company sold/provided services for cash Company recorded depreciation on equipment for the period Company paid NEXT YEAR’s insurance premium Company paid vendor for a prior purchase on account Company paid utility bill Company received cash deposit from customer for work to be performed in the future...

Complete the table by dragging each structure name or description into the appropriate place

Date Transaction Description July 1 Began business by making a deposit in a company bank account...

Date Transaction Description July 1 Began business by making a deposit in a company bank account of $40,000, in exchange for 4,000 shares of $10 par value common stock. July 1 Paid the premium on a 1-year insurance policy, $4,800. July 1 Paid the current month's store rent expense, $3,600. July 6 Purchased repair equipment from Paul's Pool Equipment Company, $7,800. Paid $600 down and the balance was placed on account. July 8 Purchased repair supplies from Mary's Repair Company...

Normalize the following table into 1NF, 2NF and 3NF. Salesperson name Sales area Customer Number Customer...

Normalize the following table into 1NF, 2NF and 3NF. Salesperson name Sales area Customer Number Customer Name Warehouse Number Warehouse Location Sales Amount 76458 Hariharan East 17658 Asani 3 Govandi 13545 17645 Madhuram 6 Vasai 10600 17623 Sumit 6 Vasai 9700 76567 Dheerendra North 18190 Dhanram 4 Thane 11560 18173 Toshi 4 Thane 2590 18150 Ganesh 1 Kalyan 8090

TABLE C6-2: Customer Satisfaction Customer Number Customer Name Satisfaction Level 1 Anderson Very high 2 Angero...

TABLE C6-2: Customer Satisfaction Customer Number Customer Name Satisfaction Level 1 Anderson Very high 2 Angero high 3 Ball medium 4 Bobak low 5 Chontos high 6 Detley very low 7 Hetfield very high 8 Iruja medium 9 Jamesson high 10 Kemp medium 11 Lehmann high 12 Lee high 13 Lewins low 14 Luo very high 15 Madras very high 16 Morris low 17 Mulder medium 18 Ngozichi low 19 Nickens very high 20 Poteau very high 21 Sakomoto medium...

7) Name two different types of soil degradation. For each type give a description of how...

7) Name two different types of soil degradation. For each type give a description of how or why it happens (what causes it) AND what the negative effects are (why is it bad).

Morrisom National Bank has requested an analysis of checking account profitability by customer type. Customers are...

Morrisom National Bank has requested an analysis of checking account profitability by customer type. Customers are categorized according to the size of their account: low balances, medium balances, and high balances. The activities associated with the three different customer categories and their associated annual costs are as follows: Opening and closing accounts $300,000 Issuing monthly statements 450,000 Processing transactions 3,075,000 Customer inquiries 600,000 Providing automatic teller machine (ATM) services 1,680,000 Total cost $6,105,000 Additional data concerning the usage of the...

Access medline University account. Click on catalog. Using the search box type in the name of...

Access medline University account. Click on catalog. Using the search box type in the name of this module. A. Falls prevention. Tools and strategies _ 20_393882. Create a teaching plan for a patient or coworker,where the learning module you just completed would be appropriate. The teaching plan must include; 1.The importance of the topic being discussed. 2.Risk factors for harm associated with this issue. 3.Strategies to prevent or ensure that appropriate interventions are in place.. Add/Modify the teaching plan to...

Subjects