You are here:

MS SQL Server/SQL for deal information

Advertisement


Question
Hey guys
Was wondering if someone could help me out with the following questions. I haven't got access to SQL Server where I'am and my SQL guy is on holidays. I was wondering if someone could be kind enough to answers the below by writing the code so I can run it tomorrow

Thanks!

1. Whats the count of purchases made on 12th January 2014.

2. Whats the deal title which was purchased on 12th January 2014.

3. Whats the city name and count of deals, where the city name starts with “Sydney”.

4. Whats the deal_title and gross billings for every deal.


Purchase
purchase_id int 1, 2, 3, 4…..
purchase_day string 2014-01-12
person_id int 1, 2, 3, 4
deal_id int 1, 2, 3, 4
coupons int 1, 1, 3, 4
gross_billings double 20.0, 30.0, 40.0
net_billings double 11.5, 17.5, 23.2

Deal
deal_id int 1, 2, 3, 4
deal_title string Dinner for 2, Holiday in Fiji
city_name string Sydney East, Sydney West, Melbourne West, ….

Additional information
purchase_id: Is an unique identifier for each purchase transaction
deal_id: Is an unique identifier for each deal
coupons: Number of vouchers purchased
person_id: Is an unique identifier for each customer
purchase_day: Day when the purchase was made
gross_billings: Is the amount charged to our customers credit card.
net_billings: Is living social’s share of the purchase revenue
deal_title Name of each deal
city_name: Name of the market where deal is based

Answer
Dear Matt,

My apologies for late reply!

Well, I believe, all these questions were more of home work....
Below are the queries those should work for you!


###########
SELECT COUNT(1)
FROM Purchase
WHERE purchase_day = '2014-01-12'


SELECT deal_title
FROM Deal
WHERE deal_id IN
  (
     SELECT deal_id
     FROM Purchase
     WHERE purchase_day = '2014-01-12'
  )


SELECT city_name
  , COUNT(deal_id) AS deal_count
WHERE city_name LIKE 'Sydney%'


SELECT deal_id
  , deal_title
  , gross_billings
FROM Additional_Information


############

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jwalant Natvarlal Soneji

Expertise

From general queries about SQL Severs both 2000 and 2005 to advanced features like Integration Services and Reporting Services.

Experience

Experience in the area: I have been working with database triggers, stored procedures, views, function, joins, dts, Query optimization. Education/Credentials: BE IT - 2005 Batch with First Class.

Publications
http://jwalantsoneji.com

Education/Credentials
BE IT, India

Awards and Honors
MCP

©2016 About.com. All rights reserved.