Basic SQL SELECT queries

A relational database consists of a set of tables, each with a fixed set of columns and a variable number of rows. For example, here is a potential database consisting of two tables, named courses and bids; as it happens, each table has three columns and three rows.

  • courses table
    coursetitleinstructor
    ARTS 130Digital ArtPayne
    CSCI 135RoboticsFerrer
    PHYS 100AstronomyWright
    bids table
    usercoursebid
    bugsPHYS 1003
    daffyCSCI 1351
    daffyPHYS 1004

    • A database management system (often abbreviated DBMS) is a piece of software written for managing a database. Though not necessarily relational, most of the prominent DBMSs today can deal with relational databases. Popular DBMS packages includes Oracle, SQL Server, PostgreSQL, MySQL, Microsoft Access, and SQLite.

    SQL is a language designed specifically for manipulating relational databases, and basically all popular DBMS packages support it. (Indeed, the language name often shows up in the product name.) The basic way to use SQL to retrieve information out of the database is the SELECT query.

    1. Show the bids that are more than 2, along with the users bidding them.

      SELECT user, bid
      FROM bids
      WHERE bid > 2
      bugs3
      daffy4

    2. Show each bid with the corresponding course title. In this case, we want to combine two tables, which we accomplish using a join, identifying which column is used to connect rows together from the two tables.

      SELECT bid, title
      FROM courses JOIN bids ON courses.course = bids.course
      3Astronomy
      1Robotics
      4Astronomy

    3. For each bid beyond 2, show the user, course title, and bid amount.

      SELECT user, title, bid
      FROM courses JOIN bids ON courses.course = bids.course
      WHERE bid > 2
      bugsAstronomy3
      daffyAstronomy4

    4. For each user, display the name and total bid amount. In this case, we want temporarily to divide a table into groups, which are basically sub-tables sharing the user name in common, and we can apply aggregate functions such as SUM to the column of each individual group. We accomplish this by adding a GROUP BY clause.

      SELECT user, SUM(bid)
      FROM bids
      GROUP BY user
      bugs3
      daffy5

    5. For each course, display the course title and total bid amount (ignoring courses with no bids).

      SELECT title, SUM(bid)
      FROM courses JOIN bids ON courses.course = bids.course
      GROUP BY courses.course
      Robotics1
      Astronomy7

    6. For each course, display the course title and total bid amount (including courses with no bids). To get all courses — not just those with a matching bid — we want a left join, where the result is just like a standard join (an inner join) except that any row in the first table not represented in the second is still represented in the result; the columns corresponding to the second table are null.

      SELECT title, SUM(bid)
      FROM courses LEFT JOIN bids ON courses.course = bids.course
      GROUP BY courses.course
      Digital Art0
      Robotics1
      Astronomy7

    7. Do the above, except ensure that the output is sorted by course title. In this case we add an ORDER BY clause.

      SELECT title, SUM(bid)
      FROM courses LEFT JOIN bids ON courses.course = bids.course
      GROUP BY courses.course
      ORDER BY title
      Astronomy7
      Digital Art0
      Robotics1