Which SQL query correctly finds the top 5 customers with the highest total order amounts?1.0 MarksSELECT CustomerID, COUNT(OrderAmount) AS TotalOrderAmount FROM Orders GROUP BY CustomerID ORDER BY TotalOrderAmount ASC LIMIT 5;SELECT CustomerID, SUM(OrderAmount) AS TotalOrderAmount FROM Orders GROUP BY CustomerID ORDER BY TotalOrderAmount DESC LIMIT 5;SELECT CustomerID, AVG(OrderAmount) AS TotalOrderAmount FROM Orders GROUP BY CustomerID ORDER BY TotalOrderAmount DESC LIMIT 5;SELECT CustomerID, SUM(OrderAmount) AS TotalOrderAmount FROM Orders WHERE OrderAmount > 100 GROUP BY CustomerID ORDER BY TotalOrderAmount DESC LIMIT 5;

Select the correct answerConsider the following SQL query: SELECT customer_id, MAX(total_amount) AS max_amount FROM Orders GROUP BY customer_id HAVING COUNT(*) > 1;What will be the output of this query?OptionsThe customer IDs and the average order amount for customers who have placed more than one order.The customer IDs and the maximum order amount for customers who have placed more than one order.An error because the HAVING clause is used without an aggregate function in the SELECT clause. An error because the GROUP BY clause is not properly used.

29. What is the result of the following SQL Query? SELECT COUNT(*), AVG(amount) FROM Orders WHERE amount > 200; Number of orders with amount > 200 and the average amount of these orders.Total number of orders and the average amount of all orders.Number of customers with orders > 200 and the average order amount per customer.Total number of orders > 200 and the average number of orders.

We have following relationorders(order_id,customer_id,order_date,amount)1) Find out the number of orders for each customer by customer_id.2) Find out the total amount by order_id and order_date.3) Find out the number of orders for each customer by customer_id. Show onlycustomer_id with number of orders above 5

Query the customer_number from the orders table for the customer who has placed the largest number of orders.It is guaranteed that exactly one customer will have placed more orders than any other customer.The orders table is defined as follows:ColumnTypeorder_number (PK)intcustomer_numberintorder_datedaterequired_datedateshipped_datedatestatuschar(15)commentchar(200)Sample Inputorder_numbercustomer_numberorder_daterequired_dateshipped_date112017-04-092017-04-132017-04-12222017-04-152017-04-202017-04-18332017-04-162017-04-252017-04-20442017-04-182017-04-282017-04-25Sample Outputcustomer_number3ExplanationThe customer with number '3' has two orders, which is greater than either customer '1' or '2' because each of them only has one order.So the result is customer_number '3'. Follow up: What if more than one customer have the largest number of orders, can you find all the customer_number in this case?Optionsselect customer_numberfrom ( select customer_number, count(*) as cnt from orders group by customer_number) as eorder by e.cnt desclimit 1;select customer_number select customer_number, count(*) as cnt from orders group by customer_number) as eorder by e.cnt desclimit 1;select customer_numberfrom ( select customer_number, count(*) as cnt from orders group by customer_number) as elimit 1;select customer_numberfrom ( select customer_number, count(*) as cnt group by customer_number) as eorder by e.cnt desclimit 1;

You have written the following SQL query, which calculates the total value of each customer’s orders:SELECT CustomerId, SUM(OrderValue) AS TotalFROM dbo.OrderGROUP BY CustomerIdYou need to modify this query in order to find only those customers whose orders exceed a total value of 1000. How can you do that?A: Add the following line after the line with the FROM statement:WHERE OrderValue > 1000B: Add the following line at the end of the query:HAVING SUM(OrderValue) > 1000C: Add the following line at the end of the query:WHERE SUM(OrderValue) > 1000D: Add the following line after the line with the FROM statement:HAVING SUM(OrderValue) > 10002.What is the maximum number of clustered indexes per table?A: 1B: 2C: You can create as many clustered indexes as non-clustered indexes.D: There is no limit.3.You want to concatenate the results of three queries into a single result set. Additionally, all duplicates should be removed. Which operator will you use?A: INTERSECTB: UNIONC: UNION ALLD: EXCEPT4.You wrote an INSERT statement that inserts data into a dbo.Order table with an auto-incremented/identity column. This column is called Id. Now you need to read the value generated for the Id column for the row inserted by your INSERT statement. What should you do?A: Use the SCOPE_IDENTITY function.B: SELECT TOP(1) Id FROM dbo.Order ORDER BY Id ASCC: SELECT TOP(1) Id FROM dbo.Order ORDER BY Id DESCD: Add an OUTPUT clause to the INSERT statement.5.You need to convert an expression of one type into another type. However, if a conversion is not possible, you do not want an error to be raised. Which function can you use?A: CASTB: TRY_CASTC: CONVERTD: TRY_CONVERT6.How can you effectively find the list of all triggers at the table level defined in a given database?A: You can use SQL Server Management Studio to browse all the tables one by one and, for each table, check if there are any triggers defined.B: You can write a query based on sys.triggers.C: You can use a SHOW TRIGGERS statement.D: You can call the system function GET_TRIGGERS.7.You wrote a script that creates a stored procedure. It begins as follows:CREATE PROCEDURE dbo.deleteOrder ( @OrderId INT )AS SET NOCOUNT ON ...The problem is that it can only be executed once; i.e. if you try to execute it more than once, an error informing you that a given stored procedure already exists will be raised. How would you fix this issue so that the script can be executed once, twice or many times? A: Change the first line of the script to:ALTER PROCEDURE …B: Add the following code at the beginning of the script:IF OBJECT_ID ( 'dbo.deleteOrder', 'P' ) IS NOT NULL    DROP PROCEDURE dbo.deleteOrderGOC: Add the following code at the beginning of the script:DROP PROCEDURE dbo.deleteOrderGOD: Add the following code at the beginning of the script:IF EXISTS(SELECT 1 FROM sys.procedures WHERE Name = 'deleteOrder')    DROP PROCEDURE dbo.deleteOrderGO8.You have written the following query, which counts the number of orders for each customer:SELECT c.CustomerId, COUNT(1) AS NoOfOrdersFROM dbo.Customer AS c INNER JOIN dbo.Order AS o ON c.CustomerId = o.CustomerId GROUP BY c.CustomerIdThe problem is that it does not return customers that have no orders. How would you fix this problem?A: Replace INNER JOIN with RIGHT JOIN.B: Replace INNER JOIN with LEFT JOIN.C: Replace INNER JOIN with LEFT OUTER JOIN.D: Replace INNER JOIN with CROSS JOIN.E: Replace INNER JOIN with RIGHT OUTER JOIN.9.You are working with a database that is case insensitive. You would like to change it so that all operations are case sensitive. What should you do?A: It is not possible. MSSQL Server is designed to be case insensitive.B: You need to change the collation at the database level. You can do that with an ALTER DATABASE COLLATE statement.C: This option is set when a database is created and cannot be changed later on.D: You need to change the collation at the table level. For each table in a database you need to execute an ALTER TABLE COLLATE statement.10.You started a new transaction:BEGIN TRANSACTIONThen you executed the following script:CREATE TABLE dbo.Temp...INSERT INTO dbo.Order VALUES...DELETE FROM dbo.Order WHERE OrderId > 100In the end, you decided to roll back the transaction. What is the final effect?A: The INSERT and DELETE statements will be rolled back, but a new table will have been created in the database.B: The rollback will not succeed because DML and DDL statements have been mixed in the script.C: Both DML and DDL statements will be rolled back.D: All three statements will be rolled back.E: Only the table creation statement will be rolled back