SQL进阶---第二单元(第五到第八课)、Queries



第十五课、Like-l

QUERIES Like-I

SELECT * FROM movies WHERE name LIKE 'Se_en';

LIKE can be a useful operator whenyou want to compare similar values. Here, we arecomparing two movies with the same name but are spelled differently.

1. LIKE is a special operator usedwith the WHERE clause to search for aspecific pattern in a column.

2. name LIKESe_en is a condition evaluating the name column for a specific pattern.

3. Se_en represents a pattern with a wildcard character. The _ means you can substitute any individual character here withoutbreaking the pattern. The names Seven and Se7en both match this pattern.

% is another wildcard characterthat can be used with LIKE. We will learn more about % in the next exercise.

      Instructions:

1.Let's use LIKE to query a few other patterns. In the code editor type

SELECT * FROM movies 

        WHERE name LIKE 'a%';

SELECT * FROM movies

                 WHEREname LIKE 'a%';

QueryResults

id

name

genre

year

imdb_rating

1

Avatar

action

2009

7.9

7

Avengers: Age of Ultron

action

2015

7.9

16

American Sniper

action

2014

7.4

79

Alvin and the Chipmunks: The Squeakquel

comedy

2009

4.4

82

Aladdin

comedy

1992

8.0

83

Alvin and the Chipmunks

comedy

2007

5.3

87

Austin Powers in Goldmember

comedy

2002

6.2

92

Austin Powers: The Spy Who Shagged Me

comedy

1999

6.6

117

Aliens

horror

1986

8.4

119

Annabelle

horror

2014

5.4

143

Anaconda

horror

1997

4.6

181

As Good as It Gets

romance

1997

7.8

182

American Pie 2

romance

2001

6.4

209

Air Force One

drama

1997

6.4

211

A Beautiful Mind

drama

2001

8.2

DatabaseSchema

Movies       220 rows

id

INTEGER

name

TEXT

genre

TEXT

year

INTEGER

imdb_rating

REAL

2.Remove the previous query. Thentype

SELECT * FROM movies 

        WHERE name LIKE '%man%';

What do you think % is doing?

SELECT * FROM movies

                 WHEREname LIKE '%man%';

 

QueryResults

id

name

genre

year

imdb_rating

9

Pirates of the Caribbean: Dead Mans Chest

action

2006

7.3

10

Iron Man 3

action

2013

7.3

11

Spider-Man

action

2002

7.3

14

Spider-Man 2

action

2004

7.3

18

Spider-Man 3

action

2007

6.2

21

Iron Man

action

2008

7.9

23

Iron Man 2

action

2010

7.1

31

Man of Steel

action

2013

7.2

34

The Amazing Spider-Man

action

2012

7.1

39

Batman

action

1989

7.6

164

Pretty Woman

romance

1990

6.9

208

Rain Man

drama

1988

8.0

DatabaseSchema

Movies           220 rows

id

INTEGER

name

TEXT

genre

TEXT

year

INTEGER

imdb_rating

REAL

 

 

第十六课、Like-II

QUERIES Like-II

SELECT * FROM movies 

        WHERE name LIKE 'A%';

This statement filters the result set to only include movieswith names that begin with the letter "A"

% is a wildcard character thatmatches zero or more missing letters in the pattern.

• A% matches all movies with names that begin with "A"

• %a matches all movies that end with "a"

SELECT * FROM movies 

        WHERE name LIKE '%man%';

Youcan use % both before and after apattern. Here, any movie that contains the word "man" in itsname will be returned in the result set.Notice, that LIKE is not case sensitive. "Batman" and "Man Of Steel" both appear inthe result set.

 

instructions

1.BETWEEN is another special operator that can be used in a WHERE clause. In the code editor, type

SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J';

QueryResults

id

name

genre

year

imdb_rating

211

A Beautiful Mind

drama

2001

8.2

209

Air Force One

drama

1997

6.4

82

Aladdin

comedy

1992

8.0

117

Aliens

horror

1986

8.4

83

Alvin and the Chipmunks

comedy

2007

5.3

79

Alvin and the Chipmunks: The Squeakquel

comedy

2009

4.4

182

American Pie 2

romance

2001

6.4

16

American Sniper

action

2014

7.4

143

Anaconda

horror

1997

4.6

119

Annabelle

horror

2014

5.4

181

As Good as It Gets

romance

1997

7.8

87

Austin Powers in Goldmember

comedy

2002

6.2

92

Austin Powers: The Spy Who Shagged Me

comedy

1999

6.6

1

Avatar

action

2009

7.9

7

Avengers: Age of Ultron

action

2015

7.9

88

Back to the Future

comedy

1985

8.5

39

Batman

action

1989

7.6

153

Beauty and the Beast

romance

1991

8.0

46

Beverly Hills Cop

action

1984

7.3

218

Big Daddy

drama

1999

6.4

78

Big Hero 6

comedy

2014

7.9

138

Blade

horror

1998

7.1

124

Blade II

horror

2002

6.7

75

Brave

comedy

2012

7.2

170

Bridesmaids

romance

2011

6.8

72

Bruce Almighty

comedy

2003

6.7

36

Captain America: The Winter Soldier

action

2014

7.8

71

Cars

comedy

2006

7.2

197

Cast Away

drama

2000

7.7

217

Catch Me If You Can

drama

2002

8.0

90

Charlie and the Chocolate Factory

comedy

2005

6.7

156

Cinderella

romance

2015

7.1

190

Click

romance

2006

6.4

127

Cloverfield

horror

2008

7.1

131

Constantine

horror

2005

6.9

203

Dances with Wolves

drama

1990

8.0

128

Dark Shadows

horror

2012

6.2

200

Dawn of the Planet of the Apes

drama

2014

7.7

68

Despicable Me

comedy

2010

7.7

55

Despicable Me 2

comedy

2013

7.5

122

Dracula

horror

1992

7.5

168

Elf

romance

2003

6.9

139

End of Days

horror

1999

5.7

45

Fast & Furious 6

action

2013

7.2

171

Fifty Shades of Grey

romance

2015

4.2

54

Finding Nemo

comedy

2003

8.2

148

Forrest Gump

romance

1994

8.8

123

Freddy vs. Jason

horror

2003

5.8

145

Friday the 13th

horror

2009

5.6

53

Frozen

comedy

2013

7.6

17

Furious Seven

action

2015

7.4

155

Ghost

romance

1990

7.0

76

Ghostbusters

comedy

1984

7.8

202

Gladiator

drama

2000

8.5

214

Gone Girl

drama

2014

8.2

157

Gone with the Wind

romance

1939

8.2

100

Gremlins

horror

1984

7.2

19

Guardians of the Galaxy

action

2014

8.1

216

Hannibal

drama

2001

6.7

95

Happy Feet

comedy

2006

6.5

192

Harry Potter and the Deathly Hallows: Part 2

drama

2011

8.1

165

Hitch

romance

2005

6.7

61

Home Alone

comedy

1990

7.4

66

How the Grinch Stole Christmas

comedy

2000

6.0

195

I Am Legend

drama

2007

7.2

133

I Know What You Did Last Summer

horror

1997

5.6

96

Ice Age: Dawn of the Dinosaurs

comedy

2009

7.0

97

Ice Age: The Meltdown

comedy

2006

6.9

30

Inception

action

2010

8.8

27

Independence Day

action

1996

6.9

22

Indiana Jones and the Kingdom of the Crystal Skull

action

2008

6.2

56

Inside Out

comedy

2015

8.6

121

Insidious: Chapter 2

horror

2013

6.6

201

Interstellar

drama

2014

8.7

106

Interview with the Vampire: The Vampire Chronicles

horror

1994

7.6

21

Iron Man

action

2008

7.9

23

Iron Man 2

action

2010

7.1

10

Iron Man 3

action

2013

7.3

DatabaseSchema

Movies         220 rows

id

INTEGER

name

TEXT

genre

TEXT

year

INTEGER

imdb_rating

REAL

 

2.Remove theprevious query. Then type

SELECT * FROM movies

       WHEREyearBETWEEN1990AND2000;

 

SELECT * FROM movies

                 WHEREyear BETWEEN 1990 AND 2000;

QueryResults

id

name

genre

year

imdb_rating

5

Star Wars: Episode I - The Phantom Menace

action

1999

6.6

27

Independence Day

action

1996

6.9

42

Twister

action

1996

6.3

49

The Lost World: Jurassic Park

action

1997

6.5

61

Home Alone

comedy

1990

7.4

66

How the Grinch Stole Christmas

comedy

2000

6.0

69

Men in Black

comedy

1997

7.2

70

Toy Story 2

comedy

1999

7.9

80

Mrs. Doubtfire

comedy

1993

6.9

82

Aladdin

comedy

1992

8.0

92

Austin Powers: The Spy Who Shagged Me

comedy

1999

6.6

99

What Lies Beneath

horror

2000

6.6

101

The Blair Witch Project

horror

1999

6.4

106

Interview with the Vampire: The Vampire Chronicles

horror

1994

7.6

108

Scream

horror

1996

7.2

110

Scream 2

horror

1997

6.1

111

Sleepy Hollow

horror

1999

7.4

113

The Haunting

horror

1999

4.9

114

Scream 3

horror

2000

5.5

122

Dracula

horror

1992

7.5

133

I Know What You Did Last Summer

horror

1997

5.6

138

Blade

horror

1998

7.1

139

End of Days

horror

1999

5.7

143

Anaconda

horror

1997

4.6

144

Wolf

horror

1994

6.2

147

Titanic

romance

1997

7.7

148

Forrest Gump

romance

1994

8.8

153

Beauty and the Beast

romance

1991

8.0

155

Ghost

romance

1990

7.0

162

What Women Want

romance

2000

6.4

164

Pretty Woman

romance

1990

6.9

167

Theres Something About Mary

romance

1998

7.1

169

Tarzan

romance

1999

7.2

172

Robin Hood: Prince of Thieves

romance

1991

6.9

175

Jerry Maguire

romance

1996

7.3

177

Runaway Bride

romance

1999

5.4

181

As Good as It Gets

romance

1997

7.8

187

Pocahontas

romance

1995

6.6

191

The Lion King

drama

1994

8.5

193

The Sixth Sense

drama

1999

8.2

197

Cast Away

drama

2000

7.7

199

Saving Private Ryan

drama

1998

8.6

202

Gladiator

drama

2000

8.5

203

Dances with Wolves

drama

1990

8.0

204

The Perfect Storm

drama

2000

6.4

209

Air Force One

drama

1997

6.4

218

Big Daddy

drama

1999

6.4

219

Se7en

drama

1995

8.6

DatabaseSchema

movies220 rows

id

INTEGER

name

TEXT

genre

TEXT

year

INTEGER

imdb_rating

REAL

 

 

第十七课、Between

QUERIES  Between

The BETWEEN operator is used to filterthe result set within a certain range. The values can be numbers, text ordates.

SELECT * FROM movies 

        WHERE name BETWEEN 'A' AND 'J';

This statement filters the result set to only include movieswith names that begin with letters"A" up to but not including "J".

SELECT * FROM movies 

        WHERE year BETWEEN 1990 AND 2000;

In this statement, the BETWEENoperator is being used to filter the result set to only includemovies with years between 1990 up to andincluding 2000.

 

Instructions:

1.Instead of returning every moviemade BETWEEN 1990 and 2000, let's just returnthe movies that are comedies. In the code editor, type

SELECT * FROM movies 

        WHERE year BETWEEN 1990 AND 2000 

                 AND genre = 'comedy';

 

SELECT * FROM movies

                 WHEREyear BETWEEN 1990 AND 2000

                                           ANDgenre = 'comedy';

QueryResults

id

name

genre

year

imdb_rating

61

Home Alone

comedy

1990

7.4

66

How the Grinch Stole Christmas

comedy

2000

6.0

69

Men in Black

comedy

1997

7.2

70

Toy Story 2

comedy

1999

7.9

80

Mrs. Doubtfire

comedy

1993

6.9

82

Aladdin

comedy

1992

8.0

92

Austin Powers: The Spy Who Shagged Me

comedy

1999

6.6

DatabaseSchema

Movies         220 rows

id

INTEGER

name

TEXT

genre

TEXT

year

INTEGER

imdb_rating

REAL

 

 

第十八课、And

QUERIES And

SELECT * FROM movies 

        WHERE year BETWEEN 1990 and 2000 

                 AND genre = 'comedy';

Sometimes you want tocombine multiple conditions in a WHEREclause to make the result setmore specific and useful. One way of doing this is to use the AND operator.

1. year BETWEEN1990 and 2000 is the first condition in the WHEREclause.

2. AND genre ='comedy' is the second condition in the WHEREclause.

3. AND is an operator that combinestwo conditions. Both conditions must be true for the row to be included in theresult set.Here, we use the ANDoperator to only return moviesmade between 1990 and 2000 that are also comedies.

 

        Instructions:

1. Similar to AND, the ORoperator can be used withthe WHERE clause, but there are someimportant differences. In the code editor, type

   SELECT * FROM movies

                     WHEREyear < 1980

                                OR genre = 'comedy';

   SELECT * FROM movies

          WHERE genre = 'comedy'

                   ORyear < 1980;

   QueryResults

id

name

genre

year

imdb_rating

6

Star Wars

action

1977

8.7

51

Shrek 2

comedy

2004

7.2

52

Toy Story 3

comedy

2010

8.4

53

Frozen

comedy

2013

7.6

54

Finding Nemo

comedy

2003

8.2

55

Despicable Me 2

comedy

2013

7.5

56

Inside Out

comedy

2015

8.6

57

Shrek the Third

comedy

2007

6.0

58

Minions

comedy

2015

6.7

59

Up

comedy

2009

8.3

60

Monsters, Inc.

comedy

2001

8.1

61

Home Alone

comedy

1990

7.4

62

Meet the Fockers

comedy

2004

6.3

63

The Hangover

comedy

2009

7.8

64

Monsters University

comedy

2013

7.4

65

Shrek

comedy

2001

7.9

66

How the Grinch Stole Christmas

comedy

2000

6.0

67

The Hangover Part II

comedy

2011

6.5

68

Despicable Me

comedy

2010

7.7

69

Men in Black

comedy

1997

7.2

70

Toy Story 2

comedy

1999

7.9

71

Cars

comedy

2006

7.2

72

Bruce Almighty

comedy

2003

6.7

73

My Big Fat Greek Wedding

comedy

2002

6.6

74

Shrek Forever After

comedy

2010

6.4

75

Brave

comedy

2012

7.2

76

Ghostbusters

comedy

1984

7.8

77

Rush Hour 2

comedy

2001

6.6

78

Big Hero 6

comedy

2014

7.9

79

Alvin and the Chipmunks: The Squeakquel

comedy

2009

4.4

80

Mrs. Doubtfire

comedy

1993

6.9

81

Ted

comedy

2012

7.0

82

Aladdin

comedy

1992

8.0

83

Alvin and the Chipmunks

comedy

2007

5.3

84

Madagascar 3: Europes Most Wanted

comedy

2012

7.0

85

Kung Fu Panda

comedy

2008

7.6

86

The Lorax

comedy

2012

6.5

87

Austin Powers in Goldmember

comedy

2002

6.2

88

Back to the Future

comedy

1985

8.5

89

Wedding Crashers

comedy

2005

7.0

90

Charlie and the Chocolate Factory

comedy

2005

6.7

91

Ratatouille

comedy

2007

8.0

92

Austin Powers: The Spy Who Shagged Me

comedy

1999

6.6

93

Tangled

comedy

2010

7.8

94

Monsters vs. Aliens

comedy

2009

6.6

95

Happy Feet

comedy

2006

6.5

96

Ice Age: Dawn of the Dinosaurs

comedy

2009

7.0

97

Ice Age: The Meltdown

comedy

2006

6.9

109

Jaws 2

horror

1978

5.7

116

The Amityville Horror

horror

1979

6.2

157

Gone with the Wind

romance

1939

8.2

161

Snow White and the Seven Dwarfs

romance

1937

7.7

174

The Sound of Music

romance

1965

8.0

194

Jaws

drama

1975

8.1

220

Seven

drama

1979

6.1

DatabaseSchema

Movies      220 rows

id

INTEGER

name

TEXT

genre

TEXT

year

INTEGER

imdb_rating

REAL