代做INFS1200/7900 Tutorial 3.3: SQL Advanced代做数据库编程

INFS1200/7900 Tutorial 3.3: SQL Advanced

Introduction

Purpose: The purpose of this tutorial is to provide you with experience in writing advanced SQL SELECT and CREATE VIEW queries. You will learn how to correctly use both correlated and non-correlated queries to return specific subsets of data.

Learning Outcomes: By the end of this tutorial you will be able to:

•   Correctly use non-correlated queries

•   Correctly use correlated queries

•    Understand why and how to create views

•   Gain experience using techniques such a double negation to solve difficult problems in SQL

Section A: Non-correlated Queries

Based on the following relational schema, write non-correlated nested SQL queries to complete the tasks in this section.

Reef [reefName, latitude, longitude]

ReefTemp [reefName, dateOfReading, temperatureReading]

Coral [coralCode, coralName, thermalThreshold]

CoralSampling [sampleNumber, coralCode, reefName, dateOfSampling,

bleachPercent]

Foreign Keys:

ReefTemp.reefName references Reef.reefName

CoralSampling.coralCode references Coral.coralCode

CoralSampling.reefName references Reef.reefName

A.1 Return the details of all reefs which have recorded a temperature-reading above 22 degrees.

A.2 What is the name of the coral that has the highest thermal threshold?

A.3 Return the details of all reefs which have recorded a temperature reading below the average temperature reading across all reefs.

A.4 What are the names of all reefs that do not have a sample of a coral with name

including "Button”?

Section B: Correlated Queries

Based on the following schema, write correlated nested SQL queries to complete the tasks in this section.

Reef [reefName, latitude, longitude]

ReefTemp [reefName, dateOfReading, temperatureReading]

Coral [coralCode, coralName, thermalThreshold]

CoralSampling [sampleNumber, coralCode, reefName, dateOfSampling,

bleachPercent]

Foreign Keys:

ReefTemp.reefName references Reef.reefName

CoralSampling.coralCode references Coral.coralCode

CoralSampling.reefName references Reef.reefName

B.1 Which reef(s) have at least all the corals that the reef “Wreck Is.” has?

B.2 Which corals are in ALL reefs?

Section C: Views

Based on the following schema, use SQL to create views to complete the following tasks and questions. Note: This may require you write several SQL queries per question.

Blog [blogSite, owner, dateCreated]

Article [blogSite, articleTitle, articleType, lengthInWords, authorName]

Foreign Keys:

Article.blogSite references Blog.blogSite

C.1 The Australian Bureau of Statistics (ABS) needs to access a list showing the number of articles posted in the blogsites of each owner. In order to meet privacy regulations however, the blogsites themselves are not allowed to be identified with the owner.

C.2 Return the owner(s) of the blog site(s) which have written the most articles on Technology, that is where ArticleType is “Technology” .