SQL Queries for Student and GPA Tables, Account and Transaction Tables

Student-GPA Table Queries

Please write the Sql to answer the following questions. You may use subquery but all questions need to be completed in One Sql. You also need to use the following tables (Student and GPA) to answer Q1— Q3.

Atlanta, GA - United States

New Orleans, LA - United States

Seoul - Korea

is the CITY in Korea

2. Please find the student (Student ID and Student Name) who has the highest GPA in MGS 3100 (Course Name) in Fall 2006.

3. List all the students (Student ID and Student Name) who enrolled in MGS 4020 in Spring 2008.

Please write the SQL to answer the following questions. You may use subquery but all questions need to be completed in ONE SQL. You also need to use the following tables (ACCOUNT and TRANSACTION) to answer Q4 and Q5.

The Standard Industrial Classification (SIC) is a North American Industry Classification System (NAICS), for classifying industries by a four-digit code. The first two digits of the code identify the major industry group, the third digit identifies the industry group, and the fourth digit identifies the industry. For example:

541 Grocery Stores

5411 Grocery Stores

5412 Convenience Stores

542 Meat And Fish (Seafood) Markets, Including Freezer Provisioners

5421 Meat And Fish (Seafood) Markets, Including Freezer Provisioners

543 Fruit And Vegetable Markets

5431 Fruit And Vegetable Markets

544 Candy, Nut, And Confectionery Stores

5441 Candy, Nut, And Confectionery Stores

545 Dairy Products Stores

546 Retail Bakeries

5461 Retail Bakeries

549 Miscellaneous Food Stores

5499 Miscellaneous Food Stores

4. Find the total number of transactions and transaction amount (in Nov 2007) for SIC 56 by SCF code (first 3 digits of the zip code).

5. We need to segment the customers by customer spending behavior based upon

i) number of transactions,

ii) total dollar amount

iii) SIC penetration

iv) spending categories.

You need to develop 5 clusters (A, B, C, D, and E) with the following characteristics: 

• A totalSlCCount >6, countSIC54 >0, countSIC53 >0

• B totalSlCCount >= 5, totalVisits >1

• C totalVolume <500, totalVisits <=10

• D countSIC54 >0, countSIC55 >0

• E Else 
Your output needs to have Customer ID and Cluster only. (25 points) 

6. You may have heard about the address matching exercise. For example: A company has a customer database which includes all existing customers' information.

We would like to determine the number of existing customers by State from daily transaction records. i.e. the number of existing customers who made the purchases(s) recently from daily transactions and as result, we will be able to determine the number of repetitive customers.

You need to write a SQL that compares the addresses (TRANSACTION) to the address attributes of a reference dataset (EXISTING_CUSTOMER) to determine whether the first 20 characters of a particular address (from the TRANSACTION table) would be the same as an address of the existing customer address (from the EXISTING_CUSTOMER table).

You may also need to consider additional attributes which improve the accuracy of the address matching.