* Which of the following statements correctly (27Admin is trying to find out the average salary of employees in each department. He noticed that the SALARY column can have NULLvalues, and he does not want the NULLS included when calculating the average. Identify the correct SQL that will produce the desiredresults.[A][B]SELECT department_id, AVG (salary)SELECT department_id, AVG (NVL (salary, 0)}FROM employeesFROM employeeaGROUP BY department_id:GROUP BY department_id:[C][D]SELECT department_id, NVL (AVG (salary), 0)SELECT department_id, AVG (salary)FROM employeesFROM employeesGROUP BY department_id;GROUP BY department_idHAVING salary IS NOT NULL;ANone of the above

To find employees from a table we need to use select statement. To find average of salary of…

Admin is trying to find out the average salary of employees in each department. He noticed that the SALARY column can have NULL values, and he does not want the NULLS included when calculating the average. Identify the correct SQL that will produce the desired results. [A] [B] SELECT department_id, AVG(salary) FROM employees GROUP BY department_id: SELECT departament_id, AVG (NVL (salary, 0)) FROM employees GROUP BY department_id; [C] [D] SELECT department_id, NVL (AVG (salary), o) SELECT department_id, AVG (salary) FROM employees FROM employees GROUP BY department_id: GROUP BY department_id HAVING salary IS NOT NULL: D

Admin is trying to find out the average salary of employees in each department. He noticed that the SALARY column can have NULL values, and he does not want the NULLS included when calculating the average. Identify the correct SQL that will produce the desired results. [A] [B] SELECT department_id, AVG(salary) FROM employees GROUP BY department_id: SELECT departament_id, AVG (NVL (salary, 0)) FROM employees GROUP BY department_id; [C] [D] SELECT department_id, NVL (AVG (salary), o) SELECT department_id, AVG (salary) FROM employees FROM employees GROUP BY department_id: GROUP BY department_id HAVING salary IS NOT NULL: D

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

I need help with the SQL query for the following question: 1. Get a list of 10 product reviews that got review rating 5 and price more than thousand. Here is the information on the field names: The table name is: Reviews Review Form Fields Product/ModelName: Product/Category: Product/Price: Retailer/Name: Retailer/ZipCode: Retailer/City: Retailer/State: Product/On Sale: Manufacturer/Name: Manufacturer/Rebate: Review/UserId: Review/Rating: Review/Date: Review/Text: Example of Review Form Filled out ProductModelName: Galaxy S4 ProductCategory: Smart Phone ProductPrice: 499 RetailerName: Bestbuy RetailerZipCode: 44114 RetailerCity: Cleveland RetailerState: OH ProductOnSale: No ManufacturerName: Samsung ManufacturerRebate: No ReviewUserId: xmm473 ReviewRating: 3 ReviewDate: 6/10/2012 ReviewText: overheats after 2 hours
SQL SUM Problem -SELECT * FROM [Sale Order] INNER JOIN Product ON [Sale Order] productID = Product.productID here i've joined two tables , now what i need to do is sum the productPrice of the rows with the same customerID .. order id and product id are the primary key
How can the CAST function be combined with other functions to enhance data type compatibility in SQL queries?
I need help with understanding how to do the SQL code for the following question (attached is the CSV database file): These are the header titles from the picture: ProductModelName, ProductCategory, ProductPrice, RetailerName, RetailerZip, RetailerCity, RetailerState, ProductOnSale, ManufacturerName, ManufacturerRebate, UserID, UserAge, UserGender, UserOccupation, ReviewRating, ReviewDate, Review Text 1. Get the total number of products reviewed and got Rating 5 in Every City
Create a PL/SQL block to retrieve and display information for all employees. Display the employee ID, manager ID, date of hire, and if any of the employee territories is in the region "Southern". CREATE TABLE Employees ( EmployeeID NUMBER NOT NULL, LastName VARCHAR2(20) NOT NULL, FirstName VARCHAR2(10) NOT NULL, Title VARCHAR2(30), TitleOfCourtesy VARCHAR2(25), BirthDate DATE, HireDate DATE, Address VARCHAR2(60), City VARCHAR2(15), Region VARCHAR2(15), PostalCode VARCHAR2(10), Country VARCHAR2(15), HomePhone VARCHAR2(24), Extension VARCHAR2(4), Photo LONG RAW, Notes VARCHAR2(600), ReportsTo NUMBER, PhotoPath VARCHAR2(255), CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID), CONSTRAINT FK_Employees_Employees FOREIGN KEY (ReportsTo) REFERENCES Employees(EmployeeID) ) / CREATE TABLE EmployeeTerritories ( EmployeeID NUMBER NOT NULL, TerritoryID VARCHAR2(20) NOT NULL, CONSTRAINT PK_EmpTerritories PRIMARY KEY (EmployeeID, TerritoryID), CONSTRAINT FK_EmpTerri_Employees FOREIGN KEY (EmployeeID)…
Write all the sql code (including create, insert and select)
In what scenarios can the UNPIVOT function be beneficial for data transformations in SQL?
QUICK SQL REQUEST (beginner level...) Back at work on Monday morning, the Vice President of Purchasing asks you to help him with the analysis of in-process storage. He asks you to pull information regarding the number of products per storage location. The result of the query should show: - The name of the warehouse (Location) - The number of different products per storage location - The total quantity of products found at this storage location - The average price of all products found at this storage location He mentions that for the moment he is only interested in the locations that have more than 50 distinct products because he wants to start inventorying the biggest locations first. Show the locations in order of smallest to largest number of distinct products. My request is linked to the question but i'm having trouble executing it I would love to know how to do it, thanks in advance.
How to create a query in SQL developer (oracle) to: Display Department_id, Job_id, the Average salary for this combination under alias Average Dept/Job Pay and the Lowest salary for this combination as well under alias Lowest Dept/Job Pay, but only if that Lowest Pay falls in the range $5000 - $15000. Exclude Vice Presidents from this query and departments IT and SALES as well. Sort the output according to the Department_id and then by Job_id. You need to use only the Subquery method (NO joins allowed).
Write sql code for Use derived tables and joins to create a list of players who have scored more than 200 runs, taken more than 10 wickets, and taken more than two catches. runs is form batting wicketstaken from bowling catches is from fielding table (which is same as other tables0
Use the given database to output the results of the following SQL queries: b.) SELECT ∗FROM course AS C INNERJOIN instructor AS I ON C.dept name = I.dept nameWHERE I.ID = 45565; c.) SELECT dept name, building, name, IDFROM department LEFTJOIN instructor ON budget = salary;
What are the potential issues with over-relying on the COALESCE function in SQL?