Homework Help is Here – Start Your Trial Now!
Engineering
Computer Engineering
Write all the sql code (including create, insert and select)

Write all the sql code (including create, insert and select)

Computer Networking: A Top-Down Approach (7th Edition)
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
See similar textbooks
icon
Related questions
Question

Write all the sql code (including create, insert and select)

Create the following table in your database with the following schema: Table: Visits | Column Name | Type +- | int | visit id customer id | int +----- visit id is the primary key for this table. This table contains information about the customers who visited the mall. Table: Transactions | Column Name | Type visit id | amount | transaction id | int | int | int +---- transaction id is the primary key for this table. This table contains information about the transactions made during the ----+ visit id. Add the following data to your tables: Input: Visits +-------- | visit id | customer id +-- | 1 | 2 | 23 | 30 | 54 | 96 | 54 | 54 | 5 Transactions +---- | transaction id | visit id | amount +-- | 5 | 5 | 5 | 1 | 2 | 2 310 | 300 | 200 | 910 | 970 | 12 | 13 +-- ----+
Transcribed Image Text:Create the following table in your database with the following schema: Table: Visits | Column Name | Type +- | int | visit id customer id | int +----- visit id is the primary key for this table. This table contains information about the customers who visited the mall. Table: Transactions | Column Name | Type visit id | amount | transaction id | int | int | int +---- transaction id is the primary key for this table. This table contains information about the transactions made during the ----+ visit id. Add the following data to your tables: Input: Visits +-------- | visit id | customer id +-- | 1 | 2 | 23 | 30 | 54 | 96 | 54 | 54 | 5 Transactions +---- | transaction id | visit id | amount +-- | 5 | 5 | 5 | 1 | 2 | 2 310 | 300 | 200 | 910 | 970 | 12 | 13 +-- ----+
Write an SQL query to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits. Return the result table sorted in any order. The results should be: Output: +-- customer_id | count_no_trans | | 54 | 30 | 96 | 2 | 1 | 1 Explanation: Customer with id = 23 visited the mall once and made one transaction during the visit with id = 12. Customer with id = 9 visited the mall once and made one transaction during the visit with id = 13. Customer with id = 30 visited the mall once and did not make any transactions. Customer with id = 54 visited the mall three times. During 2 visits they did not make any transactions, and during one visit they made 3 transactions. Customer with id = 96 visited the mall once and did not make any transactions. As we can see, users with IDs 30 and 96 visited the mall one time without making any transactions. Also, user 54 visited the mall twice and did not make any transactions.
Transcribed Image Text:Write an SQL query to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits. Return the result table sorted in any order. The results should be: Output: +-- customer_id | count_no_trans | | 54 | 30 | 96 | 2 | 1 | 1 Explanation: Customer with id = 23 visited the mall once and made one transaction during the visit with id = 12. Customer with id = 9 visited the mall once and made one transaction during the visit with id = 13. Customer with id = 30 visited the mall once and did not make any transactions. Customer with id = 54 visited the mall three times. During 2 visits they did not make any transactions, and during one visit they made 3 transactions. Customer with id = 96 visited the mall once and did not make any transactions. As we can see, users with IDs 30 and 96 visited the mall one time without making any transactions. Also, user 54 visited the mall twice and did not make any transactions.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps with 1 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY
SEE MORE TEXTBOOKS