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
Question
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
Solution
- B: Add the following line at the end of the query: HAVING SUM(OrderValue) > 1000
- A: 1
- B: UNION
- A: Use the SCOPE_IDENTITY function.
- B: TRY_CAST
- B: You can write a query based on sys.triggers.
- B: Add the following code at the beginning of the script: IF OBJECT_ID ( 'dbo.deleteOrder', 'P' ) IS NOT NULL DROP PROCEDURE dbo.deleteOrder GO
- B: Replace INNER JOIN with LEFT JOIN.
- B: You need to change the collation at the database level. You can do that with an ALTER DATABASE COLLATE statement.
- A: The INSERT and DELETE statements will be rolled back, but a new table will have been created in the database.
Similar Questions
From the following table, write an SQL query to retrieve the customer ID, order date, and maximum purchase amount for each customer on each order date where the maximum purchase amount exceeds 2000.00. The results should be sorted by customer ID.
Table: OrdersOrderIDCustomerIDOrderDateAmount11012023-01-01500.0021022023-01-03150.0031012023-01-04200.0041032023-01-07700.0051022023-01-10300.00i. Write an SQL query to count the number of orders.ii. Write an SQL query to calculate the total amount of all orders.iii.Write an SQL query to find the total amount of orders for each customer.iv. Write an SQL query to find the largest order amount.v. Write an SQL query to find customers with a total order amount greater than 500.Answer text Question 3EditViewInsertFormatToolsTableHelp
From the following table, write a query that retrieves the maximum purchase amount for each customer within a specific range of customer IDs (between 3002 and 3007) and then filters out customers whose maximum purchase amount exceeds 1000.
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;
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 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.The customer IDs and the average order amount for customers who have placed more than one order.
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.