sql tutorial
Download
Skip this Video
Download Presentation
SQL Tutorial

Loading in 2 Seconds...

play fullscreen
1 / 50

sql tutorial - PowerPoint PPT Presentation


  • 334 Views
  • Uploaded on

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 covered. CREATE INSERT UPDATE SELECT ALTER DROP. Creating Database.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'sql tutorial' - johana


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
topics to be covered
Saad Bashir AlviTopics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP
first example
Saad Bashir AlviFirst Example
  • Movie Database
    • movies
    • actors
    • casting
topics to be covered5
Saad Bashir AlviTopics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP
creating database
Saad Bashir AlviCreating 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));
topics to be covered7
Saad Bashir AlviTopics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP
populating database
Saad Bashir AlviPopulating 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);
topics to be covered9
Saad Bashir AlviTopics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP
updating record
Saad Bashir AlviUpdating 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;
topics to be covered11
Saad Bashir AlviTopics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP
selecting records
Saad Bashir AlviSelecting records
  • Problem: Select the year that Athens hosted the Olympic games.
selecting records13
Saad Bashir AlviSelecting records
  • Problem: Select the year that Athens hosted the Olympic games.
  • Solution: select yr, city from Games where city = \'Athens\';
select with group by
Saad Bashir AlviSelect with GROUP BY
  • Problem: Select the continents hosting the Olympics with the count of the number of games held.
select with group by15
Saad Bashir AlviSelect 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;
select with aggregate functions
Saad Bashir AlviSelect with aggregate functions
  • Database bbc(name, region, area, population, gdp)
  • Problem: Give the total GDP of \'Africa\'
select with aggregate functions17
Saad Bashir AlviSelect 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\'
select with aggregate functions18
Saad Bashir AlviSelect with aggregate functions
  • Database bbc(name, region, area, population, gdp)
  • Problem: How many countries have an area of at least 1000000
select with aggregate functions19
Saad Bashir AlviSelect 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
select with aggregate functions20
Saad Bashir AlviSelect with aggregate functions
  • Database bbc(name, region, area, population, gdp)
  • Problem: What is the total population of (\'France\',\'Germany\',\'Spain\')‏
select with aggregate functions21
Saad Bashir AlviSelect 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\'
select with aggregate functions22
Saad Bashir AlviSelect 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.
select with aggregate functions23
Saad Bashir AlviSelect 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
select with join
Saad Bashir AlviSelect with join
  • Problem:We want to find the year and country where the games took place.
select with join25
Saad Bashir AlviSelect 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)‏
select with join26
Saad Bashir AlviSelect 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\'
select with join27
Saad Bashir AlviSelect 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\'
select with join28
Saad Bashir AlviSelect 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\'
select with join29
Saad Bashir AlviSelect 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\'
select with join30
Saad Bashir AlviSelect 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.
select with join31
Saad Bashir AlviSelect 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
select with join32
Saad Bashir AlviSelect 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\'.
select with join33
Saad Bashir AlviSelect 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
select with join34
Saad Bashir AlviSelect 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.
select with join35
Saad Bashir AlviSelect 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
select with join36
Saad Bashir AlviSelect 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.
select with join37
Saad Bashir AlviSelect 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
select with join38
Saad Bashir AlviSelect 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
select with join39
Saad Bashir AlviSelect 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\')‏
select with join40
Saad Bashir AlviSelect 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
select with join41
Saad Bashir AlviSelect 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
select with join42
Saad Bashir AlviSelect 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.
select with join43
Saad Bashir AlviSelect 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
select with join44
Saad Bashir AlviSelect 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.
select with join45
Saad Bashir AlviSelect 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
topics to be covered46
Saad Bashir AlviTopics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP
alter
Saad Bashir AlviALTER
  • ALTER TABLE actor add column age integer;
  • ALTER TABLE actor change age newage integer;
  • ALTER TABLE actor drop column age;
topics to be covered48
Saad Bashir AlviTopics to be covered
  • CREATE
  • INSERT
  • UPDATE
  • SELECT
  • ALTER
  • DROP
slide49
Saad Bashir AlviDROP
  • drop table movie;
ad