AdventureWorks Database: Retrieve only the rows for product

SQL Query - AdventureWorks: Exercise-4 with Solution

4. From the following table write a query in SQL to return only the rows for product that have a sellstartdate that is not NULL and a productline of 'T'. Return productid, productnumber, and name. Arranged the output in ascending order on name.

productid|name                            |productnumber|makeflag|finishedgoodsflag|color       |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate          |sellenddate            |discontinueddate|rowguid                             |modifieddate           |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
        1|Adjustable Race                 |AR-5381      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
        2|Bearing Ball                    |BA-8327      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
        3|BB Ball Bearing                 |BE-2349      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
        4|Headset Ball Bearings           |BE-2908      |false   |false            |            |             800|         600|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
      316|Blade                           |BL-2036      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
      317|LL Crankarm                     |CA-5965      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |L    |     |                    |              |2008-04-30 00:00:00.000|                       |                |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
      318|ML Crankarm                     |CA-6738      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |M    |     |                    |              |2008-04-30 00:00:00.000|                       |                |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
	  -- more --

Click to view Full table

Sample Solution:

-- Selecting specific columns (productid, productnumber, and name with an alias 'producName') from the production.product table
SELECT productid, productnumber, name as producName
-- Specifying the table from which the data will be retrieved, with "production" being the schema name and "product" being the table name.
FROM production.product
-- Filtering the result set to include only records where sellstartdate is not NULL and productline is 'T'
WHERE sellstartdate IS NOT NULL
AND production.product.productline= 'T'
-- Ordering the result set by the 'name' column in ascending order
ORDER BY name;

Explanation:

• The SELECT productid, productnumber, name as producName statement specifies the columns to be retrieved from the "product" table. The alias 'producName' is assigned to the "name" column for easier reference in the result set.
• FROM production.product specifies the table from which the data will be retrieved, with "production" being the schema name and "product" being the table name.
• The WHERE sellstartdate IS NOT NULL AND production.product.productline= 'T' clause filters the result set to include only records where sellstartdate is not NULL and productline is 'T'.
• The ORDER BY name clause sorts the result set based on the "name" column in ascending order.

Sample Output:

productid|productnumber|producname                   |
---------+-------------+-----------------------------+
      890|FR-T98U-46   |HL Touring Frame - Blue, 46  |
      891|FR-T98U-50   |HL Touring Frame - Blue, 50  |
      892|FR-T98U-54   |HL Touring Frame - Blue, 54  |
      893|FR-T98U-60   |HL Touring Frame - Blue, 60  |
      887|FR-T98Y-46   |HL Touring Frame - Yellow, 46|
      888|FR-T98Y-50   |HL Touring Frame - Yellow, 50|
      889|FR-T98Y-54   |HL Touring Frame - Yellow, 54|
      885|FR-T98Y-60   |HL Touring Frame - Yellow, 60|
      947|HB-T928      |HL Touring Handlebars        |
      916|SE-T924      |HL Touring Seat/Saddle       |
      903|FR-T67U-44   |LL Touring Frame - Blue, 44  |
      895|FR-T67U-50   |LL Touring Frame - Blue, 50  |
      896|FR-T67U-54   |LL Touring Frame - Blue, 54  |
      897|FR-T67U-58   |LL Touring Frame - Blue, 58  |
	  ...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Returns all rows and a subset of the columns.
Next: Calculate the percentage of tax on subtotal.