Data Analytics – Section 1

Weekly Question

I am really enjoying reading all of your responses to the Weekly Questions!  Keep up the good work!
Leave your response as a comment on this post by the beginning of class on February 8, 2016. Remember, it only needs to be three or four sentences. For these weekly questions, I’m mainly interested in your opinions, not so much particular “facts” from the class!
If you sign in using your AccessNet ID and password you won’t have to fill in the name, email and captcha fields when you leave your comment.
Answer this question:
Describe a situation where you could use a join to retrieve information from a set of tables. For example, if you have orders in one table and customers in another table, you’d need to join those tables to figure out which orders were placed by the customers who lived in Pennsylvania.

45 Responses to Weekly Question

  • An example of joint table is using a database that records the amount of students who are CSPDed (CSPD Table) and how many people where offered a job offer in a period of time (Offered Jobs Table). Together, you can see how many how many students received job offering verses the total amount of CSPDed students to come up with a job offering percentage.

  • At a supermarket there can be a customer table and a purchase order table. You can combine the two to see how many times a customer bought a certain item from the supermarket. From there the supermarket could possibly send emails with coupon codes to customers advertising a discount price that they can use to encourage the customer to buy a specific product again.

  • A clothing store can use a table join to figure out how often customers purchase items in store vs. online. They will need to join the customer ID from the customer table to the purchase table and compare to the method of purchase. Also, you can see which products are bought mainly online and which are bought mostly in store.

  • To determine which actor starred in the movie that has been rented the most, you would have to join the actor table with the film table. From the actor table, you will query first and last name; from rental table, you will need to order the rental number in descending order and pull up the title of the movie that corresponds with that highest number .

  • You could use a join to combine an “employee” table and a “log time” table. The employee table would hold information like employee id, name, personal address, etc. The log time table would record each shift and an employee id for each one. For example, if you needed information about a particular employee who worked a particular shift, a join between these two tables would be a good way to find that.

  • A hospital can use a join table to determine where their home care nurses need to go to visit patients. There will be a nurses table and patients table. The nurses table will hold the nurses employee ID, name, etc. The patient table will hold their patient ID, name, address, phone number, reason for visit, etc. Together the hospital can use this to find what particular nurse was assigned to a patient and how many times that nurse visited that patient.

  • Using a database in a pizza store you can link your customer table to the order table. This can help you find who your frequent customers are and what they are frequently ordering. Analyzing this can help you find what products you should stock your inventory with and which orders are creating the greatest revenue. This will help you make sure you always have the ingredients that you need and that they are always fresh.

  • If you want to find out about the average salary of employees working in certain departments, you can join an “employee” table and a “department” table. The employee table would hold information such as employee_id, department, salary, social security, and position. The department table would hold information such as department_id, employees, and office. You would use department_id and employee_id as foreign keys to join the tables.

  • If you were using a realty database and you wanted to look up customers who purchased homes in a certain area you could use a join between a customer table and a table of purchased houses. The customer table would hold data pertaining to the customer such as income, customerID, FirstName, LastName, and the purchased housing table would include information pertaining to the houses such as the neighborhood, the house prices, previously sold houses, etc.

  • An example of using a joint table in a database would be linking customer table to your order table in a computer repair shop. The customer table would include customer id, first name, and last name. The order table would include issue, computer model, and computer id. This would be helpful because it would let you know which computers model have frequent problems.

  • If you were working for a financial services company and you were trying to find out who owns a certain stock, you could create a table that held customer information and a table that held stock information.Since many customers can own one or many stocks and many stocks can be bought by one or many customers, there would be a table in between called customer.stocks. This would be a great tool if you were trying to convince a customer to invest with you because you can show them how well certain portfolios have done. This would also be useful if a customer needed to find out how their portfolio was doing and you didn’t have their file on hand.

  • A retail store would have to use a joined table in order to figure out likeliness out someone being a repeat customer. The tables you would need would be the customer, product, and order tables. The customer table would include categories specific to the customer like name, email, date of birth, and a primary key of customer ID. Each table must possess a primary key so no two attributes in an entity are the same. The two join tables would be product_order and customer_order with each associating the customer with either a specific product purchased or an online order. This is useful because it tells a company the likeliness of customer purchasing again as well as the product that they purchased.

  • A retail store would have to use a joined table in order to figure out likeliness out someone being a repeat customer. The tables you would need would be the customer, product, and order tables. Each table must possess a primary key so no two attributes in an entity are the same. The two join tables would be product_order and customer_order with each associating the customer with either a specific product purchased or an online order. This is useful because it tells a company the likeliness of customer purchasing again as well as the product that they purchased.

  • A situation where a join between tables would be necessary is if you run a query that would require data being pulled from two or more tables. If a DBA wanted to figure out the amount of times a customer orders a certain product, they would ultimately have to create a join between an order, product, and customer table in order to retrieve that specific information. From those tables, the joins would be made on the primary and foreign keys that the tables posses.

  • An example of when you would need to use a joint between two entities would be if you were a company, such as Amazon, and wanted to figure out which customers frequently order certain products, and then utilizing that information to be able to use digital marketing to advertise more by personal customer preference. You could do this by making a ‘Customer’ table with a unique identifier, or primary key, of customer_id. Then you could make another entity such as a ‘Product’ table, containing a unique product_id. Finally you could then use two joints that would connect them to a relationship which in this case would be an ‘Order’ table and include not only order_id, but also customer_id and product_id to link them all together by unique identifiers.

  • A situation where you could use a join to retrieve information from a set of tables would be trying to acquire the amount of Temple Students that have garage parking passes for the semester. The tables that would be included are “student” and “parking_services”. The student table would include “first_name” , “last_name” and “tu_id” (with TUID being the primary key & unique identifier). The “parking_services” table would include the garage_id, garage_name, and the unique identifier from the student table, ‘tu_id” in order to link both of the tables to each other.

  • I have a student subscription for the Philadelphia Orchestra. When registered users want to purchase tickets online, this is what I imagine they have set up: A “Customer” table, a “Ticket Orders” table, and a “Performance” table. To ensure that there are tickets available for a particular show, the “Ticket Orders” table has to be joined with the “Performance” table. In order to make sure that customers’ subscription package allow them to buy tickets for the particular performance, the “Customer” table has to be joined with the “Performance” table and ultimately, the “Ticket Orders” table when placing the order.

  • Amazon is probably a great example to use when joining tables and mining customer data.

    For example, The tables of previously purchased items and the tables of customers and their purchase history can be combined to find the items that go into the “suggested” or “just for you” category.

    As well all know, Amazon is the king of Ads and suggestions.

  • An instance where a join would be used is when a company who hosts servers needs to see where the majority of their customers are located. If they have a heavy demand and large customer base in a state(s) on the East Coast, they would pull data from the Customer table and Product table to see who purchased the more demanding server space and where. They would use this in order to install servers in the locations that they have the most customers. For instance if the majority of their customers live in Maryland, they would use a join to pull that data and utilize the demanding location.

  • I actually am in charge of shipping and handling at my current job, and we get orders from across the country. If a customer was to call about if there order was shipped we could use a join to retrieve information from a customer. We could possibly encounter the following categories customer Id, product, product id number, and location to single out the unique customer.

  • The State Farm could use table joint to figure out which underwriter wrote the most of auto policies for the clients who are at age between 19 to 21. The client’s age and policy type information would be under client table, and underwriter’s name would be under underwriter table. These two tables join together through the client- underwriter table.

  • An example of the use of a join to receive information for a set of tables would be when Temple join the tables of Students with a table of classes to figure out which classes students are taken and when they are taking them.

  • During my internship, a part of my responsibility was to get information about 2 different stocks and show the data side by side. The company I worked for didn’t have the knowledge and access to SQL so we had to extract data for the 2 stocks separately and then join them manually on Microsoft word or excel, which would’ve been way more convenient if we had the join feature shown in SQL.

  • An example of joining tables together to retrieve more information in an entity relationship diagram is when we created a schema for the housing authority. In order to find out which unit a certain household lived in, we had to combine the the unit and household entities into a separate entity which we named unit-household. Then we were able to retrieve the move in date, move out date, and unit number that the household lived in.

  • A situation where a join table has to be used is when you want to find out which client has had the most insurance claims. This is something I have to do everyday. To find this, you query for claims and client ID so the client table and the claims table has to be joined.

  • Describe a situation where you could use a join to retrieve information from a set of tables
    When a customer wants to make an order for an incoming movie ticket in nearest theater, so there should have many queries. Very first step is finding out which theaters would have this movie on schedule, filter the position where customer lives in and gets the nearest theater. So it needs “Customer” table-address, “Theater” table-location and “The incoming movie” table-schedule. Then based on “theater” table-ticket price, “The incoming movie” table-schedule and “Customer” table-special condition, to find out the price for the customer(if this customer eligible for discount policy of theater).

  • A company like Netflix could use a join to combine a customer table with a genres table to see which genres are watched most often. This could help them to decide which genres are most popular and help make decisions not only of what to upload, but also what kind of shows to create as a Netflix series.

  • Another example of how you could use these joins to present and analyze data would be in many school districts. For many districts there are data tables related to school-/student-level academic performance (think… test scores, attendance metrics, English-as-a-Learning-Language (ELL) students, etc.); however, there are also data tables related to school-/student-level demographics (think… racial breakdowns, income metrics, government assistance reliance, etc.). By joining these two types of data tables, you could potentially look at these academically-based metrics and these socially-based metrics side-by-side to better understand if there could be a correlation between certain metrics in these tables. There could be some very fascinating insights here!

  • A movie theater could join their customer table with their movie table, to find out a variety of things about either subject; they could discern whether a certain customer only watches one genre of movies, or predict ticket sales for the sequel of a movie to decide how long they should run it for. Additionally, they could find out what shoe times that the customer prefers, and how many customers are repeat visitors to their theater. If the number is low, they might realize they need to change their strategy; if it is high, it might be a good way to grasp how well they are doing.

  • A situation where we could use a join to retrieve information from a set of tables vary. From my experience as working a manager as at a limousine company I could see how we use this technique at a daily basis. In a limousine data base there is a lot of data. All of that data is categorized in special tables and fields, and it is retrieved when we pull up a trip ID. By searching the trip ID I can get a full page about the First, last name of the guest, the method they used for payment. What kind of vehicle they requested, their home address, and a lot more of information. So by entering one piece of data it grabs other pieces of data from various tables. I actually never thought about how the system we used was filled with tables and categories until I took this class.

  • A good example would be getting your driver license. By joining the driver’s table with the road exam table to try to find out who passed or who failed the exam. So to begin with the driver table, which would contain a unique identifier driver_ID, then F_name, L_name, date of birth. On the other hand, the road exam table unique identifier would be RoadExam_ID, then instructor name, location, sections. To find out if how many drivers have passed their road exams we could join both tables and it will give us the Pass/Fail information, time/date of the exam.

  • You can use a join table to find out how many employees makes salaries $50,000 and above. To do this one would have to join the employee table with the salary table. You can also narrow these options by writing a query that states salary =$50,000>.

  • A a country club you could join a member table with a purchase order table. this could show you how many of each of your products sold to what members to assure you are okay on inventory of certain popular products. This would also show you how to create future menus that will be popular with the membership as a whole.

  • One example of a joining to retrieve information from a set of tables would be Temple using a financial aid table and student table to find out student’s GPA’s compared to amount of academic scholarship. This would be useful for seeing how much being awarded scholarship correlates with doing well in school.

  • An example of a possible joining of tables could deal with the fan base at the Super Bowl. One table could be called fans and who they are rooting for and another could be called favorite teams. You could find out which fans are rooting for one of the two teams playing in the Super Bowl, and those who have a different favorite team and are just there for the experience.

  • You could use a join to get information about how many customers have a certain size feet and which shoes fit them best. For example, if a website sells only shoes and they have the sizes they have in stock in one database and the size of the customer’s foot in another, to recommend shoes that will fit the customer best they would have to join the databases.

  • A situation where you could use a joint to retrieve information from a set of tables would be to look up customers who bought specific cars in an area. You could use a joint table to join a customer table and cars. The customer table would include all of the data pertaining the the customer such as where they live and their name, and the car table would include everything about the car and where it was purchased. Joining these two tables together would give you everything you need to know about what customers have certain cars in an area.

  • An example of joined tables would be to look up the most popular item in the store. You can join the costumers table and the items sold table. This can be useful in ordering supplies, and also in marketing strategies.

  • An example where you could use a table join to retrieve information is if you wanted to obtain information of a firm. You could have an employee table that displays an employee’s information such as DOB, age, address, etc. and in a compensation table you would have salary, benefits, bonuses and other forms of compensation. If you were interested in correlating age with any form of compensation I believe this is where that table join would be useful.

  • If you had a database on accounts receivables. You could create a joint table to see which customers still had payments to make on their receipts.

  • A good example is at retail store. These stores can look at several tables to see different areas, such as, which customers spend more than a certain amount. You can from there push them special deals and track to see if that increases their spending or decreases their spending.

  • If you sell servers to large corporations you could keep track of inventory and refresh cycles. You could link customers with purchase dates and setup alerts when it time to refresh their inventory. You could also link purchase date to service agreements.

  • A basic example of a database joining tables could be an IT company needing to see which customers had a tech come in and perform a specific task. For example they could join a “customer” table as well as a “task table” and “date” table to see exactly what task, say, HDD replacement, on which date and all of the customer details. Joining tables in a database is probably the most important feature, without it a lot of the single columned data would be useless.

  • You could use the JOIN syntax when analyzing students’ attendance at an event. Join a table containing student data, and a table containing the event checkins, using the primary key of Student ID to pair student information to attendance status.

  • You can use a join if you are doing analytics for a company selling a product. For example, if you worked for an automobile manufacture you could join customers and which vehicles they purchased. Doing this join would allow you to see what types of vehicles sold in which states.

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Comments
Subscribe to Blog via Email

Enter your email address to subscribe to this site and receive notifications of new posts by email.

Join 25 other subscribers