SQL Assignment 4 In these exercises, you’ll enter and run your own SELECT statem
ID: 3800030 • Letter: S
Question
SQL Assignment 4
In these exercises, you’ll enter and run your own SELECT statements. You will use the
MyGuitarShop database for these queries. If you do not already have the MyGuitarShop
database, the SQL script and the instructions for creating the database are located in the
Submitting Assignments module on Canvas.
Create a SQL script that will produce the requested information outlined below. After you have
completed and tested the script, submit the script using the HW4 assignment link located in the
Week 9 module. Include a comment at the top of the script with your name and assignment.
Also include a comment before each section of the script that identifies the exercise number.
Here is an example of how the script should begin:
-- Sherry Albright (SQL Assignment 4)
-- Exercise 1
1. Write a SELECT statement that returns these columns from the Products table:
The ListPrice column
A column that uses the CAST function to return the ListPrice column with 1 digit
to the right of the decimal point
A column that uses the CONVERT function to return the ListPrice column as an
integer
A column that uses the CAST function to return the ListPrice column as an
integer
2. Write a SELECT statement that returns these columns from the Products table:
The DateAdded column
A column that uses the CAST function to return the DateAdded column with its
date only (year, month, and day)
A column that uses the CAST function to return the DateAdded column with its
full time only (hour, minutes, seconds, and milliseconds)
A column that uses the CAST function to return the DateAdded column with just
the month and day
3. Write a SELECT statement that returns these columns from the Products table:
The ListPrice column
The DiscountPercent column
A column named DiscountAmount that uses the previous two columns to
calculate the discount amount and uses the ROUND function to round the result
to 2 decimal places
SQL Assignment 4 Page 2 of 2
4. Write a SELECT statement that returns these columns from the Orders table:
The OrderDate column
A column that returns the four-digit year that’s stored in the OrderDate column
A column that returns only the day of the month that’s stored in the OrderDate
column.
A column that returns the result from adding thirty days to the OrderDate column.
Explanation / Answer
First of all, please try to put sufficient details in your question. In your question you should have given the table structure with some dummy data. It would have been a great help to explain things properly.
The question here has 15 subparts. I'll try my best to finish whatever I can in the given time.
PRODUCTS
To display column ListPrice
SELECT ListPrice from Products; //here ListPrice is your column name and Products is your table name
2. A column that uses the CAST function to return the ListPrice column with 1 digit
to the right of the decimal point
SELECT cast(ListPrice as decimal(10,1)) as ColumnName from Products; //here decimal(maximum digits to left side,maximum digits to right side. I assumed your maximum range for left side digits is 10.
3. A column that uses the CONVERT function to return the ListPrice column as an integer.
SELECT convert(integer, ListPrice) as ColumnName from Products; //convert(Datatype,expression/coloumnname) and given datatype is Integer and the column name is ListPrice
4. A column that uses the CAST function to return the ListPrice column as an integer
SELECT cast(ListPrice as integer) as ColumnName from Products; //Keeping in mind the syntax for question 2 of this block
2. Write a SELECT statement that returns these columns from the Products table:
a) The DateAdded column
SELECT DateAdded from Products;
b) A column that uses the CAST function to return the DateAdded column with its
date only (year, month, and day)
SELECT CAST(DateAdded AS DATE) from Products; //Output in this format 2009-07-12, assuming the DateAdded column has a datatype of date format with time.
c) A column that uses the CAST function to return the DateAdded column with its
full time only (hour, minutes, seconds, and milliseconds)
SELECT CAST(DateAdded AS TIME) from Products; //Output in this format 08:46:25.8130000, assuming the DateAdded column has a datatype of date format with time.
d) A column that uses the CAST function to return the DateAdded column with just
the month and day.
SELECT RIGHT(CAST(DateAdded AS DATE), 5) from Products; //Output in this format 07-12, assuming the DateAdded column has a datatype of date format with time. The RIGHT keyword is used to extract only the number of characters specifed with a deliminator ','. So, we need to select only the first 5 characters from the CAST function.
3. Write a SELECT statement that returns these columns from the Products table:
a) The ListPrice column
SELECT ListPrice from Products; //here ListPrice is your column name and Products is your table name
b) The DiscountPercent column
SELECT DiscountPercent from Products; //here DiscountPercent is your column name and Products is your table name
I HAVE ATTEMPTED THE FIRST 10 QUESTIONS WITHIN MY TIME LIMIT. PLEASE LOOK FORWARD FOR THE SAME.
Thank You for using Chegg.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.