\documentclass[11pt]{amsart}
\usepackage{geometry}                % See geometry.pdf to learn the layout options. There are lots.
\geometry{letterpaper}                   % ... or a4paper or a5paper or ... 
%\geometry{landscape}                % Activate for for rotated page geometry
%\usepackage[parfill]{parskip}    % Activate to begin paragraphs with an empty line rather than an indent
\usepackage{graphicx}
\usepackage{amssymb}
\usepackage{amsmath}
\usepackage{epstopdf}
\DeclareGraphicsRule{.tif}{png}{.png}{`convert #1 `dirname #1`/`basename #1 .tif`.png}

% From http://tex.stackexchange.com/questions/20740/symbols-for-outer-joins
\def\ojoin{\setbox0=\hbox{$\bowtie$}%
  \rule[-.02ex]{.25em}{.4pt}\llap{\rule[\ht0]{.25em}{.4pt}}}
\def\leftouterjoin{\mathbin{\ojoin\mkern-5.8mu\bowtie}}
\def\rightouterjoin{\mathbin{\bowtie\mkern-5.8mu\ojoin}}
\def\fullouterjoin{\mathbin{\ojoin\mkern-5.8mu\bowtie\mkern-5.8mu\ojoin}}


\title{Homework 2}
\date{}                
\begin{document}
\maketitle

\section*{Instructions}

Due on Wednesday, November 10th, at 11:59pm

You must submit this assignment electronically, via Blackboard, as a PDF.

The first part of the assignment should be performed on the "university" database on MySQL.  This contains different data from the "textbook" database that we used in class, though you will notice that the queries are very similar.

For each of the following, you need to both give the SQL commands to perform the required action and the output from the command.

\begin{enumerate}
\item Find the names of all the instructors from Biology department
\item Find the names of courses in Computer science department which have 3 credits
\item For the student with ID 12345 (or any other value), show all course\_id and title of all courses registered for by the student.
\item As above, but show the total number of credits for such courses (taken by that student). Don't display the tot\_creds value from the student table, you should use SQL aggregation on courses taken by the student.
\item As above, but display the total credits for each of the students, along with the ID of the student; don't bother about the name of the student. (Don't bother about students who have not registered for any course, they can be omitted)
\item Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names)
\item Display the IDs of all instructors who have never taught a couse (interpret "taught" as "taught or is scheduled to teach")
\item As above, but display the names of the instructors also, not just the IDs.
\item Find the maximum and minimum enrollment across all sections, considering only sections that had some enrollment, don't worry about those that had no students taking that section
\item Find all sections that had the maximum enrollment (along with the enrollment), using a subquery.
\item Find all courses whose title contains the string "programming"
\item Find instructors who have taught all the above courses
\item Create your own query: define what you want to do in English, then write the query in SQL. Make it as difficult as you wish, the harder the better.
% \item Find all students who have 2 or more non-overridden F grades as per the takes relation, and list them along with the F grades.
\item Find all rooms that have been assigned to more than one section at the same time. Display the rooms along with the assigned sections;
\end{enumerate}

For the following section, you'll need to create tables in your personal schema.  Please include both the database modification SQL statements to create the tables and the SQL for the queries requested.  For \#2, please include which rows you inserted into your tables.

\begin{enumerate}

\item You need to create a movie database. Create three tables, one for actors(AID, name), one for movies(MID, title) and one for actor\_role(MID, AID, rolename). Use appropriate data types for each of the attributes, and add appropriate primary/foreign key constraints.
\item Insert data to the above tables (approx 3 to 6 rows in each table), including data for actor "Charlie Chaplin", and for yourself (using your roll number as ID).
\item Write a query to list all movies in which actor "Charlie Chaplin" has acted, along with the number of roles he had in that movie.
\item Write a query to list all actors who have not acted in any movie
\item List names of actors, along with titles of movies they have acted in. If they have not acted in any movie, show the movie title as null. (Do not use SQL outerjoin syntax here, write it from scratch.)

\end{enumerate}

\end{document}