代做INFS1200/7900 Tutorial 3.1: SQL Introduction代做SQL语言

INFS1200/7900 Tutorial 3.1: SQL Introduction

Introduction

Purpose: The purpose of this tutorial is to introduce you to the basics of SQL Data

Definition Language (DDL) and Data Manipulation Language (DML). You will

see how even basic SQL queries can be used to complete common tasks for a database administrator.

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

•    Implement relational schemas using SQL

•    Evaluate and justify design choices for schema implementation

•   Write SQL queries to add, modify and delete data in a database

•    Understand the basic syntax of SQL

Section A: Data Definition Language (DDL)

The following is a relational schema for a blogging database. Based on this schema, write SQL DDL queries to complete the tasks in this section.

Note: Where attribute data types are not directly given, use common sense to choose appropriately.

Blog [blogSite, owner, dateCreated]

Article [blogSite, articleTitle, articleType, lengthInWords]

Foreign Keys:

Article.blogSite references Blog.blogSite

A.1 Write an SQL query to create the BLOG table.

A.2 Write an SQL query to create the ARTICLE table, including its foreign key.

A.3  Write  an  SQL  query  to  add  a  new  column  to  the  ARTICLE  table  called “authorName” . Note: For the purposes of anonymity, not every article will have a recorded author.

A.4  Write  an  SQL  query  to  add  a  new  constraint  to  the   BLOG  table  called “UniqueOwner” which requires that each blog site have a unique owner.

Section B: Data Manipulation Language (DML)

Using the following revised schema for the blogging database, write SQL DML queries to complete the tasks in this section.

Blog [blogSite, owner, dateCreated]

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

Foreign Keys:

Article.blogSite references Blog.blogSite

B.1 Create a new entry in the BLOG table for “Jemma Jones” , the owner of the site www.fundatabaseblogs.net, who created the site on 13/02/2020.

B.2 Create a new entry in the ARTICLE table for Jemma’s blog. The title of the article was “Top 10 Reasons to take INFS1200!”, the article category was “University” , the article length was 2048 words and the author was “Hassan Khosravi” .

B.3 The blog site “freewriting.info” has decided to gift each author from their site with their own blog. The author will be the owner and the address of the site will follow the format “AuthorName.freewriting.info” . Create these new entries in the  BLOG table. Hint: Use the SQL CONCAT function to join two words.

B.4 Increase the word length by 100 for each article written by “Jack Garcia” .

B.5 After much negotiation, the blog sites “www.infs1200forever.blog” and “www.infs1200always.blog” have merged to a new website called

“www.infs1200.blog”. Assuming an entry for this new website exists in the BLOG table, update all the articles for these two old sites to the new blog site.

B.6 Remove all articles which have a word count over 10,000.

B.7 ASIO has requested that all articles be removed which contain the following spy code words and code phrases in the title:

•    Foxtrot

•   Calculus

•   Cookie Monster

•    Disco

B.8 List all blog sites that owner ‘sqlUser’ created between 1/1/2017 to 1/2/2017.

B.9 Find all blog sites with an owner starting with A.

B.10 List all the articles and their associated blog site in descending order of word length.