SQL Fundamentals

Systems & Tools

Databsase

1. DB System - SQLite
2. GUI Front End - sqlitebrowser

The DB - Music

Create DB

1. Name
2. Location

Create Tables

1. Songs
2. Artists

CRUD - List of Operations

Create

1. Create artists
   1. insert into artists (artist) values('Jagjit')
   2. insert into artists (artist) values('Himesh Reshamiya')
2. Create songs
   1. insert into songs (song) values('Jhuki Jhuki Si Nazar')
3. Relate song with artist(s)
   1. insert into song_artists(artist_id, song_id) values(6, 3)

Read Flat/Simple Data

1. Songs
   1. select * from songs
2. Artists
   1. select * from artists

Interesting Reads

1. How many songs?
   1. select count(*) from songs
2. How many artists?
   1. ???
3. Filters - Exact
   1. select * from artists where artist='Kishore Kumar'
4. Filters - Wildcard
   1. select * from artists where artist like 'Lata%'
5. Filters - Contains
   1. select * from artists where artist like '%Kumar%'

Updates

1. Update artist name
   1. update artists set artist='Jagjit Singh' where artist='Jagjit'

Delete

1. Delete an artist
   1. Delete from artists where artist = 'Himesh Reshamiya'

Complex Queries - Joins & Grouping

1. Basic Join
   1. select artist, song from artists a, songs b, song_artists c where a.artist_id=c.artist_id and b.song_id=c.song_id
2. Joins with Grouping
   1. select artist, count(*) as "No. of Songs" from song_artists, artists where song_{artists.artistid} = artists.artist_id group by artists.artist_id

Summing it up

1. [ ] Review
   1. [ ] Systems & Tools
      1. [ ] Anything else that can be imagined?
   2. [ ] Basic Operations
      1. [ ] Implication of each Operation
   3. Querying Database
      1. [ ] Simple Queries
      2. [ ] Joins
      3. [ ] Complex Queries
   4. [ ] Joins and it uses
      1. [ ] Count
      2. [ ] What else?

See you on 17th…

rahul.naskar@gmail.com