Skip to content
Advertisement

Join tables together

I need to write a SQL statement to find the ‘Action’ (category) films with the shortest running time (length). Your query should output the titles and lengths of the films. This is the database schema I have. enter image description here

CREATE TABLE category (
  category_id NUMBER(3) NOT NULL,
  name varchar2(25)
);

CREATE TABLE film_category (
  film_id NUMBER(5) NOT NULL,
  category_id NUMBER(3) NOT NULL
);

CREATE TABLE film (
  film_id NUMBER(5) NOT NULL,
  title varchar2(255),
  description varchar2(255),
  release_year NUMBER(4) DEFAULT NULL,
  language_id NUMBER(3) NOT NULL,
  original_language_id NUMBER(3) DEFAULT NULL,
  rental_duration NUMBER(3) DEFAULT 3 NOT NULL,
  rental_rate NUMBER(4,2) DEFAULT '4.99' NOT NULL,
  length NUMBER(5) DEFAULT NULL,
  replacement_cost NUMBER(5,2) DEFAULT '19.99' NOT NULL,
  rating varchar2(8) DEFAULT 'G' NOT NULL,
  special_features varchar2(255) DEFAULT NULL
);

I created 3 tables and inserted some records on these tables.

I write my query like this:

 SELECT F.title, MIN(F.length)
 FROM FILM F JOIN
 FILM_CATEGORY FC
 ON F.FILM_ID = FC.FILM_ID RIGHT JOIN
 CATEGORY C
 ON C.CATEGORY_ID = FC.CATEGORY_ID 
 WHERE C.NAME = 'ACTION'
 GROUP BY title;

But there are no rows selected when I execute this query, any hints? Thanks in advance.

Update: Insert some records to tables

INSERT INTO category VALUES(1, 'Action');
INSERT INTO category VALUES(2, 'Animation');
INSERT INTO category VALUES(3, 'Children');
INSERT INTO category VALUES(4, 'Classics');
INSERT INTO category VALUES(5, 'Comedy');
INSERT INTO category VALUES(6, 'Documentary');
INSERT INTO category VALUES(7, 'Drama');
INSERT INTO category VALUES(8, 'Family');
INSERT INTO category VALUES(9, 'Foreign');
INSERT INTO category VALUES(10, 'Games');
INSERT INTO category VALUES(11, 'Horror');
INSERT INTO category VALUES(12, 'Music');
INSERT INTO category VALUES(13, 'New');
INSERT INTO category VALUES(14, 'Sci-Fi');
INSERT INTO category VALUES(15, 'Sports');
INSERT INTO category VALUES(16, 'Travel');

INSERT INTO film_category VALUES(19, 1);
INSERT INTO film_category VALUES(21, 1);
INSERT INTO film_category VALUES(29, 1);
INSERT INTO film_category VALUES(38, 1);
INSERT INTO film_category VALUES(56, 1);
INSERT INTO film_category VALUES(67, 1);
INSERT INTO film_category VALUES(97, 1);
INSERT INTO film_category VALUES(105, 1);
INSERT INTO film_category VALUES(111, 1);
INSERT INTO film_category VALUES(115, 1);
INSERT INTO film_category VALUES(126, 1);
INSERT INTO film_category VALUES(130, 1);
INSERT INTO film_category VALUES(162, 1);
INSERT INTO film_category VALUES(194, 1);
INSERT INTO film_category VALUES(205, 1);
INSERT INTO film_category VALUES(210, 1);
INSERT INTO film_category VALUES(212, 1);
INSERT INTO film_category VALUES(229, 1);
INSERT INTO film_category VALUES(250, 1);
INSERT INTO film_category VALUES(252, 1);
INSERT INTO film_category VALUES(253, 1);
INSERT INTO film_category VALUES(271, 1);
INSERT INTO film_category VALUES(287, 1);
INSERT INTO film_category VALUES(292, 1);
INSERT INTO film_category VALUES(303, 1);
INSERT INTO film_category VALUES(318, 1);
INSERT INTO film_category VALUES(327, 1);
INSERT INTO film_category VALUES(329, 1);
INSERT INTO film_category VALUES(360, 1);
INSERT INTO film_category VALUES(371, 1);
INSERT INTO film_category VALUES(375, 1);
INSERT INTO film_category VALUES(395, 1);
INSERT INTO film_category VALUES(417, 1);
INSERT INTO film_category VALUES(501, 1);
INSERT INTO film_category VALUES(511, 1);
INSERT INTO film_category VALUES(530, 1);

INSERT INTO film VALUES(19, 'AMADEUS HOLY', 'A Emotional Display of a Pioneer And a Technical Writer who must Battle a Man in A Baloon', 1959, 1, NULL, 6, '0.99', 113, '20.99', 'PG', 'Commentaries');
INSERT INTO film VALUES(20, 'AMELIE HELLFIGHTERS', 'A Boring Drama of a Woman And a Squirrel who must Conquer a Student in A Baloon', 1965, 1, NULL, 4, '4.99', 79, '23.99', 'R', 'Commentaries');
INSERT INTO film VALUES(21, 'AMERICAN CIRCUS', 'A Insightful Drama of a Girl And a Astronaut who must Face a Database Administrator in A Shark Tank', 1966, 1, NULL, 3, '4.99', 129, '17.99', 'R', 'Trailers');
INSERT INTO film VALUES(22, 'AMISTAD MIDSUMMER', 'A Emotional Character Study of a Dentist And a Crocodile who must Meet a Sumo Wrestler in California', 1994, 1, NULL, 6, '2.99', 85, '10.99', 'G', 'Trailers');
INSERT INTO film VALUES(23, 'ANACONDA CONFESSIONS', 'A Lacklusture Display of a Dentist And a Dentist who must Fight a Girl in Australia', 1986, 1, NULL, 3, '0.99', 92, '9.99', 'R', 'Commentaries');
INSERT INTO film VALUES(24, 'ANALYZE HOOSIERS', 'A Thoughtful Display of a Explorer And a Pastry Chef who must Overcome a Feminist in The Sahara Desert', 2001, 1, NULL, 6, '2.99', 181, '19.99', 'R', 'Deleted Scenes');
INSERT INTO film VALUES(25, 'ANGELS LIFE', 'A Thoughtful Display of a Woman And a Astronaut who must Battle a Robot in Berlin', 1981, 1, NULL, 3, '2.99', 74, '15.99', 'G', 'Trailers');
INSERT INTO film VALUES(26, 'ANNIE IDENTITY', 'A Amazing Panorama of a Pastry Chef And a Boat who must Escape a Woman in An Abandoned Amusement Park', 1982, 1, NULL, 3, '0.99', 86, '15.99', 'G', 'Behind the Scenes');
INSERT INTO film VALUES(27, 'ANONYMOUS HUMAN', 'A Amazing Reflection of a Database Administrator And a Astronaut who must Outrace a Database Administrator in A Shark Tank', 1995, 1, NULL, 7, '0.99', 179, '12.99', 'NC-17', 'Commentaries');
INSERT INTO film VALUES(28, 'ANTHEM LUKE', 'A Touching Panorama of a Waitress And a Woman who must Outrace a Dog in An Abandoned Amusement Park', 1968, 1, NULL, 5, '4.99', 91, '16.99', 'PG-13', 'Commentaries');
INSERT INTO film VALUES(29, 'ANTITRUST TOMATOES', 'A Fateful Yarn of a Womanizer And a Feminist who must Succumb a Database Administrator in Ancient India', 1987, 1, NULL, 5, '2.99', 168, '11.99', 'NC-17', 'Deleted Scenes');
INSERT INTO film VALUES(30, 'ANYTHING SAVANNAH', 'A Epic Story of a Pastry Chef And a Woman who must Chase a Feminist in An Abandoned Fun House', 1980, 1, NULL, 4, '2.99', 82, '27.99', 'R', 'Trailers');
INSERT INTO film VALUES(31, 'APACHE DIVINE', 'A Awe-Inspiring Reflection of a Pastry Chef And a Teacher who must Overcome a Sumo Wrestler in A U-Boat', 1964, 1, NULL, 5, '4.99', 92, '16.99', 'NC-17', 'Commentaries');
INSERT INTO film VALUES(32, 'APOCALYPSE FLAMINGOS', 'A Astounding Story of a Dog And a Squirrel who must Defeat a Woman in An Abandoned Amusement Park', 1995, 1, NULL, 6, '4.99', 119, '11.99', 'R', 'Trailers');
INSERT INTO film VALUES(33, 'APOLLO TEEN', 'A Action-Packed Reflection of a Crocodile And a Explorer who must Find a Sumo Wrestler in An Abandoned Mine Shaft', 1980, 1, NULL, 5, '2.99', 153, '15.99', 'PG-13', 'Deleted Scenes');
INSERT INTO film VALUES(34, 'ARABIA DOGMA', 'A Touching Epistle of a Madman And a Mad Cow who must Defeat a Student in Nigeria', 1971, 1, NULL, 6, '0.99', 62, '29.99', 'NC-17', 'Behind the Scenes');
INSERT INTO film VALUES(35, 'ARACHNOPHOBIA ROLLERCOASTER', 'A Action-Packed Reflection of a Pastry Chef And a Composer who must Discover a Mad Scientist in The First Manned Space Station', 2004, 1, NULL, 4, '2.99', 147, '24.99', 'PG-13', 'Trailers');
INSERT INTO film VALUES(36, 'ARGONAUTS TOWN', 'A Emotional Epistle of a Forensic Psychologist And a Butler who must Challenge a Waitress in An Abandoned Mine Shaft', 1996, 1, NULL, 7, '0.99', 127, '12.99', 'PG-13', 'Trailers');
INSERT INTO film VALUES(37, 'ARIZONA BANG', 'A Brilliant Panorama of a Mad Scientist And a Mad Cow who must Meet a Pioneer in A Monastery', 1960, 1, NULL, 3, '2.99', 121, '28.99', 'PG', 'Commentaries');
INSERT INTO film VALUES(38, 'ARK RIDGEMONT', 'A Beautiful Yarn of a Pioneer And a Monkey who must Pursue a Explorer in The Sahara Desert', 1988, 1, NULL, 6, '0.99', 68, '25.99', 'NC-17', 'Deleted Scenes');
INSERT INTO film VALUES(39, 'ARMAGEDDON LOST', 'A Fast-Paced Tale of a Boat And a Teacher who must Succumb a Composer in An Abandoned Mine Shaft', 1961, 1, NULL, 5, '0.99', 99, '10.99', 'G', 'Trailers');
INSERT INTO film VALUES(40, 'ARMY FLINTSTONES', 'A Boring Saga of a Database Administrator And a Womanizer who must Battle a Waitress in Nigeria', 1968, 1, NULL, 4, '0.99', 148, '22.99', 'R', 'Trailers');

My dataset is bit huge and I can only show some of the data.

This is the result I have when I run the formatted code below, it doesn’t work:

enter image description here

COLUMN F.title FORMAT A20
COLUMN MIN(F.length) FORMAT 999

SELECT F.title, MIN(F.length)
FROM FILM F JOIN
FILM_CATEGORY FC
ON F.FILM_ID = FC.FILM_ID RIGHT JOIN
CATEGORY C
ON C.CATEGORY_ID = FC.CATEGORY_ID 
WHERE C.NAME = 'Action'
GROUP BY title;

Advertisement

Answer

I am assuming you are using Oracle, since you are using VARCHAR2. If no records are returned, I assume you have your database set with case sensitivity on, so ‘Action’ <> ‘ACTION’.

Either change your query so the case is correct, or change the where clause to not be case sensitive, and you should have data returned.

I also hope there are some indexes on the tables which you haven’t mentioned…

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement