Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
Where, And
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'));
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
SO_20171230_SqlServer
subquery & correlated subquery & "is null" condition
MyWall
tFuncionario
Libros
Dbms_20181cse0068
Outer Apply successer cell chek and get the result
QUAN LY MAYBAY
stackoverflow-54876809-trying-to-get-an-xml-file-into-sql-table
SQL for beginners( defined with errors while enforcing constraints)
Please log in to post a comment.