SQL Tutorial - PowerPoint PPT Presentation

Loading...

PPT – SQL Tutorial PowerPoint presentation | free to download - id: 3c5796-MjIzO



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

SQL Tutorial

Description:

SQL Tutorial Topics to be covered CREATE INSERT UPDATE SELECT ALTER DROP First Example Movie Database movies actors casting Tables of Movie Database Topics to be ... – PowerPoint PPT presentation

Number of Views:491
Avg rating:3.0/5.0
Slides: 51
Provided by: isInforma
Category:
Tags: sql | tutorial

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: SQL Tutorial


1
SQL Tutorial
2
Topics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP

3
First Example
  • Movie Database
  • movies
  • actors
  • casting

4
Tables of Movie Database
5
Topics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP

6
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))

7
Topics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP

8
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)

9
Topics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP

10
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

11
Topics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP

12
Selecting records
  • Problem Select the year that Athens
    hosted the Olympic games.

13
Selecting records
  • Problem Select the year that Athens
    hosted the Olympic games.
  • Solution
    select yr, city from Games where
    city 'Athens'

14
Select with GROUP BY
  • Problem Select the continents
    hosting the Olympics with the count
    of the number of games held.

15
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

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

17
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'

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

19
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 gt 1000000

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

21
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'

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.

23
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 gt
    10000000 group by region

24
Select with join
  • Problem
    We want to find the year and
    country where the
    games took place.

25
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)?

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'

27
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.asintrack.album)?
  • WHERE song 'Alison'

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'

29
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.asintrack.album) where title
    'Blur'

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.

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 track.
  • Solution
    SELECT title, COUNT() FROM
    album JOIN track ON (asinalbum) GROUP BY title

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'.

33
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 (asinalbum) where song like
    "Heart" GROUP BY title

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.

35
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.asintrack.album)
    group by song having count() gt 2

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.

37
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.asintrack.album) group by title having
    price/count() lt .5

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

39
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')?

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

41
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

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.

43
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

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.

45
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

46
Topics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP

47
ALTER
  • ALTER TABLE actor add column age integer
  • ALTER TABLE actor change age newage integer
  • ALTER TABLE actor drop column age

48
Topics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP

49
DROP
  • drop table movie

50
Thanks and Good luck for your exams
About PowerShow.com