I need to find out what are the queries are supposed to look like Everywhere is showing very different things and I'm not sure how to do this properly Pls help Instructions In this assignment you are going to create multi table select statements to query the tables that you created a previous assignment You will use the SQL Workshop SQL Scripts page to create a single script file to complete the assignment Save your script to a file named LastName FirstName Functions sql (where LastName is your last name and FirstName is your first name) Once you create the script in APEX, you will have the ability to save it and download for submission Your script should be well formatted and should contain comments regarding what the particular section of the script is supposed to do See the file sample script PNG for suggested script format Complete the following objectives using APEX Background Information You have been contracted by a small company named DJ's on Demand to create a database for storing information on their jobs, event venues, partners, clients, and music catalog Assignment Deliverable Create separate select statements to query data to answer each question Be sure to place a single line comment before each question to identify the question being answered (6 points each, 48 points total) Write a query that will show the average cost of the DJ's on Demand events Round to two decimal places Write a query to a list of all the DJ's on Demand CD titles that have a as the second letter in the title Write a query to display the number of months between the event date of EVENT ID 105 and todays date in the DJ's on Demand database Round to the nearest month Write a query that will display the DJ's on Demand CD titles for CD NUMBER 90 and 91 in UPPERCASE in a column aliased as DJs on Demand Collections Write a query to create usernames for the DJ's on Demand partners The usernames will be the lowercase last name the lowercase first letter in the first name Alias the column as user name For example, Mary Smythers would be smythersm Write a query that will show the total cost of the DJ's on Demand events Using a DECODE and the DJ's on Demand D SONGS table, create a query that replaces the 2 minute songs with shortest and the 10 minute songs with longest Label the output column For all null values in the specialty column in the DJ's on Demand d partners table, write a query to substitute No Specialty Show the first name and specialty columns only Write a query that will format the DJ's on Demand d packages columns, low range and high range package costs, in the format $2500 00 See the rubric for the points breakdown This assignment is worth 48 points total Submitting your Assignment Click the Submit Assignment button Click the Choose File button and upload the following files LastName FirstName Functions sql this file should contain all of your scripts Click the Submit Assignment button to submit 8 DJs on Demand Table Design D PLAY LIST ITEMS PF EVENT ID NUMBER (5) PF SONO ID NUMBER (5) COMMENTS VARCHAR2(0) D PLM PK (EVENT ID, SONO ID) D PLM EVENT ID FK (EVENT ID) D PLM SONO ID FK (SONO ID) DJOB ASSIGNMENTS PF PARTNER ID NUMBER (5) PF EVENT ID NUMBER (5) JOB DATE DATE STATUS VARCHAR2 (50) D JAT PK (PARTNER ID EVENT ID) D JAT EVENT ID FK (EVENT ID) D JAT PARTNER ID FK (PARTNER ID) D PARTNERS PID NUMBER (5) FIRST NAME VARCHAR2 (25) LAST NAME VARCHAR2 (30) EXPERTISE VARCHAR2 (25) SPECIALTY VARCHAR2 (25) AUTH EXPENSE AMT NUMBER (82) MANAGER ID NUMBER (5) PARTNER TYPE VARCHAR2 (25) D PTR ID PK (ID) D THEMES CODE NUMBER (10) DESCRIPTION VARCHAR2 (100) D TME CODE PK (CODE) D SONGS PID NUMBER (5) TITLE VARCHAR2 (50) DURATION VARCHAR2 (20) ARTIST VARCHAR2 (20) F TYPE CODE NUMBER (10) D SNG ID PK (ID) D SNG TYPE CODE FK (TYPE CODE) D EVENTS ID NUMBER (5) NAME VARCHAR2 (50) EVENT DATE DATE DESCRIPTION VARCHAR2(50) COST NUMBER (2) VENUE ID NUMBER (5) PACKAGE CODE NUMBER (10) THEME CODE NUMBER (10) CLIENT NUMBER NUMBER (5) D EVE ID PK (ID) D EVE CLIENT NUMBER FK (CLIENT NUMBER) D EVE PACKAGE CODE FK (PACKAGE CODE) D EVE THEME CODE FK (THEME CODE) D EVE VENUE ID FK (VENUE ID) D PACKAGES IP CODE NUMBER (10) LOW RANGE NUMBER (0) HIGH RANGE NUMBER (8) D PKE CODE PK(CODE) D TYPES P CODE NUMBER (10) DESCRIPTION VARCHAR2 (50) D TPE CODE PK (CODE) DTRACK LISTINGS PF SONG ID NUMBER (5) PF CD NUMBER NUMBER (5) TRACK NUMBER (2) D TLO PK (SONG ID CD NUMBER) 3D TLG CD NUMBER FK (CD NUMBER) D TLG SONG ID FK (SONG ID) SONO ID IDX (SONO ID) D VENUES ID NUMBER (5) LOC TYPE VARCHAR2(30) ADDRESS VARCHAR2 (100) RENTAL FEE VARCHAR2(50) COMMENTS VARCHAR2(100) D VNE ID PK (ID) D CDS P CD NUMBER NUMBER (6) TITLE VARCHAR2 (60) PRODUCER VARCHAR2 (50) YEAR VARCHAR2 (4) D CDS CD NUMBER PK (CD NUMBER) D CDS IDX (TITLE) D CLIENTS CUENT NUMBER NUMBER (5) FIRST NAME VARCHAR2 (26) LAST NAME VARCHAR2 (30) PHONE NUMBER (16) EMAIL VARCHAR2 (60) D CLT CLIENT NUMBER PK (CLIENT NUMBER)

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 26, 2024

I need to find out what are the queries are supposed to look like. Everywhere is showing very different things and I'm not sure how

I need to find out what are the queries are supposed to look like. Everywhere is showing very different things and I'm not sure how to do this properly. Pls help.

Instructions:

In this assignment you are going to create multi-table select statements to query the tables that you created a previous assignment. You will use the SQL Workshop - SQL Scripts page to create a single script file to complete the assignment.

Save your script to a file named LastName_FirstName_Functions.sql (where LastName is your last name and FirstName is your first name). Once you create the script in APEX, you will have the ability to save it and download for submission. Your script should be well-formatted and should contain comments regarding what the particular section of the script is supposed to do. See the file sample_script.PNG for suggested script format.

Complete the following objectives using APEX:

Background Information:

You have been contracted by a small company named DJ's on Demand to create a database for storing information on their jobs, event venues, partners, clients, and music catalog.

Assignment Deliverable:

Create separate select statements to query data to answer each question. Be sure to place a single-line comment before each question to identify the question being answered.

(6 points each, 48 points total)

Write a query that will show the average cost of the DJ's on Demand events. Round to two decimal places.Write a query to a list of all the DJ's on Demand CD titles that have a as the second letter in the title.
Write a query to display the number of months between the event_date of EVENT_ID 105 and todays date in the DJ's on Demand database. Round to the nearest month.
Write a query that will display the DJ's on Demand CD titles for CD_NUMBER 90 and 91 in UPPERCASE in a column aliased as DJs on Demand Collections.
Write a query to create usernames for the DJ's on Demand partners. The usernames will be the lowercase last name + the lowercase first letter in the first name. Alias the column as "user_name." For example, Mary Smythers would be smythersm.
Write a query that will show the total cost of the DJ's on Demand events.
Using a DECODE and the DJ's on Demand D_SONGS table, create a query that replaces the 2-minute songs with shortest and the 10-minute songs with longest. Label the output column.
For all null values in the specialty column in the DJ's on Demand d_partners table, write a query to substitute No Specialty. Show the first name and specialty columns only.
Write a query that will format the DJ's on Demand d_packages columns, low-range and high-range package costs, in the format $2500.00.

See the rubric for the points breakdown. This assignment is worth 48 points total.

Submitting your Assignment:

Click the Submit Assignment button.
Click the Choose File button and upload the following files:
1. LastName_FirstName_Functions.sql - this file should contain all of your scripts.
Click the Submit Assignment button to submit.

8 DJs on Demand Table Design D_PLAY_LIST_ITEMS PF EVENT_ID NUMBER (5) PF SONO_ID NUMBER (5) COMMENTS VARCHAR2(0) D_PLM_PK (EVENT_ID, SONO_ID) D_PLM_EVENT_ID_FK (EVENT_ID) D_PLM_SONO_ID_FK (SONO_ID) DJOB ASSIGNMENTS PF PARTNER_ID NUMBER (5) PF EVENT_ID NUMBER (5) - JOB_DATE DATE STATUS VARCHAR2 (50) D_JAT_PK (PARTNER_ID. EVENT_ID) D_JAT_EVENT_ID_FK (EVENT_ID) D_JAT_PARTNER_ID_FK (PARTNER_ID) D_PARTNERS PID NUMBER (5) FIRST NAME VARCHAR2 (25) * LAST_NAME VARCHAR2 (30) EXPERTISE VARCHAR2 (25) SPECIALTY VARCHAR2 (25) AUTH_EXPENSE_AMT NUMBER (82) MANAGER_ID NUMBER (5) * PARTNER_TYPE VARCHAR2 (25) >D_PTR_ID_PK (ID) D_THEMES -CODE NUMBER (10) * DESCRIPTION VARCHAR2 (100) D_TME_CODE_PK (CODE) D SONGS PID NUMBER (5) - TITLE VARCHAR2 (50) DURATION VARCHAR2 (20) ARTIST VARCHAR2 (20) F-TYPE_CODE NUMBER (10) D_SNG_ID_PK (ID) D_SNG_TYPE_CODE_FK (TYPE_CODE) D_EVENTS * ID NUMBER (5) NAME VARCHAR2 (50) EVENT_DATE DATE DESCRIPTION VARCHAR2(50) COST NUMBER (2) VENUE_ID NUMBER (5) PACKAGE_CODE NUMBER (10) - THEME CODE NUMBER (10) CLIENT_NUMBER NUMBER (5) D_EVE_ID_PK (ID) D_EVE_CLIENT_NUMBER_FK (CLIENT_NUMBER) D_EVE_PACKAGE_CODE_FK (PACKAGE_CODE) D_EVE_THEME_CODE_FK (THEME_CODE) D_EVE_VENUE_ID_FK (VENUE_ID) D_PACKAGES IP CODE NUMBER (10) LOW_RANGE NUMBER (0) HIGH RANGE NUMBER (8) D_PKE_CODE_PK(CODE) D_TYPES P-CODE NUMBER (10) "DESCRIPTION VARCHAR2 (50) D_TPE_CODE_PK (CODE) DTRACK LISTINGS PF SONG ID NUMBER (5) PF CD_NUMBER NUMBER (5) TRACK NUMBER (2) D_TLO_PK (SONG_ID.CD_NUMBER) 3D_TLG_CD_NUMBER_FK (CD_NUMBER) D_TLG_SONG_ID_FK (SONG_ID) SONO_ID_IDX (SONO_ID) D_VENUES ID NUMBER (5) * LOC_TYPE VARCHAR2(30) ADDRESS VARCHAR2 (100) RENTAL FEE VARCHAR2(50) COMMENTS VARCHAR2(100) >D_VNE_ID_PK (ID) D_CDS P.CD_NUMBER NUMBER (6) TITLE VARCHAR2 (60) - PRODUCER VARCHAR2 (50) YEAR VARCHAR2 (4) >> D_CDS_CD_NUMBER_PK (CD_NUMBER) D_CDS_IDX (TITLE) D_CLIENTS * CUENT_NUMBER NUMBER (5) FIRST_NAME VARCHAR2 (26) LAST_NAME VARCHAR2 (30) * PHONE NUMBER (16) EMAIL VARCHAR2 (60) >> D_CLT_CLIENT_NUMBER_PK (CLIENT_NUMBER)