• Skip to main content
  • Skip to primary sidebar

Datannum

Data science for small business applications

  • Home
  • Microsoft Excel (spreadsheet) forum
    • Project: Analyze NYSE Data
  • SQL forum
    • Project: Query a Digital Music Store Database
  • Tableau forum (data visualization)
    • Project: Data Visualization Project using Tableau
  • Linear Algebra forum
    • Curated resource to learn linear algebra
  • Web development
    • WordPress forum
    • Google Support

Practice Quiz – Writing Queries: Is my JOIN usage correct?

Data Science for Small Business Applications › Forums › SQL › Practice Quiz – Writing Queries: Is my JOIN usage correct?

Tagged: COUNT NULL in SQL, JOIN clause in SQL

  • This topic is empty.
Viewing 1 post (of 1 total)
  • Author
    Posts
  • April 22, 2022 at 9:13 am #140
    Admin
    Keymaster

      Here is the problem:

      All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

      Find the first and last name of any customer who does not have an invoice. Are there any customers returned from the query?

      SQL Coursera Source: https://www.coursera.org/learn/sql-for-data-science

      Here is my tentative solution:

      SELECT customers.FirstName, customers.LastName, COUNT(invoices.invoiceId)
      FROM customers 
      JOIN invoices
      ON customers.CustomerId  = invoices.CustomerId
      GROUP BY Customers.CustomerId
      HAVING COUNT(invoiceId) IS NULL

      Seeking help as it seems my JOIN query might not be correct.


      Reply


      COUNT differs from the other aggregate functions, it never returns NULL. You need to use COUNT(...) = 0 instead.

      You’re trying to find any customers without invoices, but when you use an INNER JOIN it will only return customers that have rows in the invoice table. You need to use LEFT JOIN. https://www.tutorialspoint.com/sql/sql-left-joins.htm

      So revised query:

    • Author
      Posts
    Viewing 1 post (of 1 total)
    • You must be logged in to reply to this topic.
    Log In

    Primary Sidebar

    profile for Techcosec Limited on Stack Exchange, a network of free, community-driven Q&A sites

    RSS CS50’s Introduction to Computer Science by Harvard University on Edx

    • How to print winner January 21, 2023
    • find_min function and infinite loop when a candidate not winner in first instance January 17, 2023
    • Bool variable won and print_winner function January 12, 2023
    • True condition with while loop January 6, 2023
    • How to finally display result in tabular form of choices entered by voters December 12, 2022

    Subscribe via Email

    Enter your email address to subscribe and receive notifications of new posts by email.

    This website hosted on WP Engine

    Initiative by Digital Splendid OPC Pvt. Ltd. & Techcosec Limited