11. Union together the two dcode columns. Display tcode, row count, minimum sala
ID: 3590333 • Letter: 1
Question
11. Union together the two dcode columns. Display tcode, row count, minimum salary, maximum salary, average salary, and total salary for each unique tcode value. Use just the Employee table. 12. 13.For each employee, display its count of rows in Assignment followed by all its columns in Employee. Include zero counts. Do not use UNION in your solution. 14. Display all the columns in Assignment for those rows that do not have a value for end. 15. Count the number of different (i.e., unique) values of pcode in Assignment. 16.Display dcode for those departments that have more than three employees. Use just the Emplovee table 17. Display all the columns in Employee for those employees that have a salary that is 18.Display all the columns in Assignment for those that have one or more rows in Hours 19. Display all the columns in Assignment for those that have one or more rows in Hours 20. Display all the columns in Assignment for those that have one or more rows in Hours outside the salary range for their title. Your overall strategy must be a subquery connected by an "IN". Your overall strategy must be a subquery connected by an “EXISTS". Your overall strategy must be a join. Your solution can not contain any subqueries. Note that the sequence of the output rows for Query 18, 19, and 20 may be different, but the overall data values returned must be identical,Explanation / Answer
Based on the structure of the database given below, following queries are formed using SQL-
11. # This query will union the two dcode columns in Employee and Project table.
SELECT decode
FROM Employee AS e INNER JOIN Department AS d
ON e.dcode = d.code
UNION
SELECT dcode
FROM Project AS p INNER JOIN Department AS de
ON p.dcode = de.code;
12. # Displaying calculated fields from Employee table
SELECT tcode, COUNT(tcode) AS row_count, MIN(salary) AS minimum_salary, MAX(salary) AS maximum_salary, AVG(salary) AS average_salary, SUM(salary) AS total_salary
FROM Employee
GROUP BY tcode;
13. # Displaying Employee detail along with number of assignment
SELECT id, COUNT(eid) AS total_assignment, tcode, dcode, ssn,
last_name, first_name, middle_init, hire, salary
FROM Employee AS e INNER JOIN Assignment AS a
ON e.id = a.eid
GROUP BY e.id;
14. # Displaying all columns in Assignment for rows that do not have value for end
SELECT * FROM Assignment
WHERE end IS NULL;
15. # Counting number of unique values of pcode in Assignment
SELECT COUNT(DISTINCT pcode) AS numberOfPcode
FROM Assignment
16. # Displaying dcode that have more than 3 employee
SELECT dcode COUNT(id)
FROM Employee
GROUP BY dcode
HAVING COUNT(id)>3;
17. # Displaying all columns of employee where salary range in not in title table.
SELECT e.id, e.tcode, e.dcode, e.ssn, e.last_name, e.first_name, e.middle_init, e.hire, e.salary
FROM Employee AS e INNER JOIN Title AS t
ON e.tcode = t.code
WHERE e.salary < t.min_salary AND e.salary > t.max_salary
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.