Write and execute a SELECT statement that will return the customer’s name, city,
ID: 3864575 • Letter: W
Question
Write and execute a SELECT statement that will return the customer’s name, city, salesman’s number assigned to customer, and the assigned salesman’s last name for customers located in the state of Pennsylvania.
Table Name
Attribute Name
PK or FK
FK Referenced Table
Costomer
cust_number
PK
cust_name
cust_street
cust_city
cust_state
cust_zip
salesman_number
FK
Employee
Employee
empl_number
PK
empl_first_name
empl_middle_name
empl_last_name
job_code
FK
Job
Job
job_code
PK
job_description
Order
ord_number
PK
cust_number
FK
Customer
ord_entry_date
ord_status
Order_detail
ord_number
PK,FK
Order
line_number
PK
prod_number
FK
Product detail
ord_quantity
ship_quantity
ship_date
PK
Product_detail
prod_number
PK,FK
Product master
vend_number
PK,FK
Vendor
qty_in_stock
Product_master
prod_number
PK
prod_description
sale_price
commission_percent
Vendor
vend_number
PK
vend_name
vend_street
vend_city
vend_state
vend_zip
Table Name
Attribute Name
PK or FK
FK Referenced Table
Costomer
cust_number
PK
cust_name
cust_street
cust_city
cust_state
cust_zip
salesman_number
FK
Employee
Employee
empl_number
PK
empl_first_name
empl_middle_name
empl_last_name
job_code
FK
Job
Job
job_code
PK
job_description
Order
ord_number
PK
cust_number
FK
Customer
ord_entry_date
ord_status
Order_detail
ord_number
PK,FK
Order
line_number
PK
prod_number
FK
Product detail
ord_quantity
ship_quantity
ship_date
PK
Product_detail
prod_number
PK,FK
Product master
vend_number
PK,FK
Vendor
qty_in_stock
Product_master
prod_number
PK
prod_description
sale_price
commission_percent
Vendor
vend_number
PK
vend_name
vend_street
vend_city
vend_state
vend_zip
Explanation / Answer
Select a.cust_name,a.cust_city,a.salesman_number,b.empl_last_name
From customer a,Employee b
Where a.salesman_number =b.empl_number and a.cust_state='Pennsylvania'
In these tables there is no salesman's last name field so I joined with employee number
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.