Please write a SELECT statement to display each DVD title with its # of copy in the DVD table. DVD titles must have at least two ‘A’s. Please display the result in descending order of title’s number copy. The result should be identical to below results:
Q1 Please write SQL codes to query data from dvd table based on below criteria
Please write a SELECT statement to display each DVD title with its # of copy in the DVD table. DVD titles must have at least two ‘A’s. Please display the result in descending order of title’s number copy. The result should be identical to below results:
[screenshot]
Here is the DVD TABLE:
CREATE TABLE DVD (
dvd_ID NUMBER(4) NOT NULL,
title_ID NUMBER(3) NOT NULL,
title VARCHAR2(20) NOT NULL,
status VARCHAR2(10) NOT NULL,
CONSTRAINT dvd_pk PRIMARY KEY (dvd_ID)
);
this is all the data in the DVD table
CREATE SEQUENCE DVD_SEQ
START WITH 1000
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 9999
NOCYCLE
CACHE 50;
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 10, 'IN STORE', 'DANCE WITH WOLF');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 10, 'IN STORE', 'DANCE WITH WOLF');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 11, 'IN STORE', 'THE PERFECT STROM');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 11, 'IN STORE', 'THE PERFECT STROM');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 12, 'IN STORE', 'CHICKEN RUN');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 13, 'IN STORE', 'THE BUGS LIFE');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 13, 'IN STORE', 'THE BUGS LIFE');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 14, 'IN STORE', 'SNOW WHITE');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 14, 'IN STORE', 'SNOW WHITE');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 15, 'IN STORE', 'TOY STORY I');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 16, 'IN STORE', 'TOY STORY II');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 17, 'IN STORE', 'LOVE STORY');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 18, 'IN STORE', 'AIR FORCE ONE');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 18, 'IN STORE', 'AIR FORCE ONE');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 19, 'IN STORE', 'AWAY FROM AFRICA');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 20, 'OUT STORE', 'FRIDAY 13THF');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 21, 'IN STORE', 'SILENT LAMB');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 22, 'IN STORE', 'SHAN HI MOON');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 22, 'IN STORE', 'SHAN HI MOON');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 23, 'IN STORE', 'THE GOLDAN POND');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 23, 'IN STORE', 'THE GOLDAN POND');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 24, 'IN STORE', 'POKIMON I');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 25, 'IN STORE', 'POKIMON II');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 25, 'IN STORE', 'POKIMON II');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 26, 'IN STORE', 'SLEEPY BEAUTY');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 26, 'IN STORE', 'SLEEPY BEAUTY');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 27, 'IN STORE', 'TOY STORY I');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 28, 'IN STORE', 'THE MELODY');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 29, 'IN STORE', 'LOVE STORY II');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 30, 'IN STORE', 'CINDERALA');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 30, 'IN STORE', 'CINDERALA');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 31, 'IN STORE', 'THE GIRASIC PARK');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 32, 'IN STORE', 'BACK TO GIRASIC PARK');
INSERT INTO DVD VALUES (DVD_SEQ.nextval, 33, 'IN STORE', 'THE GARZELA');
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
