SQL Queries - Order Items Table - Engineering Assignment Help

Assignment Task :

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump is usually done via the ALT + PRNT SCRN command sequence or use the ‘Snipping Tool’ under windows to capture parts of the screen. 

While the output helps to understand your solution, you should not be analysing the output of the query in detail. If you are confident that your query corresponds to the question completely, your output may not be significant. You can have an output that says NO ROWS FOUND and it could be a perfectly valid output if your query fulfils the requirement. Explain why the query may not contain any output in this instance. 

Questions

1. Write an SQL query that displays total number of customers (rename the field ‘Total Customers’), number of customers without referrals (rename the field ‘Direct Customers’), and number of referred customers (rename the field ‘Referred Customers’).  

2. Write a SQL query that displays customer number, last name, first name, and city for all customers who reside in Florida and who have not been referred by another customer.

3. Display the full name of all the customers in a single column (by concatenating first name followed by a comma then a space then last name), rename the filed ‘Customers’, region and referred for all customers who live in any western region (that is in any region in the western sector ‘W’, ‘SW’, ‘NW’) and who have been referred by another customer.  

4. List the customer number, first name, last name, order number and order date for all orders which have not been shipped.  Order the resulting set by the order date in ascending order.

5. Display the ISBN, title, retail, difference between the cost and retail (rename the column ‘PROFIT’), difference between cost and retail minus the discount (rename the field ‘ACTUAL PROFIT’) for books in the BOOKS table where the book has a discount and where the discount is less than the difference between the cost and retail price of the book. 

6. Display the book title, cost, retail and calculate the profit and name the new field profit for all the books where the profit from the sale of the book is more than 65% of the cost of the book.  

7. Display the ISBN, quantity, price paid for the book (rename the column ‘Paid Each’), and a created field ‘Total Order Price’ (i.e. quantity * ‘Paid Each’) for all orders in the ORDERITEMS table whose quantity is greater than 1.  Order the list by ‘Total Order Price’ descending.

8. Display publisher name, book title and retail price for all the books after subtracting any available discount for that book, only for those books that have a discount.  Name the new field ‘DISCOUNTED PRICE’ and order by publisher name ascending and discounted price of the book in descending order.

9. List ISBN, title, retail, category and published date of all the books that have the (category of ‘COMPUTER’ or where the category contains the string ‘FAMILY’ anywhere in the category description) and where (price of the books is more than 20 dollars and where the published date is before the end of the financial year for 2003/2004 – i.e. 30 June 2004). 

10. Display the customer number, customer last name and city, customer zip code (rename the field ‘Customer ZIP’) and referrer zip code (rename the field ‘Referrer ZIP’) for all the customers who have been referred by a referrer with the same last name as the customer.

This Engineering Assignment has been solved by our Engineering Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.