AdventureWorks Database: Find products between $1000 and $1220 in lower, upper, and lowerupper
65. From the following table write a query in SQL to select product names that have prices between $1000.00 and $1220.00. Return product name as Lower, Upper, and also LowerUpper.
Sample table: production.Product
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 --
Sample Solution:
-- Selecting lowercased, uppercased, and doubly lowercased-then-uppercased substrings of the 'Name' column
SELECT LOWER(SUBSTRING(Name, 1, 25)) AS Lower,
UPPER(SUBSTRING(Name, 1, 25)) AS Upper,
LOWER(UPPER(SUBSTRING(Name, 1, 25))) As LowerUpper
-- From the Production schema's Product table
FROM production.Product
-- Filtering the results to include only rows where the 'standardcost' column is between 1000.00 and 1220.00
WHERE standardcost between 1000.00 and 1220.00;
Explanation:
- The SQL query retrieves data from the Product table within the Production schema.
- It applies string manipulation functions to the Name column to generate different variations of the substring.
- The SUBSTRING() function extracts a portion of the 'Name' column, starting from the first character and up to 25 characters.
- The LOWER() function converts the extracted substring to lowercase, and the UPPER() function converts it to uppercase.
- In the case of 'LowerUpper', the substring is first converted to uppercase, then to lowercase.
- The result set includes three columns: 'Lower', 'Upper', and 'LowerUpper', representing the lowercased, uppercased, and doubly lowercased-then-uppercased substrings, respectively.
- The WHERE clause filters the results to include only rows where the value of the 'standardcost' column falls within the specified range.
Sample Output:
lower |upper |lowerupper | -------------------------+-------------------------+-------------------------+ hl road frame - black, 58|HL ROAD FRAME - BLACK, 58|hl road frame - black, 58| hl road frame - red, 58 |HL ROAD FRAME - RED, 58 |hl road frame - red, 58 | road-350-w yellow, 40 |ROAD-350-W YELLOW, 40 |road-350-w yellow, 40 | road-350-w yellow, 42 |ROAD-350-W YELLOW, 42 |road-350-w yellow, 42 | road-350-w yellow, 44 |ROAD-350-W YELLOW, 44 |road-350-w yellow, 44 | road-350-w yellow, 48 |ROAD-350-W YELLOW, 48 |road-350-w yellow, 48 |
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Provide the connections in Australia and the length of FirstName.
Next: Remove spaces from the beginning of a string.
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