Introduction

Assignments are essential in learning the material and preparing for exams. You can ask for help but make sure you write the answers in your own words. We will check for copies. You should submit your work to the dropbox on Learn, by 1pm on Nov. 2nd, 2022. Late submission will NOT be accepted. Only use SQL commands that are discussed in lectures to answer these questions. Questions answered with SQL commands outside of lecture material will be MARKED ZERO. One of the main objectives of this course (and the only objective ofthis assignment) is to help students learn and master the fundamentals of SQL. Using “advanced” SQL commands to bypass the fundamentals defeats this purpose. Also, DO NOT use VIEW as intermediate steps and DO NOT use INSERT, DELETE or UPDATE as part of your query (you are to search the data, not to modify it in anyway). There should be only one query statement for each question. These restrictions hold truey to both assignments and exams.

In order for Learn to properly render and display your file, when you submit your assignment (or exam) file, please ONLY upload PDF file or image (JPG, JPEG, BMP, PNG) file.

Questions

All the queries must be formulated with respect to the Capital Ships database whose schema is explained on the last page. A sample database is also provided (CapitalShips.db). Note that the database only contains a very small set of data. Feel free to add more data so that you can properly test your answers. It is recommended that you use DB Browser for SQLite (https://sqlitebrowser.org). The tool is quite easy to use      and      you      can      find      plenty      of     tutorials      on      YouTube.      Here      is      one: https://www.youtube.com/watch?v=oeuTw00F1as. You don’t have to use SQLite or even the sample database to complete the assignment. But if you are to learn SQL, it seems strange not to learn at least one SQL tool and run your codes on an actual database.

Write SQL expressions to answer the following questions. Try to follow the same format style as the examples in the lectures. Proper indentation makes your code much easier to read and understand.

1.   The treaty of Washington in 1921 prohibited capital ships heavier than 35,000 tones. List the ships that violated the treaty. Note that this excludes ships launched before 1921.

2.   List the name, displacement, and number of guns of the ships engaged in any battle in 1944.

3.   Find the ships that never went to battle.

4.   Find those countries that had both battleships and battlecruisers.

5.   Find the ships that never suffered any damage in any battle (nor sunk). Note that this includes ships that never went to any battle.

6.   Find the battles where no ship was sunk.

7.   Find the classes that had only one ship as a member of that class.

8.   Find the ships that were sunk in their first battle.

9.   To measure the strength of a fleet, find out how many guns the USA fleet had and how many guns the Japan fleet had in the Battle of Guadalcanal. The answer should be displayed like (I want to use USA and Japan. But somehow they don’t work in SQLite.)

10. Find the classes that had no ship left after the war (WWII). That is, all the ships in that class were sunk.

11. Find the classes that had ships participated in every battle. Say a class had two ships and there were total 4 battles. Ship 1 went to battle 1, 3 and 4, and Ship 2 went to battle 2 and 4. So this class of ships went to all 4 battles and the class should be listed in the result.

12. Find out what is the heaviest ship ever sunk in battle. List the name of the ship, the name of the battle and the date of the battle.

Marking (36 points)

Each question is worth 3 points and is marked according to the following scheme:

•    Good (no mistake or very small one): 3 points;

•    Ok (mostly correct): 2 points;

•    Poor (mostly wrong but has some merit): 1 point;

•   Wrong (totally off or no answer): 0 point.

Submission

The assignment file format should be DOC/DOCX (word document), PDF, TXT (text file), or JPG/JPEG (picture). It should be uploaded to the drop box on Learn: Submit -> Dropbox -> Assignment #2. The submission deadline is Nov 2nd, 1pm .

WII Capital Ship Database

The Capital  Ships database  stores information about WWII capital  ships. It involves the following relations:

Classes(className, type, country, numGun, bore, displacement)

Ships(name, class, launched)

Battles(name, date)

Outcomes(shipName, battleName, result)

The foreign keys should be easy to deduce from the semantics and the attribute names. A capital ship could either be a battleship or a battlecruiser. Ships are built in “classes” (think of it as the data type of the ship) from the same design, and the class is usually named after the first ship of that class. The relation Classes records the name of the class, the type (bb for battleship and bc for battlecruiser), the country that designed the class, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight, in tones). Relation Ships records the name of the ship, the name of its class, and the year in which the ship was launched. Relation Battles gives the name and date of the

battles, and table Outcomes gives the result (sunk, damaged, or ok) for ships participated in battles.

Just in case you might need it, here is how to extract year from a date (%M for month and %D for day):

SELECT

FROM

Tables on the last page provide sample data for these four relations. Note that, these are just for your reference and make it easier to understand the semantics of the relations. Information in the tables is incomplete as it will make the tables too big. As such, there are some “dangling tuples” in this data, e.g., ships mentioned in Outcomes that are not mentioned in Ships. Your query should assume the information is complete (no “dangling tuples” in the actual database). A sample capital ship database in SQL is also provided so that you can test your answers.

Classes

Battles

Outcomes