Instructor: David Schuff

Weekly Question #3: Complete by September 21, 2017

Leave your response as a comment on this post by the beginning of class on September 21, 2017. 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.

27 Responses to Weekly Question #3: Complete by September 21, 2017

  • If a company had a database filled with employee information and needed to perform a specific search, they would need to join tables. For example, if in one table consisted of hours worked, and another table consisted of the wage rate, the two tables would need to be joined to find out how much a particular employee would earn in a particular week.

  • A professor can use a database that have all of the students’ information and the results from recent exam to examine how well the students perform in that class. For example, if the professor wanted to see who got the highest and lowest marks, the professor can use the “Student” table and “Exam” table matched based on their common field to get the information from the database. The SQL workbench will give the results the name of the students who got the highest mark from the exam. The professor can also see the lowest marks.

  • If order information is in one table and product information is in another table, you can join the tables to find out what certain customers purchased. By joining the tables, you can tell how much a customer bought or how much they spent. A join helps you relate the two tables to one another.

  • If I wanted to know how many trips a customer took with uber for example in New Jersey, I would join, the customer table and the trips table (with location) to get the answer to my query. You join tables when you are trying to search for things that contain more than one entity, basically.

  • A situation where you could use join to retrieve information from a set of tables is at a shipping company. When a product comes to the company, it is given a product id, point of origin, invoice number and quantity, which all forms one table. Another table necessary to find where to ship the product number is a customer table which consists of the customer information, order details, and the invoice number. By joining the invoice number, the shipping company would know what product to send and where.

  • If we wanted to know how many customers ordered a specific product on Amazon, we would join one table that has customers and another table that has orders. Would use the “count” function to figure out how many of those products were ordered

  • I am currently undergoing treatment at Temple Hospital and I have to deal with my insurance provider because of it. I suppose whenever one of their representatives deals with my case, he or she will join tables to get information on it. I would assume they would search for my name, money spent so far on my treatment, and the location of my treatment. For example, they would look up something that would answer the question: how much have we spent on Eric Murphy in the past year, and what hospital has our money been going to? My insurance company would then choose to accept or deny my treatment with the information gathered from their database.

  • I think a way to use a join is with a product and the consumers. One provides the details of the product purchased, while the other (customer table) provides specific information about the customer; and ultimately, it’ll allow us to find how often this customer purchases said product and how many he or she purchases, etc.

  • For a telecommunications company, a marketing analyst may want to create a list of customers who use both premium TV and Cable services. To accomplish this, the customer table would be joined to both the tv_service table and the internet_service table.The query itself would address only customers who have premium for both services.

  • If courses were in one table and student details were in another table, then we would need to join the two tables to find out which students of which major took a particular course or a particular section of a course e.g. to answer the question “how many students majoring in MIS took the BA 2996 course in a particular semester” we need to join the tables Student and Course.

  • If there is information about students in one table and courses the students took in another table then we can join the two tables to find out how many students from a particular major took a particular course.

  • Thinking back to our first assignment, there are plenty of examples of joins that we could perform. For example, if I wanted to know how many trips a particular driver has handled, I could join the driver table with the trip table. This join would show me all the all the trips associated with each driver ID. We could also join the trip and customer tables in order to determine how many trips each customer has taken.

  • You could use join tables at a restaurant. If a customer orders drinks, food, and a gift card, a join table could be used to consolidate all the customer’s information. The unique key would be order # or check #. The join table would allow the customer and restaurant to see the order in one receipt even though all three items (drinks, food, and gift card) are from different departments.

  • A situation in which you could use a join to retrieve information from a set of multiple tables is if you are trying to find out how much one specific employee in a database gets paid. For example, you could have an Employee table, with EmployeeID as its PK, a Wage Rate table that also has EmployeeID as a PK, and an Hours Worked table with the actual hours that also uses EmployeeID as a PK. You could use a join to join all three tables by their PK in common to retrieve this information from the database.

  • If I was hired as a data analyst for a particular chain of bar/restaurants that wanted to know which days of the week were highest 24-hours for ordering a particular type of beer. This data retrieval would require the Order and Product(Beer) tables and the joining key would be the Order-Product table which has an additional “Date” & “quantity” fields. This join would enable the data analyst to better-inform management of which beers to order more inventory and when in the week to place these orders, as well as which beers management might want to pull from their beer lists.

  • Venmo is an app that allows you to pay and request money from your friends. Venmo could track transactions by creating a table for customers and a table for transactions. Venmo could then link the tables with certain attributes like amount and date. Venmo could see how much money total a customer has received on a certain day.

  • The Global Programs Department has to contact with many university partners in all over the world to create the partnership and allow students from both universities can exchange or study abroad. In this case, we have one table ‘partner’ which includes all the information for other universities, and one table ‘meeting’ includes all the meeting, information, message that we have with those partners. So when we ask the question who that partner is that and what the last conversation we had with them, we have to use a join to retrieve information from partner table and meeting table.

  • If we want to retrieve information about one specific student and the courses that they have taken over the semesters, we would have one table “student” which includes their name, dob, address, etc, and one table “courses” that includes all the courses that the university has. In this case, we can join the two tables together, to find out what are the courses that this student has already taken.

  • In high school I had a retail job at a sporting goods store. We had a reward system that gave customers points depending on how much they spent. This reward system might have included a customer table (first name, last name, customer ID) and a purchases table (amount, date, time). We can link these two tables to track a particular customer and their purchases to keep a record of their spending history and then reward points once they have spent the designated amount to receive reward points.

  • A situation in which you would have to join tables is if a University had students in one table and majors in another table. To find out how many students from Chester County, Pennsylvania are MIS majors, you would have to join tables.

  • Say Temple University wants to know how many students are currently taking MIS2502. In order to retrieve this information you would need two tables, one as “student” and the other one as “course”. You would also need attributes like “TuID” and “CourseNumber” in order to filter your data better. When you join these two tables, it will give you the amount of students that are taking MIS2502.

  • If you are coaching a minor league baseball team, you could perform a join on the tables “Player” and “Games”. You could determine which players attended a game on a particular date. This could help you keep track of your team’s attendance stats to be able to reward players with “perfect attendance” or for attending a particular game.

  • An example of a situation where you could use a join to retrieve information from a set of tables is if you wanted to know how many videos games Adam Wolf has bought at a store.
    In this scenario, you would need to join the order table and the customer table through the Customer_ID primary key. This allows for the access of the essential query information (the customer’s first name and last name of the customer) needed to count all of the customers ordered video games. Without joining the two tables, SQL could not retrieve that information.

  • I considered an online platform such as Youtube, which stores information about their Youtube partners who they pay, and also users who purchase their unique services such as Youtube Red. Youtube might want to examine how many of their partners also choose to purchase Youtube Red. To do so they may have to join two or more tables depending on the complexity of the database.

  • A music school that offers group classes for students will need to use a join to find out what group class a student may belong to if they have a database for the school. For example, a school may have a database where students are in one table (with attributes such as Student ID- Primary Key, First Name, and Instrument ID-Foreign Key)) and instruments in another table (with attributes such as Instrument ID – Primary Key, Classification, and Group Class) In order to create a join, the school must create a query to combine the student table and the instrument table through the Instrument ID attribute.

  • An example of a table join would occur if you were trying to determine which hotel rooms were booked by a particular customer. For this, you would need two tables. One table would include all the information about the customer. The second table would include all the information about the hotel room being booked. The one thing that both would have in common would be the Customer ID, and this is where the tables would join.

  • A situation where you can use join to get information from a set of tables would be for trying to determine customer loyalty from purchase history. If you wanted to pull the purchase history of a particular set of customer you would need the customer, order, product, and product order tables. Thus, you will be able to sort which customers purchased the most in a particular period.

  • A situation where you would need to join data tables to get information would be which new restaurants at temple were the most popular. To do this temple can link the restaurant table to the transactions table. From there Temple can see where students are spending their money and what they are buying.

  • A situation where I would possibly need to use a join and retrieve information from a set of tables would be if I wanted to know which of my customers bought a specific product or used a specific service, I would have customer information in one table and the product/service in another table and join the two to find what I am looking for.

  • Spotify, a music streaming service, can use information from their database to determine what age groups listen to certain types of music. For example, Spotify can join the tables “Customer Info” and “Music Genres” to determine if different age groups prefer different styles of music. This can also help Spotify make suggestions to their users on new music from certain genres.

  • A situation where you use a join to retrieve information from a set of tables would be looking up a employee in a department. For example, a company has different departments, each department have different employees, and each employee would have his/her own employee ID associated with the department ID. In order to look up an employee, tables such as department and employee will need to be join from to retrieve information of an employee in the department.

  • A scenario where a join could be used is if you like a specific author and are looking to read a new book by them. You would have book in one table with a primary key of Book_ID and info like the genre, length, etc. Author would be in another table with a primary key of Author_ID and containing info like first name and last name. You can join these two tables to create a Book-Genre table containing both primary keys. Then, you would be able to find a list of all the books written by the author you like.

  • The first situation that comes to mind is if Temple University wanted to know what students in a section of a course. For example, there may be a table called “MIS 2502” and another table called “Students”. From the MIS 2502 table, you can find the unique identifier of section_ID, and then in the student’s table you would have an element of what course sections the students were enrolled in, in this example their “section_ID”. This commonality would be the area you would join the tables.

  • Sites on the internet such as ticket resellers can use these to track their customers. For instance, how many times have I logged in and purchased a ticket to what venue with what artist? This allows them to have a deeper insight into they venues that i attend to target that more as well as the type of music I like to target in that genre.

  • One situation where I had to use a join to retrieve information from a database was during my internship two summers ago in the mortgage department of Firstrust. Specifically, I had to see the values of all the mortgages associated to an individual and their income level. Also, the values of the mortgages was in the mortgage table and a persons income level was in the customers table. Furthermore, the two tables where linked by the customers ID. So, I had to join the two tables to retrieve this information.

  • A situation where you could use a join to retrieve information is when a gym company needs to keep track of what customers are in a class they offer. If they are looking for customers in their yoga class who live in Norristown, you could join the customer table and class table. This way you could see who is registered for the class and where they are from.

  • If you worked in the ticketing office for the Eagles, you might want to figure out where a customer’s seats are for a specific game. To do this, you would have to join the customer table and ticket table to find out the section, row and seat number for the ticket for the customer.

  • A situation where you could use join to retrieve information from a set of tables would be to determine the age of which customers are buying a product. You would join the customer table, the age table, and the product table to see the age demographic of most of the customers buying that product. How a company advertises a product can depend on the age of the buyer, so knowing that information could increase sales.

  • A situation where a set of tables are joined to retrieve information can be in finding the number of students in a class section with a particular grade (take <90 for example). The table joins would include student and course – looking for a section ID and grade field within that range.

  • Let’s say you have a store with a loyalty rewards program. It sells groceries or whatever. In this store, customers may be able to use rewards, such as discounts, at the time of purchase if they had previously spent a certain amount of money at the store. To know whether or not a certain customer would qualify for this discount, a query would need to be made between a table tracking customers signed up to the rewards program and a table that tracks the past orders in the store. An individual would be able to join the tables and match the customer ID in the customer table with the customer ID assigned to orders made in the order table and find out the total amount of money that customer made at the store.

  • One situation where a company can use a join is if a bookstore wants to determine which book they sell is the most popular. In this situation, there would be separate tables for customers and books. These tables would need to be joined to determine the appropriate information.

  • One example in which you can use a join is an NBA team and its Player Efficiency Rating (PER). By using a table between the two, you can see who is the most effective on the field given the amount of minutes that are spent on the floor. Additionally, you can also see the correlation between a set of players and whether or not they are compatible on the floor together.

  • I actually moderate a site that makes active use of lots of database information, although it’s not exactly MySQL, I figure the example is still applicable. On a site, you have accounts, and you have chat posts, and each chat post has its own individual ID, while each user also has a user ID. You would need to join inside the database to see which chat posts were made by which users, by using the user ID as a foreign key inside of the post ID. With that, you can find out information such as when a user was both online and active inside the site chat, or how many posts they made in an hour.

  • An example where a join would be useful is inside a warehouse. Warehouses are large and in order to be efficient, you must know where to pull a product so it can be replenished. If you have an order table and a product table (location being an attribute of product), you can link the order table and product table required to find its geographical location in the warehouse.

  • If a company wanted to see from their current customer base what age range were spending the most money you may need to join tables. You could take the customer table and find the date of birth and then the order table to see the total cost of each order made and see if there was a correlation between the two you could better segment your marketing strategy.

  • In the National Hockey League, they use advanced metrics (Corsi) to determine a players efficiency in the league. Corsi would be established calculating shot attempt differential while at even strength play. In this case, you would join shots on goal, missed shots on goal, and blocked shot attempts at the opposing teams net in one table and join it with the same exact statistics but against your teams net to formulate a corsi rating.

  • One example of joining two tables could be for a website offering multiple discount codes on a purchase during a holiday weekend. Customers are in one table, and the discount codes are in the other. This way the website can access which customers used which discount codes for their purchases.

  • If Netflix wanted to determine how well different programs did in different regions of the world, a join could be used. If tables consisting of customer information and view count were joined, Netflix could gauge how well a particular show was performing by location.

  • I am a writing tutor at the Business Communications Center. A way for us to use a join is to have a table with clients and the time that they scheduled an appointment and another table with the tutor who is working at that time. If we joined these two tables, we could determine which tutor will have which client.

  • Joining tables can aid enterprise and entities make better decisions and organize data better. As the question poses, in order to find out which orders were put by customers in Pennsylvania, there is a plethora of entities or tables you can join with customers. For example, if the company grouped the drivers and deliveries, or transportation systems with the customers table, we could see which orders were put by whom. This, eventually, will ensure conglomerate of data that allows the company to better forecast and predict consumer behavior.

  • A situation where you could use a join to retrieve information could be from analyzing customers and products. For example, you can see which products are purchased from the customers. To achieve this information, you would join the product and customer tables. This data could be used to see what is the highest grossing product and what is the lowest grossing product. Also, it will show you which product the customers actually enjoy.

  • One situation where you could use a join to retrieve information from a set of tables would be if you wanted to find out which wide receivers scored the most touchdowns in the NFL for a specific season. Wide receivers would be in one table, touchdowns scored would be in another table, and also the season or the year would be in another table. You would need to join these tables to find out who scored the most touchdowns in which specific season.

  • One way that my job would use a join is to see what types of memberships a customer has. There are multiple types of memberships, some of which include a subscription. They are all tied to the customer using their Member ID which is a randomly assigned string of numbers when the customer signs up for rewards.

  • A situation where I could retrieve information from a set of tables would be to see which type of membership people have at Planet Fitness. Certain people can have the regular membership that’s $10/month or the special Black Card membership that is $21/month. By joining the customer table with the membership table, you can seek out how popular the Black Card deal is in contrast to the regular membership.

  • An example could be purchasing items on eBay or the seller’s information; you can see additional information of the seller or buyer, such as feed backs, purchase history, and etc. The join table are the feedback that are assigned with the user, or the buyer and their purchase.

  • A situation where a join would be involved is if the Eagles wanted to look at jersey sales to determine which player to put on a new billboard outside of The Linc. The Eagles could do this by looking at which player had the most jerseys sales. In this case, there would be a join between the player and jersey tables.

  • A situation in which you would join two tables would be in a wholesale distributor database, wherein one table you would have the restaurant’s in the US and on the other table, you would have the shipping orders. By joining these two tables you could figure out what shipping orders are being shipped to restaurants in Pa.

Leave a Reply

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

Where and when do we meet?
Alter Hall 232
12:30 - 1:50 Tuesdays and Thursdays
Office Hours
David Schuff (instructor):
2:00-3:00, Tuesdays and Thursdays
Speakman Hall 210E and email (see my site)

Lauren Soentgen (ITA):
1:00 - 2:00 Mondays
11:00-12:00 Fridays
Speakman Hall 210D (and see her site)