Note: This question is part of a series of questions that use the same or similar answer choices. An
answer choice may be correct for more than one question in the series. Each question is independent
of the other questions in this series. Information and details provided in a question apply to that
question.
You have a database for a banking system. The database has two tables named tblDepositAcct and
tblLoanAcct that store deposit and loan accounts, respectively. Both tables contain the following columns:
You need to run a query to find the total number of customers who have both deposit and loan accounts.
Which Transact-SQL statement should you run?
A.
SELECT COUNT(*)
FROM (SELECT AcctNo
FROM tblDepositAcct
INTERSECT
SELECT AcctNo
FROM tblLoanAcct) R
B.
SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
UNION
SELECT CustNo
FROM tblLoanAcct) R
C.
SELECT COUNT(*)
FROM (SELECT CustNoFROM tblDepositAcct
UNION ALL
SELECT CustNo
FROM tblLoanAcct) R
D.
SELECT COUNT (DISTINCT D.CustNo)
FROM tblDepositAcctD, tblLoanAcct L
WHERE D.CustNo = L.CustNo
E.
SELECT COUNT(DISTINCT L.CustNo)
FROM tblDepositAcct D
RIGHT JOIN tblLoanAcct L ON D.CustNo = L.CustNo
WHERE D.CustNo IS NULL
F.
SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
EXCEPT
SELECT CustNo
FROM tblLoanAcct) R
G.
SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo
WHERE D.CustNo IS NULL OR L.CustNo IS NULL
H.
SELECT COUNT(*)
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo
Explanation:
The SQL INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only
returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will
be omitted from the INTERSECT results.
https://www.techonthenet.com/sql/intersect.php
WRONG answer is SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo
WHERE D.CustNo IS NULL OR L.CustNo IS NULL
5th/12/2017 New 70-761 Questions:
https://drive.google.com/drive/folders/0B75b5xYLjSSNZG9yTW9reVdkZG8?usp=sharing
Could you please email me the latest 761 questions writing 1st week of Jan.
Email – [email protected]
A is the correct answer.
INTERSECT returns distinct rows that are output by both the left and right input queries operator.
Why would I expect the loan account number to equal the deposit account number? If A is the correct answer then the column should not be AccNo, but CustNo. It is the customer number CustNo that must match between the tables tblDepositAcct and tblLoanAccnt, if the customer has both a loan account and a deposit account. AcctNo need not match. The correct answer is actually D, although it is using the non recommended NON ANSI join.
ANSWER IS A , i confirm
I have latest questions and answer
[email protected]
Can you send me the question to darknessed16@ gmail.com thx
D is the answer correct.
A – is not correct because the column that must be part of the operator INTERSECT must be CustNo and not AcctNo. A customer can have many accounts in both tables, the query ask for the numbers of customers who have both accounts.
B – is not correct, the operator UNION used the column CustNo but this operator specifies that multiple result sets are to be combined and returned as a single result set. Then, you can have a result set with customers that have accounts of the type Deposit and not of the Loan, or vice versa.
C – is not correct, the operator UNION ALL used the column CustNo, the result set can include duplicates.
D – is correct because the clause WHERE modifies the cross join defined between the two tables in the clause FROM. COUNT (DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, non null values.
E – is not correct because the RIGHT JOIN and the clause WHERE exclude customers with Deposit accounts.
F – is not correct because EXCEPT operator excludes customers with Loan accounts.
G – is not correct because in addition to the FULL JOIN, the clause WHERE identifies customers with account of one type and not the other type, for example, customer with deposit account and no loan account.
H – is not correct due FULL JOIN
Today passed in second attempt using Sam peter dumps.
New question was introduced in the exam .
Thanx a lot Sam peter for your vaild dumps
Your network contains an Active Directory domain named contoso.com.
You have a Group Policy object (GPO) named GPO1. GPO1 is linked to an organizational unit (OU) named
OU1.
GPO1 contains several corporate desktop restrictions that apply to all computers.
You plan to deploy a printer to the computers in OU1.
You need to ensure that any user who signs in to a computer that runs Windows 10 in OU1 receives the new
printer. All of the computers in OU1 must continue to apply the corporate desktop restrictions from GPO1.
What should you configure?
A.
a user preference and a WMI filter on GPO1.
B.
a computer preference that uses item-level targeting
C.
a computer preference and WMI filter on GPO1
D.
a user preference that uses item-level targeting
Why (D) ? Why not computer 0tem-level targeting ?
hi sam can you send the latest dumps in the mail id [email protected]
Passed yesterday. 48 questions. Used Microsoft Press exam ref and, to train query writing skills, I used sql-ex.ru site (unfortunately there is only russian version of this site).
About five questions require you to write your own script.
D is CORRECT!
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblLoanAcct’)
BEGIN
DROP TABLE dbo.tblLoanAcct;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblDepositAcct’)
BEGIN
DROP TABLE dbo.tblDepositAcct;
END;
CREATE TABLE tblLoanAcct (
AcctNo int PRIMARY KEY CLUSTERED,
CustNo int NOT NULL,
ProdCode varchar(3) Not NULL
)
CREATE TABLE tblDepositAcct (
AcctNo int PRIMARY KEY CLUSTERED,
CustNo int NOT NULL,
ProdCode varchar(3) Not NULL
)
INSERT INTO tblLoanAcct VALUES
(10001, 125, ‘Loa’ ),
(10002, 101, ‘Loa’ ),
(10003, 185, ‘Loa’ ),
(10004, 165, ‘Loa’ ),
(10005, 142, ‘Loa’ ),
(10006, 144, ‘Loa’ ),
(10007, 152, ‘Loa’ )
INSERT INTO tblDepositAcct VALUES
(20001, 165, ‘Dep’ ),
(20002, 652, ‘Dep’ ),
(20003, 142, ‘Dep’ ),
(20004, 111, ‘Dep’ ),
(20005, 101, ‘Dep’ )
select * from tblLoanAcct
select * from tblDepositAcct
— 3 customers have both loan account and deposit account
A.
SELECT COUNT(*)
FROM (SELECT AcctNo
FROM tblDepositAcct
INTERSECT
SELECT AcctNo
FROM tblLoanAcct) R
B.
SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
UNION
SELECT CustNo
FROM tblLoanAcct) R
C.
SELECT COUNT(*)
FROM (SELECT CustNoFROM tblDepositAcct
UNION ALL
SELECT CustNo
FROM tblLoanAcct) R
D.
SELECT COUNT (DISTINCT D.CustNo)
FROM tblDepositAcct D, tblLoanAcct L
WHERE D.CustNo = L.CustNo
E.
SELECT COUNT(DISTINCT L.CustNo)
FROM tblDepositAcct D
RIGHT JOIN tblLoanAcct L ON D.CustNo = L.CustNo
WHERE D.CustNo IS NULL
F.
SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
EXCEPT
SELECT CustNo
FROM tblLoanAcct) R
G.
SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo
WHERE D.CustNo IS NULL OR L.CustNo IS NULL
H.
SELECT COUNT(*)
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo