JOIN and UNION Statements, Free Essay on Database Usage

a) What is the difference between a JOIN and UNION statement?

Is your time best spent reading someone else’s essay? Get a 100% original essay FROM A CERTIFIED WRITER!

Order now

Although both JOIN and UNION statements combine data that is given from one or more tables, their main difference is on how this data combination is done. A union statement is used to join many select queries and produces a single result set. Without the union statement, multiple result sets would be generated from multiple select queries. A join statement on the other hand is used to display data from two or more tables provided that there is similarity between them. In Joins, data is combined into new columns whereas the data is combined into new rows in unions. When joining two tables, the first table data will be shown in one column together with the column of second table in the row. This is what specifically takes place in a JOIN statement. On the other hand, when combining two tables in a UNION, the first table's data will be in one row set and that on second table will be in a separate set.

b) Examples of join and union statements

Consider the given table that stores teacher and subject information,

Table t1

Subject_id Subject name Teacher_id

1 Literature 2

2 Geography 1

3 Mathematics 3

Table t2

Teacher_id Teacher_name

2 Ian

3 Allan

Union statement: (select teacher_id from t1 where subject name='English') union (select teacher_id from t1 where subject_id='2');

The above query should yield the following result set:

Teacher_id

2

1

Join statement: select t1.subject_name, t2.teacher_name from t2 inner join t1 on t1.teacher_id=t2.teacher_id;

The above statement will yield the following results:

Subject name Teacher name

Literature Ian

Mathematics Allan

This article is a good demonstration of how a database and its information can be used in a business to impact other departments such as marketing.

a. Give 2 specific examples on how data from a database could be used to make decisions in a Human Resources department.

Database records may be significant in being able to determine employees who are performing and those who are not performing and thus may influence the decision by the human resource department on whether to extend or terminate a contract. This record may be the total amount of sales for a given employee over a given period of time and may be compared to the records of the employee's colleagues.

Database records may also be used to determine in which position an employee performs best and hence may influence the decision as to where to assign the employees. Using data from the database, the Human Resource department can increase productivity of the personnel by engaging them where their performance is high. For instance, a waiter may be able to serve more guests at the coffee shop than at the restaurant and thus the human resource department may be inclined to assign the waiter a permanent workstation at the coffee shop.

b) What type of information would they store in their database, give 2 examples of tables with 3 fields in each so that I know you understand the structure of a table and its fields.

They could store the employee details i.e. job description, name and the sales that the employee makes per shift.

Employee name Job description Employee_id

Allan waiter 1

Mike barman 2

Employee_id Shift_no sales

1 53789 13000

2 65876 15380