53600-001: Business Data Warehousing

Assignment- 1

1. SQL Refresher 1

salesman_id name city commission

----------- ---------- ---------- ----------

5001 James Hoog New York 0.15

5002 Nail Knite Paris 0.13

5005 Pit Alex London 0.11

5006 Mc Lyon Paris 0.14

5003 Lauson Hen 0.12

5007 Paul Adam Rome 0.13

With reference to the above table with 4 columns:

1. Write a SQL query to retrieve only salesman_id and City. The output needs to be sorted by ascending order of city.
2. Write a SQL to select the maximum value of commission and display the salesman_id, name and city.
3. Write a SQL to display only those rows where name has a value J in it.
4. Write a SQL to display only those rows where city is not null or empty.
5. Write a SQL to display only name where commission = 0.13
6. Write a SQL to display all rows and have commission values multiplied by 100.
7. Write a SQL to display only the total(sum) of all commission in the table.
8. Write a SQL to display only the average of all commission in the table.
9. Write SQL to display only top 2 (based on commission values) salesman_id and name .
10. Write a SQL to display all rows and columns and include a new column that will contains todays date and time. (Use getdate() function).

2. SQL Refresher 2

With reference to the data in below two tables:

Table: Order

OrderID CustomerID EmployeeID OrderDate ShipperID

10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

10319 21 11 1998-09-20 2

Table: Customer

CustomerID CustomerName

3 Ann Kooi

7 Adams Smith

8 Charlie Anthony

1. Write SQL to obtain count of total number of rows where OrderDate is older than year 1997.
2. Write SQL to obtain the row containing the latest order date.
3. Write a query to update Ann Kooi name to Ann Cooi
4. Write a query to split CustomerName on First Name and Last Name using the space
5. Write a query to display CustomerName firstname only. (Eg. Ann, Adams and Charli

3. Data can be categorized into different categories and sub-categories as below:

1. Qualitative
2. Quantitative
   1. Discrete
   2. Continuous

Note: Discrete data (mostly whole numbers) is counted whereas Continuous (can contain decimal values) data is measured.

Based on the above categorization, identify the type of data in below statements:

First one is solved as an example:

1. Dog has 4 legs.

Answer: Quantitative Data Discrete

1. The cat weighs 25.5 pounds
2. The most common name in our country is Michael.
3. Alicia has 5 kittens.
4. Amazon sells about 4 Million products.
5. The distance between the two cities is 80 miles.
6. The companys revenue increased by 20% in year 2017.
7. Total number of patients seen by Dr. Sue is 256 last month
8. It took 4 years for the museum to complete its construction
9. He weighed 160 lbs last year and is attempting to reduce it to 130lbs in next two years.
10. The average age of student is 23 years in this university.