{"id":1378,"date":"2026-09-14T14:28:21","date_gmt":"2026-09-14T19:28:21","guid":{"rendered":"https:\/\/sts.doit.wisc.edu\/training-materials\/?post_type=activity&p=1378"},"modified":"2026-09-14T15:21:32","modified_gmt":"2026-09-14T20:21:32","slug":"sql-introduction-to-databases-with-sql","status":"publish","type":"activity","link":"https:\/\/sts.doit.wisc.edu\/training-materials\/activity\/sql-introduction-to-databases-with-sql\/","title":{"rendered":"SQL: Introduction to Databases with SQL"},"content":{"rendered":"\n
\n

Set-Up<\/strong><\/h2>\n\n\n\n
    \n
  1. Open https:\/\/sqliteonline.com\/<\/strong><\/a><\/li>\n\n\n\n
  2. On the bottom right corner, click on the Book Icon, then go to the SYNTAX tab. In the Example Databases, click on CHINOOK<\/li>\n\n\n\n
  3. Once the dataset loads, the left sidebar should list all of the available tables under Chinook_Sqlite.sqlite<\/strong>:<\/li>\n<\/ol>\n\n\n\n
    Album \u00b7 Artist \u00b7 Customer \u00b7 Employee \u00b7 Genre \u00b7 Invoice \u00b7 InvoiceLine \u00b7 MediaType \u00b7 Playlist \u00b7 PlaylistTrack \u00b7 Track<\/code><\/pre>\n\n\n\n
      \n
    1. Confirm it worked by running:<\/li>\n<\/ol>\n\n\n\n
      SELECT * FROM Customer;<\/code><\/pre>\n\n\n\n
      If you see rows of customer data, you\u2019re ready to go.<\/p>\n\n\n\n
      Note on SQL style:<\/strong> SQL keywords (SELECT, FROM, WHERE, etc.) are case-insensitive, but it\u2019s good practice to write them in ALL CAPS and to spell table\/column names exactly as they appear in the schema (e.g. CustomerId, not customerid).<\/p>\n\n\n\n
      <\/p>\n<\/section>\n\n\n\n
      \n
      Activity 1: Basic Queries<\/strong><\/h2>\n\n\n\n
      1. SELECT<\/strong><\/h3>\n\n\n\n
      Purpose:<\/strong> Choose which columns (attributes) to return from a table.<\/p>\n\n\n\n
      — Select all columns with *<\/em><\/p>\n\n\n\n
      SELECT *\nFROM Genre;<\/code><\/pre>\n\n\n\n
      — Select specific columns by naming them<\/em><\/p>\n\n\n\n
      SELECT FirstName, LastName, Email\nFROM Employee;<\/code><\/pre>\n\n\n\n
      — Select only the unique values of a column with DISTINCT<\/em><\/p>\n\n\n\n
      SELECT DISTINCT Country\nFROM Customer;<\/code><\/pre>\n\n\n\n
      2. Table\/Column Aliasing (AS)<\/strong><\/h3>\n\n\n\n
      Purpose:<\/strong> Rename a column or table for readability, especially useful once we start combining tables.<\/p>\n\n\n\n
      — 3 ways to alias: with AS, with quotes, and with no quotes at all<\/em><\/p>\n\n\n\n
      SELECT FirstName AS 'First Name', LastName 'Last Name', PostalCode Zip\nFROM Customer;<\/code><\/pre>\n\n\n\n
      Use quotes around an alias whenever it contains a space, so SQL doesn\u2019t mistake it for a second column.<\/p>\n\n\n\n
      3. ORDER BY<\/strong><\/h3>\n\n\n\n
      Purpose:<\/strong> Sort the result set by one or more columns.<\/p>\n\n\n\n
      — Sort by last name, descending<\/em><\/p>\n\n\n\n
      SELECT FirstName, LastName\nFROM Customer\nORDER BY LastName DESC;<\/code><\/pre>\n\n\n\n

      — Sort by multiple columns<\/em><\/p>\n\n\n\n
      SELECT *\nFROM Invoice\nORDER BY InvoiceDate DESC, Total ASC;<\/code><\/pre>\n\n\n\n
        \n
      • Default sort order is ASC (ascending) \u2014 use DESC to reverse it.<\/li>\n<\/ul>\n\n\n\n
        4. LIMIT<\/strong><\/h3>\n\n\n\n
        Purpose:<\/strong> Cap the number of rows returned \u2014 handy for previewing a large table or grabbing a \u201ctop N.\u201d<\/p>\n\n\n\n
        — Get the 10 most expensive invoices<\/em><\/p>\n\n\n\n
        SELECT InvoiceId, CustomerId, Total\nFROM Invoice\nORDER BY Total DESC\nLIMIT 10;<\/code><\/pre>\n\n\n\n
        Summary<\/strong><\/p>\n\n\n\n
        Keyword<\/th>Definition<\/th><\/tr><\/thead>
        SELECT<\/td>Choose which columns to return<\/td><\/tr>
        DISTINCT<\/td>Return only unique values<\/td><\/tr>
        AS<\/td>Rename a column or table<\/td><\/tr>
        ORDER BY<\/td>Sort the result set<\/td><\/tr>
        LIMIT<\/td>Cap the number of rows returned<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
        \n\n\n\n
        Practice 1<\/strong><\/h3>\n\n\n\n
        All of the activities below are based on the Chinook database.<\/em><\/p>\n\n\n\n
        Practice Activity 1.1<\/strong> We are generating a mailing list to send promotional material to our customers. Query a list of full names (first and last) and mailing addresses for all of our customers. To mail something, we need their street address, city, state, country, and postal code.<\/p>\n\n\n\n
        Answer:<\/strong><\/p>\n\n\n\n
        SELECT FirstName, LastName, Address, City, State, Country, PostalCode\nFROM Customer;<\/code><\/pre>\n\n\n\n
        Practice Activity 1.2<\/strong> Generate a list of invoices in order of least total amount to greatest total amount. Feel free to use SELECT *.<\/p>\n\n\n\n
        Answer:<\/strong><\/p>\n\n\n\n
        SELECT *\nFROM Invoice\nORDER BY Total ASC;<\/code><\/pre>\n\n\n\n
        Practice Activity 1.3<\/strong> Generate a list of the 40 longest tracks in milliseconds. Include the name of the track and its runtime in milliseconds.<\/p>\n\n\n\n
        Answer:<\/strong><\/p>\n\n\n\n
        SELECT Name, Milliseconds\nFROM Track\nORDER BY Milliseconds DESC\nLIMIT 40;<\/code><\/pre>\n\n\n\n
        Chapter Exercise<\/strong> Get a list of the top 10 most-tenured employees (i.e. the ones who were hired the earliest). Display their name, title, supervisor, and the date they were hired.<\/p>\n\n\n\n
        Challenge: Name the columns something reasonable and meaningful, where applicable.<\/em><\/p>\n\n\n\n
        Answer:<\/strong><\/p>\n\n\n\n
        SELECT FirstName, LastName, Title, ReportsTo AS SupervisorId, HireDate\nFROM Employee\nORDER BY HireDate ASC\nLIMIT 10;<\/code><\/pre>\n\n\n\n
        Stretch:<\/em> swap ReportsTo AS SupervisorId for a self-join on Employee (see Practice Activity 1.2) to show the supervisor\u2019s actual name instead of their ID.<\/p>\n<\/section>\n\n\n\n
        \n
        Activity 2: Conditionals (WHERE)<\/strong><\/h2>\n\n\n\n
        1. Equality & Pattern Matching (=, LIKE, <>)<\/strong><\/h3>\n\n\n\n
        Purpose:<\/strong> Filter rows where a column exactly \u2014 or approximately \u2014 matches a value.<\/p>\n\n\n\n
        — = checks for exact, case-sensitive equality<\/em><\/p>\n\n\n\n
        SELECT FirstName, LastName, Phone, Country\nFROM Customer\nWHERE Country = 'Brazil';<\/code><\/pre>\n\n\n\n
        — LIKE is case-insensitive and more flexible<\/em><\/p>\n\n\n\n
        SELECT FirstName, LastName, Phone, Country\nFROM Customer\nWHERE Country LIKE 'brazil';<\/code><\/pre>\n\n\n\n


        — % is a wildcard for “any characters” \u2014 useful when you’re not sure of exact spelling<\/em>
        <\/p>\n\n\n\n
        SELECT FirstName, LastName, Phone, Country\nFROM Customer\nWHERE Country LIKE '%Czech%';<\/code><\/pre>\n\n\n\n


        — <> means “not equal to”<\/em>
        <\/p>\n\n\n\n
        SELECT *\nFROM Invoice\nWHERE BillingCountry <> 'USA';<\/code><\/pre>\n\n\n\n
        In general, prefer LIKE over = for text \u2014 it\u2019s case-insensitive and supports wildcards.<\/p>\n\n\n\n
        2. Comparison Operators (<, >, <=, >=)<\/strong><\/h3>\n\n\n\n
        Purpose:<\/strong> Compare numbers, dates, or text alphabetically.<\/p>\n\n\n\n
        SELECT *\nFROM Invoice\nWHERE Total < 4;<\/code><\/pre>\n\n\n\n
        3. NULL Values (IS NULL \/ IS NOT NULL)<\/strong><\/h3>\n\n\n\n
        Purpose:<\/strong> NULL represents missing data. You can\u2019t compare it with = \u2014 you must use IS NULL or IS NOT NULL.<\/p>\n\n\n\n
        SELECT *\nFROM Invoice\nWHERE BillingState IS NOT NULL;<\/code><\/pre>\n\n\n\n
        4. Combining Conditions (AND, OR, parentheses)<\/strong><\/h3>\n\n\n\n
        Purpose:<\/strong> Build more powerful filters by combining multiple conditions.<\/p>\n\n\n\n
          \n
        • AND \u2014 both conditions must be true<\/li>\n\n\n\n
        • OR \u2014 at least one condition must be true<\/li>\n\n\n\n
        • Parentheses () group conditions so they evaluate together<\/li>\n<\/ul>\n\n\n\n
          SELECT *\nFROM Invoice\nWHERE BillingCountry LIKE 'USA'\nAND Total >= 4;<\/code><\/pre>\n\n\n\n
          — Parentheses control evaluation order<\/em><\/p>\n\n\n\n
          SELECT *\nFROM Invoice\nWHERE (Total < 1 OR InvoiceDate < '2012-01-01')\nAND BillingCountry NOT LIKE 'USA';<\/code><\/pre>\n\n\n\n
          5. The IN Operator<\/strong><\/h3>\n\n\n\n
          Purpose:<\/strong> Shorthand for matching a column against a list of values \u2014 avoids writing a long chain of ORs.<\/p>\n\n\n\n
          — Verbose version<\/em><\/p>\n\n\n\n
          SELECT *\nFROM Customer\nWHERE State LIKE 'CA' OR State LIKE 'OR' OR State LIKE 'WA';<\/code><\/pre>\n\n\n\n
          — Same result, using IN<\/em><\/p>\n\n\n\n
          SELECT *\nFROM Customer\nWHERE State IN ('CA', 'OR', 'WA');<\/code><\/pre>\n\n\n\n
          6. The BETWEEN Operator<\/strong><\/h3>\n\n\n\n
          Purpose:<\/strong> Find rows within an inclusive range \u2014 another shorthand for chained comparisons.<\/p>\n\n\n\n
          SELECT *\nFROM Invoice\nWHERE Total BETWEEN 5 AND 10;<\/code><\/pre>\n\n\n\n
          Summary<\/strong><\/p>\n\n\n\n
          Operator<\/th>Meaning<\/th><\/tr><\/thead>
          =<\/td>Exact, case-sensitive equality<\/td><\/tr>
          LIKE<\/td>Flexible, case-insensitive pattern match (% = wildcard)<\/td><\/tr>
          <><\/td>Not equal to<\/td><\/tr>
          < > <= >=<\/td>Numeric \/ alphabetical \/ date comparison<\/td><\/tr>
          IS NULL \/ IS NOT NULL<\/td>Test for missing data<\/td><\/tr>
          AND \/ OR<\/td>Combine conditions<\/td><\/tr>
          IN (…)<\/td>Match against a list of values<\/td><\/tr>
          BETWEEN … AND …<\/td>Match within an inclusive range<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
          \n\n\n\n
          Practice 2<\/strong><\/h3>\n\n\n\n
          All of the activities below are based on the Chinook database.<\/em><\/p>\n\n\n\n
          Practice Activity 2.1<\/strong> Return a list of tracks composed by Wolfgang Amadeus Mozart. Include the track name and the composer name.<\/p>\n\n\n\n
          Challenge: What if you only knew his last name?<\/em><\/p>\n\n\n\n
          Answer:<\/strong><\/p>\n\n\n\n
          SELECT Name, Composer\nFROM Track\nWHERE Composer = 'Wolfgang Amadeus Mozart';<\/code><\/pre>\n\n\n\n
          Challenge:<\/em><\/p>\n\n\n\n
          SELECT Name, Composer\nFROM Track\nWHERE Composer LIKE '%Mozart%';<\/code><\/pre>\n\n\n\n
          Practice Activity 2.2<\/strong> The music store has decided to split the team into two floors by last name, in alphabetical order \u2014 people whose last name begins with A\u2013M are on one floor, and N\u2013Z on another.<\/p>\n\n\n\n
          Write a query to find the employees whose last names begin with A\u2013M. Include their first and last name, and keep the result in alphabetical order by last name.<\/p>\n\n\n\n
          Answer:<\/strong><\/p>\n\n\n\n
          SELECT FirstName, LastName\nFROM Employee\nWHERE LastName < 'N'\nORDER BY LastName ASC;<\/code><\/pre>\n\n\n\n
          Practice Activity 2.3<\/strong> Return a list of customers who are not associated with a company (in other words, they don\u2019t have a value in the Company column).<\/p>\n\n\n\n
          Answer:<\/strong><\/p>\n\n\n\n
          SELECT *\nFROM Customer\nWHERE Company IS NULL;<\/code><\/pre>\n\n\n\n
          Practice Activity 2.4<\/strong> To foster company spirit, HR believes it\u2019s important for supervisors to send cards to their direct reports on their birthday. An HR analyst wrote a query to report the data, but forgot to specify whose name was the employee\u2019s and whose was the supervisor\u2019s.<\/p>\n\n\n\n
          Rename the columns to specify which is the employee and which is the supervisor. (Hint: this previews the WHERE concept.)<\/em><\/p>\n\n\n\n
          Answer:<\/strong><\/p>\n\n\n\n
          SELECT e.EmployeeId,\n\u00a0 \u00a0 \u00a0 e.FirstName AS "Employee First Name",\n\u00a0 \u00a0 \u00a0 e.LastName\u00a0 AS "Employee Last Name",\n\u00a0 \u00a0 \u00a0 s.FirstName AS "Supervisor First Name",\n\u00a0 \u00a0 \u00a0 s.LastName\u00a0 AS "Supervisor Last Name",\n\u00a0 \u00a0 \u00a0 e.BirthDate,\n\u00a0 \u00a0 \u00a0 e.Email\nFROM Employee e, Employee s\nWHERE e.ReportsTo = s.EmployeeId;<\/code><\/pre>\n\n\n\n
          Chapter Exercise<\/strong> Write a query that obtains invoices with an amount greater than $15, that took place in either the USA or Canada, or from the years 2000\u20132010.<\/p>\n\n\n\n
          Challenge: Exclude all invoices that took place in the state of California (CA).<\/em><\/p>\n\n\n\n
          Answer:<\/strong><\/p>\n\n\n\n
          SELECT *\nFROM Invoice\nWHERE Total > 15\nAND (BillingCountry IN ('USA', 'Canada')\n\u00a0 \u00a0 OR InvoiceDate BETWEEN '2000-01-01' AND '2010-12-31');<\/code><\/pre>\n\n\n\n
          Challenge:<\/em><\/p>\n\n\n\n
          SELECT *\nFROM Invoice\nWHERE Total > 15\nAND (BillingCountry IN ('USA', 'Canada')\n\u00a0 \u00a0 OR InvoiceDate BETWEEN '2000-01-01' AND '2010-12-31')\nAND BillingState <> 'CA';<\/code><\/pre>\n\n\n\n
          Practice Activity 1.2<\/strong> To foster company spirit, HR believes it\u2019s important for supervisors to send cards to their direct reports on their birthday. An HR analyst wrote a query to report the data, but forgot to specify whose name was the employee\u2019s and whose was the supervisor\u2019s.<\/p>\n\n\n\n
          Rename the columns to specify which is the employee and which is the supervisor. (Hint: this previews the WHERE concept.)<\/em><\/p>\n\n\n\n
          Answer:<\/strong><\/p>\n\n\n\n
          SELECT e.EmployeeId,\n\u00a0 \u00a0 \u00a0 e.FirstName AS "Employee First Name",\n\u00a0 \u00a0 \u00a0 e.LastName\u00a0 AS "Employee Last Name",\n\u00a0 \u00a0 \u00a0 s.FirstName AS "Supervisor First Name",\n\u00a0 \u00a0 \u00a0 s.LastName\u00a0 AS "Supervisor Last Name",\n\u00a0 \u00a0 \u00a0 e.BirthDate,\n\u00a0 \u00a0 \u00a0 e.Email\nFROM Employee e, Employee s\nWHERE e.ReportsTo = s.EmployeeId;<\/code><\/pre>\n<\/section>\n\n\n\n
          \n
          Activity 3: Aggregates, Functions, and Arithmetic<\/strong><\/h2>\n\n\n\n
          1. GROUP BY<\/strong><\/h3>\n\n\n\n
          Purpose:<\/strong> Condense many rows into one summary row per group, so we can run aggregate calculations on each group.<\/p>\n\n\n\n
          SELECT CustomerId\nFROM Invoice\nGROUP BY CustomerId;<\/code><\/pre>\n\n\n\n
          On its own, GROUP BY just collapses each group down to one row. It becomes genuinely useful once paired with an aggregate function.<\/p>\n\n\n\n
          2. Aggregate Functions (SUM, COUNT, AVG, MIN, MAX)<\/strong><\/h3>\n\n\n\n
          Purpose:<\/strong> Calculate a single summary value \u2014 a total, a count, an average \u2014 across each group.<\/p>\n\n\n\n
          Count:<\/p>\n\n\n\n
          SELECT CustomerId, COUNT(InvoiceId) AS NumOfInvoices\nFROM Invoice\nGROUP BY CustomerId;<\/code><\/pre>\n\n\n\n
          Sum:<\/p>\n\n\n\n
          SELECT CustomerId, SUM(Total) AS TotalSpent\nFROM Invoice\nGROUP BY CustomerId;<\/code><\/pre>\n\n\n\n
          Average:<\/p>\n\n\n\n
          SELECT CustomerId, AVG(Total) AS AverageInvoice\nFROM Invoice\nGROUP BY CustomerId;<\/code><\/pre>\n\n\n\n
          Minimum:<\/p>\n\n\n\n
          SELECT CustomerId, MIN(Total) AS SmallestInvoice\nFROM Invoice\nGROUP BY CustomerId;<\/code><\/pre>\n\n\n\n
          Maximum:<\/p>\n\n\n\n
          SELECT CustomerId, MAX(Total) AS LargestInvoice\nFROM Invoice\nGROUP BY CustomerId;<\/code><\/pre>\n\n\n\n
          Function<\/th>Description<\/th><\/tr><\/thead>
          AVG()<\/td>The mean value<\/td><\/tr>
          SUM()<\/td>The total of all values<\/td><\/tr>
          COUNT()<\/td>The number of rows<\/td><\/tr>
          MIN()<\/td>The minimum value<\/td><\/tr>
          MAX()<\/td>The maximum value<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
          3. Arithmetic Operators<\/strong><\/h3>\n\n\n\n
          Purpose:<\/strong> Perform math on numeric columns directly inside a query.<\/p>\n\n\n\n
          Operator<\/th>Description<\/th>Example<\/th><\/tr><\/thead>
          +<\/td>addition<\/td>2 + 2 = 4<\/td><\/tr>
          –<\/td>subtraction<\/td>5 – 3 = 2<\/td><\/tr>
          *<\/td>multiplication<\/td>3 * 4 = 12<\/td><\/tr>
          \/<\/td>division<\/td>12 \/ 3 = 4<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
          SELECT Name, UnitPrice, UnitPrice - 0.10 AS DiscountedPrice\nFROM Track;<\/code><\/pre>\n\n\n\n
          4. String Concatenation (||)<\/strong><\/h3>\n\n\n\n
          Purpose:<\/strong> Combine (concatenate) two pieces of text into one. || doesn\u2019t add spaces automatically \u2014 add them yourself.<\/p>\n\n\n\n
          SELECT (FirstName || ' ' || LastName) AS FullName\nFROM Customer;<\/code><\/pre>\n\n\n\n
          Summary<\/strong><\/p>\n\n\n\n
          GROUP BY condenses rows into groups so aggregate functions (AVG, SUM, COUNT, MIN, MAX) can summarize each one. Arithmetic operators (+ – * \/) and the concatenation operator (||) let you compute new values directly in a query.<\/p>\n\n\n\n
          \n\n\n\n
          Practice 3<\/strong><\/h3>\n\n\n\n
          All of the activities below are based on the Chinook database.<\/em><\/p>\n\n\n\n
          Practice Activity 3.1<\/strong> Return a list of composers and the average runtime for their tracks, in milliseconds.<\/p>\n\n\n\n
          Answer:<\/strong><\/p>\n\n\n\n
          SELECT Composer, AVG(Milliseconds) AS AvgRuntimeMs\nFROM Track\nGROUP BY Composer;<\/code><\/pre>\n\n\n\n
          Practice Activity 3.2<\/strong> Due to rampant inflation, the price of each track is being raised by $0.50. Return a list of tracks and their new prices. The track name should be formatted like this: “[Track Name] by [Composer]”.<\/p>\n\n\n\n
          Answer:<\/strong><\/p>\n\n\n\n
          SELECT (Name || ' by ' || Composer) AS TrackInfo,\n\u00a0 \u00a0 \u00a0 UnitPrice + 0.50 AS NewPrice\nFROM Track;<\/code><\/pre>\n\n\n\n
          Chapter Exercise<\/strong> Write a query that lists composers and the number of tracks that composer has written.<\/p>\n\n\n\n
          Answer:<\/strong><\/p>\n\n\n\n
          SELECT Composer, COUNT(TrackId) AS NumTracks\nFROM Track\nGROUP BY Composer;<\/code><\/pre>\n<\/section>\n\n\n\n
          \n
          Activity 4: Multi-Table Queries (JOINs)<\/strong><\/h2>\n\n\n\n
          1. Primary & Foreign Keys<\/strong><\/h3>\n\n\n\n
          Purpose:<\/strong> Understand how tables relate to one another before combining them.<\/p>\n\n\n\n
          A primary key<\/strong> uniquely identifies each row in its own table (e.g. Customer.CustomerId). A foreign key<\/strong> is a column in one table that references a primary key in another (e.g. Invoice.CustomerId points back to Customer.CustomerId). These columns often \u2014 but not always \u2014 share a name.<\/p>\n\n\n\n
          2. INNER JOIN<\/strong><\/h3>\n\n\n\n
          Purpose:<\/strong> Combine rows from two tables, keeping only the rows where the key matches in both<\/em> tables.<\/p>\n\n\n\n
          — List of invoices and the customer who purchased each one<\/em>
          <\/p>\n\n\n\n
          SELECT i.InvoiceId, i.CustomerId, c.FirstName, c.LastName, i.Total\nFROM Invoice i\nINNER JOIN Customer c ON i.CustomerId = c.CustomerId\nORDER BY i.InvoiceId;<\/code><\/pre>\n\n\n\n
          <\/p>\n\n\n\n
          SELECT i.InvoiceId, i.CustomerId, c.FirstName, c.LastName, i.Total\nFROM Invoice I, Customer c\nWHERE i.CustomerId = c.CustomerId\nORDER BY i.InvoiceId;<\/code><\/pre>\n\n\n\n
          3. LEFT OUTER JOIN<\/strong><\/h3>\n\n\n\n
          Purpose:<\/strong> Keep every<\/em> row from the left-hand table, whether or not it has a match in the right-hand table. Useful for finding rows with no matching data \u2014 like tracks that have never been purchased.<\/p>\n\n\n\n
          — Find all tracks that have never appeared on an invoice<\/em>
          <\/p>\n\n\n\n
          SELECT t.TrackId, t.Name, il.InvoiceId\nFROM Track t\nLEFT OUTER JOIN InvoiceLine il ON t.TrackId = il.TrackId\nWHERE il.InvoiceId IS NULL;<\/code><\/pre>\n\n\n\n
          Summary<\/strong><\/p>\n\n\n\n
          An INNER JOIN returns only the rows with a match in both tables. A LEFT OUTER JOIN keeps every row from the left table, filling in NULL where there\u2019s no match on the right \u2014 which is exactly how you find \u201corphan\u201d rows with no related data.<\/p>\n\n\n\n
          \n\n\n\n
          Practice 4<\/strong><\/h3>\n\n\n\n
          All of the activities below are based on the Chinook database.<\/em><\/p>\n\n\n\n
          Chapter Exercise<\/strong> The music store wants to generate a catalogue of the music they sell, containing the track name, artist name, album name, and price.<\/p>\n\n\n\n
          Challenge: List the genre along with the rest of the information.<\/em><\/p>\n\n\n\n
          Answer:<\/strong><\/p>\n\n\n\n
          SELECT t.Name AS TrackName, ar.Name AS ArtistName, al.Title AS AlbumName, t.UnitPrice\nFROM Track t\nJOIN Album al ON t.AlbumId = al.AlbumId\nJOIN Artist ar ON al.ArtistId = ar.ArtistId;<\/code><\/pre>\n\n\n\n
          Challenge:<\/em><\/p>\n\n\n\n
          SELECT t.Name AS TrackName, ar.Name AS ArtistName, al.Title AS AlbumName,\n\u00a0 \u00a0 \u00a0 g.Name AS Genre, t.UnitPrice\nFROM Track t\nJOIN Album al ON t.AlbumId = al.AlbumId\nJOIN Artist ar ON al.ArtistId = ar.ArtistId\nJOIN Genre g ON t.GenreId = g.GenreId;<\/code><\/pre>\n<\/section>\n\n\n\n
          \n
          Syntax Cheat Sheet<\/h2>\n\n\n\n
          Category<\/th>Keyword \/ Operator<\/th>Purpose<\/th><\/tr><\/thead>
          Retrieve<\/td>SELECT, FROM, DISTINCT, AS<\/td>Choose and rename columns<\/td><\/tr>
          Sort & limit<\/td>ORDER BY, ASC \/ DESC, LIMIT<\/td>Sort and cap results<\/td><\/tr>
          Filter<\/td>WHERE, =, LIKE, <>, < > <= >=<\/td>Filter rows on a condition<\/td><\/tr>
          Filter \u2014 missing data<\/td>IS NULL, IS NOT NULL<\/td>Test for missing values<\/td><\/tr>
          Filter \u2014 combine<\/td>AND, OR, (), IN (…), BETWEEN … AND …<\/td>Combine or expand conditions<\/td><\/tr>
          Summarize<\/td>GROUP BY, COUNT(), SUM(), AVG(), MIN(), MAX()<\/td>Aggregate data by group<\/td><\/tr>
          Compute<\/td>+ – * \/, || (concatenation)<\/td>Arithmetic and string concatenation<\/td><\/tr>
          Combine tables<\/td>INNER JOIN, LEFT OUTER JOIN, ON<\/td>Combine related tables<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/section>\n","protected":false},"featured_media":0,"template":"","class_list":["post-1378","activity","type-activity","status-publish","hentry"],"acf":[],"_links":{"self":[{"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/activity\/1378","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/activity"}],"about":[{"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/types\/activity"}],"wp:attachment":[{"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/media?parent=1378"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}