Question

SQL MS Acess

1. Display cid, clast, and cfirst for customers whose (last name starts with a “D” and has a “v” in the third position) or (first name ends with an “a”).

2. Display all the columns in Invoice for those invoices that have less than the average iprice.

3. Display all the columns in Customer for those customers who purchased a vehicle that has the same date value in icontract and isold. In other words, customers who signed a contract and completed the sale of a vehicle on the same day so they could drive it home that same day.

4. Display all the columns in Employee for those employees who sold at least one vehicle within 7 days of being hired.

5. Display all the columns in Customer for those customers that have relationships but have never been a purchaser. In other words, formed one or more relationships with dealerships shopping for a car, but never became a purchaser by buying one.

6. Display all the columns in Employee for those employees who sold at least one vehicle owned by a dealership that is different from the dealership they work for.

7. Display all the columns in Dealership whose employees collectively sold more than $200,000 worth of vehicles in the month of February, 2018. The $200,000 limit could be exceeded by just one employee, or by a team of employees working at the same dealership each selling less than $200,000 individually; either case is valid.

8. Display all the columns in Invoice for those invoices with the 3 lowest unique iprice values. Note that there may be more than one invoice for each of the 3 lowest iprice values.

9. Display all the columns in Vehicle for those vehicles sold two or more times and at least one resale price is more than the first (initial) sale price.

10. Display vvin, vyear, vmake, vbody, vcolor, cid, clast, cfirst, cmi for those customers who purchased the same vehicle more than once.

» Relationships Invoice inum ieid vin icontract isold iprice Purchaser pinum pcid Employee eid edcode elast efirst emi ehire esalary estreet ecity estate ezip Vehicle win 3 vdcode Customer Dealership dcode dname dstreet dcity dstate dzip vmake cid clast cfirst cmi cstreet ccity cstate czip vweight vcylinders Relationship rdcode rcid Ready NUM LOCK

Answer 1

Answer 1:

select cid,clast,cfirst from Customer where (clast like 'D_v%') or (cfirst like '%a');

Answer 2:

select * from Invoice where iprice < (select avg(iprice) as avgPrice from Invoice group by inum;);
-----------------------------------------

Answer 3:

select cr.* from Customer cr
inner join (select pr.pcid as pcid from Purchaser pr
inner join Invoice in on in.inum = pr.pinum
where in.icontract=in.isold;) tmp
on cr.cid = tmp.pcid;

-----------------------------------------------------------

Answer 4:

select emp.* from Employee emp
inner join (select in.ieid as eid from Employee emp
inner join Invoice in
on emp.eid = in.ieid where (in.isold-emp.ehire)<7 group by in.ieid having count(*)>0;) tmp on tmp.eid = emp.eid;
----------------------------------------------------------------

Answer 5:

select cst.* from Customer cst
inner join (select rsp.rcid as rcid from Relationship rsp
left outer join Purchaser pr on rsp.rcid = pr.pcid
where pr.pcid is null;) tmp on tmp.rcid = cst.cid;
-----------------------------------------------------------------------------------------------------

Answer 6:

-------------------------------------------------------------------------------------------------

Answer 7:

--------------------------------------------------------------------------------------

Answer 8:

select in1.* from Invoice in1
inner join (select inum from Invoive group by inum having count(distinct iprice)>=3;) in2
on in1.inum = in2.inum;
-----------------------------------------

Answer 9:

-----------------------------------------

Answer 10:

select vc.vvin,vc.vyear,vc.vmake,vc.vbody,vc.vcolor,cst.cid,cst.clast,cst.cfirst,cst.cmi from Customer cst
inner join (select pr.pcid,in.ivin from Invoice in
inner join Purchaser pr on pr.pinum = in.inum
group by pr.pcid,in.ivin having count(*)>=1;) tmp on tmp.pcid=cst.cid
inner join Vehicle vc on vc.vvin = tmp.ivin;