Download
sql tutorial n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Tutorial PowerPoint Presentation
Download Presentation
SQL Tutorial

SQL Tutorial

433 Views Download Presentation
Download Presentation

SQL Tutorial

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Saad Bashir Alvi SQL Tutorial

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

  3. Saad Bashir Alvi First Example • Movie Database • movies • actors • casting

  4. Saad Bashir Alvi Tables of Movie Database

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

  6. Saad Bashir Alvi 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. Saad Bashir Alvi Topics to be covered • CREATE • INSERT • UPDATE • SELECT • ALTER • DROP

  8. Saad Bashir Alvi 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. Saad Bashir Alvi Topics to be covered • CREATE • INSERT • UPDATE • SELECT • ALTER • DROP

  10. Saad Bashir Alvi 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. Saad Bashir Alvi Topics to be covered • CREATE • INSERT • UPDATE • SELECT • ALTER • DROP

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

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

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

  15. Saad Bashir Alvi 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. Saad Bashir Alvi Select with aggregate functions • Database bbc(name, region, area, population, gdp) • Problem: Give the total GDP of 'Africa'

  17. Saad Bashir Alvi 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. Saad Bashir Alvi Select with aggregate functions • Database bbc(name, region, area, population, gdp) • Problem: How many countries have an area of at least 1000000

  19. Saad Bashir Alvi 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

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

  21. Saad Bashir Alvi 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. Saad Bashir Alvi 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. Saad Bashir Alvi 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

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

  25. Saad Bashir Alvi 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. Saad Bashir Alvi 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. Saad Bashir Alvi 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'

  28. Saad Bashir Alvi 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. Saad Bashir Alvi 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'

  30. Saad Bashir Alvi 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. Saad Bashir Alvi 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

  32. Saad Bashir Alvi 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. Saad Bashir Alvi 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

  34. Saad Bashir Alvi 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. Saad Bashir Alvi 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

  36. Saad Bashir Alvi 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. Saad Bashir Alvi 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

  38. Saad Bashir Alvi 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. Saad Bashir Alvi 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. Saad Bashir Alvi 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. Saad Bashir Alvi 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. Saad Bashir Alvi 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. Saad Bashir Alvi 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. Saad Bashir Alvi 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. Saad Bashir Alvi 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. Saad Bashir Alvi Topics to be covered • CREATE • INSERT • UPDATE • SELECT • ALTER • DROP

  47. Saad Bashir Alvi ALTER • ALTER TABLE actor add column age integer; • ALTER TABLE actor change age newage integer; • ALTER TABLE actor drop column age;

  48. Saad Bashir Alvi Topics to be covered • CREATE • INSERT • UPDATE • SELECT • ALTER • DROP

  49. Saad Bashir Alvi DROP • drop table movie;

  50. Saad Bashir Alvi Thanks and Good luck for your exams