Lesson 2 - Executing SQL Commands in DB Browser

While DB Browser's graphical interface provides a gentle introduction to SQLite, the true power of this database system lies in its ability to understand and respond to SQL commands. These commands offer a precise and flexible way to interact with your data, allowing you to perform tasks that would be cumbersome or impossible with the graphical interface alone.

• "movies_80s": A collection of films from the 1980s, meticulously ranked from 1 to 10 based on ticket sales for each year. It includes details such as title, release date, distributor, and genre. One could, for example, query this table to uncover the most popular genres of the decade or to track the performance of a particular distributor over time.
• "top_albums": A compilation of Rolling Stone magazine's top 500 albums of all time, as judged in 2012. Or at least, most of them. A few entries have been discreetly removed to ensure this tutorial remains suitable for younger audiences, so you may find some unexpected gaps in the rankings. The table provides information on the ranking, year of release, album title, artist, genre, and subgenre. One might, with sufficient effort, use SQL to identify trends in musical taste across different eras or to analyse the representation of various genres within the list.

In the world of SQL, the WHERE clause acts like a detective, helping you pinpoint exactly the information you need from your database. Think of it as a filter that sifts through your data and only reveals the records that match your specific criteria.

WHERE in Action: "80s_movies"

Let's say you want to find all the movies in your movies_80s table that were distributed by Warner Bros. You would use the WHERE clause with a condition:

SELECT * FROM movies_80s WHERE distributor = 'Warner Bros'

This query tells the database: "Show me all the columns (*) from the 'movies_80s' table, but only where the 'distributor' field is equal to 'Warner Bros.'"

WHERE's Versatility

The WHERE clause is incredibly versatile. You can use it with various operators to create different conditions:

• =: Equal to
• != or <>: Not equal to
• <: Less than
• >: Greater than
• <=: Less than or equal to
• >=: Greater than or equal to
• LIKE: Matches a pattern (e.g., '1985%')
• BETWEEN: Within a range of values
• IN: Matches any value in a list

You can even combine multiple conditions using AND, OR, and NOT to create more complex filters.

In SQL, you can use != or <> to express "not equal to". Both operators achieve the same result. For instance, to retrieve all movies NOT released in 1985, you could use either of the following queries:

SELECT * FROM movies_80s WHERE release_date != 1985;

SELECT * FROM movies_80s WHERE release_date <> 1985;

The keyword NOT is used in different contexts, such as with the IN and BETWEEN operators. For example, to retrieve movies with a genre NOT IN ('Comedy', 'Adventure'), you would use:

SELECT * FROM movies_80s WHERE genre NOT IN ('Comedy', 'Adventure');

To search for patterns within text data, you can use wildcards with LIKE Operator. The most common wildcards are:

• % (percent sign): Represents zero or more characters. For example, LIKE '%Rock%' would match "Rock", "Rock and Roll", "Psychedelic Rock", and any other string containing "Rock".
• _ (underscore): Represents a single character. For example, LIKE 'Bl_es' would match "Blues" but not "Blues Rock".

For instance, to find all albums with "Rock" in their subgenre (remember that an album can have multiple subgenres, separated by commas and a space), you would use:

SELECT * FROM top_albums WHERE subgenre LIKE '%Rock%';

Or to find albums where the artist starts with "The" you would use:

SELECT rank, year, album, artist FROM top_albums WHERE artist LIKE 'The%';

While SQL doesn't enforce strict formatting rules, consistent conventions improve readability. Common practices include:

• Keywords in uppercase: Write SQL keywords (SELECT, FROM, WHERE, etc.) in uppercase to distinguish them from other elements in your query.
• Table and column names in lowercase: Using lowercase for table and column names helps maintain consistency and differentiate them from keywords.
• Indentation: Indenting clauses and expressions, especially in complex queries, makes the structure of your query clearer and easier to follow. For instance, indent the content of the WHERE clause in relation to the WHERE keyword itself, and indent any AND/OR operators with respect to the WHERE.

  SELECT title, genre
            FROM movies_80s
            WHERE genre = 'Comedy';

• Newlines: Using newlines to separate different parts of your query can also improve readability. For instance, you can place each clause (SELECT, FROM, WHERE) on a new line. For longer queries, consider placing each column in the SELECT statement on a new line. It ultimately doesn't matter if you choose to write your query on a single line or multiple lines, as long as it is readable.

  SELECT title, genre FROM movies_80s WHERE genre = 'Comedy';

These practices make your SQL queries easier to read and modify.