Top of Form
Q1
Which of the following is a valid aggregate function in SQL? Check only those that apply.
Question 1 options:
AVG |
|
LEAST |
|
COUNT |
|
MIN |
|
MOST |
|
SUM |
Q2
Which of the following aggregate functions will only operate on a numeric value expression? Check only those that apply:
Question 2 options:
MIN |
|
MAX |
|
AVG |
|
COUNT |
|
SUM |
Q3
When applying DISTINCT to the value being calculated in an aggregate function like COUNT, what is the effect?
SELECT COUNT(DISTINCT StreetName)
FROM ADDRESSES;
Question 3 options:
To count only unique values |
|
To make a distinction between rows and columns |
|
To add a summary COUNT for all groupings |
|
To include duplicate values |
Q4
A query is limited to a single aggregate calculation in its SELECT clause.
Question 4 options:
True |
|
False |
Q5
The GROUP BY clause groups the rows of a result set based on one or more columns or expressions. In the example below, what will the value from COUNT(*) represent?
SELECT Department,
COUNT(*)
FROM EMPLOYEES
GROUP BY Department;
Question 5 options:
The total number of departments |
|
The total number departments for each employee |
|
The total number of employees |
|
The total number of employees in each department |
Q6
Specify the correct order in which each of the six SQL clauses must be placed:
Question 6 options:
123456 |
GROUP BY |
123456 |
WHERE |
123456 |
ORDER BY |
123456 |
SELECT |
123456 |
FROM |
123456 |
HAVING |
Q7
The GROUP BY clause can contain more than one value (separated by commas). In the query below, what does SUM(Salary) represent?
SELECT Department,
State,
SUM(Salary)
FROM EMPLOYEES
GROUP BY Department, State;
Question 7 options:
The total salary budget for the organization |
|
The salary budget for each state |
|
The salary budget for each department |
|
The salary budget for each combination of department and state |
Q8
The WHERE clause and the HAVING clause can both limit rows in the result set based on logical conditions. What is the difference between the two clauses?
Question 8 options:
HAVING cannot use the logical OR operator |
|
WHERE is limited to a single condition |
|
WHERE requires a corresponding GROUP BY |
|
HAVING can make comparisons against aggregate functions |
Q9
Conditions comparing against non-aggregate values must be placed in the WHERE clause (never in the HAVING clause).
Question 9 options:
True |
|
False |
Q10
What is the ROLLUP operator used for in the GROUP BY clause?
Question 10 options:
To save memory in query execution |
|
To add a summary total for each grouping |
|
To group by all values - even if they do not match WHERE clause conditions |
|
To group based on unique values |
Bottom of Form
SOLUTION:-
==============================================================================================
Top of Form Q1 Which of the following is a valid aggregate function in SQL? Check...
1 - Which of the following allows you to create subtotals in the output from a SELECT statement? Group of answer choices _ SUBTOTAL _ ORDER BY _ HAVING _ GROUP BY 2 - A query includes grouping done using the AVG, SUM, COUNT, MIN, or MAX function. Which SQL keyword is used to select rows after this grouping has been done? Group of answer choices _ WHERE _ DISTINCT _ HAVING _ LIKE 3 - Which of the following...
My SQL -----database---- What restrictions apply to the use of the aggregate functions within the SELECT statement? How do nulls affect the aggregate functions? Give a complete example, Explain how the GROUP BY clause works. What is the different between the WHERE and HAVING clauses? Give an example
I need help with the following problem: Using the classicmodels database you installed from Module 1, provide an example query using both a group by clause and a having clause. Show no more than ten rows of your query result. Discuss if the query you wrote can be rewritten without those clauses. I have come up with the following for the first part of the question: mysql> select offices.city, count(employees.employeenumber) as NumberOfEmployees from Employees -> inner join offices on offices.officecode...
Create the database and tables for the database. Show all SQL statements. Include primary and foreign keys. Insert data into each table. Show select statements and display the output of each table. Note:Student’s name must be inserted into table as part of the data! Perform the SQL below: Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk (*). State the purpose of the query; show the query and...
QUESTION 21 Complete the table below by matching each definition with the appropriate SQL clause. -A.B.C.D.E.F.G.H.I.J.K.L.M.N. A mandatory clause that is at the start of all SQL retrieval queries -A.B.C.D.E.F.G.H.I.J.K.L.M.N. A clause used to return only the values in a result table that are unique -A.B.C.D.E.F.G.H.I.J.K.L.M.N. A clause used to filter tuples according to some condition or predicate, like selection in relational algebra -A.B.C.D.E.F.G.H.I.J.K.L.M.N. A clause that is used to list the tables and any joins required in a query...
Question 9 (1 point) Which choice below describes the function of the following SQL statement? SELECT ID, Last Name, First Name, Address FROM Members WHERE Last Name Last Name a simple query a parameterized query a compound query a wildcard query Question 10 (1 point) The correct SQL syntax to sort a table by Last Name in order of A-Z is SORT BY Last Name ASCENDING ORDER BY Last Name Down ORDER BY Last Name ASC ORDER BY Last Name...
Summarize these pages in your own style and you have to include in your report some figures highlighting the relation between these operations. Basic AGGREGATE functions (Revision) COUNT: returns the number of tuples, which meet the specified condition: SELECT COUNT(DISTINCT Dept) AS Num_Depts FROM subject: SUM: returns the sum of the values in a specified column (i.e. numeric column) SELECT COUNT(*) AS hi_sal, SUM(salary) FROM Lecturer WHERE Salary > 4500 MIN: returns the minimum value in a specified column (numeri...
Which SQL statement retrieves the EmployeeName and City columns from the Employees table? O LIST EmployeeName, City ON Employees; O FIND EmployeeName, City ON Employees; SELECT EmployeeName, City FROM Employees; O RETRIEVE EmployeeName, City FROM Employees; Which SQL statement finds the different cities in which national parks reside with no duplicate rows listed? SELECT DISTINCT city FROM natlpark ORDER BY city; O ORDER BY natlpark DISTINCT city; O ORDER BY city FROM natlpark; O SELECT city FROM natlpark ORDER BY...
use SQL to do this, include output apply all types of aggregate functions you have learned so far (functions like avg, sum, asc, desc, count, max, min) on the appropriate data from all the columns in Figure 1.4. Show the SQL query and the resultant output (Table). building budget salary dept.name ID name 22222 12121 Einstein Wu 95000 Physics Finance History Comp. Sci Elec. Eng Biology Comp. Sci History Comp. Sci Music Physics Finance Watson Painter 70000 120000 90000 32343...
MYSQL Applied Database I just want to make sure that the syntax looks okay for my database SQL statements, any help would be much appreciated: 1.A query that asks for customer star review for Tony Stark on Opportunity Detail. SELECT CONCAT (employee_first_name,' ',employee_last_name) AS employee_name, review_star_count, FROM customer_reviews, JOIN e employees ON employee_id =employee_id, WHERE employee_id=4 AND opportunity_id=1; 2.A query that shows how many orders Additional Dot Twain License, in an order totaling over $900. SELECT product_name, COUNT(o.OrderID) AS...