Question 13

1. Using the AP database. Write a SELECT statement that returns: AccountDescirption, LineItemCount, and LineItemSum. LineItemCount is the number of entries in the InvoiceLineItems table that have that AccountNo. LineItemSum is the sum of the InvoiceLineItemAmount column for that AccountNo. Filter the result set to include only those rows with LineItemCount greater than 1 and further filter for invoices dated from December 1, 2011 to February 29, 2012. Group the result set by AccountDescription and sort it by descending LineItemCount.

		SELECT GLAccounts.AccountDescription, COUNT(*) AS LineItemCount, SUM(InvoiceLineItemAmount) AS LineItemSum FROM GLAccounts JOIN InvoiceLineItems ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo JOIN Invoices ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID WHERE InvoiceDate BETWEEN '2011-12-01' AND '2012-02-29' GROUP BY GLAccounts.AccountDescription HAVING COUNT(*) > 1 ORDER BY LineItemCount DESC;
		SELECT AccountDescription, InvoiceDate, Count (*) AS LineItemCount , SUM (InvoiceLineItemAmount) AS LineItemSum FROM GLAccounts JOIN InvoiceLineItems ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo JOIN Invoices ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID GROUP BY AccountDescription,InvoiceDate HAVING COUNT (*) > 1 AND InvoiceDate BETWEEN 12/1/2011 AND 02/29/2012 ORDER BY LineItemCount DESC
		SELECT VendorName, InvoiceLineItems.InvoiceID, InvoiceSequence, InvoiceLineItemAmount FROM InvoiceLineItems JOIN Invoices ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceLineItems.InvoiceID IN (SELECT InvoiceID FROM InvoiceLineItems WHERE InvoiceSequence > 1);
		Select AccountDescription, LineItemCount, LineItemSum, from Invoice. Where LIneItemCount > 1, and invoices from 12,1,2011 to 2,29,2012 Group by aacountDescription Sort by max( LineItemCount)

Question 14

1. Using the AP database. Write a SELECT statement that returns: VendorName, AccountDesciption, LineItemCount, and LineItemSum. LineItemCount is the row count and LineItemSum is the sum of the InvoiceLineItemAmount column. For each vendor and account, return the number and sum of line items, sorted first by vendor, then by account description.

		SELECT VendorName, AccountDescription, COUNT(*) AS LineItemCount , SUM(InvoiceLineItemAmount) AS LineItemSum FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID JOIN InvoiceLineItems ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID JOIN GLAccounts ON InvoiceLineItems.AccountNo = GLAccounts.AccountNo GROUP BY VendorName, AccountDescription ORDER BY VendorName, AccountDescription;
		SELECT Vendors.VendorName, GLAccounts.AccountDescription, COUNT(InvoiceLineItems.AccountNo) AS LineItemCount, SUM(InvoiceLineItems_1.InvoiceLineItemAmount) AS LineItemSum FROM Vendors INNER JOIN GLAccounts ON Vendors.DefaultAccountNo = GLAccounts.AccountNo INNER JOIN InvoiceLineItems ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo INNER JOIN InvoiceLineItems AS InvoiceLineItems_1 ON GLAccounts.AccountNo = InvoiceLineItems_1.AccountNo GROUP BY GROUPING SETS(Vendors.VendorName, GLAccounts.AccountDescription) ORDER BY Vendors.VendorName DESC, GLAccounts.AccountDescription
		SELECT VendorName, AccountDescription, COUNT(Invoices.InvoiceID) AS LineItemCount, SUM(InvoiceLineItemAmount) AS LineItemSum FROM Vendors JOIN Invoices ON Vendors.VendorID=Invoices.VendorID JOIN InvoiceLineItems ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID JOIN GLAccounts ON GLAccounts.AccountNo=InvoiceLineItems.AccountNo GROUP BY VendorName, AccountDescription with ROLLUP ORDER BY VendorName, AccountDescription
		SELECT Vendors.VendorName, GLAccounts.AccountDescription, COUNT(*) AS LineItemCount, SUM(InvoiceLineItems.InvoiceLineItemAmount) AS LineItemSum FROM Vendors JOIN Invoices ON Vendors.VendorID=Invoices.VendorID RIGHT JOIN InvoiceLineItems ON Invoices.InvoiceID=InvoiceLineItems.InvoiceID RIGHT JOIN GLAccounts ON GLAccounts.AccountNo=InvoiceLineItems.AccountNo GROUP BY VendorName, AccountDescription ORDER BY VendorName, AccountDescription;

Question 15

1. Write a Select statement that returns four columns based upon the InvoiceTotal column of the Invoices table: Use the Cast function to return the InvoiceTotal as data type decimal with 2 digits to the right of the decimal point. Use the Cast function to return the InvoiceTotal as data type varchar. Use the Convert function to return the InvoiceTotal as data type decimal with 2 digits to the right of the decimal point. Use the Convert function to return the InvoiceTotal as data type varchar using a style to will include commas (,) as the hundreds separator.

		SELECT InvoiceTotal ,CAST(InvoiceTotal AS decimal(5,2)) AS CastDecimalTotal ,CAST (InvoiceTotal AS varchar) AS CastVarcharTotal ,CONVERT (decimal (5,2), InvoiceTotal) AS ConvertDecimalTotal ,CONVERT (varchar, InvoiceTotal, 1) AS ConvertDecimalCommas FROM Invoices
		SELECT InvoiceTotal CAST(InvoiceTotal AS decimal(2)) AS decimalInvoiceTotal, CAST(InvoiceTotal AS varchar) AS varcharInvoiceTotal, CONVERT(decimal(2), InvoiceTotal, 0) AS decimalInvoiceTotal2, CONVERT(varchar, InvoiceTotal, 0) AS varcharInvoiceTotal2 FROM Invoices;
		SELECT InvoiceTotal, CAST(InvoiceTotal AS decimal) AS InvoiceDecimal, CAST(InvoiceTotal AS varchar) AS InvoiceVarchar, CONVERT (decimal, InvoiceTotal, 0) AS InvoiceDecimal, CONVERT (varchar, InvoiceTotal) AS VarcharTotal FROM Invoices;
		SELECT InvoiceTotal ,CAST(InvoiceTotal AS decimal(17,2)) AS CastDecimalTotal ,CAST (InvoiceTotal AS varchar) AS CastVarcharTotal ,CONVERT (decimal (17,2), InvoiceTotal) AS ConvertDecimalTotal ,CONVERT (varchar, InvoiceTotal, 1) AS ConvertDecimalCommas FROM Invoices

Question 16

1. An execution plan shows the warning 'No Join Predicate'. What does this mean?

		That an outer join is being done.
		That an inner join is being done.
		That a cross join is being done.
		That no index is being used.

Question 17

1. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

True

False

Question 18

1. A unique constraint may contain one null value.

True

False

Question 19

1. Bring back all employee information for employees whose regions is null.

		SELECT * FROM Employees WHERE Region Is Null
		SELECT * FROM Employees WHERE Region = Null
		SELECT * FROM Employees WHERE Region ''
		SELECT * FROM Employees WHERE Region IsNull

Question 20

1. I want a query that returns the first initial of the FirstName and the full LastName of employees concatenated together.

Which of the following queries does this correctly?

		SELECT Cast(FirstName,1) + ' ' + LastName FROM Employees
		SELECT LTrim(FirstName,1) + ' ' + LastName FROM Employees
		SELECT First(FirstName,1) + ' ' + LastName FROM Employees
		SELECT Left(FirstName,1) + ' ' + LastName FROM Employees

Question 21

1. In the Northwind database, Reports To column in the Employees table can be considered a _____.

		Primary Key
		Secondary Key
		Foreign Key
		Candidate Key

Question 22

1. What is the primary key of the [Order Details] table?

		There is no primary key
		Order ID and ProductID together
		OrderID only
		ProductID only

Question 23

1. What is wrong with the following query?

SELECT OrderID, ContactName, OrderDate, Freight

FROM Customers INNER JOIN Orders

		Nothing is wrong with this query.
		The Inner Join is not complete.
		Orders needs to be in brackets and it needs an ON statement.
		ContactName should be CustomerName.

Question 24

1. What kind of join is created in the the following query.

SELECT Orders.OrderID , Orders.OrderDate

, Customers.CompanyName, Orders.Freight

FROM Customers, Orders

		inner join
		left join
		cross join
		outer join

Question 25

1. Write a query that returns the employee ID, last name, and the employees region ID and region description.

Choose the best query solution.

		SELECT Distinct Employees.EmployeeID , Employees.LastName , Region.RegionID , Region.RegionDescription FROM Employees INNER JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID INNER JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID INNER JOIN Region ON Territories.RegionID = Region.RegionID ORDER BY Employees.LastName
		SELECT [Employees].EmployeeID, [Employees].Region, Region.RegionDescription FROM Employees, Region
		SELECT Employees.EmployeeID, Employees.LastName, Region.RegionID, Region.RegionDescription FROM Employees INNER JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID INNER JOIN Region ON Territories.RegionID = Region.RegionID ORDER BY Employees.LastName
		SELECT Employees.EmployeeID , Employees.LastName , Region.RegionID , Region.RegionDescription FROM Employees CROSS JOIN Region Please help ASAP! Fast thumbs up