Weekly Question #3: Complete by September 20, 2018

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.

Reader Interactions

Comments

There are many situations where someone could use a join to retrieve information from multiple tables. One example could be when one table contains information about a product and one table contains information on the product’s department. The primary key “ProductID” in the product table could become a foreign key in the department table, allowing the two tables to be able to connect with each other. A join could be used in this situation to see all the information in one big table. Therefore, you can look up what departments have certain products.

At my job doing research at Temple we must keep a record of all students who participate in research, so that we can send it to their professors for credit. We have a table for students who come in, a table for the date they come in, and a table for the class they are getting credit for. If I use a join, I can get all of this information at once and easily send it to their professor.

Suppose a database has two tables to represent an airline reservation system- ‘ticket purchase’ and ‘passenger’. If these two tables are joined we can find out which passengers bought airline tickets for destination to Cancun, Mexico on Thursday, September 13. 2018. We have the ability to ask this type of question that typically requires two tables to ask this question. A single table can make simple queries. From a single table, we can only ask, “What was the least expensive ticket sold?”

During my internship at a large insurance carrier, I used joins in SQL to figure out payments coming from and going to policy holders. We had payment amounts and dates in one table and customer information in a separate table. We joined the tables to ensure that payments were not going to a deceased customer and also that the company was not expecting additional payments from a deceased customer.

Over the summer, I held a student worker position at the MIS department of Temple University. In my main role, I collected and inputted data to report job status of alumni. In this situation, I could use join to combine graduation date with job status (yes/no) to find the percentage of students who graduated in January 2018 that found a full-time position.

I was fortunate enough to learn a little bit of SQL at my internship this past summer. I interned at a logistics company and my role was helping out the import department. The manager I was working with would help me run queries of shipments have been cleared through U.S Customs and if so have they been transported right to the inland destination.

A good time when you can use a join is when you have a rewards member of a hotel staying at a particular hotel. You could join the table of who is a member and what level they are at and a table that shows where guests are staying. I know a lot of companies offer Marriott Rewards for their traveling employees and I’m sure the hotels must have separate tables to hold the information of different level guests. Joins can be used in many different situations, though, since most databases contain more than one table.

For sponsorships in sports stadiums, for example, there can be a table that lists the sponsorships and a table that has information about the sponsors. A table join can be used to match up the sponsorship ID and the sponsor to get information about which company is being placed where within the stadium.

In one situation we were tasked to find SKU’s that could be sourced for a cheaper price from the wholesaler we recently acquired. We first wanted to see whether or not that SKU sold at a high volume to determine whether it was worth it or not to source the product from another supplier. We used two tables, product information and sales by product. We were able to forecast the total profit for each product by joining the sales data and the cost that was associated on the product information tab using the SKU as the unique identifier.

As an e-board member for Temple’s PRSSA chapter, I assist with recruitment of new members and their registration. We have tables with information about what class a student was in when they showed interest in PRSSA by signing up for our email list to come to meetings. We also have lists that are students who paid dues to join. In order to find out which class visits were the most effective in recruiting dues paying members, we can join the tables from the dues paying members and classroom interest email sign ups and see which members signed up in a particular class.

I had a project at one of my internships to find out how clients responded since the launch of the company’s new mobile application. The application is designed for equities trading. In order to measure the response, I created a list of all client details, comments, suggestions and ratings on various social media platforms and maintained two separate excel sheets of both the applications.
If a database comprising the same data were to be created using SQl, the join function could be used for many purposes.
For my task, it would be relevant for me to use SQL to provide my mentor with a detailed comparison of the feedback on multiple platforms of both applications and design a presentation that would help the software development team understand better what the customers liked and disliked. This information will help them plan how they could further enhance the performance of the app in future.

An example can be creating a join between a supplier table and a product table for an industry. We have supplier ID and product ID as primary keys. Then we join these two table which will give us supplier-product ID to tell us which products was provided by the specific suppliers correctly.

While working at an auto-body and collision service repair shop, we used joins to plot out payments from customers and their respective insurance companies while also figuring our own payments to part, paint…etc suppliers. We separated the data by date, time and amount of payments received from customers/insurance companies as well as payments to suppliers while the other table contained customer/insurance information. We combined tables to make sure that we had properly received payment from customers before returning their vehicle as well as to make sure we don’t pay suppliers for products we did not order or were returned due to defections.

There are multiple situations where you could use a join to retrieve information from a set of tables. One of the examples could be Amtrak train reservation (ticket) and a passenger. The uniqueID would be PassengerID and the ReservationID. So this way in these two table will give us a passenger-reservationID that will inform us about their relationship.

I work at a car dealership, I could use a join to figure out how many customers with a Subaru Legacy had a recall performed. I would have a Customer table, Car model table , and Service table that would all need a join to pull the information.

For a ride-sharing company, a join can be used to combine the Driver table and Rating table. You can join these tables to figure out the first and last name of drivers who have received an average rating above 4 stars on a 1-5 scale. This information could help to determine which drivers are satisfying customers.

There are so many situations where you could use a join to retrieve information from a set of tables. An example that comes to mind would be if you had two tables, one named college and another named student with an attribute being GPA. If you made a join between these two tables, you would be able to match up which students go to which college and see what the average GPA of students at a specific college would be. This could also allow a person to be able to tell where students at a specific college are from originally.

In this summer, my internship at Youth Build Philly school. I used joins in SQL to figure out which students are eligible for school and which are not. We held the information in the separate tables such as their personal information and address in separate tables and age and residence in separate in tables. We joined the tables to make sure all the students those are accepted they are eligible for school on the base of residence and age.

During my internship, we had many tables for each client. Their personal and financial information and the types of services they needed or was already provided. We could have used joins to create a more complete profile and find clients easier, especially when many of them had similar or the same name. It would have been more efficient and effective in assisting the clients.

Over the summer I worked for a law firm Phelan LLC as a data entry specialist. My job was to type information about people court cases that took longer than anticipated. I had a table with peoples names and another table with the type of case. With this information I was able to figure out which cases took longer and also why. With all the specifics from Judge names to people just missing court dates.

An example of when multiple “tables” could be utilized is when I prep a gig for one of my DJ/MC events. Each venue would have it’s own categorization based on location, event type, and host of the said event. Each event has it’s own setup, package deals, pricing, and staff working it. Each event also has different musical/speech prep depending on what the function is for – having different requests, cue points, guests giving toasts or playing games. Lastly, each client is bound to a contract which lists a mandatory deposit, payment date, specifications for the party, special requests, insurance liability clauses, and secures the DJ/MC to show on the date of the event. All of these things are constantly intermingling and crossing paths throughout the planning process, so having an SQL database to help sort the information would help greatly.

Car companies can use join tables. For example, one table can be number of vehicles sold joined with another table listing the corresponding retailer and zone. These tables will show the correlating inventory and sales to individual retailers.

I had a project in one of my previous classes where my team made an Employee database for a Car Repair Shop located outside of Philadelphia. We had an employee table and an order table to start off the database. Throughout the project, we had an instance where we needed to use a join to find which employees placed orders for a certain car part for their customers vehicles.

The company I work for utilizes a database for storing & looking up customer information & purchases. Each invoice is associated with a unique “SalesOrder# for the sole purpose of retrieving bits of information. I would use join to combine customer tables along with accessories purchased to create weekly goal/metric tables. My company uses joins to ensure that each sales market achieves their sold accessory metrics.

I am a director in BHSA and we use tables to keep track of both our meetings and our members. Using a join, we could easily find out which meetings were attended by members based on their major. By joining the Meeting table and the Member table, we could find out which majors are more interested in our meetings, and tailor our marketing strategies to people in majors which have low attendance to our meetings.

We use a join to retrieve information from a set of tables many times in our in-class exercise #5. One of the examples is that when we try to figure out how many movies are in English, we need to join the film table with the language table. Since the language_id is an attribute for both of the tables, which makes the two tables connect with each other, we can count the movies whose language name is English. The question that requires us to find out which films did Bob Fawcett star in also use a join, too.

There are many applicable examples for using a join method. One that strikes my mind is within a restaurant. If we wanted to find all of the orders by one customer, you could join a customer table and order table. This way there can be one key for the two tables combined.

Joins are very useful tools when working with tables. They help you collect data from multiple locations and compile the data into usable information. An example where you might use this would be buying books from Temple’s book store. You could have a table for author names, book titles, and prices. By joining author.booktitles, and booktitle.prices and pulling from all 5 tables, you can find out what authors wrote what books and how much each book cost. This will be very useful information when a student is trying to verify a book is the right one for a course and trying to price-compare to Amazon.

There are many instances where we could join table to find information. Supposed there is a schema about student. The first table, student contain student ID and major and classes. The classes table contain class ID, type of class. If we want to know, for example, how many Accounting student take MIS courses, we will need to join this two table.

At Dunkin Donuts you have a table of people who buy just coffee, and then people who buy donuts,
And then a join to see how many people buy a donut with their coffee to see the correlation of purchasing both and with this you can market/advertise products a certain way.

A join is often needed to retrieve information as it is common that data is connected through multiple tables. An example where you might need a join is at a doctors office when you want to connect a patient with an appointment in order to get their medical history in order to know what medicine they are currently on.

I worked as a sales representative for a wireless provider. Lots of different data would be stored in different tables. There would be a table for the customers and their information. Then there was a table for the different type of wireless plans that the company provided. If a customer wanted to change service or plan. We had to combine the two different tables to find out what other plans are suitable for them while finding out which plan they currently have. Same thing worked with additional plans they had an example. international calling, phone protection, hotspot.

There’s many situations that a join could be used to retrieve information, such as in online shopping. There’s product in one table and order in another. To associate the products that’s in each table, a order-product table should connect the two using order ID and product ID. Using this table, we are able to find out which product is more popular and adjust the inventory based on this information.

At my internship this summer I kept track of alumni of the law firm I was working at. Using SQL joins would have been helpful to me if I had tables for Location which shows what office the Lawyer worked in and Practice, which shows what the lawyer specialized in. By joining these tables, I could determine what locations specialize in certain areas of law.

At Redbull each of our missions that we plan goes into a database. Tables that are included in this database are vehicles, employee, product, customer, etc. You would use a join to combine tables when searching for specific information about a mission. For example, if you wanted to find out how many of each can were sampled at a specific location or time during the mission. You could also find out attributes of the customers were sampled such as how many were male vs. female, their age, and whether or not they were a new, infrequent, or regular user of the product.

One example of using a join to retrieve info from a set of tables would be when you are taking classes. One table would be the class information needed for the class like TUID, both first and last name, pre req’s. The next table would include the sections of that course & the join would track each person to the specific course.

When I help my sister to count about people who ordered candies or chocolate for their festival gifts during this summer for her organization, it should join different tables together. There are several tables such as employee, gifts, and shipping method. We need to determine which gift each employee wants to order and ship to their address by which shipping method.

An instance where you can use join would be customer-bank account relationship. The reason why would be when you see customer ID and AccountID. This would require a join, because, there are many different types of a bank account. For example, there is a savings account, checkings account, or a personal account, trust fund accounts and so on. This would require different tables to come and join them, to find out whether or not they are working well together. So a customer can be associated with multiple variables, as in their accounts.

When I worked for a construction company, we had a huge Microsoft access database that functioned as a set of tables for material intake. We used this database to track the materials we needed in order to complete the project. Everyday, we had to pull the information from the tables of what was ordered, what needed to be ordered, what was delivered, and what we were still waiting on. These tables were crucial in telling us which projects were ready to be started and which needed to be put on hold until further notice.

There are many reasons to use a join within SQL. One example that comes to mind is actually from the in class exercise from Tuesday’s class. The question asks how many movies in the database are in english. This requires a join from the Film table to the Language table. This join would show which language name is associated with which film, and more specifically which ones are strictly in english.

This summer I worked at a market research company that specializes in collecting its data through consumer focus groups. The company has tables of people on their panel as well another table consisting of upcoming focus groups. I could have used a join in order to determine who could be qualified participants for certain studies.

There are numerous examples of where a join could be used on a set of tables. Netflix is a company that tracks tons of data and processes it in a way that allows them to deliver relevant content to paying members. As a result, one table might represent the “show” and another table would be labeled as “subscriber.” The join might be used to obtain information on which shows were watched by a certain customer in a specific geographical area. If Netflix was conducting a study on the impact the surrounding geography would have on viewership of certain shows, this join would enable them to differentiate these nuances.

For the past few months, I have been working at a car wash center as a front desk administrator. As my main responsibility, I organized appointment slots and assisted payments for the customers. In this particular occupation circumstance, I could utilize join to combine and compare all the past customers with their specific credit card preferences. This observation can generate a precise percentage of which type of credit card was utilized/preferred the most to the least.

Working at my job over the summer, a join can be used when the boss was figuring out what to put on the menu. A supplier table and a menu table would be used as a join in this situation. The primary key in the supplier table would be supplier ID and the menu table primary key would be date of food. The boss would need to join these together to find out if the supplier that he uses is bringing the food that is on the menu and will be ready to be served.

An example of when a join could be used can occur in a large company with many different departments and a large number of employees. A join can be created between departments and employees to show which specific employees work in a certain department,

More often than not, data retrieval requires tables to be joined. For example , loyalty programs must join tables to retrieve data from their data bases. A loyalty member will likely be under the data base in a “member” table, featuring their first name, last name, member ID etc. Multiple tables contain information about the member: Purchases, Rewards, Favorites etc. If you want to see a purchase that a specific loyalty member ie: Beth Gates, made on a specific date you would need data from the member table and the purchase table, and they would need joined.

During my internship, I was responsible for transferring data from the company’s ERP into an excel sheet through “Jet Reports”. In these reports, I would make sure the our vendor’s description matched with their correct company, address, and products they either sold or requested from us.

The using join to retrieve information from a set of tables is very common in company’s HR department. when I had a HR internship last summer, I learn a lot of how join the employee information table with their given ID, by type in the ID you will get employee’s information, like name, email, phone, department and etc. Furthermore, you can join monthly timesheet with different employee ID. After join all these tables together, it becomes easily for you to find the information you want.

At a previous internship for an accounting firm, one of my duties included keeping track of a database. An example of a join in that scenario would be if I used a join to combine the list of clients I was currently working on with a table describing the services they needed done. I would then take the necessary files to the people in the office who would finish the task that way I could finish off the log.

An example of a join would be if American Express was trying to pull data on the level of members their customers are. For example they would use a join to pull information from the customer’s account such as customer id and name, then also pull what kind of card that person has.

A join can be used on this platform to link each student and their respective comment in the database. If we have two different tables; students and comments respectively. Let’s say student has attributes; first name, last name, and TU ID number, a join can be used to connect the table student to comments.

A Join clause in SQL language basically retrieves data from two separate tables and provides the user with the information that might not be that easily available from one table. To better understand the concept , below are two examples illustrating the Join clause and how it helps an user retrieve the data that is needed easily. A nurse in a hospital would be able to retrieve data to match a patient with its blood type by running the query and matching the requirement with the hospital blood bank. Patients information would be stored in one table and the nurse is able to match his/her blood type with the hospital blood bank inventory and quickly recognize if it needs to reach out to the outside blood bank. Another example of the Join clause could be seen in the hospitality industry. I currently work in the hospitality industry and store guest data in multiple tables. Guest information would be stored in one table whereas the request they make during their stay in the hotel is stored in another table. We are easily able to retrieve data and recognize that the weekend clientele require a little bit more attention as they tend to be families and groups whereas weekday clientele are usually business transients and require less than the weekend clientele. Weekend clientele required a little bit more attention as request for more amenities as the number of people staying in the room is larger.

I utilize joins daily at my internship. It’s important to utilize a left join to identify the full data set and understand not only what company has been catalogued but by who (separate table) and when (separate table). Without using a join I would be unable to identify which companies need to be updated.