Which videos are sold by Conde Nast Video or Conde Nast Entertainment Video

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

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

a. Fields: Id, Name, Year, Gender, Count

2. NamesByState

a. 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’. 

o Provide (1) SQL statements that pull data for most popular and least popular female names (2) key takeaways/insights around this data 

 

 

Attachments:

Expert's Answer

718 Times Downloaded

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