Category: Uncategorized

Overview of Foreign Key

StudentID	Name	Age
1	John	20
2	Sarah	22
3	Michael	21

CourseID	CourseName	StudentID
101	Math	1
102	Physics	2
103	Chemistry	1
104	Biology	3

In this example:

• In the “Students” table, “StudentID” is the primary key.
• In the “Courses” table, “CourseID” is the primary key, and “StudentID” is a foreign key referencing the “StudentID” column in the “Students” table.

This relationship indicates that a student can enroll in multiple courses, but each course is associated with only one student.

Hints

1. CREATE TABLE Employee_Statistics ( Add all the columns here, also create the FK constraint);
2. Use LEFT JOIN
3. INSERT INTO Employee_Statistics (names of columns)
   SELECT column names
   FROM Table1
   LEFT JOIN
   Table2 ON ….

1. Report the employee_id, the name of the employee, and all his job assignments from table HR_Job_History. Report the start date and job id of each of the employee’s jobs. Write the query in a total of 3 ways:

A. Using equijoins (the older syntax)

hr_employees w, hr_job_history l

where ….

2. Change the query of question 1 to be able to see ALL the employees, including those without previous job assignments. If an employee has no job assignments, then print ’01/01/1900′ and ‘-99’.

Use LEFT OUTER JOIN