Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SelectQueriesChap3

memorize.ai (lvl 286)

(0)

7 cards

()

Section 1

Preview this deck

Write a SELECT statement that determines whether the PaymentDate column of the Invoices table has any invalid values. To be valid, PaymentDate must be a null value if there's a balance due and a non-null value if there's no balance due. Code a compound condition in the WHERE clause that tests for these conditions.

Front

1 / 7

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

4 years ago

Date created

Mar 1, 2020

Cards (7)

Section 1

(7 cards)

Write a SELECT statement that determines whether the PaymentDate column of the Invoices table has any invalid values. To be valid, PaymentDate must be a null value if there's a balance due and a non-null value if there's no balance due. Code a compound condition in the WHERE clause that tests for these conditions.

Front

SELECT * FROM Invoices WHERE ((InvoiceTotal - PaymentTotal - CreditTotal <= 0) AND PaymentDate IS NULL) OR ((InvoiceTotal - PaymentTotal - CreditTotal > 0) AND PaymentDate IS NOT NULL);

Back

Write a SELECT statement that returns three columns: InvoiceTotal From the invoices table 10% 10% of the value of InvoiceTotal Plus 10% The value of InvoiceTotal plus 10% (For example, if InvoiceTotal is 100.0000, 10% is 10.0000, and Plus 10% is 110.0000.) Only return those rows with a balance due greater than 1000. Sort the result set by InvoiceTotal, with the largets invoice first.

Front

SELECT InvoiceTotal, InvoiceTotal / 10 AS [10%], InvoiceTotal * 1.1 AS [Plus 10%] FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 1000 ORDER BY InvoiceTotal DESC;

Back

Modify the solution to exercise 3 to filter for contacts whose last name begins with the letter A, B, C, or E.

Front

SELECT VendorContactLName + ', ' + VendorContactFName AS [Full Name] FROM Vendors WHERE VendorContactLName LIKE '[A-C,E]%' --Also acceptable: --WHERE VendorContactLName LIKE '[A-E]%' AND -- VendorContactLName NOT LIKE 'D%' ORDER BY VendorContactLName, VendorContactFName;

Back

Write a SELECT statement that returns one column from the Vendors table named Full Name. Create this column from the VendorContactFName and VendorContactLName columns. Format it as follows: last name, comma, first name (for example, "Doe, John"). Sort the result set by last name, then by first name.

Front

SELECT VendorContactLName + ', ' + VendorContactFName AS [Full Name] FROM Vendors ORDER BY VendorContactLName, VendorContactFName;

Back

Write a SELECT statement that returns four columns from the Invoices table, named Number, Total, Credits, and Balance: Number Column alias for the InvoiceNumber column Total Column alias for the InvoiceTotal column Credits Sum of the PaymentTotal and CreditTotal columns Balance InvoiceTotal minus the sum of PaymentTotal and CreditTotal Use the AS keyword to assign column aliases.

Front

SELECT InvoiceNumber AS Number, InvoiceTotal AS Total, PaymentTotal + CreditTotal AS Credits, InvoiceTotal - (PaymentTotal + CreditTotal) AS Balance FROM Invoices;

Back

Modify the solution to exercise 2 to to filter for invoices with an InvoiceTotal that's greater than or equal to $500 but less than or equal to $10,000.

Front

SELECT InvoiceNumber AS Number, InvoiceTotal AS Total, PaymentTotal + CreditTotal AS Credits, InvoiceTotal - (PaymentTotal + CreditTotal) AS Balance FROM Invoices WHERE InvoiceTotal BETWEEN 500 AND 10000; --Also acceptable: --WHERE InvoiceTotal >= 500 AND InvoiceTotal <= 10000;

Back

Write a SELECT statement that returns three columns from the Vendors table: VendorContactFName, VendorContactLName, and VendorName. Sort the result set by last name, then by first name.

Front

SELECT VendorContactFName, VendorContactLName, VendorName FROM Vendors ORDER BY VendorContactLName, VendorContactFName;

Back