sql lecture recording on 19 February 2025 at 10.32.09 AM

SQL Basics

• Select Statement: Used to read data from tables and display it.

• Syntax Example: To display all artist information: SELECT * FROM artists;

Retrieving Album Information

• To display all albums: SELECT * FROM albums;

• Running this command will provide a list of albums including columns such as Album ID, Album Title, and Artist ID.

Joining Tables

• Purpose of JOIN: To combine rows from two or more tables based on a related column.

• Example SQL Command: To show which artists relate to which albums:

  • SELECT a.artist_name, b.album_title FROM artists a, albums b WHERE a.artist_id = b.artist_id;

• This command uses the common attribute artist_id to link the two tables.

Using WHERE Clause for JOINs

• The keywords after WHERE specify the conditions on which to combine the rows.

• In the example above, a.artist_id = b.artist_id shows that both tables are linked through the artist_id field, where one is a primary key (in artists) and the other a foreign key (in albums).

Ordering Results

• To order the results, use the ORDER BY clause:

  • ... ORDER BY a.artist_name;

• This ensures the output is sorted alphabetically by artist names.

Exploring Relationships in the Database

• Linkages: To find track names for each album, identify that album_id serves as a primary key in the albums table and a foreign key in the tracks table; they can be joined.

• SQL command to find tracks for each album:

  • SELECT b.title, c.track_name FROM albums b, tracks c WHERE b.album_id = c.album_id ORDER BY b.title;

Complex Queries with Multiple Joins

• To find artist names, album titles, and track names, a more complex join is needed:

  • Tables Involved: Three tables are accessed: artists, albums, and tracks.

  • Sample SQL Command:

  SELECT a.name AS artist_name, b.title AS album_title, c.track_name
  FROM artists a, albums b, tracks c
  WHERE a.artist_id = b.artist_id AND b.album_id = c.album_id
  ORDER BY a.name, b.title;

• This joins three tables and uses aliases for clarity in the results.

Using SQL Generators in Real Life

• In practice, many development environments (like Python or Java) do not have SQL generators, requiring developers to get familiar with writing SQL queries manually.

• Understanding SQL is necessary, thus practice writing commands and recognize how they would be generated by tools.