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.
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)...
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:...
Please read the following and submit a minimum 150 word commentary at title link above: In...
Please read the following and submit a minimum 150 word commentary at title link above: In your own words, summarize the general philosophies of any two of the following famous economists: John Maynard Keynes, Milton Friedman, Adam Smith, Karl Marx, John Stuart Mills, Friedrich Hayek.
SQL- Trigger I have two tables (below) I need to write a trigger that would delete...
SQL- Trigger I have two tables (below) I need to write a trigger that would delete everything for a pid from the Appt table if the pid is deleted from the patient table. Create table Appt(                 pid numeric Foreign Key references patient(pid),                 ptname varchar(50) Foreign Key references patient(name),                 dob date Foreign Key references patient(dob),                 dr varchar(20),                 appdate date,                 apptime time, ); and Create table Patient(                 pid numeric primary key,                 name varchar(50),                ...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT