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

 

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. 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

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme