Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
SQL Movie-Rating Query Exercises
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
drop table if exists HanW_movie; drop table if exists HanW_reviewer; drop table if exists HanW_rating; create table HanW_movie("mid" int, "title" text, "year" int, "director" text); create table HanW_reviewer("rid" int, "name" text); create table HanW_rating("rid" int, "mid" int, "stars" int, "ratingdate" date); insert into HanW_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 HanW_reviewer("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 HanW_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'); /* Table Schema HanW_movie("mid","title","year","director") HanW_reviewer("rid","name") HanW_rating("rid", "mid", "stars", "ratingdate") */ -- Q1: Find the names of all reviewers who rated Gone with the Wind. SELECT DISTINCT a.name FROM HanW_reviewer a JOIN HanW_rating b on a.rid = b.rid JOIN HanW_movie c on b.mid = c.mid WHERE c.title = 'Gone with the Wind' ; -- Q2: For any rating where the reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars. SELECT a.name, c.title, b.stars FROM HanW_reviewer a JOIN HanW_rating b on a.rid = b.rid JOIN HanW_movie c on b.mid = c.mid WHERE a.name = c.director ; -- Q3: Return all reviewer names and movie names together in a single list, alphabetized. SELECT name FROM HanW_reviewer UNION ALL SELECT title as name FROM HanW_movie ORDER BY name
absolute service time: 0,43 sec
edit mode
|
history
|
discussion
name
1
Mike Anderson
2
Sarah Martinez
name
title
stars
1
James Cameron
Avatar
5
name
1
Ashley White
2
Avatar
3
Brittany Harris
4
Chris Jackson
5
Daniel Lewis
6
E.T.
7
Elizabeth Thomas
8
Gone with the Wind
9
James Cameron
10
Mike Anderson
11
Raiders of the Lost Ark
12
Sarah Martinez
13
Snow White
14
Star Wars
15
The Sound of Music
16
Titanic