Question

a) [1 pt] Import data (SQL code is provided. Test prog3_createTable.sql on your server): Each of you will work in your own database with the name cs4402xx;; Import the movie data movie-­name_score.txt into a new table called movies with the schema: movies(movie_id integer, name varchar(1000), score integer)

Import the movie cast data at movie-­cast.txt into a new table called cast with the schema:

cast(movie_id integer, cast_id integer, cast_name varchar(1000)).

j) [10 pt] Finding good collaborators: Create a view (virtual table) called ‘good collaboration’ that lists pairs of stars who appeared in movies. Each row in the table describes one pair of stars who have appeared in at least 4 movies together AND each of the movie has score >= 75. The view should have the format: collaboration (cast_member_id1, cast_member_id2, num_movies, avg_movie_score). Exclude self pairs: (cast_member_id1 == cast_member_id2). Keep symmetrical or mirror pairs. For example, keep both (A, B) and (B, A). Hint: Self-­Joins will likely be a necessary. After creating a view, list (cast_member_id1, cast_member_id2, num_movies, avg_movie_score) sorted by average movie scores from the view.

Answer 1

a)

USE SW
CREATE TABLE EMPLOYEES
(
EmployeeNo                      CHAR(10)             NOT NULL           UNIQUE,
DepartmentName            CHAR(30)             NOT NULL           DEFAULT “Human Resources”,
FirstName                           CHAR(25)             NOT NULL,
LastName                            CHAR(25)             NOT NULL,
Category                              CHAR(20)             NOT NULL,
HourlyRate                         CURRENCY          NOT NULL,
TimeCard                             LOGICAL              NOT NULL,
HourlySalaried                   CHAR(1)               NOT NULL,
EmpType                             CHAR(1)               NOT NULL,
Terminated                       LOGICAL              NOT NULL,
ExemptCode                       CHAR(2)               NOT NULL,
Supervisor                           LOGICAL              NOT NULL,
SupervisorName                CHAR(50)             NOT NULL,
BirthDate                             DATE                     NOT NULL,
CollegeDegree                     CHAR(5)               NOT NULL,
CONSTRAINT                     Employee_PK    PRIMARY KEY(EmployeeNo
);

j)

select distinct
    Movie
from 
    CastMember
where 
    Movie in (
        select Movie
        from CastMember
        where Actor = 1
    )
    and Movie in (
        select Movie
        from CastMember
        where Actor = 2
    ) 
    and Movie in (
        select Movie
        from CastMember
        where Actor = 3
    )