How many rows are included in the table gained as as result of execution of the following statement?
SELECT DISTINCT customer_name, merchandise_name, unit_price
FROM order_table, merchandise_table
WHERE order_table.merchandise_number = merchandise_table.mnrchandise_number
SELECT DISTINCT customer_name, merchandise_name, unit_price
FROM order_table, merchandise_table
WHERE order_table.merchandise_number = merchandise_table.mnrchandise_number
Answer: 2
How many tables may be included with a join?
Answer: All of the mentioned options
The IN SQL keyword?
Answer: Determines if a value matches any of the values in a list or a sub-query.
You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerID. You need to create a query that meets the following requirements:
Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
Results must include customers who have not placed any orders.
Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
Results must include customers who have not placed any orders.
Answer:"SELECT CustomerName, OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID"
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID"
You have a table named Employees. You want to identify the supervisor to which each employee reports. You write the following query.
SELECT e.EmloyeeName AS [EmployeeName], s.EmployeeName AS [SuperVisorName] FROM Employees e
You need to ensure that the query returns a list of all employees and their respective supervisor. Which join clause should you use to complete the query?
SELECT e.EmloyeeName AS [EmployeeName], s.EmployeeName AS [SuperVisorName] FROM Employees e
You need to ensure that the query returns a list of all employees and their respective supervisor. Which join clause should you use to complete the query?
Answer: LEFT JOIN Employees s ON e.ReportsTo = s.EmployeeId
The LIKE SQL keyword is used along with?
Answer: WHERE clause.
Which of the following is one of the basic approaches for joining tables?
Answer: All of the above (Subqueries, Union Join, Inner/Outer join)
There should be one condition within the WHERE clause for each pair of tables being joined.
Answer: True
In SQL language, one data manipulation command that combines the records from two tables is called
Answer: JOIN
A correlated sub-query is where the outer query depends on data from the inner query.
Answer: False
Which are the outputs of the below SQL statement on the two tables:
SELECT EmpName FROM EmpRoom
WHERE RoomID NOT IN (SELECT RoomID FROM RoomMgnt WHERE Department=’A1’)
SELECT EmpName FROM EmpRoom
WHERE RoomID NOT IN (SELECT RoomID FROM RoomMgnt WHERE Department=’A1’)
Answer: Saito, Fukuda, Oohira
Which of the following relational algebra operations do not require the participating tables to be union-compatible?
Answer: Join
A UNION query is which of the following?
Answer: Combines the output from multiple queries and must include the same number of columns.
Subqueries can be nested multiple times
Answer: True
A CASE SQL statement is which of the following
Answer: A way to establish an IF-THEN-ELSE in SQL
A function returns one value and has only output parameters.
Answer: False
The joining technique is useful when data from several relations are to be retrieved and displayed and the relationships are not necessarily nested
Answer: True
Which of the following product groups can be found when searching the “Product Inventory” table for products with a sales price of 500 dollars or more per unit and an inventory of less than 10 units?
Answer: Medium refrigerator, small refrigerator, and air conditioner
You are a developer for a Microsoft SQL Server database instance used to support a customer service application. You create tables named complaint, customer, and product as follows:
CREATE TABLE [dbo].[complaint] ([ComplaintID] [int], [ProductID] [int], [CustomerID] [int], [ComplaintDate] [datetime]);
CREATE TABLE [dbo].[customer] ([CustomerID] [int], [CustomerName] [varchar](100), [Address] [varchar](200), [City] [varchar](100), [State] [varchar](50), [ZipCode] [varchar](5));
CREATE TABLE [dbo].[product] ([ProductID] [int], [ProductName] [varchar](100), [SalePrice] [money], [ManufacturerName] [varchar](100));
You need to write a query to identify all customers who have complained about products that have an average sales price of 500 or more from September 01, 2011.
Which SQL query should you use?
CREATE TABLE [dbo].[complaint] ([ComplaintID] [int], [ProductID] [int], [CustomerID] [int], [ComplaintDate] [datetime]);
CREATE TABLE [dbo].[customer] ([CustomerID] [int], [CustomerName] [varchar](100), [Address] [varchar](200), [City] [varchar](100), [State] [varchar](50), [ZipCode] [varchar](5));
CREATE TABLE [dbo].[product] ([ProductID] [int], [ProductName] [varchar](100), [SalePrice] [money], [ManufacturerName] [varchar](100));
You need to write a query to identify all customers who have complained about products that have an average sales price of 500 or more from September 01, 2011.
Which SQL query should you use?
Answer: "SELECT c.CustomerName, AVG(p.SalePrice) AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
INNER JOIN customer c ON com.CustomerID = c.CustomerID
WHERE com.ComplaintDate > '09/01/2011'
GROUP BY c.CustomerName
HAVING AVG(p.SalePrice) >= 500"
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
INNER JOIN customer c ON com.CustomerID = c.CustomerID
WHERE com.ComplaintDate > '09/01/2011'
GROUP BY c.CustomerName
HAVING AVG(p.SalePrice) >= 500"
The Enterprise manager cannot be used to modify stored procedures?
Answer: True
SQL triggers can be used when the DBMS receives a ____________request
Answer: All
You need to identify, within a given clause, if the month of February will contain 29 days for a specified year. Which object should you use?
Answer: Scalar-valued function
Which SQL trigger does SQL Server NOT support?
Answer: all
Which of the following statements is true concerning routines and triggers?
Answer: Both consist of procedural code.
Which of the following is true concerning triggers?
Answer: They have an event, condition, and action.
Using SQL Server 2000, which of the following symbols is used to indicate parameters in stored procedures?
Answer: @
The code to create triggers and routines is stored in only one location and is administered centrally.
Answer: True
A function returns one value and has only output parameters.
Answer: False
A _________________ is a program that performs some common action on database data and that is stored in the database
Answer: stored procedure
Which of the following is true concerning a procedure?
Answer: They include procedural and SQL statements
Because SQL stored procedures allow and encourage code sharing among developers, stored procedures give database application developers the advantages of__________
Answer: all
A _______________ is a stored program that is attached to a table or a view.
Answer: trigger
You have a third-party application that inserts data directly into a table. You add two new columns to the table. These columns cannot accept NULL values and cannot use default constraints. You need to ensure that the new columns do not break the third-party application. What should you do?
Answer: Create an INSTEAD OF INSERT trigger.
Which of the following is an SQL trigger Microsoft SQL Server supports?
Answer: INSTEAD OF and AFTER only
Which of the three possible types of triggers does SQL Server support?
Answer: INSTEAD OF and AFTER only
Stored procedures have the advantage of _______
Answer: all
You are tasked to create a table that has a column that must store the current time accurate to ten microseconds. You need to use a system function in conjunction with the DEFAULT option in the column definition. Which system function should you use?
Answer: SYSDATETIME
Which type of SQL Server cursor concurrency places an update lock on a row when the row is read?
Answer: READCOMMITTED
SQL triggers are created using _____
Answer: the SQL CREATE TRIGGER statement
In a relational database system, which of the following SQL statements is used to extract rows specified by the cursor after it has been defined?
Answer: FETCH statement
If a trigger is being written to enforce referential integrity actions, you cannot use______
Answer: an AFTER trigger
The following SQL statement is curror B’s data manipulation for table A. Which is the correct character string to be entered in the blank?
UPDATE A SET A2=1, A3=2 WHERE ……………….
A(A1, A2, A3) is the structure of table A, the underline indicates the primary key
UPDATE A SET A2=1, A3=2 WHERE ……………….
A(A1, A2, A3) is the structure of table A, the underline indicates the primary key
Answer: A1=CURRENT OF B (DON'T KNOW)
A stored program that is attached to the database is called ____
Answer: stored procedure
Triggers are stored blocks of code that have to be called in order to operate
Answer: False
SQL Server program code that is executed instead of an SQL command that needs to be processed is called a(n)
Answer: INSTEAD OF trigger.
Embedded SQL is which of the following?
Answer: Hard-coded SQL statements in a program language such as Java.
Because SQL statements are set-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are accessed using_____
Answer: an SQL cursor
What is not an advantage of stored procedures?
Answer: Increased network traffic
SQL triggers are used for ____
Answer: ALL
0 nhận xét:
Đăng nhận xét