Log out Go to App
Go to App

Learning Materials

Features

Discover

Chapter 6: Problem 32

Consider the Sales database and its Store and Product tables. Construct a query to list the storeID and the productID. When you add Store and Product to the relationships area there is a line joining the two tables. Delete the join line. Run the query. Notice how many rows there are; the number of rows in the result set is the number of stores times the number of products.Exercise \(6.8 .1\)

Short Answer

Expert verified
The query to run is: \begin{verbatim} SELECT Store.storeID, Product.productID FROM Store, Product; \end{verbatim}. The result set will comprise of a number of rows equal to the product of the number of stores and the number of products, as each storeID will be paired with each productID.

Step by step solution

01

Setting up the data environment

To solve this exercise, it is assumed that there is already a Sales database in which there are two tables, Store and Product. In each of these tables, there are specific unique identifiers, storeID for the Store table, and productID for the Product table. Joining these two tables by deleting the join line will provide a new view of the data.
02

Constructing the query without join

Given that we would like a list of storeID and productID, and the join line between the Store and Product tables needs to be deleted, we create our query as follows: \begin{verbatim} SELECT Store.storeID, Product.productID FROM Store, Product; \end{verbatim} This SQL query will return every combination of storeID and productID pairs since no specific join condition is specified.
03

Running the query

After creating the query, run it in your SQL environment. The number of rows in the result set will be equal to the number of stores times the number of products, as each storeID and productID combination will have a distinct row.

Unlock Step-by-Step Solutions & Ace Your Exams!

  • Full Textbook Solutions

    Get detailed explanations and key concepts

  • Unlimited Al creation

    Al flashcards, explanations, exams and more...

  • Ads-free access

    To over 500 millions flashcards

  • Money-back guarantee

    We refund you if you fail your exam.

Start your free trial

Over 30 million students worldwide already upgrade their learning with Vaia!

One App. One Place for Learning.

All the tools & learning materials you need for study success - in one app.

Get started for free

Most popular questions from this chapter

Consider the Orders database and the Employee table. 1\. Write a query to list the employee who does not report to anyone. 2\. Write a query to list each employee and the number of employees they supervise.

Consider the Company database and its Employee table. 1\. The empId field is assigned values sequentially starting at 1. What is the last empId value that was used? (What is the empId for the last employee added to the table?) 2\. Write a query to determine the name of the oldest employee. 3\. Write a query to list all of the employee last names. If at least two employees have the same last name then this list will be shorter that a list of employees. 4\. Suppose there is a field hireDate which holds the date when an employee was hired. Write a query to determine the name of the employee who was most recently hired?

Consider the following SQL statements and show how each statement would appear in Design View. You can confirm your result if you create a query, switch to SQL View, type the query statement and then switch to Design View. Unfortunately, if you make any syntax errors, Access will be unable to switch to Design View. Your database must contain the tables in the From clause. 1\. SELECT Products.ProductID, Products.ProductName, Categories.CategoryName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE Categories.CategoryName = "Beverages" AND Products.Discontinued = Yes; 2\. SELECT Auto.Year, Dealer.name FROM Auto INNER JOIN Dealer ON Auto.Did = Dealer.Did WHERE Auto.Price > 10000 OR Auto.Colour = “blue” ; 3\. SELECT Auto.Year, Dealer.name FROM Auto RIGHT OUTER JOIN Dealer ON Auto.Did = Dealer.Did WHERE Auto.Price > 10000 OR Auto.Colour = “blue” ;

Consider the Orders database. 1\. Create a query to list each customer and their orders (order id and order date). Are there any customers who have not placed an order? 2\. Modify the above query to list each customer and the number of orders they have placed (include all customers).

Consider the University database. 1\. Create a query to list all courses in a department (for which the user supplies the department code). 2\. Create a query to list all course titles where the user supplies both the department code and the credit hours. Note that two separate criteria, each with their own parameter, must be specified.
See all solutions

Recommended explanations on Computer Science Textbooks

Computer Organisation and Architecture

Read Explanation

Problem Solving Techniques

Read Explanation

Data Structures

Read Explanation

Computer Network

Read Explanation

Game Design in Computer Science

Read Explanation

Algorithms in Computer Science

Read Explanation
View all explanations

What do you think about this solution?

We value your feedback to improve our textbook solutions.

Study anywhere. Anytime. Across all devices.

Sign-up for free