What Transact SQL statement would accomplish this task?
CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
database named HRDB with tables named Staff and SalaryHistory. The Staff and SalaryHistory
tables were created using the following Transact-SQL code:
CREATE TABLE HRDB.Staff
(
StaffID int NOT NULL PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
StaffAddress nvarchar (200) NULL,
StaffCity nvarchar(50) NOT NULL,
StaffState nvarchar(50) NOT NULL,
StaffPhone varchar (10) NOT NULL,
Salary smallmoney NOT NULL
)
GO
CREATE TABLE HRDB.SalaryHistory
(
StaffID int NOT NULL,
IncreaseDate date DEFAULT GETDATE(),
PrevSalary smallmoney NOT NULL,
NewSalary smallmoney NOT NULL
)
GO
You must write a Transact-SQL query to affect a salary increase of 6.5% for all staff members with
a JobTitle of Support Technician. Your query must also update the data in the SalaryHistory table.
What Transact SQL statement would accomplish this task?
To answer, type the correct code in the answer area.
How would you create this table?
CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
database named SalesDB with tables named Customer and Orders. The Customer and Orders
tables were created using the following Transact-SQL code:
CREATE TABLE SalesDB.Customers
(
CustomerID int NOT NULL PRIMARY KEY,
FirstName varchar (150) NOT NULL,
LastName varchar (150) NOT NULL,
Address1 varchar (200) NOT NULL,
Address2 varchar (200) NULL,
City varchar (100) NOT NULL,
StateCode varchar (2) NOT NULL,
Zip varchar (5) NOT NULL,
Phone varchar (10) NOT NULL
)
GO
CREATE TABLE SalesDB.Orders
(
OrderID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
ShipDate datetime NOT NULL,
CustomerID int NOT NULL,
SalesRepID int NOT NULL
)
GO
You must now create an OrderDetails table as shown in the following database diagram:
The TotalPrice column must be a computed column based on the product of the UnitPrice and
Quantity columns and the data must be stored in the table.
How would you create this table?
To answer, type the correct code in the answer area.
Which of the following SELECT statement would you write?
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The relevant part of the ProductsDB is shown in the following database
diagram:
You need to write a Transact-SQL query that display a single row in the following XML format:
<row ProductID=”1001″ Product=”Product Name”, Price=”24.99″ InStock=”16″
Supplier=”Company Name” Contact=”Contact Name” Phone=”346 959 2215″ />
Which of the following SELECT statement would you write?
Which of the following SELECT statement would you write?
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB is shown in the following database diagram:
You need to write a Transact-SQL query that display a single row in the following XML format:
<Invoices InvoiceID=”1001″ Date=”2012-10-01T00:00:00″, Value=”1000.00″ Customer=”Customer
Name” ShippedTo=”Customer City” />
Which of the following SELECT statement would you write?
Which of the following SELECT statement would you write?
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The ProductsDB database is shown in the following database diagram:
You need to write a Transact-SQL query that displays all the products received by a single
supplier in the following XML format:
<Suppliers SupplierID=”22″ Company=”Company Name” ContactNumber=”510 250 6400″>
<Products ProductID=”100″ UnitPrice=”249.00″ UnitsInStock=”7″ />
<Products ProductID=”118″ UnitPrice=”559.00″ UnitsInStock=”12″ />
</Suppliers>
Which of the following SELECT statement would you write?
Which of the following SELECT statement would you use?
CORRECT TEXT
You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes
over the last 12 months. You want the top 10 cricket players to have a rank of 1, the next 10 to
have a rank of 2, and the last 10 to have a rank of 3.
Which of the following SELECT statement would you use?
To answer, type the correct code in the answer area.
Which ranking function should you use?
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named WeeklySales. The WeeklySales table records the sales
amount for each of ABC.com’s 20 sales representitives.
You need to write a Transact-SQL query that ranks the sales representatives by the average sales
amount for the past year. You want the sales representatives with the same average sales amount
to have the same rank with the subsequent rank being skipped.
Which ranking function should you use?
Which of the following SELECT statement would accomplish this task?
You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that returns the cricket player that received the most
votes for each week, as well as the number of votes they received.
Which of the following SELECT statement would accomplish this task?
What Transact-SQL statements would accomplish this task?
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named Inventory.
The Inventory table has three columns named ProductID, InStore and InWarehouse. The
ProductID column is the primary key and is linked to the Products table. The InStore column
stores the quantity of a product that is held at ABC.com’s retail shop, while the InWarehouse
column stores the quantity of a product that is held at ABC.com’s warehouse.
You need to add a computed column that stores the total number of a product that ABC.com has.
What Transact-SQL statements would accomplish this task?
Which of the following statements should you use in the trigger definition?
ABC.com has a SQL Server 2012 database instance that hosts a database named ComDB. The
ComDB database has a table named Partners that was created using the following Transact-SQL
code:
CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL,
[CompanyName] [nvarchar] (50) NOT NULL,
[Location] [nvarchar] (50) NOT NULL,
[ContactName] [nvarchar] (50) NOT NULL,
[Email] [nvarchar] (50) NOT NULL,
[Phone] [nvarchar] (10) NOT NULL,
CONSTRAINT [PK_Partners] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)
ON PRIMARY
)
You want to create a FOR UPDATE trigger that will track changes to the ContactName and Phone
columns.
Which of the following statements should you use in the trigger definition?