\documentclass[12pt]{article}
\usepackage{amsfonts, amssymb, amsmath, comment}
\usepackage{times}
\usepackage{fancyhdr}
\usepackage{hyperref}
\usepackage{url}
\usepackage{listings}
\lstset{language=SQL}
%\usepackage{xypic}
%\usepackage{epsf}
%\usepackage{epsfig}
\usepackage{enumitem}
%\usepackage{comment}

\setlist[itemize]{nosep}
\setlist[enumerate]{nosep}

\setlength{\oddsidemargin}{0in}
\setlength{\evensidemargin}{0in}
\setlength{\textwidth}{6.5in}
\setlength{\textheight}{9.0in}
\setlength{\topmargin}{-0.5in}
\setlength{\headheight}{0in}
\parskip12pt
\parindent0pt
\pagestyle{fancy}
\lhead{CS 338 - Spring 2026}
\chead{CM A02}
\rhead{Due Friday, July 10, 11:59 PM EST}
\cfoot{\normalfont\medskip Copyright \copyright 2026}

\newcommand{\rmp}{\reversemarginpar\marginpar}

\providecommand{\SolutionOne}{}
\providecommand{\SolutionTwo}{}
\providecommand{\SolutionThree}{}
\providecommand{\SolutionFour}{}
\providecommand{\SolutionFive}{}
\providecommand{\SolutionSix}{}
\providecommand{\SolutionSeven}{}
\providecommand{\SolutionEight}{}
\providecommand{\SolutionNine}{}
\providecommand{\SolutionTen}{}

%\input xy
%\xyoption{all}

\begin{document}

\begin{itemize}
\item{
\textbf{Topic:} SQL SELECT Queries
}
\item{
Assignments must be completed individually.
}
\item{
No late assignments will be accepted.
}
\item{
Only use SQL commands that are discussed in lectures to answer these questions.
}
\item{
Follow the same format and style as the examples in the lectures.
}
\item{
Proper indentation will make your code much easier to read and understand.
}
\item{
Questions answered with SQL commands outside of lecture material will be ASSIGNED A GRADE OF ZERO.
}
\item{
One of the main objectives of this course (and the only objective of this assignment) is to help students learn and master the fundamentals of SQL.
}
\item{
Using ``advanced'' SQL commands to bypass the fundamentals defeats this purpose.
}
\item{
Accordingly, DO NOT use
\begin{itemize}
\item{
\lstinline!VIEW! as intermediate steps, or
}
\item{
\lstinline!INSERT!, \lstinline!DELETE! or \lstinline!UPDATE! as part of your query (you are to inquire on the data, NOT to modify it in any way).
}
\end{itemize}
}
\item{
There should be only one query statement for each question.
It may contain subqueries.
%These restrictions hold true to both assignments and exams.
}
\item{
All the queries must be formulated with respect to the Capital Ships database, whose schema is explained on the last page.
}
\item{
A sample database is also provided (CapitalShips.db).
}
\item{
Note that the database only contains a very small set of data.
}
\item{
Feel free to add more data so that you can properly test your queries.
}
\item{
Submit your completed solutions to \textbf{Crowdmark}.
}
\item{
\textbf{Rubric:}
Each question is worth 3 points, and will be graded according to the following rubric:
\begin{itemize}
\item{
3 points: Good (no mistake or only a very small mistake)
}
\item{
2 points: OK (mostly correct)
}
\item{
1 point: Poor (mostly wrong but has some merit)
}
\item{
0 points: Wrong (totally off or no answer)
}
\end{itemize}
}
\end{itemize}

\newpage

\begin{enumerate}

\item \label{QuestionOne}
The \rmp{[3]}1921 treaty of Washington prohibited capital ships with displacements exceeding 35,000 tonnes.
List the ships that violated the treaty and participated in at least one WWII battle.
Exclude ships launched before 1921, when the treaty was signed.
The result should display the ship names, their class names, displacements and country names.
\SolutionOne

\newpage

\item \label{QuestionTwo}
Find \rmp{[3]}the class(es) where no ship of that class was ever sunk.
The result should list the class names, and their country names.
\SolutionTwo

\newpage

\item \label{QuestionThree}
Find \rmp{[3]}the pair of ships (A, B) that never fought in the same battle (regardless of fighting on the same side or against each other).
Note that (A, B) and (B, A) is the same pair and only one should be included.
The result should display the ship names.
\SolutionThree

\newpage

\item \label{QuestionFour}
Find \rmp{[3]}the heaviest (in terms of displacement) battleship(s) that survived the war (that is, among all the ships that survived the war, find the heaviest \lstinline!bb!).
The result should display the ship names and their country names.
\SolutionFour

\newpage

\item \label{QuestionFive}
For \rmp{[3]}countr(ies) having both battleships and battlecruisers, find the countries whose battleships(s) have never engaged in any battle.
The result should display the country names.
\SolutionFive

\newpage

\item \label{QuestionSix}
Find \rmp{[3]}the luckiest ship(s).
That is, they were damaged in every battle in which they participated, but were never sunk.
Exclude ships that never participated in any battle.
The result should display the ship names and their class names.
\SolutionSix

\newpage

\item \label{QuestionSeven}
Find \rmp{[3]}the capital ship(s) that participated in all the battles that took place in 1942.
The result should show ship names.
\SolutionSeven

\newpage

\item \label{QuestionEight}
Find \rmp{[3]}the country(s) that had ships which participated in every battle.
For example, say a country had two ships and there were four battles in total.
Ship \#1 went to battles \#1, \#3 and \#4, and Ship \#2 went to battles \#2 and \#4.
This country had ships that went to all four battles, and so this country should be listed in the result.
The result should show country names.
\SolutionEight

\newpage

\item \label{QuestionNine}
Find \rmp{[3]}the ship(s) that survived the most brutal battle.
Brutality of a battle is measured by the total number of ships sunk, from all sides.
The result should show ship names and their country names.
\SolutionNine

\newpage

\item \label{QuestionTen}
Which \rmp{[3]}country suffered more naval losses, Gt. Britain or Japan?
To find the answer, list the number of ships Gt. Britain and Japan lost (i.e. sunk) during the war.
The result should be displayed in the following format.

\begin{tabular}{|l|l|}
\hline
Gt. Britain & Japan \\
\hline
26 & 31 \\
\hline
\end{tabular}
\SolutionTen

\end{enumerate}

\newpage

\noindent
\textbf{The WWII Capital Ships Database}

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

\noindent
\textbf{Relations:}

\begin{enumerate}
\item{
\textbf{Battle}

\begin{tabular}{|l|l|}
\hline
\underline{name} & date \\
\hline
\end{tabular}
}
\item{
\textbf{Class}

\begin{tabular}{|l|l|l|l|l|l|}
\hline
\underline{className} & type & country & numGun & bore & displacement \\
\hline
\end{tabular}
}
\item{
\textbf{Outcome}

\begin{tabular}{|l|l|l|}
\hline
\underline{shipName} & \underline{battleName} & result \\
\hline
\end{tabular}
\begin{itemize}
\item{
\lstinline!FOREIGN KEY("battleName") REFERENCES "Battle"("name")!,
}
\item{
\lstinline!FOREIGN KEY("shipName") REFERENCES "Ship"("name")!.
}
\end{itemize}
}
\item{
\textbf{Ship}

\begin{tabular}{|l|l|l|}
\hline
\underline{name} & class & launched \\
\hline
\end{tabular}
\begin{itemize}
\item{
\lstinline!FOREIGN KEY("class") REFERENCES "Class"("className")!.
}
\end{itemize}
}
\end{enumerate}

\noindent
\textbf{Assumptions:}

\begin{itemize}
\item{
A capital ship can either be a battleship or a battlecruiser.
}
\item{
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.
}
\item{
The relation \lstinline!Battle! records
\begin{itemize}
\item{
the \lstinline!name! and
}
\item{
date of each battle.
}
\end{itemize}
}
\item{
The relation \lstinline!Class! records
\begin{itemize}
\item{
\lstinline!className!, the name of the class,
}
\item{
the \lstinline!type! (\lstinline!bb! for battleship and \lstinline!bc! for battlecruiser),
}
\item{
the \lstinline!country! that designed the class,
}
\item{
\lstinline!numGun!, the number of main guns,
}
\item{
the \lstinline!bore! (diameter of the gun barrel, in inches) of the main guns and
}
\item{
the \lstinline!displacement! (weight, in tonnes).
}
\end{itemize}
}
\item{
The relation \lstinline!Outcome! gives the \lstinline!result! (\lstinline!sunk!, \lstinline!damaged!, or \lstinline!ok!) for each ship that participated in a battle.
}
\item{
The relation \lstinline!Ship! records
\begin{itemize}
\item{
the \lstinline!name! of the ship,
}
\item{
\lstinline!class!, the name of its class, and
}
\item{
\lstinline!launched!, the year in which the ship was launched.
}
\end{itemize}
}
\end{itemize}

\noindent
Just in case you might need it, here is how to extract year from a date (replace \%Y by \%M for month and \%D for day):
\lstinputlisting{Year-Query.sql}

\noindent
\textbf{Sample Data}

\begin{itemize}
\item{
Here we provide sample data for the above four relations.
}
\item{
Note that, these are just for your reference and make it easier to understand the semantics of the relations.
}
\item{
Information in these tables is incomplete as complete data would make these tables too big.
}
\item{
As such, there are some ``dangling tuples'' in this data, e.g., ships mentioned in \lstinline!Outcome! that are not mentioned in \lstinline!Ship!.
}
\item{
Your query should assume the information is complete (i.e. no ``dangling tuples'' in the actual database).
}
\item{
A sample CapitalShip database in SQL is also provided so that you can test your queries.
}
\end{itemize}
\begin{enumerate}
\item{
\textbf{Battle}

\begin{tabular}{|l|l|}
\hline
\textbf{name} & \textbf{date} \\
\hline
North Atlantic & 1941-05-24 \\
Guadalcanal & 1942-11-15 \\
North Cape & 1943-12-26 \\
Surigao Strait & 1944-10-25 \\
\hline
\end{tabular}
}
\item{
\textbf{Class}

\begin{tabular}{|l|l|l|l|l|l|}
\hline
\textbf{className} & \textbf{type} & \textbf{country} & \textbf{numGun} & \textbf{bore} & \textbf{displacement} \\
\hline
Bismarck & bb & Germany & 8 & 15 & 42000 \\
Iowa & bb & USA & 9 & 16 & 46000 \\
Kongo & bc & Japan & 8 & 14 & 32000 \\
North Carolina & bb & USA & 9 & 16 & 37000 \\
Renown & bc & Gt. Britain & 6 & 15 & 32000 \\
Revenge & bb & Gt. Britain & 8 & 15 & 29000 \\
Tennessee & bb & USA & 12 & 14 & 32000 \\
Yamato & bb & Japan & 9 & 18 & 65000 \\
\hline
\end{tabular}
}
\item{
\textbf{Outcome}

\begin{tabular}{|l|l|l|}
\hline
\textbf{shipName} & \textbf{battleName} & \textbf{result} \\
\hline
Bismarck & North Atlantic & sunk \\
California & Surigao Strait & ok \\
Duke of York & North Cape & ok \\
Fuso & Surigao Strait & sunk \\
Hood & North Atlantic & sunk \\
King George V & North Atlantic & ok \\
Kirishima & Guadalcanal & sunk \\
Prince of Wales & North Atlantic & damaged \\
Rodney & North Atlantic & ok \\
Scharnhorst & North Cape & sunk \\
South Dakota & Guadalcanal & damaged \\
Tennessee & Surigao Strait & ok \\
Washington & Guadalcanal & ok \\
West Virginia & Surigao Strait & ok \\
Yamashiro & Surigao Strait & sunk \\
\hline
\end{tabular}
}
\item{
\textbf{Ship}

\begin{tabular}{|l|l|l|}
\hline
\textbf{name} & \textbf{class} & \textbf{launched} \\
\hline
California & Tennessee & 1921 \\
Haruna & Kongo & 1915 \\
Hiei & Kongo & 1914 \\
Iowa & Iowa & 1943 \\
Kirishima & Kongo & 1915 \\
Kongo & Kongo & 1913 \\
Missouri & Iowa & 1944 \\
Musashi & Yamato & 1942 \\
New Jersey & Iowa & 1943 \\
North Carolina & North Carolina & 1941 \\
Ramillies & Revenge & 1917 \\
Renown & Renown & 1916 \\
Repulse & Renown & 1916 \\
Resolution & Revenge & 1916 \\
Revenge & Revenge & 1916 \\
Royal Oak & Revenge & 1916 \\
Royal Sovereign & Revenge & 1916 \\
Tennessee & Tennessee & 1920 \\
Washington & North Carolina & 1941 \\
Wisconsin & Iowa & 1944 \\
Yamato & Yamato & 1941 \\
\hline
\end{tabular}
}
\end{enumerate}

\end{document}