Sql tutorial
Download
1 / 50

sql tutorial - PowerPoint PPT Presentation


  • 334 Views
  • Updated 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
Sql tutorial l.jpg

Saad Bashir Alvi

SQL Tutorial


Topics to be covered l.jpg

Saad Bashir Alvi

Topics to be covered

  • CREATE

  • INSERT

  • UPDATE

  • SELECT

  • ALTER

  • DROP


First example l.jpg

Saad Bashir Alvi

First Example

  • Movie Database

    • movies

    • actors

    • casting


Tables of movie database l.jpg

Saad Bashir Alvi

Tables of Movie Database


Topics to be covered5 l.jpg

Saad Bashir Alvi

Topics to be covered

  • CREATE

  • INSERT

  • UPDATE

  • SELECT

  • ALTER

  • DROP


Creating database l.jpg

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


Topics to be covered7 l.jpg

Saad Bashir Alvi

Topics to be covered

  • CREATE

  • INSERT

  • UPDATE

  • SELECT

  • ALTER

  • DROP


Populating database l.jpg

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


Topics to be covered9 l.jpg

Saad Bashir Alvi

Topics to be covered

  • CREATE

  • INSERT

  • UPDATE

  • SELECT

  • ALTER

  • DROP


Updating record l.jpg

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;


Topics to be covered11 l.jpg

Saad Bashir Alvi

Topics to be covered

  • CREATE

  • INSERT

  • UPDATE

  • SELECT

  • ALTER

  • DROP


Selecting records l.jpg

Saad Bashir Alvi

Selecting records

  • Problem: Select the year that Athens hosted the Olympic games.


Selecting records13 l.jpg

Saad Bashir Alvi

Selecting records

  • Problem: Select the year that Athens hosted the Olympic games.

  • Solution: select yr, city from Games where city = 'Athens';


Select with group by l.jpg

Saad Bashir Alvi

Select with GROUP BY

  • Problem: Select the continents hosting the Olympics with the count of the number of games held.


Select with group by15 l.jpg

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;


Select with aggregate functions l.jpg

Saad Bashir Alvi

Select with aggregate functions

  • Database bbc(name, region, area, population, gdp)

  • Problem: Give the total GDP of 'Africa'


Select with aggregate functions17 l.jpg

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'


Select with aggregate functions18 l.jpg

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


Select with aggregate functions19 l.jpg

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


Select with aggregate functions20 l.jpg

Saad Bashir Alvi

Select with aggregate functions

  • Database bbc(name, region, area, population, gdp)

  • Problem: What is the total population of ('France','Germany','Spain')‏


Select with aggregate functions21 l.jpg

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'


Select with aggregate functions22 l.jpg

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.


Select with aggregate functions23 l.jpg

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


Select with join l.jpg

Saad Bashir Alvi

Select with join

  • Problem:We want to find the year and country where the games took place.


Select with join25 l.jpg

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


Select with join26 l.jpg

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'


Select with join27 l.jpg

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'


Select with join28 l.jpg

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'


Select with join29 l.jpg

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'


Select with join30 l.jpg

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.


Select with join31 l.jpg

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


Select with join32 l.jpg

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


Select with join33 l.jpg

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


Select with join34 l.jpg

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.


Select with join35 l.jpg

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


Select with join36 l.jpg

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.


Select with join37 l.jpg

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


Select with join38 l.jpg

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


Select with join39 l.jpg

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')‏


Select with join40 l.jpg

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


Select with join41 l.jpg

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


Select with join42 l.jpg

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.


Select with join43 l.jpg

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


Select with join44 l.jpg

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.


Select with join45 l.jpg

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


Topics to be covered46 l.jpg

Saad Bashir Alvi

Topics to be covered

  • CREATE

  • INSERT

  • UPDATE

  • SELECT

  • ALTER

  • DROP


Alter l.jpg

Saad Bashir Alvi

ALTER

  • ALTER TABLE actor add column age integer;

  • ALTER TABLE actor change age newage integer;

  • ALTER TABLE actor drop column age;


Topics to be covered48 l.jpg

Saad Bashir Alvi

Topics to be covered

  • CREATE

  • INSERT

  • UPDATE

  • SELECT

  • ALTER

  • DROP


Slide49 l.jpg

Saad Bashir Alvi

DROP

  • drop table movie;


Thanks and good luck for your exams l.jpg

Saad Bashir Alvi

Thanks and Good luck for your exams


ad