Can someon please assist in creating a query that supports the highlighted areas
ID: 3727004 • Letter: C
Question
Can someon please assist in creating a query that supports the highlighted areas. The query commnad needs to include at least one JOIN and a WHERE or HAVING clause from the 5 tables. Thanks in advance.
PROMO NAME PICTURE PROMO STATUs Applies to CUSTOMER PROMO PERIOD Has OB_DATE PICTURE BEFORE CUS LNAME FK1 JOB ID FK2 WISH ITEM ID WEIGHT I WEIGHT OUT WEIGHT DIFF ESTIMATE_PRICE Updates dR CUS CNAME CUS EMAIL JOB STATus JOB RETURN BILLING ADDRESS BILLING CITY BILLING ZIP CODE SHIPPING ADDRESS SHIPPING CITY SHIPPING_ZIP_CODE CUS CITY CUS ZIP_coDE CUS ANNIVERSARY DATE REFERENCE MAILING UIST OB STATUS_NAME JOB STATUS_DESC FK2 STATE ID FK3 CUS STATUS_ID FK4 COUNTRY ID JEWELRY TYPE Entered into FK4 JEWELRY TYPE ID FKS COUNTRY ID FKE STATE ID FK7 SIZE ID EWELRY TYPE PK SIZE ID ---H-FK9J0B_STATUS ID INSIDE DIAMETER MM Consists of ORDER COMP_ORDER COST ates FK1 VENDOR ID COMPONENT_ORDER_STATUS PK COS ID I Produces NE CUT Included in COMPONENT TYPE E CLARITY Entered into COMP TYPE E_CLARITY GOLD KARAT FK8 COMP TYPE ID FK1 STONE_CUT ID FK2 STONE CLARITY ID FK3 STONE_CARAT IDR FK4 STONE ID FKS METAL ID Is implemented E CARAT implemented GOLD KARAT NE CARAT METAL PURITY UR FK COLOR ID dR FK9 METAL PURITY ID FK10 GOLD_KARAT ID FK13 COMP ORDER IDR Determines METAL PURITYExplanation / Answer
A SQL stands for Structured Query Language where SQL join as the name suggest is used to join data in two or more columns together i.e. the data in column one is joined with the data other column/s.depending upon the join selected.
There are various types of join :
1. INNER JOIN: It is used to return the matched data in both the tables .
As per the given question :
Among the 5 highlighted tables
In table 1:Job - Job id is the unique key
In table 2: Component _order : job id is present ( as a foreign key ) hence data in table 1(job) and table 2(Component order) can be joined through inner join.(i.e data is matching in both the tables )
Syntax of Inner join
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
SELECT JOB.JEWELRY_TYPE_ID, COMPONENT _ORDER.COMP _ORDER_COST
FROM JOB
INNER JOIN COMPONENT _ORDER ON JOB.JOB_ID = COMPONENT _ORDER.JOB_ID;
THIS WILL SELECT THE RECORDS jewelry type from table job and record component order cost from table component order based on the common record in both tables i.e job id .
Syntax : SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
Syntax : SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Syntax : SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;;
Syntax :
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
Now using these Joins solution of the problem is ::
Query :
SELECT COMPONENT_ORDER.COMP_ORDER_COST,JOB.JEWELRY_TYPE_ID ,COMPONENT.COLOR_ID,VENDOR.VENDOR_NAME,COMPONENT_ORDER_STATUS.COS_NAME
INNER JOIN VENDOR ON COMPONENT_ ORDER .VENDOR _ID= VENDOR.VENDOR_ID
LEFT JOIN