Scribd
Upload
65 views3 pages

SQL Assignment for Analytics Club IITB

This document contains instructions and SQL queries for an assignment on analyzing movie and actor databases. It includes queries to: 1) Find instructors from the Biology department and Computer Science courses with 3 credits 2) Find courses and total credits for a specific student ID 3) Find total credits for each student and their ID 4) Find names of students who have taken Computer Science courses 5) Find instructor IDs who have never taught a course It also provides instructions to create tables for a movie database and insert sample data, including for Charlie Chaplin. Additional queries find movies Charlie Chaplin acted in, actors with no roles, and all actors with their movie titles or null if none.

Uploaded by

GAYATHRI S S
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
65 views3 pages

SQL Assignment for Analytics Club IITB

This document contains instructions and SQL queries for an assignment on analyzing movie and actor databases. It includes queries to: 1) Find instructors from the Biology department and Computer Science courses with 3 credits 2) Find courses and total credits for a specific student ID 3) Find total credits for each student and their ID 4) Find names of students who have taken Computer Science courses 5) Find instructor IDs who have never taught a course It also provides instructions to create tables for a movie database and insert sample data, including for Charlie Chaplin. Additional queries find movies Charlie Chaplin acted in, actors with no roles, and all actors with their movie titles or null if none.

Uploaded by

GAYATHRI S S
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

LS SQL Assignment

Analytics Club, IITB


[Link]

Set 1 Queries:
1. Find the names of all the instructors from Biology department

SELECT name FROM instructor WHERE dept_name = 'Biology';

2. Find the names of courses in Computer science department which have 3


credits

SELECT title FROM course WHERE dept_name = 'Comp. Sci.' and credits = 3;

3. For the student with ID 12345 (or any other value), show all course id and
title of all courses registered for by the student.

SELECT takes.course_id, title FROM takes, course WHERE ID = 25946


AND takes.course_id = course.course_id;

4. 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.

SELECT sum(credits) FROM takes, course WHERE ID = 25946 AND


takes.course_id = course.course_id;

5. 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)

SELECT ID, SUM (credits) FROM takes, course WHERE takes.course_id =


course.course_id GROUP BY ID;

6. Find the names of all students who have taken any Comp. Sci. course
ever (there should be no duplicate names)

SELECT DISTINCT name FROM takes, course, student WHERE


takes.course_id = course.course_id AND course.dept_name = 'Comp. Sci.'
AND [Link] = [Link];

7. Display the IDs of all instructors who have never taught a course. Interpret
“taught” as “taught” or “is scheduled to teach”.

SELECT ID FROM instructor WHERE ID NOT IN (SELECT ID FROM


teaches);

8. As above, but display the names of the instructors also, not just the IDs.

SELECT ID, name FROM instructor WHERE ID NOT IN (SELECT ID FROM


teaches);

Set 2 Queries:
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.

create table actors (AID varchar(20) , name varchar(20) NOT NULL,


PRIMARY KEY(AID));

create table movies (MID varchar(20), title varchar(20) NOT NULL,


PRIMARY KEY(MID));

create table actor_role (MID varchar(20), AID varchar(20), rolename


varchar(20) NOT NULL, FOREIGN KEY (MID) references movies (MID),
FOREIGN KEY (AID) references movies (AID));

9. 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).

INSERT INTO actors VALUES ('22B1535 ', [Link]');


INSERT INTO actors VALUES ('24968N ', 'Charlie Chaplin ');
INSERT INTO actors VALUES ('67829H ', Jack Mink ');
INSERT INTO actors VALUES ('19295K ', 'Sheila Hopper ');
INSERT INTO movies VALUES ('123 ', ‘Death at the Park ');
INSERT INTO movies VALUES ('156 ', ‘Allegiant ');
INSERT INTO movies VALUES ('143 ', 'Garfield, The Great');
INSERT INTO movies VALUES ('100 ', 'Inception ');
INSERT INTO actor_role VALUES ('100 ', '67829H ', 'Missile man');
INSERT INTO actor_role VALUES ('143 ', '19295K ', 'Garfield ');
INSERT INTO actor_role VALUES ('123 ', '24968N ', 'High school student');
INSERT INTO actor_role VALUES ('156 ', '22B1535', ‘Tris ');
10. 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.

SELECT [Link], COUNT(rolename) FROM movies, actors, actor_role


WHERE actor_role.AID = [Link] AND actor_role.MID = [Link]
AND [Link] = 'Charlie Chaplin' GROUP BY [Link];

11. Write a query to list all actors who have not acted in any movie

SELECT * FROM actors WHERE AID NOT IN (SELECT AID FROM actor_role);

12. 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.

SELECT name,[Link] FROM movies, actors, actor_role WHERE


actor_role.AID = [Link] AND actor_role.MID = [Link] union
SELECT name, null FROM actors WHERE AID NOT IN (SELECT AID
FROM actor_role);

Common questions

Powered by AI

To create a movie database, define tables for actors, movies, and actor roles with appropriate constraints. For example: CREATE TABLE actors (AID varchar(20), name varchar(20) NOT NULL, PRIMARY KEY(AID)); CREATE TABLE movies (MID varchar(20), title varchar(20) NOT NULL, PRIMARY KEY(MID)); CREATE TABLE actor_role (MID varchar(20), AID varchar(20), rolename varchar(20) NOT NULL, FOREIGN KEY(MID) REFERENCES movies(MID), FOREIGN KEY(AID) REFERENCES actors(AID)); Primary keys like actors' AID and movies' MID uniquely identify records. Foreign keys ensure referential integrity by linking roles to actors and movies .

You can determine which instructors have never taught or been scheduled to teach a course with the query: SELECT ID, name FROM instructor WHERE ID NOT IN (SELECT ID FROM teaches); This query is important as it helps identify instructors who are listed in the system but have no teaching assignments, which might indicate data entry errors or inactive personnel .

Challenges include ensuring data integrity and establishing clear relationships among entities. Foreign key constraints address these by enforcing referential integrity, preventing orphan records, and ensuring that relationships between tables—such as actors and their roles in movies—are maintained correctly .

To find the names of all instructors from the Biology department, you can use the SQL query: SELECT name FROM instructor WHERE dept_name = 'Biology'; This query considers the 'dept_name' attribute to filter records specifically for the Biology department .

Use the query: SELECT movies.title, COUNT(rolename) FROM movies, actors, actor_role WHERE actor_role.AID = actors.AID AND actor_role.MID = movies.MID AND actors.name = 'Charlie Chaplin' GROUP BY movies.title; This query lists all movies Charlie Chaplin acted in and counts his roles in each, revealing his versatility in taking on multiple roles within the same movie .

The query SELECT sum(credits) FROM takes, course WHERE ID = 25946 AND takes.course_id = course.course_id aggregates credits for a student, providing insights into their workload and academic commitments. This data is useful for advising on course loads, measuring academic progress, and planning graduation timelines .

To insert data, use statements like INSERT INTO actors VALUES ('24968N', 'Charlie Chaplin'); These statements populate the database with essential entities, allowing subsequent queries and analyses of relationships like actor roles. Accurate data insertion is critical for maintaining database integrity and supporting meaningful applications .

To list all actors and their movie titles, including those with no roles, the query is: SELECT name, movies.title FROM movies, actors, actor_role WHERE actor_role.AID = actors.AID AND actor_role.MID = movies.MID UNION SELECT name, null FROM actors WHERE AID NOT IN (SELECT AID FROM actor_role); This provides a complete view of actor participation, highlighting both engagements and inactivity, crucial for thorough data reporting and analysis .

You can find students who have taken any Computer Science course with the query: SELECT DISTINCT name FROM takes, course, student WHERE takes.course_id = course.course_id AND course.dept_name = 'Comp. Sci.' AND takes.ID = student.ID; Using DISTINCT is beneficial as it prevents duplicate names, making the result set cleaner and more accurate, which is particularly useful in reporting .

The query to find total credits for each student along with their ID is: SELECT ID, SUM(credits) FROM takes, course WHERE takes.course_id = course.course_id GROUP BY ID; Aggregation with SUM is necessary to calculate the total credits across all courses a student is registered for, providing insights into their academic workload .

You might also like