Question

write a select statement that returns vendor contact last name, vendor contact first name, and full name that joins the vendor contact last name and vendor contact first name columns. format this full name column with the last name, a comma, a space, and the first name like sample doe, john sort the result set by last name and then first name in descending order. return only the contacts whose last name begins with the letters a, b, c, or e.

Answer 1

The answer includes a SQL query to obtain a list of vendor contacts' last names, first names, and formatted full names, sorted and filtered according to the given criteria.

Step-by-step explanation:

The student is requesting a SQL select statement to retrieve specific data from a database consisting of vendor contacts. The returned data should include the vendor contact's last name, first name, and a concatenated full name in a particular format. Moreover, the results need to be sorted by last name and first name in descending order while filtering to include only those contacts whose last names start with 'A', 'B', 'C', or 'E'. The SQL query that can fulfill this request is as follows:

SELECT
LastName AS 'Last Name',
FirstName AS 'First Name',
LastName + ', ' + FirstName AS 'Full Name'
FROM
VendorContacts
WHERE
LastName LIKE 'A%' OR
LastName LIKE 'B%' OR
LastName LIKE 'C%' OR
LastName LIKE 'E%'
ORDER BY
LastName DESC,
FirstName DESC;

This SQL statement uses the ORDER BY clause for sorting and the WHERE clause to filter the result set based on the specified criteria.