Where, And, Sql Server

Language: Layout:

CREATE TABLE Director 
(
  Director_ID INTEGER NOT NULL,
   Director_FName VARCHAR(25),
   Director_LName VARCHAR(25),
   CONSTRAINT Director_pk Primary Key (Director_ID)
);
CREATE TABLE Genre
(
 Genre_ID INTEGER NOT NULL,
 Genre_Class varchar(20),
 CONSTRAINT Genre_pk Primary Key (Genre_ID)
);
 
CREATE TABLE Movies 
( 
Movie_id INTEGER NOT NULL,
Movie_Name varchar (30),
Released_Year INTEGER,
Movie_Time INTEGER,
Planned_budget decimal (10,2),
Genre_ID INTEGER NOT NULL,
Director_ID INTEGER NOT NULL,
CONSTRAINT Movies_pk Primary Key (Movie_id),
CONSTRAINT Movies_Director_fk Foreign Key (Director_ID) References Director (Director_ID),
CONSTRAINT Movies_Genre_fk Foreign Key (Genre_ID) References Genre (Genre_ID)
);

/*Director Table*/
INSERT INTO Director values (3001, 'James', 'Cook')
INSERT INTO Director values (3002, 'John', 'Books')
INSERT INTO Director values (3003, 'Sam', 'Wills')
INSERT INTO Director values (3004, 'Gary', 'Joe')
INSERT INTO Director values (3005, 'Mery', 'Ann')
INSERT INTO Director values (3006, 'Billy', 'Joe')
INSERT INTO Director values (3007, 'Peter', 'Doc')
INSERT INTO Director values (3008, 'Tashi', 'Dorji')
INSERT INTO Director values (131, 'Pema', 'Wangmo')
INSERT INTO Director values (132, 'Karma', 'Dem')

/*Task 3 Insert 5 records to Director table.*/
INSERT INTO Director values (142, 'James', 'Alex')
INSERT INTO Director values (1589, 'James', 'Cameroon')
INSERT INTO Director values (162, 'Yezor', 'Thapa')
INSERT INTO Director values (172, 'Peter', 'Gurung')
INSERT INTO Director values (182, 'Reshu', 'Rai')

/*Genre Table*/
INSERT INTO  Genre values (2001, 'Drama')
INSERT INTO  Genre values (2002, 'Horror')
INSERT INTO  Genre values (2003, 'Drama')
INSERT INTO  Genre values (2007, 'Comedy')
INSERT INTO  Genre values (2008, 'Drama')
INSERT INTO  Genre values (2009, 'Adventure')
INSERT INTO  Genre values (275, 'Action')
INSERT INTO  Genre values (276, 'Romantic')
INSERT INTO  Genre values (277, 'Animated')
INSERT INTO  Genre values (278, 'Action')

/*Extra data added for Task 20*/
INSERT INTO  Genre values (2021, 'Play')
INSERT INTO  Genre values (2011, 'Cartoon')
INSERT INTO  Genre values (2081, 'Romantic Comedy')
INSERT INTO  Genre values (2082, 'Romantic Drama')

/*Extra data added for Task19 */
INSERT INTO  Genre values (2123, 'Animated')
INSERT INTO  Genre values (27237, 'Animated')

/*Movies Table*/
/*Task 2 Insert 10 records to Movies table. */

INSERT INTO  Movies values (11, 'Goodpeople', 2018, 9, 3.50, 2001, 3001)
INSERT INTO  Movies values (21, 'Playful', 2001, 210, 0.50, 2002, 3002)
INSERT INTO  Movies values (31, 'Frozen', 2018, 119, 3.0, 2003, 3003)
INSERT INTO  Movies values (41, 'Joy', 2001, 729, 10.0, 2007, 3004)
INSERT INTO  Movies values (51, 'Aladdin', 2000, 339, 5.0, 2008, 3005)
INSERT INTO  Movies values (61, 'Finding Nemo', 2017, 149, 2.20, 2009, 3006)
INSERT INTO  Movies values (71, 'GoodBye', 2001, 59, 6.50, 275, 3007)
INSERT INTO  Movies values (81, 'Breakthrough', 2008, 169, 12.50, 276, 3008)
INSERT INTO  Movies values (82, 'Long shot', 2018, 79, 2.50, 277, 131)
INSERT INTO  Movies values (10, 'Great someone', 2001, 189, 9.50, 278, 1589)

/*Extra data added for Task19 */
INSERT INTO  Movies values (123, 'Angry Bird', 2018, 9, 3.50, 2123, 182)
INSERT INTO  Movies values (1164, 'Room', 2018, 9, 3.50, 27237, 172)

/*Task 17 Display all the movies written by Director “James Cameroon”*/
SELECT Movie_id, Movie_Name,Director_id
FROM Movies 
WHERE Director_id=(SELECT Director_id 
FROM Movies WHERE Director_id=(SELECT Director_id 
FROM Director 
WHERE Director_FName='James' 
AND Director_LName='cameroon'));

View schema
Execution time: 0,05 sec, rows selected: 1, rows affected: 43, absolute service time: 0,22 sec, absolute service time: 0,22 sec