AdventureWorks Database: Find stores whose name is the same name as a vendor
147. From the following tables write a query in SQL to find stores whose name is the same name as a vendor.
Sample table: Sales.Store
businessentityid|name |salespersonid|demographics|rowguid |modifieddate |
----------------+-----------------------------------------+-------------+------------+------------------------------------+-----------------------+
292|Next-Door Bike Store | 279|[XML] |a22517e3-848d-4ebe-b9d9-7437f3432304|2014-09-12 11:15:07.497|
294|Professional Sales and Service | 276|[XML] |b50ca50b-c601-4a13-b07e-2c63862d71b4|2014-09-12 11:15:07.497|
296|Riders Company | 277|[XML] |337c3688-1339-4e1a-a08a-b54b23566e49|2014-09-12 11:15:07.497|
298|The Bike Mechanics | 275|[XML] |7894f278-f0c8-4d16-bd75-213fdbf13023|2014-09-12 11:15:07.497|
300|Nationwide Supply | 286|[XML] |c3fc9705-a8c4-4f3a-9550-eb2fa4b7b64d|2014-09-12 11:15:07.497|
302|Area Bike Accessories | 281|[XML] |368be6dd-30e5-49bb-9a86-71fd49c58f4e|2014-09-12 11:15:07.497|
304|Bicycle Accessories and Kits | 283|[XML] |35f40636-5105-49d5-869e-27e231189150|2014-09-12 11:15:07.497|
306|Clamps & Brackets Co. | 275|[XML] |64d06bfc-d060-405c-8c60-c067fe7c67df|2014-09-12 11:15:07.497|
308|Valley Bicycle Specialists | 277|[XML] |59386b0c-652e-4668-b44b-4e1711793330|2014-09-12 11:15:07.497|
310|New Bikes Company | 279|[XML] |47e4b6bd-5cd1-45a3-a231-79d930381c56|2014-09-12 11:15:07.497|
312|Vinyl and Plastic Goods Corporation | 282|[XML] |dc610525-e373-49b1-b786-ea040ec25c06|2014-09-12 11:15:07.497|
314|Top of the Line Bikes | 288|[XML] |e290e93f-a980-4ba3-86c3-9858f15c8a6d|2014-09-12 11:15:07.497|
-- more --
Sample table: Purchasing.Vendor
businessentityid|accountnumber|name |creditrating|preferredvendorstatus|activeflag|purchasingwebserviceurl |modifieddate |
----------------+-------------+---------------------------------+------------+---------------------+----------+---------------------------+-----------------------+
1492|AUSTRALI0001 |Australia Bike Retailer | 1|true |true | |2011-12-23 00:00:00.000|
1494|ALLENSON0001 |Allenson Cycles | 2|true |true | |2011-04-25 00:00:00.000|
1496|ADVANCED0001 |Advanced Bicycles | 1|true |true | |2011-04-25 00:00:00.000|
1498|TRIKES0001 |Trikes, Inc. | 2|true |true | |2012-02-03 00:00:00.000|
1500|MORGANB0001 |Morgan Bike Accessories | 1|true |true | |2012-02-02 00:00:00.000|
1502|CYCLING0001 |Cycling Master | 1|true |true | |2011-12-24 00:00:00.000|
1504|CHICAGO0002 |Chicago Rent-All | 2|true |true | |2011-12-24 00:00:00.000|
1506|GREENWOO0001 |Greenwood Athletic Company | 1|true |true | |2012-01-25 00:00:00.000|
1508|COMPETE0001 |Compete Enterprises, Inc | 1|true |true | |2011-12-24 00:00:00.000|
1510|INTERNAT0001 |International | 1|true |true | |2012-01-25 00:00:00.000|
1512|LIGHTSP0001 |Light Speed | 1|true |true | |2011-12-23 00:00:00.000|
1514|TRAINING0001 |Training Systems | 1|true |true | |2012-02-03 00:00:00.000|
-- more --
Sample Solution:
-- Selecting distinct store names from the Store table where at least one vendor exists with the same name
SELECT DISTINCT
-- Selecting distinct store names from the Name column of the Store table
s.Name
-- Selecting data from the Store table with an alias 's'
FROM
Sales.Store AS s
-- Checking for existence of records in a subquery
WHERE
EXISTS
(
-- Selecting all records from the Vendor table where the store name matches
SELECT *
FROM
Purchasing.Vendor AS v
WHERE
s.Name = v.Name
) ;
Explanation:
- This SQL code retrieves distinct store names from the Store table where at least one vendor exists with the same name.
- The SELECT statement specifies the column to be included in the result set, and DISTINCT ensures only unique store names are returned.
- The WHERE clause includes a subquery using EXISTS to check for the existence of records in the Vendor table where the store name matches.
- The subquery selects all records from the Vendor table where the store name matches with the outer query's store name.
- The outer query selects distinct store names from the Store table where the EXISTS condition is true, i.e., where the subquery returns at least one row.
- The result set will contain distinct store names from the Store table where at least one vendor exists with the same name.
OR
Sample Solution:
-- Selects distinct names from the 'Sales.Store' table
SELECT DISTINCT s.Name
-- Specifies the 'Sales.Store' table as the source of data and aliases it as 's'
FROM Sales.Store AS s
-- Filters the result set to include only rows where the store name matches any name in the subquery's result set
WHERE s.Name = ANY
-- Subquery: Selects names from the 'Purchasing.Vendor' table
(SELECT v.Name
-- Specifies the 'Purchasing.Vendor' table as the source of data and aliases it as 'v'
FROM Purchasing.Vendor AS v ) ;
Explanation:
- The outer SELECT statement retrieves distinct names from the 'Sales.Store' table.
- The FROM clause specifies the source of the data, which is the 'Sales.Store' table, and it is aliased as 's'.
- The WHERE clause filters the result set. It uses the = ANY operator to filter the rows where the store name matches any name in the subquery's result set.
- The subquery retrieves names from the 'Purchasing.Vendor' table, which is aliased as 'v'.
Sample Output:
name| ----+
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Get employees with Johnson last names.
Next: Find employees of departments that start with P.
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