title: Whats wrong with these SQL statements?
body: <p>Problem 1: Using the SQL CREATE TABLE statement, create a table, MOVSTARDIR, with attributes for the movie number, star number, and director number and the 4 acting awards. The primary key is the movie number, star number and director number (all 3), with referential integrity enforced. The director number is the director for that movie, and the star must have appeared in that movie. 
  Load MOVSTARDIR (from existing tables) using INSERt INTO.    </p>
  
  <p>My answer:</p>
  
  <pre><code>CREATE TABLE MOVSTARDIR 
  (MVNUM SHORT NOT NULL, STARNUM SHORT NOT NULL, DIRNUM SHORT NOT NULL, BESTF TEXT, BESTM       TEXT, SUPM TEXT, SUPF TEXT) 
  
  ALTER TABLE MOVSTARDIR 
  ADD PRIMARY KEY (MVNUM,STARNUM,DIRNUM) 
  
  INSERT INTO MOVSTARDIR 
  SELECT MOVIE.MVNUM,STAR.STARNUM,DIRECTOR.DIRNUM... BESTF,BESTM,SUPM,SUPF 
  FROM MOVSTAR, DIRECTOR, MOVIE 
  WHERE MOVSTAR.MVNUM=MOVIE.MVNUM 
  AND MOVIE.DIRNUM=DIRECTOR.DIRNUM`
  </code></pre>
  
  <p>*<strong><em>Its giving me an error saying something is wrong with "create table" statement and it highlights the word "alter" in the SQL statement. Also how do i add referential integrity?</em>*</strong> </p>
  
  <p>Problem 2:List the directors in MOVSTARDIR with the total awards won from the 4 award categories included in the table. List the director name (not number), and the count in each of the 4 categories and the sum for all 4 categories. Group the report by the director name (i.e. one line per director, each director appears once), and order it by the sum (descending). Only show lines where the sum is more than 3. </p>
  
  <pre><code>SELECT DISTINCT DIRNAME, COUNT(BESTF) AS BESTFE, COUNT(BESTM) AS BESTML, 
  COUNT(SUPM) AS SUPML, COUNT(SUPF) AS SUPFE, 
  (COUNT(BESTM) COUNT(BESTF) COUNT(SUPM) COUNT(SUPF)) AS TOTAL 
  FROM MOVSTARDIR, DIRECTOR 
  WHERE MOVSTARDIR.DIRNUM=DIRECTOR.DIRNUM 
  AND ((BESTM IS NOT NULL) OR (BESTF IS NOT NULL) OR (SUPM IS NOT NULL) 
  OR (SUPF IS NOT NULL))  
  GROUP BY DIRNAME 
  HAVING (COUNT(BESTM) COUNT(BESTF) COUNT(SUPM) COUNT(SUPF)) 3 
  ORDER BY (COUNT(BESTM) COUNT(BESTF) COUNT(SUPM) COUNT(SUPF))DESC`
  </code></pre>
  
  <p>*<strong><em>Problem with this is it list all records not just wins</em>*</strong>
  if the database is needed i can send the data base through email.</p>