DATABASE FUNDAMENTALS 98-364 (examslabs) EXAM
LATEST 2024-2025 WITH ACTUAL QUESTIONS AND
CORRECT VERIFIED ANSWERS ALREADY GRADED A+
100%GUARANTEED PASS!
What data type can store characters of alphanumeric data and supports
multiple languages with minimal storage space requirements? - ANSWER-An
NVARCHAR data type can store alphanumeric data and supports multiple
languages, but requires minimal storage.
An NVARCHAR data type stores Unicode strings of different lengths. Unicode
strings should be used to provide multilingual support. The storage required for
an NVARCHAR data type is the maximum number of bytes to be stored
multiplied by two.
What does DML stand for? - ANSWER-Data Manipulation Language
What are DML statements used for? - ANSWER-DML statements are used to
retrieve, add, change, or delete data in a database.
You would include a HAVING clause in a query to: - ANSWER-Filter the results
after grouping.
,You would include a HAVING clause in a query to filter the results grouped by a
GROUP BY clause. First, the WHERE clause is applied to filter the result set.
Then, the GROUP BY clause is applied to group the results. After grouping is
done, the HAVING clause is evaluated to specify a condition that is checked
after grouping has occurred to further restrict the results. A HAVING clause can
only reference aggregate functions or columns that are also specified in the
GROUP BY clause. For example, suppose you have the following query:
SELECT LocationName
FROM Instructor INNER JOIN Location
ON Instructor.LocationID = Location.LocationID
WHERE Status = 'Part-time'
GROUP BY LocationName
HAVING COUNT(*) < 10
This query uses an INNER JOIN in the FROM clause to join the two tables
together. An INNER JOIN clause is used to display only rows that meet the
criteria specified in the ON clause. The WHERE clause is used to filter the rows
before they are grouped by location so that only Part-time instructors are
retrieved. After the WHERE clause is applied, the rows are grouped by location
using a GROUP BY clause so that the number of part-time instructors for each
location can be determined. Finally, the HAVING clause is applied to filter the
results to include only the locations that have less than ten part-time
instructors.
You have a database that has an OrderDetails table containing one or more
rows for each order. Each order is sequentially assigned an OrderID to identify
the order.
You execute the following query against the OrderDetails table:
,SELECT TOP 20 OrderID, SUM(LineTotal) AS OrderTotal
FROM OrderDetails
GROUP BY OrderID
ORDER BY OrderTotal DESC
What is the result? - ANSWER-The result is the 20 orders with the highest
total. The GROUP BY clause allows you to perform an aggregate calculation
over rows that have a matching value in a column. In this case, the statement
groups all rows associated with a specific order and sums the values in the
LineTotal column for each order. The ORDER BY clause is specified after the
GROUP BY clause and orders the results in descending order by the total
summed value by including the alias for the summed value and the DESC
keyword. The ORDER BY clause can only reference aggregate values, identified
with or without an alias, or a column referred to in the GROUP BY clause. Then,
the TOP clause returns the first 20 rows in the ordered result, which in this case
would be the 20 orders with the highest total.
When a view definition contains a join: - ANSWER-DML can be performed
using the view, but only on a single base table.
A single UPDATE, INSERT, or DELETE statement can only modify, add, or remove
data from one base table at a time.
You have a database table named TransactionHistory that contains millions of
rows. The table has a primary key named TrxID. Users frequently use a query
that includes the region and status in the WHERE clause condition.
You need to optimize performance of this query.
What should you do? - ANSWER-You should create a composite nonclustered
index on region and status.
, A nonclustered index on columns frequently used in a WHERE clause can
improve query performance. A nonclustered index can be created on an
individual column or multiple columns. An index created on multiple columns is
known as a composite index.
Which database object should you create to optimize how the underlying data
is physically accessed by queries? - ANSWER-An index.
An index is a database object that is used to optimize how the underlying data
is physically accessed by queries. Each table can include one clustered index
that represents the order of the data and multiple nonclustered indexes that
use pointers to locate the desired data. Because the entire table does not have
to be scanned, query performance can be improved.
You need to log data about each record deleted from the Members table in the
FormerMembers table.
What should you create? - ANSWER-A DELETE trigger
A DELETE trigger occurs after a record is deleted. In this case, the trigger would
use the columns in the deleted table to add a row to the FormerMembers
table. The deleted table is created automatically and is only available within a
trigger that fires in response to a DELETE statement or an UPDATE statement.
What statement should you use to insert values from one table into another
existing table that already contains data? - ANSWER-You should use an INSERT
statement. You can include a SELECT statement instead of a VALUES clause to
use a SELECT statement as the source of the data to be inserted rather than
specifying a VALUES clause explicitly. The SELECT statement can query one or
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller trustednurse. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $26.99. You're not tied to anything after your purchase.
