Question

In: Computer Science

SQL 9. Link the following two tables: HumanResources.Employee and Sales.SalesPerson then display Employee PK, job title,...

SQL

9. Link the following two tables: HumanResources.Employee and Sales.SalesPerson then display Employee PK, job title, Date of birth, Gender, Sales quotas, Commission percent and bonus. Use an outer join to display all the employee whether they are in sales or not. Make sure to sort by bonus desc. Explain why some of the field from the Sales.SalesPerson table are null.

Solutions

Expert Solution

Here we have used left outer join to join the two tables with the help of a common key which is not specified but I am assuming employee_pk and sales_id to be same.
If it is different then you can changed accordingly or you can comment I will change and edit the code for you.

The table in joint using left join which is same as left outer join and required columns are shown and it is sorted by bonus in descending order.

here we have used left outer join because in the question it is ask to display all the employee whether they are in sales or not so here we have to use left outer join.

Some of the fields from sales.salesperson table are null because no matching row in the   sales.salesperson table is found in humanresources.employee table so some fields from the sales.salesperson table are null

The SQL code

SELECT Employee PK,
       job title,
       Date of birth,
       Gender,
       Sales quotas,
       Commission percent,
       bonus,
FROM   HumanResources.Employee h
LEFT  JOIN Sales.SalesPerson s
ON h.employee_PK=s.sales_ID
ORDER BY bonus DESC ;

You can comment if you have any doubt.
Upvote if you got your answer.
Thanks


Related Solutions

Link the following two tables: HumanResources.Employee and Sales.SalesPerson then display Employee PK, job title, Date of...
Link the following two tables: HumanResources.Employee and Sales.SalesPerson then display Employee PK, job title, Date of birth, Gender, Sales quotas, Commission percent and bonus. Use an outer join to display all the employee whether they are in sales or not. Make sure to sort by bonus desc. Explain why some of the field from the Sales.SalesPerson table are null.
How to populate an SQL table that has two PK (foreign) that have different number of...
How to populate an SQL table that has two PK (foreign) that have different number of rows? Example: there are 3 tables. student, assigment, and hoursDedicated. there are 5 students, and 3 assigments, and x hoursDedicated. hoursDedicated has 2 Primary Keys, studentID and assigmentID. how do you populate hoursDedicated?? Thank you for your time.
1. write SQL statements to create the following two tables: a) Department table that contains the...
1. write SQL statements to create the following two tables: a) Department table that contains the following columns(dept_no,deptname,location) set the deptno to be the primary key. b) Employee table contains the following columns(emp_no,empname,deptno,salary)set the emp_no to be the primary key and dept_no to be the foreign key. 2. Write SQL statements to Insert the following 3 rows in Employee table:        (101,’Sami’,’D-101’,5000)        (102,’Yousef’,’D-101’,4000)        (103,’Sami’,’D-102’,7000) 3. Write SQL statements to Insert the following 3 rows in Department table:       ...
PostgreSQL 1. Write a query to join two tables employees and departments to display the department...
PostgreSQL 1. Write a query to join two tables employees and departments to display the department name, first_name and last_name, hire date, and salary for all managers who have more than 15 years of experience. 2. Write a query to join the employees and departments table to find the name of the employee including the name and last name, department ID and department name. 3. Write a SQL query to join three tables of employees, departments, and locations to find...
Consider the following schema: Publisher (name, phone, city), PK: name. Book (ISBN, title, year, published_by, previous_edition,...
Consider the following schema: Publisher (name, phone, city), PK: name. Book (ISBN, title, year, published_by, previous_edition, price), PK: ISBN, FK: published_by refs Publisher, previous_edition refs Book. Author (SSN, first_name, last_name, address, income), PK: SSN. Write (aSSN, bISBN), PK: (aSSN, bISBN), FK: aSSN refs Author, bISBN refs Book. Editor (SSN, first_name, last_name, address, salary, works_for, book_count), PK: SSN, FK: works_for refs Publisher. Edit (eSSN, bISBN), PK: (eSSN, bISBN), FK: eSSN refs Editor, bISBN refs Book. Author_Editor (aeSSN, hours), PK: aeSSN, FK:...
Consider the following schema: Publisher (name, phone, city), PK: name. Book (ISBN, title, year, published_by, previous_edition,...
Consider the following schema: Publisher (name, phone, city), PK: name. Book (ISBN, title, year, published_by, previous_edition, price), PK: ISBN, FK: published_by refs Publisher, previous_edition refs Book. Author (SSN, first_name, last_name, address, income), PK: SSN. Write (aSSN, bISBN), PK: (aSSN, bISBN), FK: aSSN refs Author, bISBN refs Book. Editor (SSN, first_name, last_name, address, salary, works_for, book_count), PK: SSN, FK: works_for refs Publisher. Edit (eSSN, bISBN), PK: (eSSN, bISBN), FK: eSSN refs Editor, bISBN refs Book. Author_Editor (aeSSN, hours), PK: aeSSN, FK:...
SQL ONLY- Given Tables- Employee (EmployeeID, FirstName, LastName, Address) Department (DeptNo, DepartmentName, City) DepartmentEmployee(DeptNo, EmployeeID, Position)...
SQL ONLY- Given Tables- Employee (EmployeeID, FirstName, LastName, Address) Department (DeptNo, DepartmentName, City) DepartmentEmployee(DeptNo, EmployeeID, Position) Project (ProjectNo, ProjectName, DeptNo) Project Team ( ProjectNo, EmployeeID, Role) MAKE THE QUERY FOR- 1. List the name and position of all the employee who works in the (DeptNo = ‘22C’). 2. List the total number of employees in each department for those departments with more than 5 employees. 3. List the project number, project name and the number of employees who worked on...
Write the SQL DDL to create the following 5 tables for an App store: Publisher, Category,...
Write the SQL DDL to create the following 5 tables for an App store: Publisher, Category, App, AppVersion, AppVersionReview: A Publisher table where each publisher is identified by an integer id and has a name (up to 40 characters). (1 mark) A Category table where each category has an id (integer), a name (up to 50 characters), and a parentId to identify its parent category. The parentId should be a foreign key to the Category table. (1.5 marks) An App...
SQL code Create entities (tables) to represent the following ternary relationship. Note that we are not...
SQL code Create entities (tables) to represent the following ternary relationship. Note that we are not storing all the required information in these entities to simplify it. The underlined attributes are the primary keys. Don’t forget to set the constraints for primary and foreign keys: •A student entity ( bannerId, first name, last name, date of birth) •A course entity ( crnNumber, course name, # of credits) •An examination entity ( examId, exam_type, exam_date). (exam types can be “Midterm”, “Final”,...
Using the HotelDB tables, provide the following result as a screen image. WRITE SQL to retrieve...
Using the HotelDB tables, provide the following result as a screen image. WRITE SQL to retrieve rows from any hotel with Family room types and price less than $150. use hoteldb; CREATE TABLE HOTEL ( hotelNo numeric primary key , name varchar(40) , address varchar(40) , city varchar(200) ); CREATE TABLE ROOM ( roomNo numeric Primary Key , hotelNo numeric References HOTEL , type varchar(20) , price dec(9,2) ); CREATE TABLE GUEST ( guestNo numeric primary key , name varchar(40)...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT