For the following database schema create SQL queries(SELECT, FROM, WHERE) to fin
ID: 3835110 • Letter: F
Question
For the following database schema create SQL queries(SELECT, FROM, WHERE) to find the desired result:
Jobs (JobID, CustID, EmpID, JobSite, Completed, Government)
Customers (CustID, CustFirstName, CustLastName, Address, PhoneNo)
Employees (EmpID, EmpFirstName, EmpLastName)
Billings (BillNo,JobID, CustID, Billing Date, AmountBilled, AmountReceived, OutstandingBalance)
a.) Find the names of each employee who has worked at least on at least one job at the “Humansville" job site
b.) Find the number of jobs that have been completed in the "Morrisville" job site
c.) Find the name and phone number of each customer that has not paid any portion of a bill they are responsible for. (amountReceived = 0). Sort the list alphabetically by last name
Explanation / Answer
Q A. Find the names of each employee who has worked at least on at least one job at the “Humansville" job site
Answer :
select e.EmpFirstName,j.JobSite from Employees e, Jobs j e.EmpId = j.EmpId and j.JobSite = 'Humansville';
Q B. Find the number of jobs that have been completed in the "Morrisville" job site
Answer :
select count(Completed) from jobs where Completed = 'Morrisville';
Q C. Find the name and phone number of each customer that has not paid any portion of a bill they are responsible for. (amountReceived = 0). Sort the list alphabetically by last name
Answer :
select c.CustFirstName,c.CustLastName,c.PhoneNo from customers c , Billings b where c.custid = b.custid and b.AmountReceived = 0 order by c.lastName asc;
Thanks...