/*
When you open this file, save it under another name.
Create all your SQL code first. Enter it below the
problem statement. Test you commands and correct
errors before creating the output file to submit.
Use SQL Developer in script mode to run your
solution script.
Save the output to a TXT or SQL file.
Submit your solution file and the output file online.
*/
show user;
select sysdate from dual;
PROMPT **********************************;
PROMPT 3.1;
/* moviedb
Why don't the SQL statements below work? We want to count how
many female and male artists there are (2 queries).
Correct the code and ADD A COLUMN ALIAS to each one that clearly
labels each count.
*/
select count(*)
from artist
where upper(artgender) = 'F';
select count(*)
from artist
where artgender = 'M';

PROMPT **********************************;
PROMPT 3.2;
/* moviedb
List actors who've been in comedies.
Show the artist ID, full name, movie title and movie category.
Keep in mind that movie category may have inconsistent
use of upper and lowercase. Make sure your code deals
with this.
Sort by artist last name.
Use the JOIN operator.
*/

PROMPT **********************************;
PROMPT 3.3;
/* moviedb
List actors who've been in comedies.
Show the artist ID, full name, movie title and movie category.
Keep in mind that movie category may have inconsistent
use of upper and lowercase. Make sure your code deals
with this.
Sort by artist last name.
Use the CROSS PRODUCT method to join tables.
*/

PROMPT **********************************;
PROMPT 3.4;
/* PeerEval
List workshops and the students that have attended them.
Show the full workshop name, the student ID, and student's
full name using a concatenated column and column alias.
For example, the student name would appear like this:
...
...

STDID
smith

Student Name
Bill Smith

Sort by workshop name and student ID.
*/

PROMPT **********************************;
PROMPT 3.5;
/* movieDB
What's wrong with this code?
List male artists who've acted in movies.
Show the actor's ID, last name, movie ID and movie title.
Sort by actor ID.
Don't change the CROSS PRODUCT style of joining the tables,
just correct what's wrong.
*/
select artid, artlname, movid, movtitle
from moviedb.artist, moviedb.aid_mid, moviedb.movie

where artid = aid
order by artid;