Write an equijoin query and inequality join by using the WHERE clause.

Learn the basic JOIN operation rules.

Write complex JOIN queries with more than two tables, and more than two columns.

Write OUTER JOIN queries.

Write SELF-JOIN queries

1. The manager in the human resources department needs a listing of department managers, their salaries, and their assigned parking spaces. The query must display the DepartmentNumber, DepartmentName, FirstName and LastName (concatenate the name columns into a single column), Salary and ParkingSpace column values. Name the columns in the result table as follows: Dept., Department, Manager, Salary, and Parking, respectively. Sort the result table by DepartmentNumber. Use the FROM clause to join the tables. Use COLUMN-FORMAT commands to product a listing that fits easily on one page.

2. The company's vice president for project management needs a listing of projects that are supervised by each department for projects located in Alton or Edwardsville. The result table needs to display the department name (DepartmentName), project number (ProjectNumber), project name (ProjectTitle), and project location (Location). Use the department and project tables and assign each output column an appropriate column name. Use the FROM clause to join the tables. Use COLUMN-FORMAT commands to product a listing that fits easily on one page.

3. The company's vice president for project management needs a listing of employees assigned to projects. The result table should list the LastName and FirstName column values (concatenated into one column) and their ProjectTitle and HoursWorked (from the projectAssignment table). The result table should only list employees assigned to a project. Use alias names for the table names. Give each column an appropriate column name.

4. Produce a query that will list all employee last names, employee gender, dependent names and dependent gender where the employee's have dependents of the opposite gender. Also list the dependent relationship. The columns needed in the result table are LastName, employee.Gender, dependent.Name, dependent.Gender, and RelationshipToEmployee. Use the employee and dependent tables. Use the FROM clause to join the tables. Use the column names and formats shown below. Sort the result table by LastName.

COLUMN "Employee" FORMAT A10;

COLUMN "Emp Gender" FORMAT A10;

COLUMN "Dependent" FORMAT A10;

COLUMN "Dep Gender" FORMAT A10;

COLUMN "Relationship" FORMAT A12;

5. Several physicians have requested that you write a query that displays the patient history for hospital patients who have the word surgery somewhere in the physicians comments. This will assist them in diagnosing medical problems. Your result table displays the PatientID and LastName columns from the patient table and the PhysicianComment column from the PatientNote table. Use the FROM clause to join the tables and use the default column headings based on COLUMN-FORMAT statements shown here.

COLUMN PatientID FORMAT A9;

COLUMN LastName FORMAT A10;

COLUMN PhysicianComment FORMAT A55;

6. Management is expecting to start several new projects in the near future. A list of employees who are not currently assigned to a project is needed. This will provide management with a list of employees who are potentially available to be assigned to projects. The result table should list the LastName and FirstName column values (concatenated into one column) from the employee table. Sort the output by LastName and FirstName. Use the FROM clause to join the tables.

7. Produce a listing of employees that are supervised by the manager of department 3. The result table should include the manager's last name, department name, and employee's last name name each column "Supervisor," "Department," and "Employee Supervised," respectively. You must determine which tables and columns are required to produce the result table.

8. Produce a listing of number of employees assigned to each project. The result table should list the ProjectTitle and number of employees. Give each column an appropriate column name.

9. Rewrite the query for question 8 to produce a listing of number of employees assigned to each project in each department. The result table should list the DepartmentName, ProjectTitle and number of employees. Give each column an appropriate column name. Use either the WHERE or the FROM clause to join the tables.

10. Produce a listing of the number of patients treated and the total service charges ChargeAmount) received by each doctor (.M.D.). The result table should have three columns: the LastName and FirstName concatenated into one column, number of patients, and the total service charges. Give each column an appropriate column name. Use either the WHERE or the FROM clause to join the tables. You must determine on your own which tables and columns are required to produce the result table.

*Create a single table and join table views.

1. The payroll department needs to regularly access information about employee salary information. The DBA of the company has directed you to create a view based on the employee table named vwSalary. This view should include the employee identifying number, employee last and first names (LastName and FirstName), and the salary for each employee . Name the columns of the view as follows: EmpID, EmpLastName, EmpFirstName, and EmpSalary. Write the SQL code needed to create this view. Write a SELECT statement to display rows from the view for employees with salaries at or above $30,000. Format all output appropriately.

2. Replace the view named vwSalary created in the previous question with a new view (same name) that also includes the DepartmentNumber column. Name this column Department in the new view. Write a SELECT statement to display rows from the view where employees are in department 8 and their salary is equal to or above $10,000. Format all output appropriately.

3. The Company's senior project manager needs to access information about departments that manage projects for a specific set of projects, namely those located in either Maryville or Alton. Create a view named vwDepartmentProjects that includes the DepartmentNumber and DepartmentName columns from the department table and the ProjectTitle and Location columns from the project table. The view should only reference rows for projects that are located in either Maryville or Alton. The columns in the view should be named DeptNo, Department, Project, and Location, respectively. Write a SELECT statement to display all of the rows that are accessible through the view. Format the output columns of the SELECT statement as A25 for Project and Department, and A15 for Location.

4. Create a materialized view named vwProjectHours that will be used by the senior project manager to access information about work hours that have been reported for different projects. The view should join the project and projectAssignment tables. The view should show each project's title as well as the average hours worked on each project. Name the columns Project and AverageHours in the view. The rows in the view should be grouped by the project name. Write a SELECT statement to display projects where the average hours is equal to or greater than 15. (2 Points)

Relationships MedicineCode EmployeelD SpecieltyiD EmployeeSpeci StateCode Employee Emplayeeln Project ProjectNumbe ProjectTitle BedClassificati. ServiceCategory DateTreated CategorylD EmployeeD CategorylD ChargeAmount Relationships MedicineCode EmployeelD SpecieltyiD EmployeeSpeci StateCode Employee Emplayeeln Project ProjectNumbe ProjectTitle BedClassificati. ServiceCategory DateTreated CategorylD EmployeeD CategorylD ChargeAmount