The schema for a video database within the Conde Nast Entertainment(CNE) group Which videos are sold by "Conde_Nast Video " or "Conde_Nast_Entertainment Video"?

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

SQL Test: Commerce Analyst

PART 1:  The schema for a video database within the Conde Nast Entertainment(CNE) group is as follows:

Users

UserID

LastName

FirstName

Inventory Record

CassetteID

VideoID

Videos

VideoID

VideoName

VideoSeller

SellerID

VideoID

Price

Orders

OrderID

SellerID

VideoID

Copies

Rentals

UserID

CassetteID

CheckoutDate

Duration

Sellers

SellerID

SellerName

1. Write out SQL statements for each of the following 5 queries:

• Which videos are sold by "Conde_Nast Video " or "Conde_Nast_Entertainment Video"?

• Which sellers supply all the videos in the inventory? (Hint:  first get a list of the video sellers and all the videos in the inventory using the cross product.  Then find out which of these tuples are invalid.)
• How many videos in the inventory record does each video seller supply?  That is, for each video seller, calculate the number of videos it supplies that also happen to be videos in the inventory.
• Which seller has the cheapest price for the video "Vogue 50 Fall Fashion Ideas"?
• For which videos are there more than 1 copy in our inventory?  (Note that the CassetteID in inventory is different for different copies of the same VideoID)

 

PART 2:  For this section of the test, please refer to the following datasets (csv attachments). Please supplement each answer with SQL statements, key insights and data visualizations (where applicable)

Available Tables >>

1. USANames
   1. Fields: Id, Name, Year, Gender, Count
2. NamesByState
   1. Id, Name, Year, Gender, State, Count,

 

• Analyze the 50 most popular and the 50 least popular female names in the last 5 years, under 6 characters and start with the letter “A’.
  • Provide (1) SQL statements that pull data for most popular and least popular female names (2) key takeaways/insights around this data
• Analyze extinct female names in the USA (extinct names were in use before 1960, but have not been used since 1999).
  • Provide (1) SQL statement that pulls data for extinct names (2) key takeaways/insights around this data

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of