SQL Script - PostgreSQL on turing - IT Assessment Answer

IT Assessment Task

Construct an SQL script (i.e. a file with the .sql extension) that contains the definitions of the following views.

• In this part you will be constructing an SQL script that builds a series of database views within the Book Town database.
• The views that you construct will return that data specified in the following problem set. Please make sure that you complete your task using the template that is provided. Your task will be marked with the assistance of automated tools and if the names of the views and their attributes are not correct, you may lose marks
• Make sure that your .sql runs correctly within a database in PostgreSQL on turing - You will lose marks if your script does not create all views correctly.

1. Create a view called 'old_books' that lists the author's first name and last name, book title and edition for every book published before 1990.

2. Create a view called 'programming_or_perl' that Returns a list of the titles of all books with the words 'Programming' or 'Perl' in the title.

3. Create a view called 'retail_price_hike' that returns the ISBN , retail price and a final column that contains the retail price increased by 25%.

4. Create a view called 'book_summary' which returns the first name, last name of each book author along with the books title and subject.

5. Create a view called 'value_summary' that returns the total retail value (retail*stock) and total cost value (cost*stock) across all stock.

6. Create a view called 'profits_by_isbn' that returns the book title, isbn for each book along with the difference between the sum of the cost and retail values across all shipments for each book. The results should be grouped by book title and isbn.

7. Create a view called 'sole_python_author' that returns the first name and last name of any author (If one exists) who publishes all of the books with the text 'Python' in the title (Note the author may publish other books as well but if there are multiple authors of books with 'Python' in the title, no records should be returned).

8. Create a view called 'no_cat_customers' that returns the first name, last name of any customer who has not been shipped any edition of the book named 'The Cat in the Hat'.

This IT??????? Assessment has been solved by our IT 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.