Queries Database Assignment Help | Project | Homework Problem Solution

We save students from the efforts of getting more marks by our queries data base assignment help. From the mode of DatabaseHomeworkHelp we are able to show our talent in queries data base homework help. We won’t take more time than you tell us for giving help with queries data base assignment. We are fast and reliable for help with queries data base project.

You can keep using us if you get good marks with our queries data base project help. Taking the stress of receiving real help for queries data base problem solution is now not anymore if you call us. Customer support is showing the enthusiasm of queries data base project help.

We have data base experts for assisting you for any kind of queries data base project help. With us the quality is guaranteed as our queries data base online expert tutor provision is also teaching students. We never say no to any kind of complex problem in data base and always offer queries data base assignment help.

From time to time we update our knowledge in terms of providing help with queries data base assignment. We do not use random knowledge of data base solution but we use full literature and experience support in shape of our help with queries data base project. We save your time of searching of queries data base project help.

Database queries are generated in order to retrieve, fetch and store the information in the database. It is also a piece of code that is sent to database to get the information.

Note:- The below assignment is just a solved example provided by our tutors for your reference and it is not the solution of our previous homework delivered to the students. We always maintain privacy on our assignment delivered to the students. They undergo quality check along with plagiarism free so that they can provide great values to our students.

Sample Question:-

Question:-The task is to create the database queries for the mysql database engine. The mysql database has the phisical design and we want to get the required information by using the sql and database queries.

Solution

qd

Question:-A list of the names and phone numbers (including description of phone type) of all customers in alphabetical order by contact name.

Solution:-

SELECT `OrganisationName` , PhoneNumber, Description, ContactName
FROM customer, phone, phonetype, extbusiness
WHERE phonetype.phonetype = phone.phonetype
AND phone.ExtBusinessID = customer.ExtBusinessID
AND extbusiness.ExtBusinessID = Customer.ExtBusinessID
ORDER BY ContactName

Question:-A list of all government customers and their contact details (contact name, email and all phone details).

Solution:-

SELECT `OrganisationName` , PhoneNumber, Description, ContactName, EmailAddress
FROM `customer` , phone, phonetype, extbusiness
WHERE phonetype.phonetype = phone.phonetype
AND phone.ExtBusinessID = customer.ExtBusinessID
AND extbusiness.ExtBusinessID = Customer.ExtBusinessID
AND orgtype =2
ORDER BY ContactName
LIMIT 0 , 30

Question:-List all supplier products where the product description begins with an E (either upper case or lower case).

Solution:-

SELECT description as Product

FROM `supplierproduct` ,product

wheresupplierproduct.productid=product.productid

and Upper(description) like ‘E%’

Question:-A list of all customers (id and name) together with their display(s) including date, time and type of the display and the percentage markup less discount percentage for the display.

Solution:-

SELECT customer.customerid,`OrganisationName` as Name , displayDate,DisplayTime,location,description, (displaytype.Markup-display.discount)  as Markup

FROM `customer` ,displaytype,display

WHERE

customer.customerid=display.customerid

anddisplaytype.displaytype=display.displaytype

Question:-A list of the contact details (names, postal address and phone number) of all of the suppliers who supplied products for more than one display.

Solution:-

SELECT `SupplierName`as Name, phoneNumber,CONCAT_WS(‘,’, Line1 ,city,Postcode) as “Postal Address”

FROM `supplier` ,address,phone,displaysupplierproduct

wheresupplier.supplierid =displaysupplierproduct.supplierid

andsupplier.extbusinessid=phone.extbusinessid

andsupplier.extbusinessid=address.extbusinessid

anddisplaysupplierproduct.productid in (SELECT productid

FROM `displaysupplierproduct`

group by productid

HAVING COUNT(PRODUCTID) >1)

Question:-List each display (displayed is sufficient) and all of the details of staff who have worked on the display, including any certifications;

Solution:-

SELECT `DisplayDate`,`NumberAttendees`,`Location`,Staffname as Name,TFN, CONCAT_WS(‘,’, Line1 ,city,Postcode) as “Postal Address”, Phone,certificatetype.description as certification

FROM `display`,staff,displaystaff,staffcertifications,certificateType

wheredisplay.displayID=displaystaff.displayid

andstaff.staffid=displaystaff.staffid

andstaff.staffid=staffcertifications.staffid

and certificateType.certificatetype=staffcertifications.certificatetype;

Question:-Display a list of products that have had a unit charge update in the last 12 months.

Solution:-         SELECT distinct description,Price

FROM product,supplierproduct,displaysupplierproduct

wheresupplierproduct.supplierid =displaysupplierproduct.supplierid

andproduct.productid=supplierproduct.productid

andsupplierproduct.productid=displaysupplierproduct.productid

and (curDate()-dateused)>365;

Question:-Provide a list of all displays, including customer name, display id, date, time, location for which actual quantities used have been greater than allocated quantities. Include details of the product to which this applies as well as the cost difference.

Solution:-

SELECT `DisplayDate` , display.displayID, `NumberAttendees` , `Location` , staffName as Manager , customer.organisationname AS Name, description as Product,ABS(Price*(qty-qtyrequired)) as “Cost Difference”

FROM display, product, customer, displaysupplierproduct, supplierproduct,staff

WHERE display.displayID = displaysupplierproduct.displayid

AND customer.customerid = display.customerid

AND displaysupplierproduct.productid = supplierproduct.productid

AND product.productid = supplierproduct.productid

AND staff.staffid=display.displayManager

AND (qtyrequired-qty)>1

Question:-Display the itemised actual product cost of all items for displays in January 2013 (based on DisplayDate).

Solution:-

SELECT `DisplayDate`,description,Price

FROM display,product,customer,displaysupplierproduct,supplierproduct

wheredisplay.displayID=displaysupplierproduct.displayid

andcustomer.customerid=display.customerid

anddisplaysupplierproduct.productid=supplierproduct.productid

andproduct.productid=supplierproduct.productid

andDisplaydate between ‘2013-01-01’ and ‘2013-01-31’;

Question:-Increase by 5% the insurance cost for all insurance categories update insurance set cost=cost*1.0511.List the product with the highest quantity of actual usage in the database.

Soluion:-

select max(productCount) as ProductTotal

from (SELECT count(displaysupplierproduct.productid) as productCount

FROM displaysupplierproduct,supplierproduct, product

wheresupplierproduct.supplierid =displaysupplierproduct.supplierid

andsupplierproduct.productid=displaysupplierproduct.productid

andproduct.productid=supplierproduct.productid);

Question:-Provide a list of all displays, including customer name, display id, date, time, location for which actual hours worked have been greater than allocated hours. Include details of the cost difference and total change in cost for the display for the difference in hours.

Solution:-

SELECT `DisplayDate` , display.displayID, `NumberAttendees` , `Location` , `DisplayManager` , customer.organisationname AS Name, description,TotalCost

FROM display, product, customer, displaysupplierproduct, supplierproduct

WHERE display.displayID = displaysupplierproduct.displayid

AND customer.customerid = display.customerid

AND displaysupplierproduct.productid = supplierproduct.productid

AND product.productid = supplierproduct.productid

andtotalcost<  Price;

Question:-Find and list any displays (include details of the display id, date and time) where the actual price charged to the customer is below the total actual costs of the display. If you choose to use more than one query, justify this choice.

Solution:-

Charged to customer

SELECT `DisplayDate` , display.displayID, `NumberAttendees` , `Location` , `DisplayManager` , customer.organisationname AS Name, description,TotalCost

FROM display, product, customer, displaysupplierproduct, supplierproduct

WHERE display.displayID = displaysupplierproduct.displayid

AND customer.customerid = display.customerid

AND displaysupplierproduct.productid = supplierproduct.productid

AND product.productid = supplierproduct.productid

And the actual cost is ,

select  (select qty*price + hoursworked*0.25) as cost

fromdisplaysupplierproduct, displaystaff

wheredisplaysupplierproduct.displayid=displaystaff.displayid

Question:-Provide details of the display that has made the most money for FFC. You may use more than one query but you must justify this choice.

Solution:-

SELECT max(TotalCost), displaydate,Location

FROM display, product, customer, displaysupplierproduct, supplierproduct

WHERE display.displayID = displaysupplierproduct.displayid

AND customer.customerid = display.customerid

AND displaysupplierproduct.productid = supplierproduct.productid

AND product.productid = supplierproduct.productid

group by displaydate, location,displaydate

Question:- Provide some ideas on how you might improve the efficiency of complicated queries such as 13 and 14.

Solution:- We can make the views of the basic queries that are needed for to calculate the actual cost. If the actual cost is calculated using the views, then we can make a simple query from the display table and the views to retrieve the data.

We can add the calculated field into the database and then the queries will be simple.