HomeworkLib
Question

For the following question: In a table called AllColors there are columns called Colors and ColorName....

For the following question: In a table called AllColors there are columns called Colors and ColorName. In another table called Items there are columns called ItemID, ItemName, SupplierID, and the connecting column which Colors. Return the Colors, ColorName, and the count items for each color. And order results by the count, DESC,

I have the following answer:

SELECT Colors, ColorName, COUNT(*) as cnt

FROM AllColors A, Items I

WHERE A.Colors = I.Colors GROUP BY Colors, ColorName

ORDER BY cnt DESC;

However, it is almost correct, I should be getting 36 rows, but I'm only getting 7. there are null colors. and when I add the following in the where clause I get 36 rows but the results are incorrect, (WHER A.Colors = I.Colors OR (I.Colors IS NULL))

PLZ HELP!

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

SELECT A.Colors, ColorName, COUNT(*) as cnt

FROM AllColors A, Items I

WHERE A.Colors = I.Colors

GROUP BY A.Colors, ColorName

ORDER BY cnt DESC;

If any results are incorrect, please provide table data for better output...

Let me know if you have any clarifications. Thank you

0 0
Add a comment
Know the answer?
Add Answer to:
For the following question: In a table called AllColors there are columns called Colors and ColorName....
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • In a table called Sales, there are columns named Quantity and StockID. In another table named...

    In a table called Sales, there are columns named Quantity and StockID. In another table named Items there are columns named StockID, which is the connecting column to the other table, ItemID, and ItemName. Write a query that returns the ItemID, ItemName, and the average, min, and max quantities for all stock items. And sort the results by average quantity, DESC TSQL

  • Answer the following SQL study guide questions... Question 1 (1 point) Unless you assign a column...

    Answer the following SQL study guide questions... Question 1 (1 point) Unless you assign a column name in the base table. the column name in the result set is the same as the 1) unique syntax 2) column alias 3) qualification 4) all of the above Question 2 (1 point) Which clause specifies the number of rows that should be skipped before rows are returned from the result set? 1) FETCH 2) OFFSET 3) FIRST 4) NEXT Question 3 (1...

  • QUESTION 32 Which statement can be used for conditional queries equal to if-then-else CASE WHERE DISTINCT...

    QUESTION 32 Which statement can be used for conditional queries equal to if-then-else CASE WHERE DISTINCT DECODE 2.5 points    QUESTION 33 The GROUP BY clause comes before the ORDER BY clause True False 2.5 points    QUESTION 34 In the statement: SELECT Order_Item from orders o where order_id=1001; the "o" is a column alias table alias part of the column name a join operator 2.5 points    QUESTION 35 Include which clause to this statement to return the null...

  • -- Create a table with the following columns, named bsg_spaceship -- -- id - an auto-incrementing...

    -- Create a table with the following columns, named bsg_spaceship -- -- id - an auto-incrementing integer which is also the primary key -- name - variable-length string with a max of 255 characters, cannot be null -- separate_saucer_section - a boolean property which specifies whether or not there is a separate saucer section on the spaceship. This defaults to No. -- length - integer, cannot be null -- -- Once you have created the table, run the query "DESCRIBE...

  • Using the Cash table below, show the output for the following SQL query: (Using the dropdowns,...

    Using the Cash table below, show the output for the following SQL query: (Using the dropdowns, identify which rows and columns would included in the SQL query output shown below. Select "Not included" for rows and columns that would not be included in the output.) SELECT Account#, Balance FROM Cash WHERE Bank = ‘Boston5’ ORDER BY Amount DESC; Cash Account # Type Bank Balance 253 48.000 75,000 950 Checking Checking Draft Checking ncluded BA-6 BA-7 ot Included BA-8 BA-9 Boston...

  • 1. Use the data in the contingency table to answer the question. Columns Rows 1 2...

    1. Use the data in the contingency table to answer the question. Columns Rows 1 2 3 Total 1 36 35 92 163 2 67 57 113 237 Total 103 92 205 400 You wish to test the null hypothesis of "independence"—that the probability that a response falls in any one row is independent of the column it falls in—and you plan to use a chi-square test. You are given that there are 2 degrees of freedom associated with the...

  • Which SQL statement retrieves the EmployeeName and City columns from the Employees table? O LIST EmployeeName,...

    Which SQL statement retrieves the EmployeeName and City columns from the Employees table? O LIST EmployeeName, City ON Employees; O FIND EmployeeName, City ON Employees; SELECT EmployeeName, City FROM Employees; O RETRIEVE EmployeeName, City FROM Employees; Which SQL statement finds the different cities in which national parks reside with no duplicate rows listed? SELECT DISTINCT city FROM natlpark ORDER BY city; O ORDER BY natlpark DISTINCT city; O ORDER BY city FROM natlpark; O SELECT city FROM natlpark ORDER BY...

  • For this problem, assume salesperson data are stored in a database table named staff. Also assume the columns in the table are named name, carsSold, and totalSales Write a Python function named ge...

    For this problem, assume salesperson data are stored in a database table named staff. Also assume the columns in the table are named name, carsSold, and totalSales Write a Python function named getSalesSortedByNames. Your function will have 2 parameters. The first parameter is a database cursor and the second parameter is a float. Your function should start by retrieving those rows in the staff table whose totalsales field is equal to the second parameter. (The next paragraph shows the Python...

  • Question 9 (1 point) Which choice below describes the function of the following SQL statement? SELECT...

    Question 9 (1 point) Which choice below describes the function of the following SQL statement? SELECT ID, Last Name, First Name, Address FROM Members WHERE Last Name Last Name a simple query a parameterized query a compound query a wildcard query Question 10 (1 point) The correct SQL syntax to sort a table by Last Name in order of A-Z is SORT BY Last Name ASCENDING ORDER BY Last Name Down ORDER BY Last Name ASC ORDER BY Last Name...

  • (1) Use a single SQL statement to create a relational table and to load into the...

    (1) Use a single SQL statement to create a relational table and to load into the table all information about the orders submitted in 1996 or in 1998 by the customers located in Paris or in London or in Madrid. Next, enforce the appropriate consistency constraints on the new table. (2) Create a new relational table to store information about the company names of all suppliers and the total number of products supplied by each supplier. Enforce, the appropriate consistency...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT