AdventureWorks Database: Find the rows where middlename differs from NULL
174. From the following table write a query in SQL to find the rows where middlename differs from NULL. Return businessentityid, persontype, firstname, middlename,and lastname. Sort the result set in ascending order on firstname.
Sample table: Person.Person
businessentityid|persontype|namestyle|title|firstname |middlename |lastname |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid |modifieddate | ----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+ 1|EM |false | |Ken |J |Sánchez | | 0| |[XML] |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000| 2|EM |false | |Terri |Lee |Duffy | | 1| |[XML] |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000| 3|EM |false | |Roberto | |Tamburello | | 0| |[XML] |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000| 4|EM |false | |Rob | |Walters | | 0| |[XML] |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000| 5|EM |false |Ms. |Gail |A |Erickson | | 0| |[XML] |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000| 6|EM |false |Mr. |Jossef |H |Goldberg | | 0| |[XML] |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000| 7|EM |false | |Dylan |A |Miller | | 2| |[XML] |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000| 8|EM |false | |Diane |L |Margheim | | 0| |[XML] |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000| 9|EM |false | |Gigi |N |Matthew | | 0| |[XML] |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000| -- more --
Sample Solution:
-- This SQL query selects specific columns from the person.person table, applying a condition to filter the results based on values in the MiddleName column.
-- Selecting columns: BusinessEntityID, PersonType, FirstName, MiddleName, and LastName.
SELECT businessentityid, persontype, firstname, middlename, lastname
-- From the person.person table.
FROM person.person
-- Filtering the results where the MiddleName column is distinct from NULL.
WHERE middlename IS DISTINCT FROM NULL
-- Ordering the results by the FirstName column in ascending order.
ORDER BY firstname;
Explanation:
- The query selects several columns from the person.person table: BusinessEntityID, PersonType, FirstName, MiddleName, and LastName.
- It filters the results to include only rows where the MiddleName column is not NULL.
- The IS DISTINCT FROM operator is used to check for inequality while treating NULL values as distinct.
- Finally, the results are ordered by the FirstName column in ascending order.
- This query retrieves records from the person table where the MiddleName is not NULL, ensuring that only rows with a non-null MiddleName are included in the result set, and orders the results alphabetically by FirstName.
Sample Output:
businessentityid|persontype|firstname |middlename |lastname | ----------------+----------+---------------+----------------+-----------------+ 2321|GC |A. |Scott |Wright | 1305|SC |A. |Francesca |Leonetti | 15883|IN |Aaron |L |Perez | 16923|IN |Aaron |M |Young | 16167|IN |Aaron |C |Campbell | 5522|IN |Aaron |C |Yang | 5523|IN |Aaron |M |Kumar | 16591|IN |Aaron |C |Scott | 5529|IN |Aaron |J |Sharma | 5495|IN |Aaron |J |Hughes | 5497|IN |Aaron |L |Washington | 5483|IN |Aaron |N |Ross | 16420|IN |Aaron |J |Carter | ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Retrieve the rows where Adam is the firstname.
Next: Look for rows where middlename is NULL.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics