Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
MovieRating SQL
drop table if exists yuhaoz_highschooler; drop table if exists yuhaoz_friend; drop table if exists yuhaoz_likes; create table yuhaoz_highschooler("id" int, "name" varchar(30), "grade" int); create table yuhaoz_friend("id1" int, "id2" int); create table yuhaoz_likes("id1" int, "id2" int); insert into yuhaoz_highschooler("id","name","grade") values (1510, 'Jordan', 9), (1689, 'Gabriel', 9), (1381, 'Tiffany', 9), (1709, 'Cassandra', 9), (1101, 'Haley', 10), (1782, 'Andrew', 10), (1468, 'Kris', 10), (1641, 'Brittany', 10), (1247, 'Alexis', 11), (1316, 'Austin', 11), (1911, 'Gabriel', 11), (1501, 'Jessica', 11), (1304, 'Jordan', 12), (1025, 'John', 12), (1934, 'Kyle', 12), (1661, 'Logan', 12); insert into yuhaoz_friend("id1","id2") values (1510, 1381), (1101, 1501), (1510, 1689), (1689, 1709), (1381, 1247), (1709, 1247), (1689, 1782), (1782, 1468), (1782, 1316), (1782, 1304), (1468, 1101), (1468, 1641), (1101, 1641), (1247, 1911), (1247, 1501), (1911, 1501), (1501, 1934), (1316, 1934), (1934, 1304), (1304, 1661), (1661, 1025), (1689, 1709), (1709, 1689), (1782, 1709), (1911, 1247), (1247, 1468), (1641, 1468), (1316, 1304), (1501, 1934), (1934, 1501), (1025, 1101); insert into yuhaoz_likes("id1","id2") values (1689, 1709), (1709, 1689), (1782, 1709), (1911, 1247), (1247, 1468), (1641, 1468), (1316, 1304), (1501, 1934), (1934, 1501), (1025, 1101), (1101, 1381), (1025, 1641); drop table if exists yuhaoz_movie; drop table if exists yuhaoz_reviewer; drop table if exists yuhaoz_rating; create table yuhaoz_movie("mid" int, "title" text, "year" int, "director" text); create table yuhaoz_review("rid" int, "name" text); create table yuhaoz_rating("rid" int, "mid" int, "stars" int, "ratingdate" date); insert into yuhaoz_movie("mid","title","year","director") values (101, 'Gone with the Wind', 1939, 'Victor Fleming'), (102, 'Star Wars', 1977, 'George Lucas'), (103, 'The Sound of Music', 1965, 'Robert Wise'), (104, 'E.T.', 1982, 'Steven Spielberg'), (105, 'Titanic', 1997, 'James Cameron'), (106, 'Snow White', 1937, null), (107, 'Avatar', 2009, 'James Cameron'), (108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg'); insert into yuhaoz_review("rid","name") values (201, 'Sarah Martinez'), (202, 'Daniel Lewis'), (203, 'Brittany Harris'), (204, 'Mike Anderson'), (205, 'Chris Jackson'), (206, 'Elizabeth Thomas'), (207, 'James Cameron'), (208, 'Ashley White'); insert into yuhaoz_rating("rid", "mid", "stars", "ratingdate") values (201, 101, 2, '2011-01-22'), (201, 101, 4, '2011-01-27'), (202, 106, 4, null), (203, 103, 2, '2011-01-20'), (203, 108, 4, '2011-01-12'), (203, 108, 2, '2011-01-30'), (204, 101, 3, '2011-01-09'), (205, 103, 3, '2011-01-27'), (205, 104, 2, '2011-01-22'), (205, 108, 4, null), (206, 107, 3, '2011-01-15'), (206, 106, 5, '2011-01-19'), (207, 107, 5, '2011-01-20'), (208, 104, 3, '2011-01-02'); select distinct title, director from yuhaoz_movie where director = 'Steven Spielberg'; select distinct ratingdate from yuhaoz_movie a join yuhaoz_rating b on a.mid = b.mid where stars>3 and b.ratingdate is not null order by 1
run
|
edit
|
history
|
help
0
create myStudents table
Webiste SQL Analysis
Inner Join
Assignment 1(SET A)
Tememons Dashboard
stackoverflow example for 47702144
w3c sql data
Cilinica_X
Weekly Average Starting on Different Days
Ass2 q2