SQL Tutorial Saad Bashir Alvi 1 Topics to

SQL Tutorial Saad Bashir Alvi 1

Topics to be covered CREATE INSERT UPDATE SELECT ALTER DROP Saad Bashir Alvi 2

First Example Movie Database movies actors casting Saad Bashir Alvi 3

Tables of Movie Database Saad Bashir Alvi 4

Topics to be covered CREATE INSERT UPDATE SELECT ALTER DROP Saad Bashir Alvi 5

Creating Database create table movie (id int NOT NULL primary key, title varchar(70), yr decimal(4), score float, votes integer); create table actor(id int NOT NULL primary key, name varchar(30)); create table casting(movieid int, actorid int, ord integer, primary key (movieid, actorid)); Saad Bashir Alvi 6

Topics to be covered CREATE INSERT UPDATE SELECT ALTER DROP Saad Bashir Alvi 7

Populating Database insert into table movie(id, title, yr, score, votes) values (1, “Lione King”, 2001, 5, 20000); insert into actor(id, name) values (1, “Sambda”); insert into casting(movieid, actorid, ord) values (1, 1, 5); Saad Bashir Alvi 8

Topics to be covered CREATE INSERT UPDATE SELECT ALTER DROP Saad Bashir Alvi 9

Updating Record update table movie set title = “Lion King” where id = 1; update table actor set name = “simba” where id = 1; update table casting set ord = 1 where movieid = 1 and actorid = 1; Saad Bashir Alvi 10

Topics to be covered CREATE INSERT UPDATE SELECT ALTER DROP Saad Bashir Alvi 11

Selecting records Problem: Select the year that Athens hosted the Olympic games. Saad Bashir Alvi 12

Selecting records Problem: Select the year that Athens hosted the Olympic games. Solution: select yr, city from Games where city = 'Athens'; Saad Bashir Alvi 13

Select with GROUP BY Problem: Select the continents hosting the Olympics with the count of the number of games held. Saad Bashir Alvi 14

Select with GROUP BY Problem: Select the continents hosting the Olympics with the count of the number of games held. Solution: select continent, count(yr) from Games group by continent; Saad Bashir Alvi 15

Select with aggregate functions Database bbc(name, region, area, population, gdp) Problem: Give the total GDP of 'Africa' Saad Bashir Alvi 16

Select with aggregate functions Database bbc(name, region, area, population, gdp) Problem: Give the total GDP of 'Africa' Solution: select sum(gdp) from bbc where region = 'Africa' Saad Bashir Alvi 17

Select with aggregate functions Database bbc(name, region, area, population, gdp) Problem: How many countries have an area of at least 1000000 Saad Bashir Alvi 18

Select with aggregate functions Database bbc(name, region, area, population, gdp) Problem: How many countries have an area of at least 1000000 Solution: select count(name) from bbc where area >= 1000000 Saad Bashir Alvi 19

Select with aggregate functions Database bbc(name, region, area, population, gdp) Problem: What is the total population of ('France', 'Germany', 'Spain') Saad Bashir Alvi 20

Select with aggregate functions Database bbc(name, region, area, population, gdp) Problem: What is the total population of ('France', 'Germany', 'Spain') Solution: select sum(population) from bbc where name = 'France' or name = 'Germany' or name = 'Spain' Saad Bashir Alvi 21

Select with aggregate functions Database bbc(name, region, area, population, gdp) Problem: For each region show the region and number of countries with populations of at least 10 million. Saad Bashir Alvi 22

Select with aggregate functions Database bbc(name, region, area, population, gdp) Problem: For each region show the region and number of countries with populations of at least 10 million. Solution: select region, count(name) from bbc where population >= 10000000 group by region Saad Bashir Alvi 23

Select with join Problem: We want to find the year and country where the games took place. Saad Bashir Alvi 24

Select with join Problem: We want to find the year and country where the games took place. Solution: SELECT games. yr, city. country FROM games JOIN city ON (games. city = city. name) Saad Bashir Alvi 25

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: Find the title and artist who recorded the song 'Alison' Saad Bashir Alvi 26

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: Find the title and artist who recorded the song 'Alison' Solution: SELECT title, artist FROM album JOIN track ON (album. asin=track. album) WHERE song = 'Alison' Saad Bashir Alvi 27

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: Show the song for each track on the album 'Blur' Saad Bashir Alvi 28

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: Show the song for each track on the album 'Blur' Solution: select song FROM album JOIN track ON (album. asin=track. album) where title = 'Blur' Saad Bashir Alvi 29

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: For each album show the title and the total number of track. Saad Bashir Alvi 30

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: For each album show the title and the total number of track. Solution: SELECT title, COUNT(*) FROM album JOIN track ON (asin=album) GROUP BY title Saad Bashir Alvi 31

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: For each album show the title and the total number of tracks containing the word 'Heart'. Saad Bashir Alvi 32

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: For each album show the title and the total number of tracks containing the word 'Heart'. Solution: SELECT title, COUNT(*) FROM album JOIN track ON (asin=album) where song like "%Heart%" GROUP BY title Saad Bashir Alvi 33

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up. Saad Bashir Alvi 34

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up. Solution: select song, count(*) FROM album JOIN track ON (album. asin=track. album) group by song having count(*) > 2 Saad Bashir Alvi 35

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: A "good value" album is one where the price per track is less than 50 cents. Find the good value album - show the title, the price and the number of tracks. Saad Bashir Alvi 36

Select with join Database album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song) Problem: A "good value" album is one where the price per track is less than 50 cents. Find the good value album - show the title, the price and the number of tracks. Solution: select title, price, count(*) FROM album JOIN track ON (album. asin=track. album) group by title having price/count(*) <. 5 Saad Bashir Alvi 37

Select with join Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the films in which 'Harrison Ford' has appeared Saad Bashir Alvi 38

Select with join Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the films in which 'Harrison Ford' has appeared Solution: select title from movie join casting on id = movieid where actorid = (select id from actor where name = 'Harrison Ford') Saad Bashir Alvi 39

Select with join Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the films together with the leading star for all 1962 films Saad Bashir Alvi 40

Select with join Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the films together with the leading star for all 1962 films Solution: select title, name from movie, actor, casting where yr = '1962' and ord = 1 and movie. id = casting. movieid and actor. id = casting. actorid Saad Bashir Alvi 41

Select with join Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year. Saad Bashir Alvi 42

Select with join Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year. Solution: select yr, count(*) from movie, casting, actor where actor. id = casting. actorid and movie. id = casting. movieid and actor. name = 'John Travolta' group by yr order by count(*) desc limit 1 Saad Bashir Alvi 43

Select with join Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the 1978 films by order of cast list size. Saad Bashir Alvi 44

Select with join Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the 1978 films by order of cast list size. Solution: select title, count(actor. id) from movie, actor, casting where actor. id = casting. actorid and movie. id = casting. movieid and yr = 1978 group by title order by count(actor. id) desc Saad Bashir Alvi 45