Description : Given three tables: salesperson, company, orders.Output all the names in the table salesperson, who didn’t have sales to company 'RED'.ExampleInputTable: salespersonsales_idnamesalarycommission_ratehire_date1John10000064/1/20062Amy12000055/1/20103Mark650001212/25/20084Pam25000251/1/20055Alex50000102/3/2007The table salesperson holds the salesperson information. Every salesperson has a sales_id and a name.Table: companycom_idnamecity1REDBoston2ORANGENew York3YELLOWBoston4GREENAustinThe table company holds the company information. Every company has a com_id and a name.Table: ordersorder_idorder_datecom_idsales_idamount11/1/20143410000022/1/201445500033/1/2014115000044/1/20141425000The table orders holds the sales record information, salesperson and customer company are represented by sales_id and com_id.OutputnameAmyMarkAlexExplanationAccording to order '3' and '4' in table orders, it is easy to tell only salesperson 'John' and 'Alex' have sales to company 'RED', so we need to output all the other names in table salesperson.Optionsselect s.namewhere s.sales_id not in( select sales_id from orders as o left join company as con o.com_id = c.com_id where c.name = 'RED');select s.namefrom salesperson as swhere s.sales_id not in( select sales_id from orders as o left con o.com_id = c.com_id where c.name = 'RED');select s.namewhere s.sales_id not in( select sales_id from orders as o left join company as con o.com_id where c.name = 'RED');select s.namefrom salesperson as swhere s.sales_id not in( select sales_id from orders as o left join company as con o.com_id = c.com_id where c.name = 'RED');
Question
Description : Given three tables: salesperson, company, orders.Output all the names in the table salesperson, who didn’t have sales to company 'RED'.ExampleInputTable: salespersonsales_idnamesalarycommission_ratehire_date1John10000064/1/20062Amy12000055/1/20103Mark650001212/25/20084Pam25000251/1/20055Alex50000102/3/2007The table salesperson holds the salesperson information. Every salesperson has a sales_id and a name.Table: companycom_idnamecity1REDBoston2ORANGENew York3YELLOWBoston4GREENAustinThe table company holds the company information. Every company has a com_id and a name.Table: ordersorder_idorder_datecom_idsales_idamount11/1/20143410000022/1/201445500033/1/2014115000044/1/20141425000The table orders holds the sales record information, salesperson and customer company are represented by sales_id and com_id.OutputnameAmyMarkAlexExplanationAccording to order '3' and '4' in table orders, it is easy to tell only salesperson 'John' and 'Alex' have sales to company 'RED', so we need to output all the other names in table salesperson.Optionsselect s.namewhere s.sales_id not in( select sales_id from orders as o left join company as con o.com_id = c.com_id where c.name = 'RED');select s.namefrom salesperson as swhere s.sales_id not in( select sales_id from orders as o left con o.com_id = c.com_id where c.name = 'RED');select s.namewhere s.sales_id not in( select sales_id from orders as o left join company as con o.com_id where c.name = 'RED');select s.namefrom salesperson as swhere s.sales_id not in( select sales_id from orders as o left join company as con o.com_id = c.com_id where c.name = 'RED');
Solution
The question is asking for a SQL query that will return the names of salespeople who have not made any sales to the company named 'RED'.
Here is the correct SQL query:
SELECT s.name
FROM salesperson AS s
WHERE s.sales_id NOT IN (
SELECT sales_id
FROM orders AS o
LEFT JOIN company AS c ON o.com_id = c.com_id
WHERE c.name = 'RED'
);
This query works by first selecting all sales_id from the orders table that are associated with the company 'RED'. It then checks the salesperson table and selects the names of salespeople whose sales_id is not in the list of sales_id that were associated with 'RED'. The result is a list of names of salespeople who have not made any sales to 'RED'.
Similar Questions
Problem StatementIn a company's HR database, you are responsible for ensuring data integrity. Your task is to write a query to identify employees with missing information – either a name or salary is absent. The result should be ordered by employee ID in ascending order, providing a comprehensive overview for data quality assurance.The Sample records are given belowTable Name: EmployeesTable Name: SalariesInput format :The input records are already prepopulated, as given in the problem statement.Output format :The output displays a list of employee IDs that are present in one table but not in the other, sorted in ascending order as shown below.employee_id12Refer to the sample output for the column headers.Note :The program will be evaluated only after the “Submit Code” is clicked.Extra spaces and new line characters in the program output will result in the failure of the test case.
You are tasked with managing sales data for a company. The data includes information about salesmen, customers, and orders. Write a query to find the name and numbers of all salesmen who had more than one customer.The following table is already created, and the records are inserted into the table.The Sample records are given below Input format :The input records are already prepopulated, as given in the problem statement.Output format :The output should include the Salesman_id, Salesman_Name, and Customer_Count for all salesmen who had more than one customer as shown below.Salesman_id Salesman_Name Customer_Count5 Chris Wilson 2Refer to the sample output for the column headers.
Consider the following tables:Table: EmployeesEmployeeIDNameDepartmentID1Alice102Bob203Charlie304David40Table: DepartmentsDepartmentIDDepartmentName10HR20IT30Finance50Marketing i. Write an SQL query to list all employees with their corresponding department names using an INNER JOIN.ii. Write an SQL query to list all employees and their department names, including those who do not belong to any department (use Left Join)Answer text Question 5EditViewInsertFormatToolsTableHelp
From the following tables, create a view named mcustomer to display details of salesmen who are associated with more than one customer. Return all the fields of the salesperson.Sample Table: salesmanSample Table: customerNote: Table names and column names are case-sensitive.Input format :The input tables are already prepopulated, as given in the problem statement.Output format :The output is a view named mcustomer that provides details of salesmen who are associated with more than one customer, including their ID, name, city, and commission as shown belo
Problem StatementWrite a query to display the details of employees who are not in the 'Developer' department. Table: EmployeeInput format :The input table is already created, and records are already prepopulated, as mentioned in the problem statement.Output format :The output displays a list of employees who work in departments other than 'Developer' as shown below.id name Department102 Stark HR104 Jack Finance106 Scott AdminRefer to the sample output for the column headers.Note :The program will be evaluated only after the “Submit Code” is clicked.Extra spaces and new line characters in the program output will result in the failure of the test case.
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.