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.
Question
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.
Solution
To solve this problem, you would need to write a SQL query. Here's a step-by-step guide on how to do it:
-
First, you need to identify the structure of your tables. For this problem, we have two tables: Employees and Salaries. We don't have the exact structure, but from the problem, we can infer that both tables have an 'employee_id' field. The Employees table has a 'name' field and the Salaries table has a 'salary' field.
-
The problem requires us to find employees with missing information, either a name or a salary. This means we need to find employee_ids that are present in one table but not in the other.
-
To do this, we can use a UNION of two queries: one that finds employee_ids in the Employees table that are not in the Salaries table, and another that finds employee_ids in the Salaries table that are not in the Employees table.
-
Here's how you can write the SQL query:
SELECT employee_id FROM Employees
WHERE employee_id NOT IN (SELECT employee_id FROM Salaries)
UNION
SELECT employee_id FROM Salaries
WHERE employee_id NOT IN (SELECT employee_id FROM Employees)
ORDER BY employee_id ASC;
-
This query first selects all employee_ids from the Employees table that are not in the Salaries table. It then unions this with the result of selecting all employee_ids from the Salaries table that are not in the Employees table. The UNION operator combines the result sets of two or more SELECT statements (removing duplicate rows). The final result is ordered by employee_id in ascending order.
-
Run this query in your SQL environment to get the list of employee IDs with missing information.
Similar Questions
Write a query to retrieve the names of all employees and sort them in ascending order.Table: EmployeeInput records:Input format :The input table is already created, and records are already prepopulated, as mentioned in the problem statement.Output format :The output displays the names of all employees in ascending order as shown below.NameAngelMarkMathewSteveRefer to the sample output for the column headers.
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.
Write a query to display the ename of employees working in 'HR' department and who draw an annual salary greater than 1000000. [Consider employees who work in multiple departments too]. Display the records sorted in ascending order based on employee ename.Note:1. Display all the ename of employees working in 'HR' department and having annual salary greater than 1000000.2. Use Joins. Tables involved are employee, works and department3. Order by is mandatory.
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');
Write a query to find employees who have the highest salary in each of the departments.Input format :The input tables are already prepopulated, as given in the problem statement.Output format :The output display the name of each department along with the name of the employee who has the highest salary within that department and their respective salary. Order the output by department name as shown below.Refer to the sample output for the column headers.
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.