1 – 4. Write four SQL queries to join two or more tables where each query contains multiple
nested single-row functions. Make sure to use different functions in each query. Explain
what each query is intended to do.
1.
This query retrieves the first name, last name, order date of the person by joining the tables
PERSON, CUSTOMER, ORDER. I used a nested CONCAT function to get the first name and
last name with a comma in between as person name.
2.
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:39:18 GMT -06:00
https://www.coursehero.com/file/146946529/M6-2-Assignment-Functionsdocx/
This query retrivevs the product id, order id, product name, order date, sysdate and the age of the
order by using the nested TRUNC function with the MONTHS_BETWEEN function and by
joining the tables PRODUCT, ORDER, PRODUCTS_ORDERED.
3.
This query retrieves the product id, product name, category name, product price , increases the
price of electronics by 15% and books by 25% by using DEODE function and is displayed as
revised price by adding a dollar sign and allowing 2 decimal points by joining the tables
PROD_CATEGORY, PRODUCT tables.
4.
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:39:18 GMT -06:00
https://www.coursehero.com/file/146946529/M6-2-Assignment-Functionsdocx/
This query retrieves the product name, supplier name, combines the supplier address, supplier
city, supplier region with a comma in between with NESTED CONCAT function and by joining
the tables PRODUCT, SUPPLIER.
5. Write a query which is based on a single table and includes the GROUP B statement.
Explain what the query is supposed to do.
This query retrieves the supplier_id and the total value of the products supplied by each supplier.
6. Repeat problem 5 but add the WHERE statement. Explain what the query is supposed to
do.
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:39:18 GMT -06:00
https://www.coursehero.com/file/146946529/M6-2-Assignment-Functionsdocx/
This query retrieves the supplier id and the total value of the inventory supplied by each supplier
and considers the values that satisfy the condition sold number must be greater than 30.
7. Repeat problem 6 but add the HAVING statement. Explain what the query is supposed
to do.
This query retrieves the supplier id and the total value of the inventory supplied by each supplier
and satisfy a condition sold number must be greater than 30 and the supplier with vtotal value
greater than 1500.
8. Write a query which is based on three tables and includes the GROUP BY, WHERE and
HAVING statements. Explain what the query is supposed to do.
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:39:18 GMT -06:00
https://www.coursehero.com/file/146946529/M6-2-Assignment-Functionsdocx/
This query retrieves the category id and the total value of the products sold in each category
where the inventory is greater than 25 and having a total sale value greater than 3000.
9. Write an SQL query that uses the GROUP BY ROLL statement. Explain what the query
is supposed to do.
This query returns the category Id, supplier id and the total price of few products in each
combination of category and supplier along with the grand total which is the last column in the
output. This column is the result of ROLLUP function.
10. Write an SQL query that uses the GROUP BY CUBE statement. Explain what the
query is supposed to do.
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:39:18 GMT -06:00
https://www.coursehero.com/file/146946529/M6-2-Assignment-Functionsdocx/
This query retrieves the category id, supplier id and the combination of total prices of the
products supplied by each supplier and category along with the grand total.
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:39:18 GMT -06:00
https://www.coursehero.com/file/146946529/M6-2-Assignment-Functionsdocx/
Powered by TCPDF (www.tcpdf.org)
