Implement basic sql queries for a given database schema

Assignment Help PL-SQL Programming

Reference no: EM132283876

Aims

Work with an exiting database, by interpreting its schema form SQL.

Implement some basic SQL queries for a given database schema.

Create database views.

Your Task

In this assignment you will be constructing an SQL script that builds a series of database views within the Book Town database. The SQL script containing the Book Town database and its data is available for download

To start your assignment, create a new database and run the booktown.sql script:

[mwelch8@turing mwelch8]$ createdb p1_example

[mwelch8@turing mwelch8]$ psql p1_example psql (9.4.6)
Type "help" for help.

p1_example=> \i /home/cosc210/public_html/assignments/a2/p1_book_town.sql CREATE TABLE
COPY 18 CREATE TABLE COPY 16 CREATE TABLE COPY 15 CREATE TABLE COPY 13 CREATE TABLE COPY 17 CREATE TABLE COPY 16 CREATE TABLE COPY 31
CREATE SEQUENCE CREATE TABLE COPY 36
p1_example=> \dt

public | books | table | mwelch8

public | customers | table | mwelch8

public | editions | table | mwelch8

public | publishers | table | mwelch8

public | shipments | table | mwelch8

public | stock | table | mwelch8

public | subjects | table | mwelch8
(8 rows)

This will create a copy of the Book Town database for you to work with.

The views that you construct will return that data speci?ed in the following problem set. Please make sure that you complete your assignment using the template that is provided. Your assignment 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.

Question Set

Construct an SQL script (i.e. a ?le with the .sql extension) that contains the de?nitions of the following views.

1. Create a view called 'old_books' that lists the author's ?rst 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 ?nal column that contains the retail price increased by 25%.

4. Create a view called 'book_summary' which returns the ?rst 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 cost value (cost*stock) and total retail value (retail*stock) across all stock.

6. Create a view called 'pro?ts_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 ?rst 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 ?rst name, last name of any customer who has not been shipped any edition of the book named 'The Cat in the Hat'.

Submission

Submit your assignment via turing.une.edu's submit program. The instructions for submit are available here

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.

Attachment:- Database Management Systems.rar

Reference no: EM132283876

Questions Cloud

How can we redefine our ideas about success : How will you, as a principal, teach your staff and students that we need to learn from our mistakes to improve our performance?

Parents repeat this game for 52 weeks : On a given week, two selfish parents simultaneously choose between spending time with their children (S) or not (N). The payoff matrix is the following.

How will you have to prepare for the new assessment : How has this video motivated you to look at other assessments that you may not have tried before and as a principal, challenge your staff to do the same?

What is m-l condition : This relation is described by the J-curve. What is M-L condition and how it is related to J-curve?

Implement basic sql queries for a given database schema : COSC210 - Database Management Systems - University of New England Work with an exiting database, by interpreting its schema form SQL - Implement some basic SQL

Does the federal reserve have too much power : Does the Federal Reserve have too much power? Did they overstep their authority in the ways that they have dealt with the problem?

Neoclassical model of investment : In class, we discussed the idea of net investment, and how it could change in response to changes in economic conditions.

Why are mid-ocean ridges : Why are mid-ocean ridges (divergent boundaries) usually offset by numerous transform faults along their length? Feel free to refer to your

How does the analysis impact your perspective of business : Submit a 5- to 6-page synthesis in which you detail the process and results for your small-scale qualitative research project.

User Account

All Pages