Starting from:

$20

ICSI410- Assignment 3 Solved

Write 10 separate SQL queries, named “Query21” through “Query30”. Put them in the same Access file you used to submit your first and second assignments. Each query will contain a SQL statement that is your solution for the translation of a “plain English” request. Each query is worth one point. The 10 “plain English” queries are:

 

21.        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”).

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

23.        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.  

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

25.        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.

26.        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.

27.        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.

28.        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.

29.        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.

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

More products