Home

Exam 70-433 Preparation: Microsoft SQL Server Database Development
   

Questions
  1. James is working for a department store. He has created two tables as follows: 
    CREATE TABLE Inventory.Categories
(
    CategoryID int identity(1, 1) primary key,
    Category nvarchar(20) not null
);
GO
CREATE TABLE Inventory.StoreItems
(
    ItemNumber nvarchar(10) primary key,
    CategoryID int foreign key
        references Inventory.Categories(CategoryID),
    ItemName nvarchar(60) not null,
    Size nvarchar(20),
    UnitPrice money
);
GO

INSERT INTO Inventory.Categories(Category)
VALUES(N'Men'), (N'Women'), (N'Boys'), (N'Girls'),(N'Miscellaneous');
GO
    The employees of the company are in charge of adding the records to the tables. To keep track of the records added to the StoreItems table, James creates a table as follows: 
    CREATE TABLE Inventory.DatabaseOperations
(
    OperationID int identity(1,1) NOT NULL,
    ObjectType nchar(20) default N'Table',
    ObjectName nvarchar(40),
    PerformedBy nvarchar(50),
    ActionPerformed nvarchar(max),
    TimePerformed datetime,
    CONSTRAINT PK_Operations PRIMARY KEY(OperationID)
);
GO
    When a new item is added to the StoreItems table, James would like the DatabaseOperations table to receive a new record to that effect. How can James create a trigger to perform that operation? 
    1. CREATE TRIGGER Inventory.NewProductCreated
ON Inventory.StoreItems
AFTER INSERT
AS
BEGIN
    INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
			ActionPerformed, TimePerformed)
    VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	   N'New product added', GETDATE())
END
GO
    2. CREATE TRIGGER Inventory.NewProductCreated
AFTER INSERT
FOR OBJECT::Inventory.StoreItems
AS
BEGIN
    INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
			ActionPerformed, TimePerformed)
    VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
           N'New product added', GETDATE())
END
GO
    3. CREATE OBJECT::Inventory.NewProductCreated
ON Inventory.StoreItems
AS TRIGGER FOR INSERT
BEGIN
    RETURN
        INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
			ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'New product added', GETDATE());
END
GO
    4. CREATE TRIGGER Inventory.NewProductCreated
ON Inventory.StoreItems
SET TRIGGER = INSERT
AS
    RETURN
        INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
        VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'New product added', GETDATE());
GO
    5. CREATE TRIGGER Inventory.NewProductCreated
ON Inventory.StoreItems
FOR INSERT
AS
    INSERT INTO Inventory.DatabaseOperations(ObjectType,
			        ObjectName, PerformedBy,
			 ActionPerformed, TimePerformed)
    VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	   N'New product added', GETDATE());
GO
  2. Dave has a table named Products and that has a few records. The table was created as follows: 
    CREATE TABLE Products
(
    ProductID int identity(1, 1) not null,
    DateAcquired date,
    Name nvarchar(50),
    Size nvarchar(32),
    UnitPrice money,
    DiscountRate decimal(4, 2),
    CONSTRAINT PK_Products PRIMARY KEY(ProductID)
);
GO
    He wants to get a list of products using their names, their sizes, and prices. He wants the list to be sorted by the acquired dates but he does not want to see the acquired date column. What code can he use to do that? 
    1. SELECT DateAcquired, Name, Size, UnitPrice
FROM Products
ORDER BY DateAcquired
SET DateAcquired = NULL;
GO
    2. SELECT DateAcquired, Name, Size, UnitPrice
FROM Products
HIDE DateAcquired
ORDER BY DateAcquired;
GO
    3. SELECT Name, Size, UnitPrice
FROM Products
ORDER BY DateAcquired;
GO
    4. SELECT Name, Size, UnitPrice
ORDER BY DateAcquired
FROM Products
SET DateAcquired SHOW = OFF;
GO
    5. SELECT DateAcquired, Name, Size, UnitPrice
SORT(DateAcquired) IS NULL
FROM Products;
GO
  3. Andrew has been using a table of products that was created as follows: 
    CREATE TABLE Products
(
    ProductID int identity(1, 1),
    Name nvarchar(50),
    UnitPrice money
);
GO
    The table already has a few records that were added previously. This morning, to add a few records, Andrew writes the following code: 
    INSERT Products(Name, UnitPrice)
VALUES(N'Mid Lady Bag - Lizard', 228),
      (N'Holly Gladiator Heel Shoes', 198),
      (N'Short Black Skirt', 55.85);
GO
    When he executes that code, Andrew receives the following error: 
    Explicit value must be specified for identity column in 
table 'Products' either when IDENTITY_INSERT is set 
to ON or when a replication user is inserting into 
a NOT FOR REPLICATION identity column.
    What must Andrew do to let the database engine generate a product number?
    1. He must first execute the sp_autogenerate stored procedure
    2. He must include the list of column in the INSERT statement, as in 
      INSERT Products(ProductID, Name, UnitPrice)
VALUES(N'Mid Lady Bag - Lizard', 228),
      (N'Holly Gladiator Heel Shoes', 198),
      (N'Short Black Skirt', 55.85);
GO
    3. She must first use a SELECT statement that calls the MAX(ProductID) aggregate function to get the highest number that the ProductID column holds. Then increment it by 1 when adding a new record
    4. He must set IDENTITY_INSERT to OFF
    5. He must set AUTOINCREMENT to FALSE
  4. Steve inherited a table named Customers and that was created as follows: 
    CREATE TABLE Customers
(
  AccountNumber nchar(7),
  FirstName nvarchar(20),
  LastName nvarchar(20),
  PhoneNumber nvarchar(20),
  EmailAddress nvarchar(40)
);
    Now he wants an index tied to that table. What code can he use to create the index? 
    1. CREATE INDEX IDX_Customers ON Customers SET IDX_Customers = AccountNumber;
    2. CREATE INDEX IDX_Customers AS AccountNumber FROM Customers;
    3. CREATE INDEX IDX_Customers ON Customers(AccountNumber);
    4. SELECT AccountNumber FROM Customers CREATE INDEX IDX_Customers;
    5. EXEC CREATE INDEX ON Customers(AccountNumber) = IDX_Customers;
  5. Ashley receives instructions to create a table of customers with the following recommendations:
    1. The table will be named Customers
    2. The table must have a column that includes a primary key that uses natural numbers starting at 1000 and automacally incrementing by 1
    3. The table must have a column for the customer's name
    4. The table must have a column for the customer's phone number
    In what three ways can Ashley create the table? 
    1. CREATE TABLE Customers
(
    CustomerID INT IDENTITY(1000, 1),
    Name nvarchar(50),
    [Phone Number] nvarchar(20),
    CREATE PRIMARY KEY WITH CustomerID
);
GO
    2. CREATE TABLE Customers
(
    CustomerID INT IDENTITY(1000, 1)
	CONSTRAINT PK_Customers PRIMARY KEY(CustomerID),
    Name nvarchar(50),
    [Phone Number] nvarchar(20),
);
GO
    3. CREATE TABLE Customers
(
    CustomerID INT IDENTITY(1000, 1) PRIMARY KEY,
    Name nvarchar(50),
    [Phone Number] nvarchar(20),
);
GO
    4. CREATE TABLE Customers
(
    CustomerID INT IDENTITY(1000, 1),
    Name nvarchar(50),
    [Phone Number] nvarchar(20),
    PRIMARY KEY REFERENCES(CustomerID)
	
);
GO
    5. CREATE TABLE Customers
(
    CustomerID INT IDENTITY(1000, 1),
    Name nvarchar(50),
    [Phone Number] nvarchar(20),
    CONSTRAINT PK_Customers PRIMARY KEY(CustomerID)
);
GO
  6. Hank has the following table of employees: 
    CREATE TABLE Personnel.Employees
(
    EmployeeNumber nchar(7) not null primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    EmploymentStatus smallint,
    HourlySalary money
);
GO
INSERT Personnel.Employees
VALUES(N'284-680', N'Anselme', N'Bongos', 2, 18.62),
      (N'730-704', N'June', N'Malea', 1, 9.95),
      (N'735-407', N'Frank', N'Monson', 3, 14.58),
      (N'281-730', N'Jerry', N'Beaulieu', 1, 16.65);
GO
    Hank also has the following table of sales made by employees during a certain period: 
    CREATE TABLE Commercial.Sales
(
    SaleID int identity(1, 1),
    EmployeeNumber nchar(7) not null,
    SaleDate date,
    Amount money
);
GO
INSERT INTO Commercial.Sales(EmployeeNumber, SaleDate, Amount)
VALUES(N'284-680', N'2011-02-14', 4250),
      (N'735-407', N'2011-02-14', 5300),
      (N'730-704', N'2011-02-14', 2880),
      (N'281-730', N'2011-02-14', 4640),
      (N'284-680', N'2011-02-15', 4250),
      (N'281-730', N'2011-02-15', 3675);
GO
    Now, Hank wants to get a list that displays the name of each employee and the number of sales he or she made. What code can he use? 
    1. SELECT LastName + N', ' + FirstName Employee,
       COUNT(EmployeeNumber) AS [Number of Sales]
FROM   Commercial.Sales cs JOIN
       Personnel.Employees pe ON
       cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY LastName + N', ' + FirstName
    2. SELECT LastName + N', ' + FirstName Employee,
       COUNT(Amount) AS [Number of Sales]
FROM   Commercial.Sales cs JOIN
       Personnel.Employees pe ON
       cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY LastName + N', ' + FirstName
    3. SELECT LastName + N', ' + FirstName Employee,
       COUNT(*) AS [Number of Sales]
GROUP BY LastName + N', ' + FirstName
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
     cs.EmployeeNumber = pe.EmployeeNumber
    4. SELECT LastName + N', ' + FirstName Employee,
       COUNT(*) AS [Number of Sales]
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
	 cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY LastName + N', ' + FirstName
HAVING Amount NOT NULL
    5. SELECT LastName + N', ' + FirstName Employee,
       COUNT(*) AS [Number of Sales]
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
	 cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY LastName + N', ' + FirstName
WHERE Amount IS NOT NULL
  7. Mark has a table named Employees created in a schema named Personnel as follows: 
    CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	EmploymentStatus tinyint,
	HourlySalary money
);
GO
INSERT Personnel.Employees
VALUES(N'284-680', N'Anselme', N'Bongos', 2, 18.62),
      (N'730-704', N'June', N'Malea', 1, 9.95);
GO
    To protect the table, Mark wants to create a view used for data entry. The view will be named NewHire. The view must include a condition that states that the staus of the new employee can be 1, 2, or 3 and no other number. Mark wants to make sure no record is added to the table if that record violates the condition in the view. What code can he use to accomplish that? 
    1. CREATE VIEW Personnel.NewHire
WITH CHECK OPTION
AS
    SELECT EmployeeNumber,
	   FirstName,
	   LastName,
	   EmploymentStatus,
	   HourlySalary
    FROM   Personnel.Employees
    WHERE  EmploymentStatus IN(1, 2, 3); -- Full Time, Part Time, Unknown/Other
GO
    2. CREATE VIEW Personnel.NewHire
AS
    WITH CHECK OPTION
    SELECT EmployeeNumber,
	   FirstName,
	   LastName,
	   EmploymentStatus,
	   HourlySalary
    FROM   Personnel.Employees
    WHERE  EmploymentStatus IN(1, 2, 3); -- Full Time, Part Time, Unknown/Other
GO
    3. CREATE VIEW Personnel.NewHire
SET CHECK OPTION ON
AS
    SELECT EmployeeNumber,
	   FirstName,
	   LastName,
	   EmploymentStatus,
	   HourlySalary
    FROM   Personnel.Employees
    WHERE  EmploymentStatus IN(1, 2, 3); -- Full Time, Part Time, Unknown/Other
GO
    4. CREATE VIEW Personnel.NewHire
AS
    SELECT EmployeeNumber,
	   FirstName,
	   LastName,
	   EmploymentStatus,
	   HourlySalary
    FROM   Personnel.Employees
    WHERE  EmploymentStatus IN(1, 2, 3) -- Full Time, Part Time, Unknown/Other
    WITH CHECK OPTION;
GO
    5. CREATE VIEW Personnel.NewHire
AS
    SELECT EmployeeNumber,
	   FirstName,
	   LastName,
	   EmploymentStatus,
	   HourlySalary
    FROM   Personnel.Employees
    CONSTRAINT CHECK EmploymentStatus IN(1, 2, 3); -- Full Time, Part Time, Unknown/Other
GO
  8. What is wrong with the following code? 
    CREATE TABLE Employees
(
	EmployeeNumber nchar(60) not null unique primary key,
	[Full Name] nvarchar(50),
	HourlySalary money,
	Department nchar(4)
)
    1. Nothing
    2. The nchar data type has a limit length of 12 character but the code indicates 60. Consequently, this code will produce an error
    3. UNIQUE and PRIMARY KEY cannot be combined in the definition of a column
    4. The name of a column cannot have a space
    1. The code should end with a semi-colon
  9. Imagine you have the following table: 
    CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	DepartmentCode nchar(6),
	HourlySalary money
);
GO
    Then you create the following view: 
    CREATE VIEW EmployeesRecords
AS
    SELECT EmployeeNumber, FirstName, LastName 
    FROM Employees;
GO
    Now you want to modify the view to add the HourlySalary field. What code would you use to do that? 
    1. UPDATE VIEW EmployeesRecords
BEGIN
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary 
    FROM Employees;
END
    2. ALTER VIEW EmployeesRecords
AS
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary 
    FROM Employees;
GO
    3. EXECUTE sp_change EmployeesRecords
BEGIN
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary 
    FROM Employees;
END
    4. ALTER VIEW EmployeesRecords
AS
    ADD HourlySalary 
    FROM Employees;
END
    5. ALTER VIEW EmployeesRecords
AS
    ADD Employees.HourlySalary;
GO
  10. Daniel has the following table in his organization: 
    CREATE TABLE Members
(
    MemberID int unique,
    Name nvarchar(50),
    MembershipStatus nvarchar(20)
        CHECK(MembershipStatus IN (N'In Review', N'Active', N'Suspended'))
);
INSERT INTO Members
VALUES(100, N'Albert Welch', N'Active'),
      (104, N'Daniel Simpson', N'Suspended'),
      (264, N'Ann Plants', N'Active'),
      (275, N'Jane Womack', N'In Review'),
      (279, N'June Palau', N'Suspended'),
      (288, N'Paul Motto', N'Active');
GO
    He wants to remove all members who have been suspended from the organization. What code can he use? 
    1. DELETE ALL * FROM Members
WHERE MembershipStatus IS N'Suspended';
GO
    2. DELETE FROM Members
WHERE MembershipStatus != N'Suspended';
GO
    3. REMOVE Members
WHERE MembershipStatus = N'Suspended';
GO
    4. EXECUTE sp_remove FROM Members
WHERE MembershipStatus = N'Suspended';
GO
    5. DELETE FROM Members
WHERE MembershipStatus = N'Suspended';
GO
  11. Jane has the following tables and their records: 
    CREATE TABLE Departments
(
	DepartmentCode nchar(6) not null primary key,
	DepartmentName nvarchar(50) not null
);
GO

INSERT INTO Departments
VALUES(N'HMRS', N'Human Resources'),
      (N'ACNT', N'Accounting'),
      (N'PSNL', N'Personnel'),
      (N'RSDV', N'Research & Development');
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	DepartmentCode nchar(6)
);
GO
INSERT INTO Employees
VALUES(N'283-947', N'Timothy', N'White', N'RSDV'),
      (N'572-384', N'Jeannette', N'Welch', N'PSNL'),
      (N'279-242', N'Ann', N'Welch', N'HMRS'),
      (N'495-728', N'Robert', N'Simms', N'RSDV'),
      (N'382-505', N'Paula', N'Waters', N'PSNL'),
      (N'268-046', N'Martine', N'Nyamoto', N'ACNT'),
      (N'773-148', N'James', N'Larsen', N'RSDV'),
      (N'958-057', N'Peter', N'Aut', N'HMRS');
GO
    She wants to change the department of employees from the personnel department to the human resources department. When the operation has been performed, she wants to see the list of records that were affected. What code can she use? 
    1. UPDATE Employees
SET    DepartmentCode = N'PSNL'
OUTPUT INSERTED.*
WHERE  DepartmentCode = N'HMRS';
GO
    2. UPDATE Employees
SET    DepartmentCode = N'HMRS'
OUTPUT INSERTED.*
WHERE  DepartmentCode = N'PSNL';
GO
    3. UPDATE Employees
SET    DepartmentCode = N'PSNL'
WHERE  DepartmentCode = N'HMRS'
OUTPUT DELETED.*;
GO
    4. SET    Employees
OUTPUT UPDATED.*
UPDATE DepartmentCode = N'HMRS'
WHERE  DepartmentCode = N'PSNL';
GO
    5. SET    Employees
UPDATE DepartmentCode = N'PSNL'
WHERE  DepartmentCode = N'HMRS'
OUTPUT INSERTED.*;
GO
  12. Consider the following table: 
    CREATE TABLE StoreItems
(
	ItemNumber int,
	ItemName nvarchar(60),
	ItemDescription nvarchar(max),
	UnitPrice money
);
GO

    What two segment codes can be used to create three records (select 2)?

    1. INSERT StoreItems
VALUES(190857, N'Men Deodorant', 5.05);
INSERT INTO StoreItems
VALUES(838273, N'Currently on sale bar soaps', N'Sold as 6 bars', 1.95);
INSERT StoreItems(ItemNumber, ItemName, ItemDescription)
VALUES(828305, N'Cereal in a Jar', N'');
    2. INSERT StoreItems
VALUES(149752, N'Chocolate Bar', NULL, 75.00);
INSERT INTO StoreItems(ItemNumber, ItemName, UnitPrice)
VALUES(350882, N'1Gal Milk 2%', 3.85);
INSERT StoreItems
VALUES(247014, N'Peanut Butter Jar', N'Sold in 24 Units', 2.55);
    3. INSERT StoreItems
VALUES(197084, N'Tomato Juice', 4.55),
      (274859, N'Orange Juice', 8.95),
      (927304, N'2-Litter Soda Bottle', 2.25);
    4. INSERT StoreItems(ItemNumber, ItemName, UnitPrice)
VALUES(918839, N'Ground Beef', 6.55),
      (829307, N'Tomato Sauce', 2.95);
INSERT StoreItems(ItemNumber, ItemName, ItemDescription)
VALUES(941155, N'12-Doughnuts Pack', N'On sale - must go');
  13. You wrote code as follows to give the appropriate permission(s) to Jeremy Andrew Blasick (username: jblasick) to be able to change records on the Employees table: 
    CREATE DATABASE NationalBank;
GO

USE NationalBank;
GO
CREATE TABLE Employees
(
    EmployeeNumber int,
    FirstName nvarchar(20),
    LastName  nvarchar(20),
    Salary decimal(20, 6)
);
GO

CREATE USER [Jeremy Blasick]
FOR LOGIN jblasick;
GO

GRANT UPDATE 
ON OBJECT::Employees
TO [Jeremy Blasick];
GO

INSERT INTO Employees
VALUES(500727, N'John', N'Harrolds', 18.25);
GO

    So you call Jeremy and ask him to change the record in the Employees table. After a few minutes, he calls you back saying he can't and he doesn't understand why. What do you think is the problem?

    1. In your code, the INSERT INTO code should have been written before the CREATE USER code
    2. The table doesn't have a primary key and therefore cannot allow record change
    3. The permission(s) given to the user are not enough
    4. You should have revoked the UPDATE permission before granting it because granting the UPDATE permission automatically invokes the INSERT permission
    5. In the CREATE USER statement, you must use either his actual username or his full name, including his middle name
  14. You had already created a user as Kirk Blankey. He has been asked to manage the Students table. He must be able to create and change records. He also must be able to give manage the rights of other users on the database. What code can you write to give him the appropriate rights? 
    1. GRANT INSERT, UPDATE, SELECT
TO [Kirk Blankey]
ON OBJECT::Students;
GO
    2. GRANT CONNECT, INSERT, UPDATE, 
ON OBJECT::Students
TO User = N'Kirk Blankey'
WITH GRANT OPTION;
GO
    3. GRANT INSERT, UPDATE, SELECT
ON OBJECT::Students
TO [Kirk Blankey]
WITH GRANT OPTION;
GO
    4. GRANT OPTION = ALL
TO [Kirk Blankey]
ON OBJECT::Students;
GO
    5. GRANT ALTER, CONNECT
ON OBJECT::Students
TO [Kirk Blankey]
WITH OPTION GRANT ALL;
GO
  15. Consider the following statement from the State University database: 
    USE StateUniversity1;
GO
SELECT TeacherID, FullName, Teachers.CourseID
FROM Teachers;
GO

    What will this statement produce?

    1. Nothing, because Teachers.CourseID will create an error
    2. It will show the list of teachers and their TeacherID but excluding the CourseID
    3. It will show the list of teachers, including their TeacherID, their names, and their CourseID
    4. An error because it doesn't specify what the primary key of the table is
    5. It will show the list of teachers that includes only CourseID while excluding the TeacherID and the full name
  16. Consider the following table of records
     
    TeacherID TeacherName CourseCode
    218440 Roberta Jerseys CMST 385
    250077 Mary Shamberg CMIS 101
    270480 Olympia Sumners CMIS 320
    274692 Leslie Aronson CMIS 170
    294057 Peter Sonnens CMST 306
    493748 John Franks CMIS 101
    948025 Chryssa Lurie CMIS 320
    972837 Hellah Zanogh CMST 306

    What statements would produce appropriate results (select 3)? 
    1. SELECT * FROM Teachers
WHERE CourseID = [CMST 320];
GO
    2. SELECT TeacherID, TeacherName, CourseID
FROM Teachers;
GO
    3. SELECT * FROM Teachers
WHEN CourseID = N'CMIS 101';
GO
    4. SELECT TeacherName FROM Teachers
WHERE TeacherName = N'Leslie Aronson';
GO
    5. SELECT * FROM Teachers
WHERE CourseID = N'CMIS 101';
GO
  17. Consider the following statement from the State University database: 
    SELECT CourseID, COUNT(*)
FROM Teachers
GROUP BY CourseID;
GO

    What will this statement produce?

    1. An error: CourseID should have been written Teachers.CourseID
    2. The list of teachers and the course(s) they teach
    3. The list of teachers, excluding the course(s) they teach
    4. The list of courses and the number of teachers associated with that course
    5. An error because the statement left out the other columns of the table
  18. Consider the following tables:
     
    Table Name: Courses
     
    CourseID CourseName Credits
    CMIS 101 Introduction to Problem Solving and Algorithm Design 3
    CMIS 170 Introduction to XML 3
    CMIS 320 Relational Databases 3
    CMIS 420 Advanced Relational Databases 3
    CMST 306 Introduction to Visual Basic Programming 3
    CMST 385 Internet and Web Design 3
     
    Table Name: Teachers
     
    TeacherID FullName
    218440 Roberta Jerseys
    250077 Mary Shamberg
    270480 Olympia Sumners
    274692 Leslie Aronson
    294057 Peter Sonnens
    493748 John Franks
    948025 Chryssa Lurie
    972837 Hellah Zanogh
     
    Table Name: TeachersAndCourses
     
    CourseID CourseName Credits
    1 493748 CMIS 101
    2 270480 CMIS 328
    3 294057 CMST 306
    4 274692 CMIS 170
    5 274692 CMIS 101
    6 972837 CMIS 320
    7 250077 CMIS 101
    8 294057 CMST 309
    9 294057 CMST 412
    10 948025 CMIS 320
    11 972837 CMST 306
    12 270480 CMIS 320
    13 218440 CMST 385

    What statements would produce appropriate results (select 2)? 
    1. SELECT TeacherID, CourseID FROM TeachersAndCourses
GROUP BY CourseID;
GO
    2. SELECT *
FROM TeachersAndCourses
WHERE CourseID = N'CMIS 328'
GROUP BY CourseID;
GO
    3. SELECT CourseID, COUNT(*)
FROM TeachersAndCourses
GROUP BY CourseID;
GO
    4. SELECT TeacherID, COUNT(*)
FROM TeachersAndCourses
GROUP BY TeacherID;
GO
    5. SELECT TeacherID, COUNT(*)
FROM TeachersAndCourses
GROUP BY CourseID;
GO
  19. Consider the following two tables: 
    CREATE TABLE EmploymentTypes
(
	EmplTypeID int not null,
	EmplType nvarchar(20)
);
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(7) not null,
	EmplTypeID int,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO

INSERT INTO EmploymentTypes(EmplTypeID, EmplType)
VALUES(10, N'Employee'), (20, N'Contractor'),
      (40, N'Seasonal'), (60, N'Other');
GO

INSERT INTO Employees(EmployeeNumber, EmplTypeID, FirstName,
                      LastName, HourlySalary)
VALUES(N'202-725', 20, N'Julie', N'Flanell', 36.55),
      (N'927-205', 10, N'Paulette', N'Simms', 26.65),
      (N'840-202', 40, N'Alexandra', N'Ulm', 12.85),
      (N'472-095', 10, N'Ellie', N'Tchenko', 11.95),
      (N'268-046', 60, N'Martine', N'Nyamoto', 15.52),
      (N'273-148', 20, N'James', N'Larsen', 18.24),
      (N'481-729', 10, N'Faye', N'Cross', 14.92);
GO
    What will the following code produce? 
    SELECT EmplTypeID FROM Employees
WHERE HourlySalary < ALL(SELECT AVG(HourlySalary)
			 FROM Employees
			 GROUP BY EmplTypeID);
GO
    1. The employment type ID of the staff member who has the highest salary
    2. The names of all staff members who have the highest salaries per employment type
    3. The name of the staff member who has the highest salary in each type of employment
    4. The highest salary in each type of employment
    5. The salary of the staff member with the highest hourly salary
  20. Your boss created a database named SuperMarket1 for a new client. He wants you to create a new table for the employees and to let Hermine Nkolo (Login Name: hnkolo) manage that table. To make it happen, you write the following script: 
    CREATE SCHEMA Personnel;
GO
CREATE TABLE Personnel.Employees
(
	EmplNbr nchar(10),
	FirstName nvarchar(20),
	LastName nvarchar(20),
	Salary money,
	FullTime bit
);
GO
INSERT INTO Personnel.Employees
VALUES(N'29730', N'Philippe', N'Addy', 20.05, 1),
      (N'28084', N'Joan', N'Shepherd', 12.72, 0),
      (N'79272', N'Joshua', N'Anderson', 18.26, 0),
      (N'22803', N'Gregory', N'Swanson', 15.95, 0),
      (N'83084', N'Josephine', N'Anderson', 22.25, 1);
GO

CREATE USER Hermine
FOR LOGIN   hnkolo;
GO
GRANT INSERT
ON OBJECT::Personnel.Employees
TO Hermine;
GO
GRANT UPDATE
ON OBJECT::Personnel.Employees
TO Hermine;
GO
    After executing the script, you let Hermine know that she can use the table. After a few minutes, Hermine calls you and says that when she tries to create records, she receives an error that she was denied the object:
     
    Selection Error
     
    How would you solve the problem: 
    1. It appears that Hermine doesn't have the CONNECT permission. Therefore, grant the CONNECT permission to Hermine
    2. In the SQL Server Management Studio, open the properties of the Employees table and change the User name from Hermine to hrnkolo
    3. Grant the SELECT permission
    4. Your account probably does not have the WITH GRANT permission
    5. The table was marked as read-only
  21. Pamela McDugan wrote the following statement to create a view: 
    CREATE VIEW Personnel.Contractors
AS
SELECT empl.EmployeeNumber, empl.[First Name], empl.[Last Name]
FROM Personnel.Employees empl
ORDER BY empl.[Last Name]
GO
    When she executes the statement to create the view, she receives an error. What is wrong?
    1. You cannot include an ORDER BY clause in this view
    2. The error is because empl is not in square brackets but the names of columns are
    3. The names of columns must not contain space
    4. The ORDER BY clause must be written before the FROM expression
    5. The name of the view must not be preceded by Personnel. In can only be preceded by the name of the database
  22. Michael wants to test something on a view. So he writes the following code to create a temporary table and a view: 
    CREATE TABLE #Employees
(
	EmployeeNumber nchar(10) not null,
	[First Name] nvarchar(20),
	[Last Name] nvarchar(20) not null,
	Title nvarchar(40),
	Salary money
);
GO

CREATE VIEW Contractors
AS
SELECT EmployeeNumber, [First Name], [Last Name]
FROM #Employees;
GO
    When he executes the code, she receives an error. What do you think is the problem?
    1. You cannot create a view for a temporary table
    2. The name of the view must be preceded by #
    3. The names of columns must not contain space
    4. The name of the table in the view definition must not start with #
    5. In order to create a view from a temporary table, the table must have a primary key
  23. Consider the tables from the State University database. Imagine you want to get a list of all teachers whether they have a matching CourseID in the Courses table or not. What statement would produce that result? 
    1. SELECT TeacherID, FullName, Teachers.CourseID
FROM Teachers
LEFT OUTER JOIN Courses
ON Teachers.CourseID = Courses.CourseID;
GO
    2. SELECT TeacherID, FullName, Teachers.CourseID
FROM Teachers
CROSS OUTER JOIN Courses
ON Teachers.CourseID = Courses.CourseID;
GO
    3. SELECT TeacherID, FullName, Teachers.CourseID
FROM Teachers
    4. SELECT Courses.CourseID, Courses.CourseName
FROM Courses
LEFT OUTER JOIN Teachers
ON Teachers.CourseID = Courses.CourseID;
GO
    5. SELECT TeacherID, FullName, Teachers.CourseID
FROM Teachers CROSS JOIN Courses
  24. Consider the following tables: 
    CREATE TABLE Departments
(
	DepartmentCode nchar(6) not null primary key,
	DepartmentName nvarchar(50) not null
);
GO

CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	DepartmentCode nchar(6)
);
GO

    What would the following statement produce?

    SELECT Employees.EmployeeNumber,
       Employees.FirstName,
       Employees.LastName,
       Departments.DepartmentName
FROM   Employees INNER JOIN Departments
    ON Departments.DepartmentCode = Employees.DepartmentCode;
GO
    1. The list of all employees and the department each employee belongs to
    2. The list of each employee associated with each department (not just the department the employee actually belongs to)
    3. Nothing, because the Employees table does not specify a foreign key
    4. An error, because the SELECT statement should start with the Departments table
    5. The list of departments and all employees
  25. Consider the following tables: 
    CREATE TABLE Departments
(
	DepartmentCode nchar(6) not null primary key,
	DepartmentName nvarchar(50) not null
);
GO

CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	DepartmentCode nchar(6)
);
GO

    What is the difference in result between this code:

    SELECT Employees.EmployeeNumber,
       Employees.FirstName,
       Employees.LastName,
       Departments.DepartmentName
FROM   Employees INNER JOIN Departments
    ON Departments.DepartmentCode = Employees.DepartmentCode;
GO

    and this one?

    SELECT Employees.EmployeeNumber,
       Employees.FirstName,
       Employees.LastName,
       Departments.DepartmentName
FROM   Employees JOIN Departments
    ON Departments.DepartmentCode = Employees.DepartmentCode;
GO
    1. The first code would show the list of each employee associated with each department. The second code would produce an error
    2. No difference: both would produce the same result
    3. The first code  would produce an error. The second code would show each department associated with each employee
    4. Both codes would produce errors because the assignment is ill-formed
    5. The first code will show the list of each employee and the department he or she belongs to.
      The second code will show each department with each employee of the company, even if the employee doesn't belong to the department
  26. Consider the following tables: 
    CREATE TABLE Departments
(
	DepartmentCode nchar(6) not null primary key,
	DepartmentName nvarchar(50) not null
);
GO

INSERT INTO Departments
VALUES(N'HMRS', N'Human Resources'),
      (N'ACNT', N'Accounting'),
      (N'RSDV', N'Research & Development');
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	DepartmentCode nchar(6)
);
GO
INSERT INTO Employees
VALUES(N'283-947', N'Timothy', N'White', N'RSDV'),
      (N'495-728', N'Robert', N'Simms', N'RSDV'),
      (N'268-046', N'Martine', N'Nyamoto', N'ACNT'),
      (N'273-148', N'James', N'Larsen', N'RSDV'),
      (N'958-057', N'Peter', N'Aut', N'HMRS');
GO
    What would the following code produce? 
    SELECT Employees.EmployeeNumber,
       Employees.FirstName,
       Employees.LastName,
       Departments.DepartmentName
FROM Personnel.Employees JOIN Departments
    ON Departments.DepartmentCode = Employees.DepartmentCode
WHERE Departments.DepartmentCode = N'RSDV';
GO
    1. An error, because the WHERE clause must be written before the FROM clause
    2. An error because the Departments.DepartmentCode field is not specified in the SELECT statement
    3. A list of employees who belong to the Research & Development department
    4. A list of departments that are joined to the employees
    5. A list that contains each department and all employees, start with those that belong to the department that has RSDV as code
  27. Maggie is in charge of managing an old table from an existing database. Now she wants to delete the table but is worried about the consequences and calls you to get your input. What type of answer would you present her?
    1. You can't delete a view once it has been created and executed at least once
    2. Delete the table or tables that hold(s) the records of the view
    3. Delete the schema first
    4. Check the records first and copy them to a temporary table
    5. Check the permissions first and find out how they are used on the view
  28. Gary wants to monitor the transactions in a bank database. So he wrote code as follows: 
    CREATE SCHEMA Management;
GO
CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Transactions;
GO

GOCREATE TABLE Transactions.Customers
(
    CustomerID int Identity(1, 1) NOT NULL,
    DateCreated date,
    AccountNumber nvarchar(20),
    CustomerName nvarchar(50)
);
GO
CREATE TABLE Transactions.DatabaseOperations
(
    OperationID int identity(1,1) NOT NULL,
    ObjectName nvarchar(40),
    PerformedBy nvarchar(50),
    ActionPerformed nvarchar(50),
    CONSTRAINT PK_DBOperations PRIMARY KEY(OperationID)
);
GO

CREATE TRIGGER Personnel.ForCustomers
ON Transactions.Customers
AFTER INSERT
AS
BEGIN
    INSERT INTO Management.DatabaseOperations(ObjectName,
    				 PerformedBy, ActionPerformed)
    VALUES(N'Customers', SUSER_SNAME(), N'Processed a deposit')
END
GO
    Frank executes that code and it works fine.
    To test the database, Gary writes the following code to add a new record to the Customers table: 
    INSERT INTO Transactions.Customers(DateCreated, AccountNumber, CustomerName)
VALUES(N'02/20/2011', N'000-2860-3982', N'John Doe');
GO
    When he executes it, Gary receives an error. What do you think is the problem?
    1. Triggers don't need a schema
    2. Since neither a user nor a login was created in the code, it appears that Frank doesn't have the right permissions to the trigger
    3. In the code for the trigger, Frank must specify the schema of the Customers table as Management (as in Management.Customers)
    4. Both the Customers table and the ForCustomers triger must use the same schema
    5. The DatabaseOperations table called in the trigger must not have a primary key constraint
  29. Martha inherited the following table and its records 
    CREATE SCHEMA Listing;
GO
CREATE TABLE Listing.Apartments
(
	UnitNumber int not null,
	Bedrooms int,
	Bathrooms real,
	Price money,
	Deposit money,
	Available bit
);
GO
INSERT Listing.Apartments
VALUES(104, 2, 1.00, 1050.00, 300.00, 0),
      (306, 3, 2.00, 1350.00, 425.00, 1),
      (105, 1, 1.00, 885.00,  250.00, 1),
      (202, 1, 1.00, 950.00,  325.00, 0),
      (304, 2, 2.00, 1250.00, 300.00, 0),
      (106, 3, 2.00, 1350.00, 425.00, 1),
      (308, 0, 1.00, 875.00,  225.00, 1),
      (203, 1, 1.00, 885.00,  250.00, 1),
      (204, 2, 2.00, 1125.00, 425.00, 1),
      (205, 1, 1.00, 1055.00, 350.00, 0);
GO
    The documentation of the database indicates that the first symbol of a unit number indicates the floor level. What two codes would let Martha see the list of apartments from the second floor? 
    1. SELECT *
FROM Units
WHERE UnitNumber IN N'200' UP;
GO
    2. SELECT ALL *
FROM Units
WHERE UnitNumber LIKE N'2%';
GO
    3. SELECT ALL *
FROM Units
WHERE UnitNumber FROM 200 TO 299;
GO
    4. SELECT ALL *
FROM Units
WHERE UnitNumber BETWEEN N'200' AND N'300';
GO
    5. SELECT ALL *
FROM UnitNumber
FROM 200 TO 300;
GO
  30. Ann has been asked to create a table of employees. The table must contain a column for hourly salaries and, during data entry, the table must reject any salary lower than 12.50. What code can she use to take care of this (Select Two)? 
    1. CREATE TABLE Employees
(
	EmployeeNumber nchar(60) not null,
	[Full Name] nvarchar(50),
	HourlySalary money CHECK(HourlySalary !< 12.50),
	Department nchar(4)
);
GO
    2. CREATE TABLE Employees
(
	EmployeeNumber nchar(60) not null,
	[Full Name] nvarchar(50),
	HourlySalary money CONSTRAINT CHECK(HourlySalary >= 12.50),
	Department nchar(4)
);
GO
    3. CREATE TABLE Employees
(
	EmployeeNumber nchar(60) not null,
	[Full Name] nvarchar(50),
	HourlySalary money,
	Department nchar(4),
	CONSTRAINT CK_HourlySalary CHECK (HourlySalary >= 12.50)
);
GO
    4. CREATE TABLE Employees
(
	EmployeeNumber nchar(60) not null,
	[Full Name] nvarchar(50),
	HourlySalary money,
	Department nchar(4),
	CONSTRAINT CK_HourlySalary CHECK ON (HourlySalary >= 12.50)
);
GO
    5. CREATE TABLE Employees
(
	EmployeeNumber nchar(60) not null,
	[Full Name] nvarchar(50),
	HourlySalary money,
	Department nchar(4),
	CONSTRAINT ON HourlySalary FOR CHECK(HourlySalary >= 12.50)
);
GO
  31. Annouar has the following tables and their records: 
    CREATE TABLE Departments
(
	Code nchar(4),
	Name nvarchar(40)
);
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(6) not null unique,
	[Full Name] nvarchar(50),
	HourlySalary money,
	Department nchar(4)
);
GO

INSERT Departments
VALUES(N'HMNR', N'Human Resources'), (N'ACNT', N'Accounting'),
      (N'RNDV', N'Research & Development');
GO
INSERT Employees
VALUES(N'60-224', N'Frank Roberts', 20.15, N'RNDV'),
      (N'29-742', N'Marcial Engolo', 12.58, N'HMNR'),
      (N'82-073', N'John Duchant', 22.75, N'RNDV'),
      (N'82-503', N'Marthe Mengue', 15.86, N'ACNT'),
      (N'47-582', N'Hervey Arndt', 12.24, N'HMNR'),
      (N'82-263', N'Mark Edwards', 18.14, N'RNDV');
GO
    He is using the following code to get some statistics about the employees: 
    SELECT MAX(HourlySalary)
FROM Employees empls
WHERE empls.Department = N'RNDV';
GO
    What will that code produce?
    1. 20.15
    2. 12.58
    3. 22.75
    4. 15.86
    5. 18.14
  32. Benjamin has the followng tables and their records: 
    CREATE TABLE Departments
(
	Code nchar(4),
	Name nvarchar(40)
);
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(6) not null unique,
	[Full Name] nvarchar(50),
	Department nchar(4)
);
GO

INSERT Departments
VALUES(N'HMNR', N'Human Resources'), (N'ACNT', N'Accounting'),
      (N'RNDV', N'Research & Development');
GO
INSERT Employees
VALUES(N'60-224', N'Frank Roberts', N'RNDV'),
      (N'29-742', N'Marcial Engolo', N'HMNR'),
      (N'82-073', N'John Duchant', N'RNDV'),
      (N'82-503', N'Marthe Mengue', N'ACNT'),
      (N'47-582', N'Hervey Arndt', N'HMNR'),
      (N'82-263', N'Mark Edwards', N'RNDV');
GO
    He wants to know the number of employees in the human resources department. Which one of the following codes can he use?
    1. SELECT ALL COUNT(*) AS [# of Employees] FROM Employees empls
WHERE empls.Department = N'HMNR';
GO 
    2. SELECT ALL COUNT(*) FROM Employees empls AS [# of Employees]
WHERE empls.Department = N'HMNR';
GO 
    3. SELECT FROM Employees empls ALL COUNT(*) AS [# of Employees]
WHERE empls.Department = N'HMNR';
GO 
    4. SELECT COUNT(*) AS [# of Employees]
WHERE empls.Department = N'HMNR'
FROM Employees empls;
GO 
    5. SELECT WHERE empls.Department = N'HMNR'
COUNT(*) AS [# of Employees] FROM Employees empls;
GO
  33. Peter has the following table and its records: 
    CREATE TABLE Employees
(
	EmployeeNumber nchar(6) not null unique,
	[Full Name] nvarchar(50),
	HourlySalary money
);
GO

INSERT Employees
VALUES(N'60-224', N'Frank Roberts', 12.48),
      (N'29-742', N'Marcial Engolo', 30.15),
      (N'82-073', N'John Duchant', 8.75),
      (N'82-503', N'Marthe Mengue', 14.85),
      (N'47-582', N'Hervey Arndt', 10.06),
      (N'82-263', N'Mark Edwards', 25.55);
GO
    He wants to get the highest salary. What code can he use? 
    1. SELECT FROM Employees empls MAX(HourlySalary) AS [Highest Salary];
GO
    2. SELECT FROM Employees empls HIGH(HourlySalary) AS [Highest Salary];
GO
    3. SELECT MAX(HourlySalary) AS [Highest Salary] FROM Employees empls;
GO
    4. SELECT HIGH(HourlySalary) AS [Highest Salary] FROM Employees empls;
GO
    5. SELECT MAX(HourlySalary) FROM Employees empls AS [Highest Salary];
GO
  34. Frank has various employees in different departments created in the following Departments and Employees tables: 
    CREATE TABLE Departments
(
	Code nchar(4),
	Name nvarchar(40)
);
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(6) not null unique,
	[Full Name] nvarchar(50),
	HourlySalary money,
	Department nchar(4)
);
GO

INSERT Departments
VALUES(N'HMNR', N'Human Resources'), (N'ACNT', N'Accounting'),
      (N'RNDV', N'Research & Development');
GO
INSERT Employees
VALUES(N'60-224', N'Frank Roberts', 20.15, N'RNDV'),
      (N'29-742', N'Marcial Engolo', 12.58, N'HMNR'),
      (N'82-073', N'John Duchant', 22.75, N'RNDV'),
      (N'66-080', N'Mark Roberts', 10.12, N'HMNR'),
      (N'82-503', N'Marthe Mengue', 15.86, N'ACNT'),
      (N'47-582', N'Hervey Arndt', 12.24, N'HMNR'),
      (N'82-263', N'Mark Edwards', 18.14, N'RNDV');
GO
    He wants to find the highest salary in the human resources department. What code can he use to get it? 
    1. SELECT MAX(HourlySalary)
FROM Employees empl INNER JOIN Departments dept
ON empl.Department = dept.Code
WHERE dept.Code = N'RNDV';
GO
    2. SELECT MAX(HourlySalary)
FROM Employees empl INNER JOIN Departments dept
ON empl.Department = dept.Code
WHERE dept.Code = N'HMNR';
GO
    3. SELECT MAX(HourlySalary)
FROM Employees empl CROSS OUTER JOIN Departments dept
ON empl.Department = dept.Code
WHERE dept.Code = N'HMNR';
GO
    4. SELECT HIGH(HourlySalary)
FROM Employees empl INNER JOIN Departments dept
ON empl.Department = dept.Code
WHERE NOT dept.Code = N'RNDV';
GO
    5. SELECT sp_high(HourlySalary)
FROM Employees empl JOIN Departments dept
ON empl.Department = dept.Code
WHERE dept.Code = N'HMNR';
GO
  35. Lynda has the following table and its records: 
    CREATE TABLE Employees
(
	EmployeeNumber nchar(6) not null unique,
	[Full Name] nvarchar(50),
	HourlySalary money,
	Manager nchar(6)
);
GO
INSERT Employees
VALUES(N'60-224', N'Frank Roberts', 20.15, NULL),
      (N'29-742', N'Marcial Engolo', 12.58, NULL),
      (N'82-073', N'John Duchant', 22.74, N'29-742'),
      (N'82-503', N'Marthe Mengue', 15.86, N'60-224'),
      (N'44-440', N'Paul Motto', 16.22, N'29-742'),
      (N'47-582', N'Hervey Arndt', 12.24, N'60-224'),
      (N'82-263', N'Mark Edwards', 18.14, N'29-742');
GO
    She wants to get the list of employees with the last column showing each employee's manager name. What code would produce that list? 
    1. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       empl.HourlySalary AS Salary,
       (SELECT mgr.[Full Name]
        FROM Employees mgr
        WHERE empl.Manager = mgr.EmployeeNumber) AS Manager
FROM Employees empl;
GO
    2. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       empl.HourlySalary AS Salary,
       (SELECT mgr.EmployeeNumber, mgr.[Full Name]
        FROM Employees mgr
        WHERE empl.Manager = mgr.EmployeeNumber) AS Manager
FROM Employees empl;
GO
    3. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       empl.HourlySalary AS Salary,
       (SELECT mgr.[Full Name]
        FROM Employees mgr
        WHERE empl.Manager = mgr.Manager) AS Manager
FROM Employees empl;
GO
    4. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       empl.HourlySalary AS Salary,
       (SELECT mgr.EmployeeNumber
        FROM Employees mgr
        WHERE empl.Manager = mgr.EmployeeNumber) AS Manager
FROM Employees empl;
GO
    5. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       empl.HourlySalary AS Salary,
       (SELECT mgr.[Full Name]
        FROM Employees mgr
        WHERE empl.[Full Name] = mgr.[Full Name]) AS Manager
FROM Employees empl;
GO
  36. Janice has the following table and its records: 
    CREATE TABLE Employees
(
	EmployeeNumber nchar(6) not null unique,
	[Full Name] nvarchar(50),
	HourlySalary money,
	Manager nchar(6)
);
GO
INSERT Employees
VALUES(N'60-224', N'Frank Roberts', 20.15, NULL),
      (N'29-742', N'Marcial Engolo', 12.58, NULL),
      (N'82-073', N'John Duchant', 22.74, N'29-742'),
      (N'82-503', N'Marthe Mengue', 15.86, N'60-224'),
      (N'44-440', N'Paul Motto', 16.22, N'29-742'),
      (N'47-582', N'Hervey Arndt', 12.24, N'60-224'),
      (N'82-263', N'Mark Edwards', 18.14, N'29-742');
GO
    She wants to get the list of employees with the last column showing each employee's manager name but the list must include only the employees who have a manager. What code would produce that list? 
    1. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       empl.HourlySalary AS Salary,
       (SELECT mgr.[Full Name]
        FROM Employees mgr
        WHERE empl.Manager = mgr.EmployeeNumber) AS Manager
FROM Employees empl
WHERE empl.Manager IS NOT NULL;
GO
    2. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       empl.HourlySalary AS Salary,
       (SELECT mgr.[Full Name]
        FROM Employees mgr
        WHERE (empl.Manager = mgr.EmployeeNumber)
        AND empl.Manager IS NOT NULL) AS Manager
FROM Employees empl;
GO
    3. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       empl.HourlySalary AS Salary,
       (SELECT mgr.[Full Name]
        FROM Employees mgr
        WHERE empl.Manager = mgr.Manager) AS Manager IS NOT NULL
FROM Employees empl;
GO
    4. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       empl.HourlySalary AS Salary,
       (SELECT mgr.EmployeeNumber
        FROM Employees mgr
        WHERE (empl.Manager = mgr.EmployeeNumber)
        AND empl.Manager IS NOT NULL) AS Manager
FROM Employees empl;
GO
    5. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       empl.HourlySalary AS Salary,
       (SELECT mgr.[Full Name] NOT NULL
        FROM Employees mgr
        WHERE empl.[Full Name] = mgr.[Full Name]) AS Manager
FROM Employees empl;
GO
  37. Ann created the following table of employees and filled it up with the necessary records as follows: 
    CREATE TABLE Employees
(
	EmployeeNumber nchar(6) not null unique,
	[Full Name] nvarchar(50),
	Manager nchar(6)
);
GO

INSERT Employees
VALUES(N'60-224', N'Frank Roberts', NULL),
      (N'29-742', N'Marcial Engolo', NULL),
      (N'82-073', N'John Duchant', N'29-742'),
      (N'82-503', N'Marthe Mengue', N'60-224'),
      (N'44-440', N'Paul Motto', N'29-742'),
      (N'47-582', N'Hervey Arndt', N'60-224'),
      (N'82-263', N'Mark Edwards', N'29-742');
GO
    To keep employee's personal information private, she created an additional table that contains employees salaries as follows: 
    CREATE TABLE Salaries
(
    SalaryID int identity(1, 1) primary key,
    EmployeeNumber nchar(6) not null,
    HourlySalary money not null,
);
GO

INSERT Salaries(EmployeeNumber, HourlySalary)
VALUES(N'60-224', 20.15),
      (N'29-742', 12.58),
      (N'82-073', 22.74),
      (N'82-503', 15.86),
      (N'44-440', 16.22),
      (N'47-582', 12.24),
      (N'82-263', 18.14);
GO
    Now, Ann wants to see the list of employees so that each record shows an employee's salary and his or her manager name. What code would produce that result? 
    1. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       (SELECT sal.SalaryID, sal.HourlySalary
        FROM Salaries sal
        WHERE empl.EmployeeNumber = sal.EmployeeNumber) AS Salary,
       (SELECT mgr.EmployeeNumber, mgr.[Full Name]
        FROM Employees mgr
        WHERE empl.Manager = mgr.EmployeeNumber) AS Manager
FROM Employees empl;
GO
    2. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       (SELECT sal.HourlySalary
        FROM Salaries sal
        WHERE empl.EmployeeNumber = sal.EmployeeNumber) AS Salary,
       (SELECT mgr.[Full Name]
        FROM Employees mgr
        WHERE empl.Manager = mgr.EmployeeNumber) AS Manager
FROM Employees empl;
GO
    3. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       (SELECT sal.HourlySalary
        FROM Salaries sal
        WHERE empl.EmployeeNumber = sal.SalaryID) AS Salary,
       (SELECT mgr.[Full Name]
        FROM Employees mgr
        WHERE empl.Manager = mgr.EmployeeNumber) AS Manager
FROM Employees empl;
GO
    4. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       (SELECT sal.EmployeeNumber
        FROM Salaries sal
        WHERE empl.EmployeeNumber = sal.EmployeeNumber) AS Salary,
       (SELECT mgr.EmployeeNumber
        FROM Employees mgr
        WHERE empl.SalaryID = mgr.SalaryID) AS Manager
FROM Employees empl;
GO
    5. SELECT empl.EmployeeNumber AS [Empl #],
       empl.[Full Name],
       (SELECT sal.EmployeeNumber
        FROM Salaries sal
        WHERE empl.EmployeeNumber = sal.EmployeeNumber) AS Salary,
       (SELECT mgr.EmployeeNumber
        FROM Employees mgr
        WHERE empl.Manager = mgr.EmployeeNumber) AS Manager
FROM Employees empl;
GO
  38. To monitor the base salaries of employees in his company, John's has a table named StartingSalaries as follows: 
    CREATE SCHEMA Personnel;
GO
CREATE TABLE Personnel.StartingSalaries
(
	Category nvarchar(30) not null,
	StartingSalary money null
);
INSERT INTO Personnel.StartingSalaries
VALUES(N'Base', 10.00),
      (N'Intern', 12.35),
      (N'Regular', 14.50),
      (N'Manager', 20.00);
GO
    John also has a table of employees as follows: 
    CREATE TABLE Personnel.Employees
(
    EmployeeID int identity(1,1) NOT NULL, 
    EmployeeNumber nchar(10),
    FirstName nvarchar(32),
    LastName nvarchar(32) NOT NULL,
    Title nvarchar(50),
    HourlySalary money,
    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
);
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName,
                                LastName, Title, HourlySalary)
VALUES(N'662-286', N'Lienev', N'Zbrnitz', N'Cashier', 15.75),
	  (N'487-525', N'Paulin', N'Guerrero', N'Intern', 10.85),
	  (N'395-138', N'Plant', N'Waste', N'Head Teller', 16.75),
	  (N'822-730', N'Steven', N'Chang', N'Intern', 14.15),
	  (N'930-717', N'Abedi', N'Kombo', N'Shift Leader', 10.02),
	  (N'573-048', N'Paul', N'Landsford', N'Cashier', 12.25);
GO
    Now, John wants to get a list of employees whose salary is lower than the intern's salary of the StartingSalaries table. What code can John use: 
    1. SELECT empl.*
FROM Personnel.Employees empl
WHERE empl.Title <= (SELECT Category
                     FROM Personnel.StartingSalaries sal);
GO
    2. SELECT empl.*
FROM Personnel.Employees empl
WHERE empl.HourlySalary <= (SELECT sal.Category
                            FROM Personnel.StartingSalaries sal
                            WHERE sal.StartingSalary = 12.35);
GO
    3. SELECT empl.*
FROM Personnel.Employees empl
WHERE empl.HourlySalary <= (SELECT sal.StartingSalary
                            FROM Personnel.StartingSalaries sal
                            WHERE sal.Category = N'Intern');
GO
    4. SELECT empl.EmployeeNumber,
       empl.FirstName,
       empl.LastName,
       empl.Title,
       (SELECT sal.StartingSalary
        FROM Personnel.StartingSalaries sal
        WHERE sal.Category = N'Intern')
FROM Personnel.Employees empl;
GO
    5. SELECT empl.EmployeeNumber,
       empl.FirstName,
       empl.LastName,
       empl.Title,
       (SELECT sal.Category
        FROM Personnel.StartingSalaries sal
        WHERE sal.StartingSalary = 12.3500)
FROM Personnel.Employees empl;
GO
  39. Eric has the following tables and their records: 
    CREATE TABLE Departments
(
	DepartmentCode nchar(6) not null primary key,
	DepartmentName nvarchar(50) not null
);
GO

INSERT INTO Departments
VALUES(N'HMRS', N'Human Resources'),
      (N'ACNT', N'Accounting'),
      (N'PSNL', N'Personnel'),
      (N'RSDV', N'Research & Development');
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	DepartmentCode nchar(6)
);
GO
INSERT INTO Employees
VALUES(N'283-947', N'Timothy', N'White', N'RSDV'),
      (N'572-384', N'Jeannette', N'Welch', N'PSNL'),
      (N'279-242', N'Ann', N'Welch', N'HMRS'),
      (N'495-728', N'Robert', N'Simms', N'RSDV'),
      (N'382-505', N'Paula', N'Waters', N'PSNL'),
      (N'268-046', N'Martine', N'Nyamoto', N'ACNT'),
      (N'773-148', N'James', N'Larsen', N'RSDV'),
      (N'958-057', N'Peter', N'Aut', N'HMRS');
GO
    He wants to move all employees from the personnel department to the human resources department. Which of the following codes would produce the right result? 
    1. UPDATE Employees
SET    DepartmentCode = N'PSNL'
WHERE  DepartmentCode = N'HMRS';
GO
    2. UPDATE Employees
SET    DepartmentCode = N'HMRS'
WHERE  DepartmentCode = N'PSNL';
GO
    3. UPDATE FROM Employees
SET    DepartmentCode = N'PSNL'
WHERE  DepartmentCode = N'HMRS';
GO
    4. SET    Employees
UPDATE DepartmentCode = N'HMRS'
WHERE  DepartmentCode = N'PSNL';
GO
    5. SET    Employees
UPDATE DepartmentCode = N'PSNL'
WHERE  DepartmentCode = N'HMRS';
GO
  40. You have a table named ObsoleteProducts and you are asked to remove all of its records. What code would you use to do it? 
    1. DROP ObsoleteProducts;
    2. REMOVE ALL * FROM ObsoleteProducts;
    3. DELETE * FROM ObsoleteProducts;
    4. DELETE ObsoleteProducts;
    5. EXECUTE sp_removerecords FROM ObsoleteProducts;
  41. Consider the following table of employees: 
    CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	DepartmentCode nchar(6)
);
    Imagine you want to provide your users with a view that shows the employees numbers and their name. What code can you use to create such a view? 
    1. CREATE VIEW EmployeesRecords
BEGIN
	SELECT empl.EmployeeNumber, empl.FirstName, empl.LastName 
	FROM Employees empl;
END
    2. CREATE VIEW EmployeesRecords
AS
	SELECT empl.EmployeeNumber, empl.FirstName, empl.LastName 
	FROM Employees empl;
    3. CREATE VIEW EmployeesRecords
RETURN TABLE
AS
	SELECT empl.EmployeeNumber, empl.FirstName, empl.LastName 
	FROM Employees empl;
    4. CREATE VIEW EmployeesRecords1
WITH SELECT empl.EmployeeNumber, empl.FirstName, empl.LastName 
     FROM Employees empl;
END
    5. EXECUTE CREATE EmployeesRecords1 AS VIEW
    SELECT empl.EmployeeNumber, empl.FirstName, empl.LastName 
    FROM Employees empl;
  42. Imagine you have the following table: 
    CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	DepartmentCode nchar(6)
);
GO
    Now you want to change the table to add a column named HourlySalary of type money. What code would you use to do that? 
    1. UPDATE TABLE Employees
BEGIN
    ADD HourlySalary money;
END
    2. ALTER TABLE Employees ADD HourlySalary money;
    3. EXECUTE MODIFY TABLE Employees
AS
    ADD HourlySalary money;
END
    4. EXECUTE sp_change TABLE Employees
AS
BEGIN
    ADD HourlySalary money;
END
    5. ALTER TABLE Employees
AS
BEGIN
    ADD COLUMN HourlySalary money;
END
  43. You inherited a table that was created as follows: 
    CREATE TABLE Employees
(
    FirstName nvarchar(20),
    LastName nvarchar(20),
    DepartmentCode nchar(6)
);
GO
    Now you want to add a primary key column named EmployeeNumber of type int. What two codes can you use to perform that operation? 
    1. WITH Employees
    SET EmployeeNumber int not null PRIMARY KEY(EmployeeNumber);
GO
    2. ALTER TABLE Employees
    ADD EmployeeNumber int not null PRIMARY KEY;
GO
    3. ALTER TABLE Employees
    ADD EmployeeNumber int not null
    CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber);
GO
    4. UPDATE TABLE Employees
    ADD COLUMN EmployeeNumber int not null PRIMARY KEY(EmployeeNumber);
GO
    5. ALTER TABLE Employees
    ADD EmployeeNumber int not null
    ALTER EmployeeNumber AS PRIMARY KEY(EmployeeNumber);
GO
  44. Mark has a table of employees and it contains a column named LastName. Mark wants to get a list of employees whose last name ends with the letter a. What code can he use? 
    1. SELECT ALL * FROM Employees
WHERE LastName LIKE N'%a';
GO
    2. SELECT ALL * FROM Employees
WHERE LastName LIKE N'_a';
GO
    3. SELECT ALL * FROM Employees
WHERE LastName LIKE N'[a]';
GO
    4. SELECT * FROM Employees
WHERE LastName LIKE N'[^a]';
GO
    5. SELECT ALL * FROM Employees
WHERE LastName LIKE N'!a';
GO
  45. Alex has the following table named Products: 
    CREATE TABLE Products
(
    ItemNumber int primary key,
    Name nvarchar(50),
    UnitPrice money,
    Discount decimal(4, 2)
);
    Many records have been added to the table. Some records have a value for the discount and some do not. To get a list of products that do not have a discount, what code can Alex use? 
    1. SELECT ItemNumber, Name, UnitPrice, Discount
FROM Products
WHERE Discount = 0;
GO
    2. SELECT ItemNumber, Name, UnitPrice, Discount
FROM Products
WHERE Discount = NULL;
GO
    3. SELECT *
FROM Products
WHERE Discount <> 0;
    4. SELECT *
FROM Products
WHERE Discount NULL;
    5. SELECT ItemNumber, Name, UnitPrice, Discount
FROM Products
WHERE Discount IS NULL;
  46. Mark is creating a view based on an Employees table. He wants the statement that creates the view to be scrambled when added to the database. What code can mark use to do this? 
    1. CREATE VIEW Payroll
SET ENCRYPTION ON
AS
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary
    FROM Employees
GO
    2. CREATE VIEW Payroll
AS
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary
    FROM Employees;
    ENCRYPT WHEN DONE
GO
    3. CREATE VIEW Payroll
WITH ENCRYPTION
AS
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary
    FROM Employees
GO
    4. CREATE VIEW Payroll
AS
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary
    FROM Employees;
    WITH ENCRYPTION
GO
    5. CREATE VIEW Payroll
AS
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary
    FROM Employees
    SET ENCRYPTION ON
GO
  47. You want to allow an employee whose login name is Peter to be able to change the design or code of a table named Employees of the Personnel schema. What code would allow you to do that? 
    1. SELECT ALL USERS FROM Personnel.Employees
GRANT ALTER TO Peter;
    2. GRANT ALTER
ON OBJECT::Personnel.Employees
TO Peter;
    3. EXECUTE sp_grant(ALTER)
TO Peter
ON OBJECT::Personnel.Employees;
    4. GRANT PERMISSION ALTER
TO Peter
ON OBJECT::Personnel.Employees;
    5. SET PERMISSION ALTER
ON OBJECT::Personnel.Employees
TO Peter;
  48. Jimmy has a table named Employees created in a schema named Personnel. He wants to create a view named Payroll in the same schema but he wants to make sure that no change in the Employees table is allowed if that change would affect the Payroll view. What code can he use to accomplish that? 
    1. CREATE VIEW Personnel.Payroll
DO SCHEMA BINDING
AS
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary
    FROM Personnel.Employees
GO
    2. CREATE VIEW Personnel.Payroll
AS
    WITH SCHEMA BINDING
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary
    FROM Personnel.Employees
GO
    3. CREATE VIEW Personnel.Payroll
FOR SCHEMABINDING
AS
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary
    FROM Personnel.Employees
GO
    4. CREATE VIEW Personnel.Payroll
WITH SCHEMABINDING
AS
	SELECT EmployeeNumber, FirstName, LastName, HourlySalary
    FROM Personnel.Employees
GO
    5. CREATE VIEW Personnel.Payroll
AS
    SELECT EmployeeNumber, FirstName, LastName, HourlySalary
    FROM Personnel.Employees
    SET SCHEMA BINDING ON
GO
  49. Harriett has the following table of employees: 
    CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(7) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	EmploymentStatus smallint,
	HourlySalary money
);
GO
INSERT Personnel.Employees
VALUES(N'284-680', N'Anselme', N'Bongos', 2, 18.62),
      (N'730-704', N'June', N'Malea', 1, 9.95);
      (N'735-407', N'Frank', N'Monson', 3, 14.58),
      (N'281-730', N'Jerry', N'Beaulieu', 1, 16.65);
GO
    Harriett also has the following table of sales made by employees during a certain period: 
    CREATE TABLE Commercial.Sales
(
	SaleID int identity(1, 1),
	EmployeeNumber nchar(7) not null,
	SaleDate date,
	Amount money
);
GO
INSERT INTO Commercial.Sales(EmployeeNumber, SaleDate, Amount)
VALUES(N'284-680', N'2011-02-14', 4250),
      (N'735-407', N'2011-02-14', 5300),
      (N'730-704', N'2011-02-14', 2880),
      (N'281-730', N'2011-02-14', 4640),
      (N'284-680', N'2011-02-15', 4250),
      (N'281-730', N'2011-02-15', 3675),
      (N'735-407', N'2011-02-15', 3420),
      (N'730-704', N'2011-02-15', 3675),
      (N'284-680', N'2011-02-16', 5500),
      (N'281-730', N'2011-02-16', 2675),
      (N'735-407', N'2011-02-16', 4400),
      (N'730-704', N'2011-02-16', 2605);
GO
    Now, Harriett wants to see the highest sales made by employees but the list must include only employees who sold over 4750. What code can she use? 
    1. SELECT LastName + N', ' + FirstName, MAX(Amount)
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
     cs.EmployeeNumber = pe.EmployeeNumber
HAVING MAX(Amount) > 4750.00;
GROUP BY LastName + N', ' + FirstName
GO
    2. SELECT LastName + N', ' + FirstName, MAX(Amount)
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
     cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY LastName + N', ' + FirstName
HAVING SUM(Amount) > 4750.00;
GO
    3. SELECT LastName + N', ' + FirstName, MAX(Amount)
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
     cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY LastName + N', ' + FirstName
HAVING MAX(Amount) > 4750.00;
GO
    4. SELECT LastName + N', ' + FirstName, MAX(Amount)
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
     cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY LastName + N', ' + FirstName
WHERE Amount > 4750.00;
GO
    5. SELECT LastName + N', ' + FirstName, MAX(Amount)
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
	 cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY LastName + N', ' + FirstName
HAVING MAX(Amount) > 4750.00
WHERE Amount IS NOT NULL;
GO
  50. Charles has the following table of employees: 
    CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(7) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	EmploymentStatus smallint,
	HourlySalary money
);
GO
INSERT Personnel.Employees
VALUES(N'284-680', N'Anselme', N'Bongos', 2, 18.62),
      (N'730-704', N'June', N'Malea', 1, 9.95);
      (N'735-407', N'Frank', N'Monson', 3, 14.58),
      (N'281-730', N'Jerry', N'Beaulieu', 1, 16.65);
GO
    He also has the following table of sales made by employees during a certain period: 
    CREATE TABLE Commercial.Sales
(
	SaleID int identity(1, 1),
	EmployeeNumber nchar(7) not null,
	SaleDate date,
	Amount money
);
GO
INSERT INTO Commercial.Sales(EmployeeNumber, SaleDate, Amount)
VALUES(N'284-680', N'2011-02-14', 4250),
      (N'735-407', N'2011-02-14', 5300),
      (N'730-704', N'2011-02-14', 2880),
      (N'281-730', N'2011-02-14', 4640),
      (N'284-680', N'2011-02-15', 4250),
      (N'281-730', N'2011-02-15', 3675);
GO
    Charles wants to get the list employees so that each record shows the first name, the last name and the numer of sales the employee made. The list must show the number of sales in incremental order. What code can he use? 
    1. SELECT pe.LastName [First Name], pe.FirstName [Last Name],
       COUNT(cs.Amount) AS [Number of Sales]
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
	 cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY pe.LastName, pe.FirstName
ORDER BY [Number of Sales];
    2. SELECT pe.LastName [First Name], pe.FirstName [Last Name],
       COUNT(*) AS [Number of Sales]
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
	 cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY Amount
ORDER BY Amount;
    3. SELECT pe.LastName AS [First Name], pe.FirstName AS [Last Name],
       COUNT(cs.Amount) AS [Number of Sales]
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
	 cs.EmployeeNumber = pe.EmployeeNumber
GROUP BY cs.Amount
HAVING   cs..Amount
ORDER BY cs.Amount;
    4. SELECT pe.LastName AS [First Name], pe.FirstName AS [Last Name],
       COUNT(*) AS [Number of Sales]
GROUP BY cs.Amount
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
	 cs.EmployeeNumber = pe.EmployeeNumber
ORDER BY cs.Amount;
    5. SELECT pe.LastName AS [First Name], pe.FirstName AS [Last Name],
       COUNT(cs.Amount) AS [Number of Sales]
ORDER BY cs.Amount
GROUP BY cs.Amount
FROM Commercial.Sales cs JOIN
     Personnel.Employees pe ON
     cs.EmployeeNumber = pe.EmployeeNumber;
  51. Imagine you have the following table: 
    CREATE TABLE Contractors
(
	EmployeeID int identity(1, 1),
	FullName nvarchar(50),
	Wage money
);
    Imagine you want to rename the last column from Wage to HourlySalary. What code would let you do that?
    1. RENAME COLUMN N'Contractors.Wage' AS N'Contractors.HourlySalary';
    2. RENAME SET N'Contractors.Wage' AS N'Contractors.HourlySalary';
    3. sp_rename N'Contractors.Wage', N'HourlySalary', N'COLUMN';
    4. EXECUTE sp_changename COLUMN N'Contractors.Wage' TO N'HourlySalary'
    5. RENAME N'Wage' TO N'HourlySalary' FROM Contractors;
  52. Imagine you have a table named Employees that includes a column named EmploymentStatus but that column has become useless. What code can you use to delete that column? 
    1. ALTER TABLE Contractors
DELETE COLUMN EmploymentStatus;
GO
    2. ALTER TABLE Contractors
SET EmploymentStatus = NULL;
GO
    3. UPDATE TABLE Contractors
DELETE EmploymentStatus;
GO
    4. ALTER TABLE Contractors
DROP COLUMN EmploymentStatus;
GO
    5. UPDATE TABLE Contractors
SET EmploymentStatus = NULL;
GO
  53. John has a table named Employees and another table named Products. The tables were created using the following code: 
    CREATE TABLE Employees
(
    [Empl #] nchar(7),
    [First Name] nvarchar(20),
    [Last Name] nvarchar(20),
    [Hourly Salary] money
);
GO
CREATE TABLE Products
(
    Number int,
    Name nvarchar(50),
    UnitPrice money,
);
GO

INSERT INTO Employees
VALUES(N'207-025', N'Julie', N'Flanell', 36.55),
      (N'926-705', N'Paulette', N'Simms', 26.65),
      (N'240-002', N'Alexandra', N'Ulm', 12.85),
      (N'847-295', N'Ellie', N'Tchenko', 11.95);
GO
INSERT INTO Products
VALUES(217409, N'Short Black Skirt', 55.85),
      (790279, N'Classic Fit Pinpoint Shirt', 82.00),
      (284001, N'Pencil Skirt', 49.00);
GO
    John wants to see a list of employees mixed with products. The list should include only products that cost more than 50.00. What code can he use? 
    1. SELECT [Empl #], [First Name], [Last Name], Name, UnitPrice
FROM Employees, Products
WHERE UnitPrice > 50;
GO
    2. SELECT empl.[Empl #], empl.[First Name], empl.[Last Name],
       empl.Name, DISTINCT(empl.UnitPrice)
FROM Employees empl, Products prod
WHERE empl.UnitPrice > 50;
GO
    3. SELECT empl.[Empl #], empl.[First Name], empl.[Last Name],
       prod.Name, prod.UnitPrice
FROM Employees empl INNER JOIN Products prod
ON empl.[Empl #] = prod.Number
HAVING prod.UnitPrice > 50;
GO
    4. SELECT empl.[Empl #], empl.[First Name], empl.[Last Name],
       prod.Name, prod.UnitPrice
FROM Employees empl JOIN Products prod
ON empl.[Empl #] = prod.Number
GROUP BY prod.UnitPrice
HAVING prod.UnitPrice > 50;
GO
    5. SELECT empl.[Empl #], empl.[First Name], empl.[Last Name],
       prod.Name, prod.UnitPrice
FROM Employees empl, Products prod
HAVING prod.UnitPrice > 50;
GO
  54. Ashley has a table of employees created as follows: 
    CREATE TABLE Employees
(
  EmployeeNumber nchar(7),
  [First Name] nvarchar(20),
  [Last Name] nvarchar(20),
  [Hourly Salary] money
);
GO
    She wants to create a table named Sales so that each record in the Sales table indicates the employee who made the sale. What two codes can she use to create the new table? 
    1. CREATE TABLE Sales
(
    SaleID int IDENTITY(1, 1) PRIMARY KEY,
    SaleDate date,
    EmployeeNumber nchar(7),
    ProductName nvarchar(60),
    UnitPrice money,
    CONSTRAINT FK_Employees FOREIGN KEY REFERENCES Employees(EmployeeNumber)
);
    2. CREATE TABLE Sales
(
    SaleID int IDENTITY(1, 1) PRIMARY KEY,
    SaleDate date,
    EmployeeNumber nchar(7) FOREIGN KEY REFERENCES Employees(EmployeeNumber),
    ProductName nvarchar(60),
    UnitPrice money,
);
    3. CREATE TABLE Sales
(
    SaleID int IDENTITY(1, 1) PRIMARY KEY,
    SaleDate date,
    EmployeeNumber nchar(7)
       CONSTRAINT FK_Employees FOREIGN KEY REFERENCES Employees(EmployeeNumber),
    ProductName nvarchar(60),
    UnitPrice money,
    
);
    4. CREATE TABLE Sales
(
    SaleID int IDENTITY(1, 1) PRIMARY KEY,
    SaleDate date,
    EmployeeNumber nchar(7)
       CONSTRAINT FOREIGN KEY REFERENCES Employees(EmployeeNumber),
    ProductName nvarchar(60),
    UnitPrice money,
    
);
    5. CREATE TABLE Sales
(
    SaleID int IDENTITY(1, 1) PRIMARY KEY,
    SaleDate date,
    EmployeeNumber nchar(7),
    ProductName nvarchar(60),
    UnitPrice money,
    CREATE FOREIGN KEY WITH EmployeeNumber FROM Employees
    
);
  55. Imagine you have an index named IDX_Customers on a table named Customers but you don't need that index anymore. What code can you use to delete that index? 
    1. DROP INDEX IDX_Customers ON Customers(AccountNumber);
    2. DELETE INDEX IDX_Customers ON Customers;
    3. DROP INDEX IDX_Customers FROM Customers;
    4. DROP INDEX IDX_Customers ON Customers;
    5. DELETE INDEX IDX_Customers AccountNumber FROM Customers;
  56. Jason had created two tables as follows: 
    CREATE TABLE Employees
(
  EmployeeNumber nchar(7) PRIMARY KEY,
  FirstName nvarchar(20),
  LastName nvarchar(20),
  HourlySalary money
);
GO
CREATE TABLE Sales
(
    SaleID int IDENTITY(1, 1) PRIMARY KEY,
    SaleDate date,
    ProductName nvarchar(60),
    UnitPrice money
);
GO
    Now he wants to add a column to the Sales table so that the new field gets its values from the Employees table. How can he write code to do that? 
    1. ALTER TABLE Sales
ADD FOREIGN KEY EmployeeNumber nchar(7) FOR Employees(EmployeeNumber);
    2. UPDATE TABLE Sales
ADD COLUMN EmployeeNumber nchar(7) FOREIGN KEY Employees(EmployeeNumber);
GO
    3. CHANGE TABLE Sales
ADD EmployeeNumber nchar(7) FOREIGN KEY Employees(EmployeeNumber);
GO
    4. UPDATE TABLE Sales
ADD FOREIGN KEY AS EmployeeNumber nchar(7)
	REFERENCES EmployeeNumber FROM Employees;
    5. ALTER TABLE Sales
ADD EmployeeNumber nchar(7) FOREIGN KEY REFERENCES Employees(EmployeeNumber);
  57. Jimmy wants to create a custom data type named Integer and based on the int data type. How can he write to accomplish that? 
    1. CREATE DATATYPE Integer FROM int;
    2. EXECUTE sp_createtype Integer AS Natural;
    3. CREATE TYPE SET Integer = int;
    4. CREATE OBJECT:TYPE Integer FROM int;
    5. CREATE TYPE Integer FROM int;
  58. In order to rightly perform data analysis on a database with international names, Jimmy wants to add a flag on a string-based column. What code can he use to take or latin-based characters? 
    1. CREATE TABLE Customers
(
    CustomerID int identity(1, 1),
    FullName nvarchar(50),
    PhoneNumber nvarchar(20)
)
WITH COLLATE SQL_Latin1_General_CP1_CI_AS;
    2. WITH COLLATE SQL_Latin1_General_CP1_CI_AS
BEGIN
CREATE TABLE Customers
(
    CustomerID int identity(1, 1),
    FullName nvarchar(50),
    PhoneNumber nvarchar(20)
)
END;
    3. CREATE TABLE Customers
(
    CustomerID int identity(1, 1),
    FullName nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
    PhoneNumber nvarchar(20)
);
    4. CREATE TABLE Customers
(
    CustomerID int identity(1, 1),
    FullName nvarchar(50) WITH COLLATE SQL_Latin1_General_CP1_CI_AS,
    PhoneNumber nvarchar(20)
);
    5. CREATE TABLE Customers
(
    CustomerID int identity(1, 1),
    FullName nvarchar(50),
    PhoneNumber nvarchar(20),
    CONSTRAINT CT_Customers COLLATE SQL_Latin1_General_CP1_CI_AS
);
  59. Michael has a database with a table named Customers. He created a user named James Galvin and he wants to give him the ability to create new records or update existing records on that table. What code can he use? 
    1. GRANT INSERT, UPDATE
ON OBJECT::Sales.Customers
TO [James Galvin];
GO
    2. GRANT INSERT, UPDATE
TO [James Galvin];
ON OBJECT::Sales.Customers
GO
    3. SET GRANT INSERT, UPDATE
ON OBJECT::Sales.Customers
TO N'James Galvin';
GO
    4. GRANT INSERT AND UPDATE
FOR OBJECT::Sales.Customers
TO [James Galvin];
GO
    5. WITH GRANT INSERT, UPDATE
SET OBJECT::Sales.Customers
TO [James Galvin];
GO
  60. Imagine you have already created a login name for an employee as jpalau. Now you that employee to be able to create databases on the server. What code would you use to give that right? 
    1. GRANT CREATE DATABASES
TO jpalau;
    2. FOR ANY DATABASE
GRANT CREATE TO jpalau;
    3. WITH OBJECT::jpalau
GRANT CREATE ANY DATABASE;
    4. GRANT CREATE ANY DATABASE
TO jpalau;
    5. EXECUTE CREATE ANY DATABASE
FOR USER::jpalau;
  61. Mike has already created two login names as gsanders and hnorm for two employees. He wants to give each the ability to create databases on the server and be able to change the login account of other employees. What code can he use to take care of that? 
    1. WITH gsanders, hnorm
GRANT CREATE ANY DATABASE, ALTER ANY LOGIN;
GO
    2. GRANT CREATE ANY DATABASE, ALTER ANY LOGIN
TO gsanders, hnorm;
GO
    3. GRANT PERMISSION::CREATE ANY DATABASE, PERMISSION::ALTER ANY LOGIN
TO gsanders, hnorm;
GO
    4. GRANT PERMISSION(CREATE ANY DATABASE, ALTER ANY LOGIN)
TO OBJECT::gsanders;
GO

GRANT PERMISSION(CREATE ANY DATABASE, ALTER ANY LOGIN)
TO OBJECT::hnorm;
GO
    5. EXECUTE GRANT PERMISSION
SET CREATE ANY DATABASE = TRUE
SET ALTER ANY LOGIN = TRUE
TO OBJECT::gsanders;
GO

EXECUTE GRANT PERMISSION
SET CREATE ANY DATABASE = TRUE
SET ALTER ANY LOGIN = TRUE
TO OBJECT::hnorm;
GO
  62. You have a table of products that was created as follows: 
    CREATE TABLE Products
(
    ProductCode nchar(6) not null,
    Name nvarchar(50) not null,
    UnitPrice money not null,
    CONSTRAINT PK_Products PRIMARY KEY(ProductCode)
);
    When you add a few records, you want to immediately see the list of records that were added. What example of code would do that? 
    1. INSERT Products
VALUES(N'293804', N'Mid Lady Bag - Lizard', 228),
      (N'400571', N'Holly Gladiator Heel Shoes', 198)
OUTPUT inserted.*;
    2. INSERT Products
VALUES(N'293804', N'Mid Lady Bag - Lizard', 228),
      (N'400571', N'Holly Gladiator Heel Shoes', 198)
SHOW OUTPUT;
    3. INSERT Products
OUTPUT inserted.*
VALUES(N'293804', N'Mid Lady Bag - Lizard', 228),
      (N'400571', N'Holly Gladiator Heel Shoes', 198);
    4. INSERT Products
SELECT VALUES(N'293804', N'Mid Lady Bag - Lizard', 228),
      (N'400571', N'Holly Gladiator Heel Shoes', 198);
    5. WHEN INSERT Products
VALUES(N'293804', N'Mid Lady Bag - Lizard', 228),
      (N'400571', N'Holly Gladiator Heel Shoes', 198)
GO TO OUTPUT INSERT;
  63. Barbara is creating a table of products that will contain columns named ProductID, Name, and UnitPrice. The value for the product number must be an integer automatically generated by the database engine, the name will be a string with a maximum of 50 characters, and the unit price will be set to the local currency. What code can she use to create the table? 
    1. CREATE TABLE Products
(
    ProductID int AUTONUMBER,
    Name nvarchar(50),
    UnitPrice money
);
GO
    2. CREATE TABLE Products
(
    ProductID int COUNTER,
    Name nvarchar(50),
    UnitPrice money
);
GO
    3. CREATE TABLE Products
(
    ProductID int AUTOINCREMENT,
    Name nvarchar(50),
    UnitPrice money
);
GO
    4. CREATE TABLE Products
(
    ProductID int IDENTITY(1, 1),
    Name nvarchar(50),
    UnitPrice money
);
GO
    5. CREATE TABLE Products
(
    ProductID int,
    Name nvarchar(50),
    UnitPrice money,
    CONSTRAINT AI_Products IDENTITY(ProductID)
);
GO
  64. Diane created a table of products that includes a column named ProductID that automatically gets its values from the database engine. The table was created as follows: 
    CREATE TABLE Products
(
    ProductID int identity(1, 1),
    Name nvarchar(50),
    UnitPrice money
);
GO
    Diane wants to add a new record but she wants to specify the product number. What code can she use to create the table? 
    1. SET IDENTITY_INSERT Products ON;
GO
INSERT Products(ProductID, Name, UnitPrice)
VALUES(1002, N'Mid Lady Bag - Lizard', 228),
      (84, N'Holly Gladiator Heel Shoes', 198),
      (519, N'Short Black Skirt', 55.85);
GO
    2. WITH IDENTITY_INSERT ON Products;
GO
INSERT Products(ProductID, Name, UnitPrice)
VALUES(1002, N'Mid Lady Bag - Lizard', 228),
      (84, N'Holly Gladiator Heel Shoes', 198),
      (519, N'Short Black Skirt', 55.85);
GO
    3. WITH AUTOINCREMENT = FALSE;
GO
INSERT Products(ProductID, Name, UnitPrice)
VALUES(1002, N'Mid Lady Bag - Lizard', 228),
      (84, N'Holly Gladiator Heel Shoes', 198),
      (519, N'Short Black Skirt', 55.85);
GO
    4. SET IDENTITY ON Products = NULL;
GO
INSERT Products(ProductID, Name, UnitPrice)
VALUES(1002, N'Mid Lady Bag - Lizard', 228),
      (84, N'Holly Gladiator Heel Shoes', 198),
      (519, N'Short Black Skirt', 55.85);
    5. INSERT Products(ProductID, Name, UnitPrice)
WITH IDENTITY_INSERT = TRUE
VALUES(1002, N'Mid Lady Bag - Lizard', 228),
      (84, N'Holly Gladiator Heel Shoes', 198),
      (519, N'Short Black Skirt', 55.85);
  65. Justin has a table named Products and that has a few records. The table was created as follows: 
    CREATE TABLE Products
(
    ProductID int identity(1, 1) not null,
    Name nvarchar(50),
    Size nvarchar(32),
    UnitPrice money,
    DiscountRate decimal(4, 2),
    CONSTRAINT PK_Products PRIMARY KEY(ProductID)
);
    Justin wants to get a list of product names, their sizes and prices. He also wants the list to be sorted alphabetically according to the names. What code can he use to do that? 
    1. SELECT Name, Size, UnitPrice
FROM Products
SORT(Name);
GO
    2. SELECT SORT(Name), Size, UnitPrice
FROM Products;
GO
    3. SELECT Name, Size, UnitPrice
ORDER BY Name
FROM Products;
GO
    4. SELECT Name, Size, UnitPrice
FROM Products
ORDER BY Name;
GO
    5. SELECT Name, Size, UnitPrice
SET SORT FOR Name
FROM Products;
GO
  66. Linette has a table named Products and that has a few records. The table was created as follows: 
    CREATE TABLE Products
(
    ProductID int identity(1, 1) not null,
    DateAcquired date,
    Name nvarchar(50),
    Size nvarchar(32),
    UnitPrice money,
    DiscountRate decimal(4, 2),
    CONSTRAINT PK_Products PRIMARY KEY(ProductID)
);
GO
INSERT Products(DateAcquired, Name, UnitPrice)
VALUES(N'2011-12-06', N'Mid Lady Bag - Lizard', 228),
      (N'2010-08-09', N'Midnight Floral Cardigan', 78),
      (N'2011-10-12', N'Zip Front Sheath Dress', 138),
      (N'2010-05-24', N'Holly Gladiator Heel Shoes', 198),
      (N'2011-02-16', N'Short Black Skirt', 55.85);
GO
    Linette wants to get a list of products using with the date they were acquired, their names, their sizes and prices. He also wants the list to be sorted by the acquired dates from the most recent to the oldest item. What code can he use to do that? 
    1. SELECT DateAcquired, Name, Size, UnitPrice
FROM Products
ORDER BY DateAcquired ASC;
GO
    2. SELECT DateAcquired, Name, Size, UnitPrice
FROM Products
ORDER BY DateAcquired DESC;
GO
    3. SELECT DateAcquired, Name, Size, UnitPrice
SORT(DateAcquired) ASCENDING
FROM Products;
GO
    4. SELECT DateAcquired, Name, Size, UnitPrice
FROM Products
ORDER BY DateAcquired DESCENDING;
GO
    5. SELECT DateAcquired, Name, Size, UnitPrice
SET SORT FOR DateAcquired ASC
FROM Products;
GO
  67. Stan created a table named Products and filled it with records as follows: 
    CREATE TABLE Products
(
    ProductCode nchar(6) not null,
    DateAcquired date DEFAULT GETDATE(),
    Name nvarchar(50),
    Size nvarchar(32),
    UnitPrice money,
    DiscountRate decimal(4, 2),
    CONSTRAINT PK_Products PRIMARY KEY(ProductCode)
);
GO
INSERT Products(ProductCode, Name, Size, UnitPrice)
VALUES(N'274978', N'Mid Lady Bag - Lizard', N'14', 228),
      (N'827480', N'Midnight Floral Cardigan', N'12', 78),
      (N'183518', N'Zip Front Sheath Dress', N'Small', 138),
      (N'384680', N'Holly Gladiator Heel Shoes', N'7.5', 198),
      (N'247008', N'Short Black Skirt', N'Medium', 55.85);
GO
    Now he wants to copy all the records and put them in a new table named Sales. What code can he use to do that? 
    1. SELECT FROM Products INTO Sales;
GO
    2. COPY * FROM Products INTO Sales;
GO
    3. GET ALL * INTO Sales FROM Products;
GO
    4. WITH Products SELECT * INTO Sales;
GO
    5. SELECT ALL * INTO Sales FROM Products;
GO
  68. Adekunle has a table named Departments and another table named Employees. They were created and filled as follows: 
    CREATE TABLE Departments
(
	DepartmentCode nchar(6) not null primary key,
	DepartmentName nvarchar(50) not null
);
GO

INSERT INTO Departments
VALUES(N'HMRS', N'Human Resources'),
      (N'ACNT', N'Accounting'),
      (N'PSNL', N'Personnel'),
      (N'RSDV', N'Research & Development');
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	EmploymentStatus nvarchar(32),
	DepartmentCode nchar(6)
);
GO
INSERT INTO Employees
VALUES(N'283-947', N'Timothy', N'White', N'Part Time', N'RSDV'),
      (N'572-384', N'Jeannette', N'Welch', N'Part Time', N'PSNL'),
      (N'279-242', N'Ann', N'Welch', N'Full Time', N'HMRS'),
      (N'495-728', N'Robert', N'Simms', N'Part Time', N'RSDV'),
      (N'382-505', N'Paula', N'Waters', N'Part Time', N'PSNL'),
      (N'958-057', N'Peter', N'Aut', N'Part Time', N'HMRS'),
      (N'268-046', N'Martine', N'Nyamoto', N'Part Time', N'ACNT'),
      (N'400-752', N'James', N'Palau', N'Full Time', N'HMRS'),
      (N'773-148', N'James', N'Larsen', N'Part Time', N'RSDV');
GO
    What code can Ade use to get a list of full-time employees from the human resources department? 
    1. SELECT * FROM Employees
WHERE (EmploymentStatus = N'Full Time') AND (DepartmentCode = N'HMRS');
GO
    2. SELECT * FROM Employees
WHERE (EmploymentStatus = N'Full Time') OR (DepartmentCode = N'HMRS');
GO
    3. WITH (EmploymentStatus = N'Full Time') OR (DepartmentCode = N'HMRS')
SELECT * FROM Employees;
GO
    4. WHERE (EmploymentStatus = N'Full Time') AND (DepartmentCode IN N'HMRS');
SELECT * FROM Employees
GO
    5. WHERE (EmploymentStatus = N'Full Time') OR (DepartmentCode = N'HMRS');
SELECT * FROM Employees
GO
  69. Daniel has a table named Departments and another table named Employees. They were created and filled as follows: 
    CREATE TABLE Departments
(
	DepartmentCode nchar(6) not null primary key,
	DepartmentName nvarchar(50) not null
);
GO

INSERT INTO Departments
VALUES(N'HMRS', N'Human Resources'),
      (N'ACNT', N'Accounting'),
      (N'PSNL', N'Personnel'),
      (N'RSDV', N'Research & Development');
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	EmploymentStatus nvarchar(32),
	DepartmentCode nchar(6)
);
GO
INSERT INTO Employees
VALUES(N'283-947', N'Timothy', N'White', N'Part Time', N'RSDV'),
      (N'572-384', N'Jeannette', N'Welch', N'Part Time', N'PSNL'),
      (N'279-242', N'Ann', N'Welch', N'Full Time', N'HMRS'),
      (N'495-728', N'Robert', N'Simms', N'Part Time', N'RSDV'),
      (N'382-505', N'Paula', N'Waters', N'Part Time', N'PSNL'),
      (N'958-057', N'Peter', N'Aut', N'Part Time', N'HMRS'),
      (N'268-046', N'Martine', N'Nyamoto', N'Part Time', N'ACNT'),
      (N'400-752', N'James', N'Palau', N'Full Time', N'HMRS'),
      (N'773-148', N'James', N'Larsen', N'Part Time', N'RSDV');
GO
    What code can he use to get a list of all part-time employees regardless of their departments and all employees of the the Personnel department regardless of their employment status? 
    1. SELECT * FROM Employees
WHERE (EmploymentStatus = N'Part Time') AND (DepartmentCode = N'PSNL');
GO
    2. WHEN (EmploymentStatus = N'Part Time') AND (DepartmentCode = N'HMRS')
SELECT * FROM Employees;
GO
    3. SELECT * FROM Employees
WHERE (EmploymentStatus = N'Part Time') OR (DepartmentCode = N'PSNL');
GO
    4. WHEN (EmploymentStatus = N'Part Time') OR (DepartmentCode IN N'HMRS');
SELECT * FROM Employees
GO
    5. SELECT * FROM Employees
WITH (EmploymentStatus = N'Part Time') AND (DepartmentCode = N'HMRS');
GO
  70. Daniel has a table named Departments and another table named Employees. They were created and filled as follows: 
    CREATE TABLE Departments
(
	DepartmentCode nchar(6) not null primary key,
	DepartmentName nvarchar(50) not null
);
GO

INSERT INTO Departments
VALUES(N'HMRS', N'Human Resources'),
      (N'ACNT', N'Accounting'),
      (N'PSNL', N'Personnel'),
      (N'RSDV', N'Research & Development');
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	EmploymentStatus nvarchar(32),
	DepartmentCode nchar(6)
);
GO
INSERT INTO Employees
VALUES(N'283-947', N'Timothy', N'White', N'Part Time', N'RSDV'),
      (N'572-384', N'Jeannette', N'Welch', N'Part Time', N'PSNL'),
      (N'279-242', N'Ann', N'Welch', N'Full Time', N'HMRS'),
      (N'495-728', N'Robert', N'Simms', N'Part Time', N'RSDV'),
      (N'382-505', N'Paula', N'Waters', N'Part Time', N'PSNL'),
      (N'958-057', N'Peter', N'Aut', N'Part Time', N'HMRS'),
      (N'268-046', N'Martine', N'Nyamoto', N'Part Time', N'ACNT'),
      (N'400-752', N'James', N'Palau', N'Full Time', N'HMRS'),
      (N'773-148', N'James', N'Larsen', N'Part Time', N'RSDV'),
      (N'208-255', N'William', N'Aula', N'Full Time', N'PSNL');
GO
    He wants to get a list of part-time employees whose last name start with w. What code can he write to get that list? 
    1. SELECT * FROM Employees
WHERE (EmploymentStatus = N'Part Time') OR (LastName LIKE N'w%');
GO
    2. WHEN (EmploymentStatus = N'Part Time') AND (LastName LIKE N'w%')
SELECT * FROM Employees;
GO
    3. WHEN (EmploymentStatus = N'Part Time') OR (LastName LIKE N'w%');
SELECT * FROM Employees
GO
    4. SELECT * FROM Employees
WHERE (EmploymentStatus = N'Part Time') AND (LastName LIKE N'w%');
GO
    5. SELECT * FROM Employees
WITH (EmploymentStatus = N'Part Time') AND (LastName LIKE N'w%');
GO
  71. Daniel has a table named Departments and another table named Employees. They were created and filled as follows: 
    CREATE TABLE Departments
(
	DepartmentCode nchar(6) not null primary key,
	DepartmentName nvarchar(50) not null
);
GO

INSERT INTO Departments
VALUES(N'HMRS', N'Human Resources'),
      (N'ACNT', N'Accounting'),
      (N'PSNL', N'Personnel'),
      (N'RSDV', N'Research & Development');
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	EmploymentStatus nvarchar(32),
	DepartmentCode nchar(6)
);
GO
INSERT INTO Employees
VALUES(N'283-947', N'Timothy', N'White', N'Part Time', N'RSDV'),
      (N'572-384', N'Jeannette', N'Welch', N'Part Time', N'PSNL'),
      (N'279-242', N'Ann', N'Welch', N'Full Time', N'HMRS'),
      (N'495-728', N'Robert', N'Simms', N'Part Time', N'RSDV'),
      (N'382-505', N'Paula', N'Waters', N'Part Time', N'PSNL'),
      (N'958-057', N'Peter', N'Aut', N'Part Time', N'HMRS'),
      (N'268-046', N'Martine', N'Nyamoto', N'Part Time', N'ACNT'),
      (N'400-752', N'James', N'Palau', N'Full Time', N'HMRS'),
      (N'773-148', N'James', N'Larsen', N'Part Time', N'RSDV'),
      (N'208-255', N'William', N'Aula', N'Full Time', N'PSNL');
GO
    He wants to get a list of full-time employees of the human resources department and the part time employees of the personnel department. What code can he use to get that result? 
    1. SELECT * FROM Employees
WHERE ((EmploymentStatus = N'Full Time') OR (DepartmentCode = N'HMRS'))
      AND
      ((EmploymentStatus = N'Part Time') OR (DepartmentCode = N'PSNL'));
GO
    2. SELECT * FROM Employees
WHERE ((EmploymentStatus = N'Full Time') AND (DepartmentCode = N'HMRS'))
      OR
      ((EmploymentStatus = N'Part Time') AND (DepartmentCode = N'PSNL'));
GO
    3. SELECT * FROM Employees
WHERE ((EmploymentStatus = N'Full Time') AND (DepartmentCode = N'HMRS'))
      WITH
      ((EmploymentStatus = N'Part Time') AND (DepartmentCode = N'PSNL'));
GO
    4. SELECT * FROM Employees
WHERE ((EmploymentStatus = N'Full Time') OR (DepartmentCode = N'HMRS'))
      OR
      ((EmploymentStatus = N'Part Time') OR (DepartmentCode = N'PSNL'));
GO
    5. SELECT * FROM Employees
WHERE ((EmploymentStatus = N'Full Time') AND (DepartmentCode = N'HMRS'))
      AND
      ((EmploymentStatus = N'Part Time') AND (DepartmentCode = N'PSNL'));
GO
  72. Joel has to create a table to hold employees records. The columns must be defined as follows
     
    Column Name Data Type Size
    EmployeeID int  
    FirstName nvarchar 20
    LastName nvarchar 20
    EmploymentStatus nvarchar 32

    The values of the employment status column must be restristed to Part Time, Full Time, or Unknown. Any other value must be excluded. How can Joel write code to create that table? 
    1. CREATE TABLE Employees
(
    EmployeeID int unique,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    EmploymentStatus nvarchar(32)
	SET EmploymentStatus(N'Full Time', N'Part Time', N'Unknown')
);
GO
    2. CREATE TABLE Employees
(
    EmployeeID int unique,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    EmploymentStatus nvarchar(32)
)WITH EmploymentStatus AS (N'Full Time', N'Part Time', N'Unknown');
GO

    3. CREATE TABLE Employees
(
    EmployeeID int unique,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    EmploymentStatus nvarchar(32),
	CONSTRAINT FOR EmploymentStatus IN(N'Full Time', N'Part Time', N'Unknown'))
);
GO
    4. CREATE TABLE Employees
(
    EmployeeID int unique,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    EmploymentStatus nvarchar(32) SET AS (N'Full Time', N'Part Time', N'Unknown')
);
GO
    5. CREATE TABLE Employees
(
    EmployeeID int unique,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    EmploymentStatus nvarchar(32)
	CHECK(EmploymentStatus IN(N'Full Time', N'Part Time', N'Unknown'))
);
GO
  73. Kellie created a table named Employees and filled it up with some records as follows: 
    CREATE TABLE Employees
(
	EmployeeID int unique,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	EmploymentStatus nvarchar(32)
		CHECK(EmploymentStatus IN(N'Full Time', N'Part Time', N'Unknown'))
);
GO
INSERT INTO Employees
VALUES(10, N'Timothy', N'White', N'Part Time'),
      (50, N'Jeannette', N'Welch', N'Part Time'),
      (36, N'Ann', N'Welch', N'Full Time'),
      (60, N'Robert', N'Simms', N'Part Time'),
      (20, N'Paula', N'Waters', N'Part Time'),
      (80, N'Peter', N'Aut', N'Part Time'),
      (30, N'Martine', N'Nyamoto', N'Part Time'),
      (24, N'James', N'Palau', N'Full Time'),
      (53, N'James', N'Larsen', N'Part Time'),
      (15, N'William', N'Aula', N'Full Time');
GO
    Now he wants to get a list of employees using their IDs from 20 to 40. What statement can help him get that result? 
    1. SELECT * FROM Employees
WHERE EmployeeID BETWEEN 20 AND 40;
GO
    2. SELECT * FROM Employees
WHERE EmployeeID IN(20, 40);
GO
    3. SELECT * FROM Employees
WHERE (EmployeeID >= 20) AND (EmployeeID >= 40);
GO
    4. SELECT * FROM Employees
WHERE EmployeeID IN (20 TO 40);
GO
    5. SELECT * FROM Employees
WHERE EmployeeID FROM 20 TO 40;
GO
  74. Sanko has a table of empoyees created as follows: 
    CREATE TABLE Employees
(
    EmployeeNumber nchar(6) not null primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null
);
GO
    He wants to create another table named Sales that has a column named EmployeeNumber whose values would come from the EmployeeNumber of the Employees table. To prevent people from breaking the relationships among records, he wants to make sure that when a record is deleted from the Employees table, the database engine would display an error. How must he create the new table? 
    1. CREATE TABLE Sales
(
    SaleCode nchar(10) not null primary key,
    SaleDate date not null,
    EmployeeNumber nchar(6)
    CONSTRAINT FK_Employees FOREIGN KEY 
	REFERENCES Employees(EmployeeNumber),
	Amount money
)ON DELETE CASCADE ERROR;
GO
    2. CREATE TABLE Sales
(
    SaleCode nchar(10) not null primary key,
    SaleDate date not null,
    EmployeeNumber nchar(6) FOREIGN KEY 
	REFERENCES Employees(EmployeeNumber),
    Amount money
)ON DELETE SET NULL;
GO
    3. CREATE TABLE Sales
(
    SaleCode nchar(10) not null primary key,
    SaleDate date not null,
    EmployeeNumber nchar(6)
	CONSTRAINT FK_Employees FOREIGN KEY 
	    REFERENCES Employees(EmployeeNumber)
		ON DELETE NO ACTION,
    Amount money
);
    4. CREATE TABLE Sales
(
    SaleCode nchar(10) not null primary key,
    SaleDate date not null,
    EmployeeNumber nchar(6) FOREIGN KEY 
	REFERENCES Employees(EmployeeNumber)
	ON DELETE SHOW ERROR,
	Amount money
);
GO
    5. CREATE TABLE Sales
(
    SaleCode nchar(10) not null primary key,
    SaleDate date not null,
    EmployeeNumber nchar(6) FOREIGN KEY 
	REFERENCES Employees(EmployeeNumber),
	Amount money
);
WITH DELETE SET REFERENCE ERROR
GO
  75. July has a table of employees as follows: 
    CREATE TABLE Employees
(
    EmployeeNumber nchar(6) not null primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null
);
GO
    Now she is creating a Products table that will contain a column that uses some values from the employees table. If a record of the Employees table is updated, she wants to display an error. How should she create the Products table? 
    1. CREATE TABLE Products
(
    ProductCode nchar(8) primary key,
    EmployeeNumber nchar(6) FOREIGN KEY REFERENCES Employees(EmployeeNumber)
	ON UPDATE SHOW ERROR,
    DateAcquired date,
    Name nvarchar(60),
    UnitPrice money
);
GO
    2. CREATE TABLE Products
(
    ProductCode nchar(8) primary key,
    EmployeeNumber nchar(6) FOREIGN KEY REFERENCES Employees(EmployeeNumber),
    DateAcquired date,
    Name nvarchar(60),
    UnitPrice money
)ON UPDATE CASCADE ERROR;
GO
    3. DROP TABLE Products;
GO
CREATE TABLE Products
(
    ProductCode nchar(9) primary key,
    EmployeeNumber nchar(6) FOREIGN KEY REFERENCES Employees(EmployeeNumber)
    ON UPDATE SET NULL,
    DateAcquired date,
    Name nvarchar(60),
    UnitPrice money
);
GO
    4. CREATE TABLE Products
(
    ProductCode nchar(9) primary key,
    EmployeeNumber nchar(6) FOREIGN KEY REFERENCES Employees(EmployeeNumber)
    ON UPDATE NO ACTION,
    DateAcquired date,
    Name nvarchar(60),
    UnitPrice money
);
GO
    5. CREATE TABLE Products
(
    ProductCode nchar(9) primary key,
    EmployeeNumber nchar(6) FOREIGN KEY REFERENCES Employees(EmployeeNumber),
    DateAcquired date,
    Name nvarchar(60),
    UnitPrice money
)ON UPDATE SET ERROR;
GO
  76. John is creating a new table named Products after creating an Employees table as follows: 
    CREATE TABLE Employees
(
    EmployeeNumber nchar(6) not null primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null
);
GO
    He wants to make sure that when a record is deleted from the Employees table, any record of the Products table that was using the correspoding value of the Employees table is deleted. What code can he use to create the Products table? 
    1. CREATE TABLE Products
(
    ProductCode nchar(9) primary key,
    EmployeeNumber nchar(6) FOREIGN KEY REFERENCES Employees(EmployeeNumber),
    DateAcquired date,
    Name nvarchar(60),
    UnitPrice money
)ON DELETE SET DELETE;
GO
    2. CREATE TABLE Products
(
    ProductCode nchar(9) primary key,
    EmployeeNumber nchar(6) FOREIGN KEY REFERENCES Employees(EmployeeNumber)
    ON DELETE CASCADE,
    DateAcquired date,
    Name nvarchar(60),
    UnitPrice money
);
GO
    3. CREATE TABLE Products
(
    ProductCode nchar(9) primary key,
    EmployeeNumber nchar(6) FOREIGN KEY REFERENCES Employees(EmployeeNumber),
    DateAcquired date,
    Name nvarchar(60),
    UnitPrice money
)ON DELETE NO ACTION;
GO
    4. CREATE TABLE Products
(
    ProductCode nchar(9) primary key,
    EmployeeNumber nchar(6) FOREIGN KEY REFERENCES Employees(EmployeeNumber)
    ON DELETE SET NULL,
    DateAcquired date,
    Name nvarchar(60),
    UnitPrice money
);
GO
    5. CREATE TABLE Products
(
    ProductCode nchar(9) primary key,
    EmployeeNumber nchar(6) FOREIGN KEY REFERENCES Employees(EmployeeNumber),
    DateAcquired date,
    Name nvarchar(60),
    UnitPrice money
)ON DELETE SET NULL;
GO
  77. Gio has a table that holds the types of houses in a database he created for a customer. The table was structured as follows: 
    CREATE TABLE Categories
(
    CategoryID nchar(4) not null PRIMARY KEY,
    Category nvarchar(32) not null
);
GO
    Gio now has to create a new table for the houses. The table must include a column that will get the house types for the above table. If a category is changed from the above table, Gio would like the records that were using that category in the new table to be changed to reflect the new value. How should he create the new table? 
    1. CREATE TABLE Houses
(
	Code nchar(11) not null PRIMARY KEY,
	CategoryID nchar(4)
	FOREIGN KEY REFERENCES Categories(CategoryID)
	ON UPDATE SET DELETE,
	City nvarchar(40),
	MarketValue money
);
GO
    2. CREATE TABLE Houses
(
	Code nchar(11) not null PRIMARY KEY,
	CategoryID nchar(4)
	FOREIGN KEY REFERENCES Categories(CategoryID)
	ON UPDATE CASCADE,
	City nvarchar(40),
	MarketValue money
);
GO
    3. CREATE TABLE Houses
(
	Code nchar(11) not null PRIMARY KEY,
	CategoryID nchar(4)
	FOREIGN KEY REFERENCES Categories(CategoryID),
	City nvarchar(40),
	MarketValue money
)ON UPDATE NO ACTION;
GO
    4. CREATE TABLE Houses
(
	Code nchar(11) not null PRIMARY KEY,
	CategoryID nchar(4)
	FOREIGN KEY REFERENCES Categories(CategoryID)
	ON UPDATE SET NULL,
	City nvarchar(40),
	MarketValue money
);
GO
    5. CREATE TABLE Houses
(
	Code nchar(11) not null PRIMARY KEY,
	CategoryID nchar(4)
	FOREIGN KEY REFERENCES Categories(CategoryID),
	City nvarchar(40),
	MarketValue money
)ON UPDATE NULL;
GO
  78. Charlie is creating a real estate database for a new customer. He has already created a table for house types as follows: 
    CREATE TABLE HouseTypes
(
    HouseTypeID int not null PRIMARY KEY,
    HouseType nvarchar(32) not null
);
GO
    Now he has to create a table that holds the properties the company sells. The table of properties will have a column that specifies the type of house. The value of that column will come from the above table. When a house type deleted from the first table, Charlie wants the corresponding records of the new table to be changed to NULL. How can he create the new table to implement that functionality? 
    1. CREATE TABLE Properties
(
    PropertyNumber nchar(11) not null PRIMARY KEY,
    HouseTypeID int CONSTRAINT FK_PropTypes FOREIGN KEY
	REFERENCES HouseTypes(HouseTypeID)
	ON DELETE CASCADE NULL,
    City nvarchar(40),
    MarketValue money
);
GO
    2. CREATE TABLE Properties
(
    PropertyNumber nchar(11) not null PRIMARY KEY,
    HouseTypeID int CONSTRAINT FK_PropTypes FOREIGN KEY
	REFERENCES HouseTypes(HouseTypeID)
	ON DELETE NO ACTION,
    City nvarchar(40),
    MarketValue money
);
GO
    3. CREATE TABLE Properties
(
    PropertyNumber nchar(11) not null PRIMARY KEY,
    HouseTypeID int CONSTRAINT FK_PropTypes FOREIGN KEY
	REFERENCES HouseTypes(HouseTypeID),
    City nvarchar(40),
    MarketValue money
)ON DELETE NULL IS TRUE;
GO
    4. CREATE TABLE Properties
(
    PropertyNumber nchar(11) not null PRIMARY KEY,
    HouseTypeID int CONSTRAINT FK_PropTypes FOREIGN KEY
	REFERENCES HouseTypes(HouseTypeID)
	ON DELETE SET NULL,
    City nvarchar(40),
    MarketValue money
);
GO
    5. CREATE TABLE Properties
(
    PropertyNumber nchar(11) not null PRIMARY KEY,
    HouseTypeID int CONSTRAINT FK_PropTypes FOREIGN KEY
	REFERENCES HouseTypes(HouseTypeID),
    City nvarchar(40),
    MarketValue money
)ON DELETE SET CASCADE NULL;
GO
  79. Courtney is creating a database for a community organization. The datable will have a table of members where each record is represented by a membership category. To limit the number of categories, Courney first creates a table of membership categories as follows: 
    CREATE TABLE Categories
(
    CategoryID int not null PRIMARY KEY,
    Category nvarchar(32) not null
);
GO
    In the table for members, since Courtney will include a column for the categories, when a record of the Categories table is are updated, she wants the corresponding field in the members table to receive a NULL value. How should she create the table for the members? 
    1. CREATE TABLE Members
(
    MemberID int unique,
    Name nvarchar(50),
    CategoryID int FOREIGN KEY REFERENCES Categories(CategoryID)
    ON UPDATE CASCADE NULL,
    MembershipStatus nvarchar(20)
);
GO
    2. CREATE TABLE Members
(
    MemberID int unique,
    Name nvarchar(50),
    CategoryID int FOREIGN KEY REFERENCES Categories(CategoryID)
    ON UPDATE NO ACTION,
    MembershipStatus nvarchar(20)
);
GO
    3. CREATE TABLE Members
(
    MemberID int unique,
    Name nvarchar(50),
    CategoryID int FOREIGN KEY REFERENCES Categories(CategoryID)
    ON UPDATE SET NULL,
    MembershipStatus nvarchar(20)
);
GO
    4. CREATE TABLE Members
(
    MemberID int unique,
    Name nvarchar(50),
    CategoryID int FOREIGN KEY REFERENCES Categories(CategoryID),
    MembershipStatus nvarchar(20)
)ON UPDATE NULL IS TRUE;
GO
    5. CREATE TABLE Members
(
    MemberID int unique,
    Name nvarchar(50),
    CategoryID int FOREIGN KEY REFERENCES Categories(CategoryID),
    MembershipStatus nvarchar(20)
)ON UPDATE SET NULL;
GO
  80. Douglas is creating a database for his company to manage employees records. One of the details about each employee will be the employment status. Douglas creates a table for employment status as follows: 
    CREATE TABLE EmploymentTypes
(
    EmploymentTypeID int identity(1000, 10) primary key,
    EmploymenStatus nvarchar(20) not null,
);
GO
INSERT INTO EmploymentTypes(EmploymenStatus)
VALUES(N'Full Time'),(N'Part Time'),(N'Unknown');
GO
    To keep track of employees, Douglas starts creating a table as follows: 
    CREATE TABLE Employees
(
    EmployeeNumber nchar(6) not null primary key,
    EmploymentTypeID int FOREIGN KEY
        REFERENCES EmploymentTypes(EmploymentTypeID)
        DEFAULT 3,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null,
    HourlySalary money
);
GO
    If an employment status is deleted from the EmploymentStatus table, Douglas would like the employees who use that status to get the default value. How should he change the Employees table to take care of this requirement? 
    1. CREATE TABLE Employees
(
    EmployeeNumber nchar(6) not null primary key,
    EmploymentTypeID int FOREIGN KEY
        REFERENCES EmploymentTypes(EmploymentTypeID)
        ON DELETE SET DEFAULT
        DEFAULT 3,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null,
    HourlySalary money
);
GO
    2. CREATE TABLE Employees
(
    EmployeeNumber nchar(6) not null primary key,
    EmploymentTypeID int FOREIGN KEY
        REFERENCES EmploymentTypes(EmploymentTypeID)
        DEFAULT 3,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null,
    HourlySalary money
)ON DELETE DEFAULT = NULL;
GO
    3. CREATE TABLE Employees
(
    EmployeeNumber nchar(6) not null primary key,
    EmploymentTypeID int FOREIGN KEY
        REFERENCES EmploymentTypes(EmploymentTypeID)
        DEFAULT 3
        ON DELETE CASCADE DEFAULT,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null,
    HourlySalary money
);
GO
    4. CREATE TABLE Employees
(
    EmployeeNumber nchar(6) not null primary key,
    EmploymentTypeID int FOREIGN KEY
        REFERENCES EmploymentTypes(EmploymentTypeID)
        ON DELETE NO ACTION
        DEFAULT 3,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null,
    HourlySalary money
);
GO
    5. CREATE TABLE Employees
(
    EmployeeNumber nchar(6) not null primary key,
    EmploymentTypeID int FOREIGN KEY
        REFERENCES EmploymentTypes(EmploymentTypeID)
        DEFAULT 3,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null,
    HourlySalary money
)ON DELETE DEFAULT IS TRUE;
GO
  81. Joseph is working on a database for a department store. He creates a table for the categories of items sold in the store: 
    CREATE TABLE ItemsTypes
(
    ItemTypeID int identity(1, 1) primary key,
    ItemType nvarchar(20) not null,
);
GO
INSERT INTO ItemsTypes(ItemType)
VALUES(N'Miscellaneous'),(N'Shirts'),(N'Dresses'),(N'Pants');
GO
    In the table of items, there will be a column that gets its values from the ItemsTypes table. To start with the items sold in the store, Josephs write the following code without executing it: 
    CREATE TABLE StoreItems
(
    ItemCode nchar(10) not null primary key,
    ItemTypeID int FOREIGN KEY
        REFERENCES ItemsTypes(ItemTypeID)
        DEFAULT 1,
    Name nvarchar(50),
    Size nvarchar(20) not null,
    UnitPrice money
);
GO
    Sometimes the employees will change the name of a type in the ItemsTypes table. When this happens, Joseph wants the items that use that category to get the default value. How can Joseph change the code the StoreItems table to take care of this? 
    1. CREATE TABLE StoreItems
(
    ItemCode nchar(10) not null primary key,
    ItemTypeID int FOREIGN KEY
        REFERENCES ItemsTypes(ItemTypeID)
        DEFAULT 1,
    Name nvarchar(50),
    Size nvarchar(20) not null,
    UnitPrice money
) ON UPDATE DEFAULT = NULL;
GO
    2. CREATE TABLE StoreItems
(
    ItemCode nchar(10) not null primary key,
    ItemTypeID int FOREIGN KEY
        REFERENCES ItemsTypes(ItemTypeID)
        ON UPDATE CASCADE DEFAULT
        DEFAULT 1,
    Name nvarchar(50),
    Size nvarchar(20) not null,
    UnitPrice money
);
GO
    3. CREATE TABLE StoreItems
(
    ItemCode nchar(10) not null primary key,
    ItemTypeID int FOREIGN KEY
        REFERENCES ItemsTypes(ItemTypeID)
        DEFAULT 1,
    Name nvarchar(50),
    Size nvarchar(20) not null,
    UnitPrice money
)ON UPDATE NO ACTION;
GO
    4. CREATE TABLE StoreItems
(
    ItemCode nchar(10) not null primary key,
    ItemTypeID int FOREIGN KEY
        REFERENCES ItemsTypes(ItemTypeID)
        DEFAULT 1
        ON UPDATE DEFAULT IS TRUE,
    Name nvarchar(50),
    Size nvarchar(20) not null,
    UnitPrice money
);
GO
    5. CREATE TABLE StoreItems
(
    ItemCode nchar(10) not null primary key,
    ItemTypeID int FOREIGN KEY
        REFERENCES ItemsTypes(ItemTypeID)
        ON UPDATE SET DEFAULT
        DEFAULT 1,
    Name nvarchar(50),
    Size nvarchar(20) not null,
    UnitPrice money
);
GO
  82. John has the following tables of employees and contractors who work for his company: 
    CREATE TABLE Employees
(
    EmployeeNumber nchar(9),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    [Status] nvarchar(20) default N'Employee'
);
GO
CREATE TABLE Contractors
(
    ContractorCode nchar(7),
    Name1 nvarchar(20),
    Name2 nvarchar(20),
    Wage decimal(6, 2),
    [Type] nvarchar(20) default N'Contractor'
);
GO

INSERT INTO Employees(EmployeeNumber, FirstName, LastName, HourlySalary)
VALUES(N'2930-4708', N'John', N'Franks', 20.05),
      (N'8274-9571', N'Peter', N'Sonnens', 10.65),
      (N'6359-8079', N'Leslie', N'Aronson', 15.88);
GO
INSERT INTO Contractors(ContractorCode, Name1, Name2, Wage)
VALUES(N'350-809', N'Mary', N'Shamberg', 14.20),
      (N'286-606', N'Chryssa', N'Lurie', 20.26);
GO
    In preparation of payroll, John wants to preview the employees and contractors in one common list. What code can he write to get that list?
    1. He can't because some data types in the tables are not compatible
    2. SELECT * FROM Employees;
GO
SELECT * FROM Contractors;
GO
    3. SELECT * FROM Employees
UNION
SELECT * FROM Contractors;
GO
    4. SELECT * FROM Employees;
GO
UNION
SELECT * FROM Contractors;
GO
    5. WITH UNION
SELECT * FROM Employees
AND
SELECT * FROM Contractors;
GO
  83. Marc has the following tables of seasonal employees and contractors who do side jobs for his company: 
    CREATE TABLE Seasonals
(
    Number nchar(9),
    FName nvarchar(20),
    LName nvarchar(20),
    HourlySalary money
);
GO
CREATE TABLE Contractors
(
    Code nchar(7),
    Name1 nvarchar(20),
    Name2 nvarchar(20),
    Wage decimal(6, 2)
);
GO

INSERT INTO Seasonals
VALUES(N'2930-4708', N'John', N'Franks', 20.05),
      (N'8274-9571', N'Peter', N'Sonnens', 10.65),
      (N'6359-8079', N'Leslie', N'Aronson', 15.88);
GO
INSERT INTO Contractors
VALUES(N'350-809', N'Mary', N'Shamberg', 14.20),
      (N'286-606', N'Chryssa', N'Lurie', 20.26);
GO
    All these seasonal employees and contractors have been hired by the company. To prepare their inclusion into the company, Mark creates a new table named Employees as follows: 
    CREATE TABLE Employees
(
    EmployeeNumber nchar(9),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money
);
GO
    Marc wants to add the seasonal employees and the contractors to the Employees table. What code can let him do that? 
    1. SELECT FROM Seasonals INTO Employees
UNION
SELECT FROM Contractors INTO Employees;
GO
    2. INSERT INTO Employees
SELECT * FROM Seasonals
UNION
SELECT * FROM Contractors;
GO
    3. UNION ALL
SELECT ALL * FROM Seasonals
AND
SELECT ALL * FROM Contractors
INTO Employees;
GO
    4. SELECT ALL * FROM Seasonals
AND
SELECT ALL * FROM Contractors
INTO Employees
UNION ALL;
GO
    5. SELECT ALL * FROM Seasonals, Contractors
INTO Employees
UNION ALL;
GO
  84. George has the following tables with records: 
    CREATE TABLE Contractors
(
    ContractorCode nchar(10),
    FName nvarchar(20),
    LName nvarchar(20),
    Wage decimal(6, 2)
);
GO
CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    DateHired date,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    EmploymentStatus nvarchar(20) null
);
GO
INSERT INTO Contractors
VALUES(N'35080', N'Mary', N'Shamberg', 14.20),
      (N'286606', N'Chryssa', N'Lurie', 20.26),
      (N'415905', N'Ralph', N'Sunny', 15.55);
GO
INSERT INTO Employees
VALUES(N'286018', N'20020426', N'Julie', N'Chance', 12.84, N'Full Time'),
      (N'286606', N'19981008', N'Ayinda', N'Kaihibu', 9.52, N'Part Time'),
      (N'922620', N'20100815', N'Ann', N'Keans', 20.52, N'Full Time'),
      (N'415905', N'20061222', N'Godwin', N'Harrison', 18.75, N'Full Time'),
      (N'682470', N'20080430', N'Timothy', N'Journ', 21.05, NULL);
GO
    He wants to merge the records to add those of the contractors to the Employees table by comparing the employee numbers to the contractors codes. During the operation, if a record from the Contractors table matches an employee number, two leading 0s will be added to the employeee number to make sure the numbers remain unique (but the other parts of the record will not be changed). Otherwise, the record should be added. What code would accomplish this? 
    1. USING Contractors AS SOURCE
MERGE Employees AS TARGET
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode
WHEN NOT MATCHED BY SOURCE
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage);
GO
    2. USING Contractors AS SOURCE
MERGE Employees AS TARGET
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN NOT MATCHED BY SOURCE
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage)
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode;
GO
    3. MERGE Employees AS Teachers
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode
WHEN NOT MATCHED BY SOURCE
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage)
USING Contractors AS Workers
ON (Workers.ContractorCode = Teachers.EmployeeNumber);
    4. MERGE Employees AS Teachers
USING Contractors AS Workers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode
WHEN NOT MATCHED BY TARGET
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage);
GO
    5. WITH MERGE Employees AS Teachers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
USING Contractors AS Workers
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode
WHEN NOT MATCHED BY SOURCE
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage);
GO
  85. While working for a department store, Evelyne receives two tables that contain lists of items sold in the store. The tables were created as follows: 
    CREATE TABLE Products
(
    ProductNumber int not null,
    DateAcquired date,
    Name nvarchar(50),
    Size nvarchar(32),
    UnitPrice money,
    CONSTRAINT PK_Products PRIMARY KEY(ProductNumber)
);
GO
CREATE TABLE StoreItems
(
    ItemCode int identity(1, 1) not null PRIMARY KEY,
    Arrival date,
    [Description] nvarchar(50),
    Value money
);
GO
INSERT Products
VALUES(2, N'2011-12-06', N'Mid Lady Bag - Lizard', N'12', 228),
      (888, N'2010-08-09', N'Midnight Floral Cardigan', N'Small', 78),
      (105583, N'2011-10-12', N'Zip Front Sheath Dress', N'8', 138),
      (4, N'2010-05-24', N'Holly Gladiator Heel Shoes', N'7.5', 198),
      (3680, N'2011-02-16', N'Short Black Skirt', N'14', 55.85),
      (28, NULL, N'Color-Block Chambray Shirt', N'10', 85.25);
GO
INSERT StoreItems(Arrival, [Description], Value)
VALUES(N'2010-02-22', N'Short-Sleeved Bush Shirt', 59.95),
      (N'2011-10-12', N'Zip Front Sheath Dress', 138),
      (N'2010-05-24', N'Pure Cashmere Sweater', 165),
      (N'2011-02-16', N'Short Black Skirt', 55.85);
GO
    It appears that some records are duplicated. To start, Evelyne wants to merge the records from the StoreItems to the Products tables. During this operation, if a record from one table matches a record from the other table, the matching record should be removed. Otherwise, if the record is not found in the StoreItems table, it should be added to the Products table. How can she write code to merge the records? 
    1. USING StoreItems AS Items
MERGE Products AS Inventory
ON Inventory.ProductNumber = Items.ItemCode
WHEN MATCHED
    THEN DELETE
WHEN NOT MATCHED THEN
    INSERT(ProductNumber, DateAcquired, Name, UnitPrice)
    VALUES(ItemCode, Arrival, [Description], Value);
GO
    2. MERGE Products AS Inventory
USING StoreItems AS Items
ON Inventory.ProductNumber = Items.ItemCode
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN
    INSERT(ProductNumber, DateAcquired, Name, UnitPrice)
    VALUES(ItemCode, Arrival, [Description], Value);
GO
    3. WITH StoreItems AS Items
MERGE Products AS Inventory
ON Inventory.ProductNumber = Items.ItemCode
WHEN MATCHED
    THEN DELETE
WHEN NOT MATCHED THEN
    INSERT(ProductNumber, DateAcquired, Name, UnitPrice)
    VALUES(ItemCode, Arrival, [Description], Value);
GO
    4. WITH StoreItems AS Items
MERGE Products AS Inventory
ON Inventory.ProductNumber = Items.ItemCode
WHEN MATCHED SET NULL
OR
    INSERT(ProductNumber, DateAcquired, Name, UnitPrice)
    VALUES(ItemCode, Arrival, [Description], Value);
GO
    5. SELECT * FROM Products AS Inventory
AND
SELECT * FROM StoreItems AS Items
MERGE
ON Inventory.ProductNumber = StoreItems.ItemCode
IF MATCH THEN
    DELETE
ELSE
    INSERT(ProductNumber, DateAcquired, Name, UnitPrice)
    VALUES(ItemCode, Arrival, [Description], Value);
GO
  86. Imagine you have a table as follows: 
    CREATE TABLE Rooms
(
    RoomNumber nchar(10) not null,
    RoomType nvarchar(20) default N'Bedroom',
    BedType nvarchar(40) default N'Queen',
    Rate money default 75.85,
    Available bit
);
GO
INSERT INTO Rooms(RoomNumber, BedType, Rate, Available)
VALUES(N'104', default, 80.25, 0),
      (N'105', N'King', 95.50, 1),
      (N'108', N'King', 92.50, 1),
      (N'109', default, 68.95, 0),
      (N'110', default, 74.95, 1);
GO
    How can you write a common table expression to see its records? 
    1. WITH BedRooms AS
(
    SELECT * FROM Rooms
)

SELECT * FROM BedRooms;
GO
    2. WITH BedRooms AS SELECT * FROM BedRooms;
BEGIN
    SELECT * FROM Rooms
END
    3. SELECT * FROM BedRooms
AS BedRooms
BEGIN
    SELECT * FROM Rooms
END
    4. WITH BedRooms
AS 
BEGIN
    SELECT * FROM Rooms
END
    5. SELECT * FROM Rooms AS BedRooms 
BEGIN
    SELECT * FROM Bedrooms
END
  87. Imagine you have a table as follows: 
    CREATE TABLE Rooms
(
    RoomNumber nchar(10) not null,
    RoomType nvarchar(20) default N'Bedroom',
    BedType nvarchar(40) default N'Queen',
    Rate money default 75.85,
    Available bit
);
GO
INSERT INTO Rooms(RoomNumber, BedType, Rate, Available)
VALUES(N'104', default, 80.25, 0),
      (N'105', N'King', 95.50, 1),
      (N'108', N'King', 92.50, 1),
      (N'109', default, 68.95, 0),
      (N'110', default, 74.95, 1);
GO
    What code allows you to create a common table expression that includes only King bedrooms? 
    1. CREATE BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
AS CTE
BEGIN
    SELECT RoomNumber, RoomType, BedType, Rate, Available
    FROM Rooms
    WHERE BedType = N'King'
END
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO
    2. SELECT RoomNumber, RoomType, BedType, Rate, Available
FROM Rooms
WHERE BedType = N'King'
AS BedRooms(RoomNumber, RoomType, BedType, Rate, Available);
GO
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO
    3. WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
AS
(
    SELECT RoomNumber, RoomType, BedType, Rate, Available
    FROM Rooms
    WHERE BedType = N'King'
)
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO
    4. SET BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
BEGIN
	SELECT RoomNumber, RoomType, BedType, Rate, Available
	FROM Rooms
	WHERE BedType = N'King'
END
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO
    5. WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
BEGIN
    SELECT RoomNumber, RoomType, BedType, Rate, Available
    FROM Rooms
    WHERE BedType = N'King'
    SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
END
GO
  88. Lance is creating a database for a hotel. Based on papers provided by the customer, he created two tables as follows: 
    CREATE TABLE SleepingRooms (
    RoomNumber nchar(10) not null,
    RoomType nvarchar(20) default N'Bedroom',
    BedType nvarchar(40) default N'Queen',
    Rate money default 75.85,
    Available bit
);
GO

CREATE TABLE ConferenceRooms (
    RoomNumber nchar(10) not null,
    RoomType nvarchar(20) default N'Conference',
    BedType nvarchar(40),
    Rate money default 75.85,
    Available bit
);
GO

INSERT INTO SleepingRooms(RoomNumber, BedType, Rate, Available)
VALUES(N'104', default, 80.25, 0),
      (N'105', N'King', 95.50, 1),
      (N'108', N'King', 92.50, 1),
      (N'109', default, 68.95, 0),
      (N'110', default, 74.95, 1);
GO

INSERT INTO ConferenceRooms(RoomNumber, Rate)
VALUES(N'C-120', 525.00),
      (N'C-122', 450.00);
GO
    How can he create a common table expression to get a preview of bedrooms and conference rooms in one list? 
    1. WITH HotelRooms
BEGIN
    SELECT * FROM SleepingRooms
    UNION
    SELECT * FROM ConferenceRooms
END
SELECT * FROM HotelRooms;
GO
    2. SET CTE
BEGIN
    SELECT * FROM SleepingRooms
    UNION
    SELECT * FROM ConferenceRooms
END
WITH HotelRooms
SELECT * FROM HotelRooms;
GO
    3. DECLARE @HotelRooms AS VIEW
BEGIN
    SELECT * FROM SleepingRooms
    UNION
    SELECT * FROM ConferenceRooms
END
SELECT * FROM @HotelRooms;
GO
    4. WITH HotelRooms AS TABLE
BEGIN
    SELECT * FROM SleepingRooms
    UNION ALL
    SELECT * FROM ConferenceRooms
END
SELECT * FROM @HotelRooms;
GO
    5. WITH HotelRooms
AS
(
    SELECT * FROM SleepingRooms
    UNION
    SELECT * FROM ConferenceRooms
)
SELECT * FROM HotelRooms;
GO
  89. Lance is creating a database for a hotel. Based on papers provided by the customer, he created two tables as follows: 
    CREATE TABLE SleepingRooms
(
    RoomNumber nchar(10) not null,
    RoomType nvarchar(20) default N'Bedroom',
    BedType nvarchar(40) default N'Queen',
    Rate money default 75.85,
    Available bit
);
GO

CREATE TABLE ConferenceRooms
(
    RoomNumber nchar(10) not null,
    RoomType nvarchar(20) default N'Conference',
    BedType nvarchar(40),
    Rate money default 75.85,
    Available bit
);
GO

INSERT INTO SleepingRooms(RoomNumber, BedType, Rate, Available)
VALUES(N'104', default, 80.25, 0),
      (N'105', N'King', 95.50, 1),
      (N'108', N'King', 92.50, 1),
      (N'109', default, 68.95, 0),
      (N'110', default, 74.95, 1);
GO

INSERT INTO ConferenceRooms(RoomNumber, Rate)
VALUES(N'C-120', 525.00),
      (N'C-122', 450.00);
GO
    How can he create a common table expression that shows a list available bedrooms and conference rooms? 
    1. SET HotelRooms
BEGIN
    SELECT * FROM SleepingRooms
    UNION
    SELECT * FROM ConferenceRooms
END
SELECT RoomNumber, RoomType, BedType, Rate
FROM HotelRooms
WHERE Available = 1;
GO
    2. WITH HotelRooms
AS
(
    SELECT * FROM SleepingRooms
    UNION
    SELECT * FROM ConferenceRooms
)
SELECT RoomNumber, RoomType, BedType, Rate
FROM HotelRooms
WHERE Available = 1;
GO
    3. WITH HotelRooms
BEGIN
    SELECT * FROM SleepingRooms
    UNION
    SELECT * FROM ConferenceRooms
END
AS
SELECT RoomNumber, RoomType, BedType, Rate
FROM HotelRooms
WHERE Available = 1;
GO
    4. WITH HotelRooms
MERGE AS
(
    SELECT * FROM SleepingRooms
    UNION
    SELECT * FROM ConferenceRooms
)
SELECT RoomNumber, RoomType, BedType, Rate
FROM HotelRooms
WHERE Available = 1;
GO
    5. WITH HotelRooms
MERGE AS
BEGIN
    SELECT * FROM SleepingRooms
    WHERE Available = 1
    UNION
    SELECT * FROM ConferenceRooms
    WHERE Available = 1
END
SELECT RoomNumber, RoomType, BedType, Rate
FROM HotelRooms;
GO
  90. Consider the following table: 
    CREATE TABLE Rooms
(
    RoomNumber nchar(10) not null,
    RoomType nvarchar(20) default N'Bedroom',
    BedType nvarchar(40) default N'Queen',
    Rate money default 75.85,
    Available bit
);
GO
INSERT INTO SleepingRooms(RoomNumber, BedType, Rate, Available)
VALUES(N'104', default, 80.25, 0),
      (N'105', N'King', 95.50, 1),
      (N'108', N'King', 92.50, 1),
      (N'109', default, 68.95, 0),
      (N'110', default, 74.95, 1);
GO
    Write an inline table-valued function that would produce all records of that table 
    1. CREATE FUNCTION GetRooms() AS TABLE
RETURN
    SELECT ALL * FROM Rooms;
GO
    2. CREATE FUNCTION GetRooms()
RETURN
    SELECT ALL * FROM Rooms AS TABLE;
GO
    3. CREATE FUNCTION GetRooms()
RETURNS TABLE
BEGIN
    SELECT ALL * FROM Rooms;
END
GO
    4. CREATE FUNCTION GetRooms()
RETURNS TABLE
AS
RETURN SELECT ALL * FROM Rooms;
GO
    5. CREATE FUNCTION GetRooms()
BEGIN
    RETURN SELECT ALL * FROM Rooms;
END
GO
  91. While managing a furniture store, Elise has a table of employees and their sales as follows: 
    CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Commercial;
GO

CREATE TABLE Personnel.Employees
(
    EmployeeNumber nchar(7) not null primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    EmploymentStatus smallint,
    HourlySalary money
);
GO
CREATE TABLE Commercial.Sales
(
    SaleID int identity(1, 1),
    EmployeeNumber nchar(7) not null,
    SaleDate date,
    Amount money
);
GO
INSERT Personnel.Employees
VALUES(N'284-680', N'Anselme', N'Bongos', 2, 18.62),
      (N'730-704', N'June', N'Malea', 1, 9.95),
      (N'735-407', N'Frank', N'Monson', 3, 14.58),
      (N'281-730', N'Jerry', N'Beaulieu', 1, 16.65);
GO

INSERT INTO Commercial.Sales(EmployeeNumber, SaleDate, Amount)
VALUES(N'284-680', N'2011-02-14', 4250),
      (N'735-407', N'2011-02-14', 5300),
      (N'730-704', N'2011-02-14', 2880),
      (N'281-730', N'2011-02-14', 4640),
      (N'284-680', N'2011-02-15', 4250),
      (N'281-730', N'2011-02-15', 3675);
GO
    Instead of a view, she wants to create an inline table-valued function named GetSales that can produce a list of all sales made by the employees. Each record will include the employee's full name (the last name followed by a comma and followed by the first name), the date a sale was made, and the amount of the sale. How can she write code for that function? 
    1. CREATE FUNCTION GetSales()
AS TABLE
RETURN
    SELECT pe.LastName + N', ' + pe.FirstName, 
           cs.SaleDate, cs.Amount
    FROM Commercial.Sales cs
    INNER JOIN Personnel.Employees pe
    ON cs.EmployeeNumber = pe.EmployeeNumber;
GO
    2. CREATE FUNCTION GetSales()
RETURN TABLE
BEGIN
RETURNS
    SELECT pe.LastName + N', ' + pe.FirstName AS [Full Name], 
           cs.SaleDate, cs.Amount
    FROM Commercial.Sales cs
    INNER JOIN Personnel.Employees pe
    ON cs.EmployeeNumber = pe.EmployeeNumber;
END
GO
    3. CREATE INLINE FUNCTION GetSales()
RETURN TABLE
BEGIN
    SELECT pe.LastName + N', ' + pe.FirstName, 
           cs.SaleDate, cs.Amount
    FROM Commercial.Sales cs
    INNER JOIN Personnel.Employees pe
    ON cs.EmployeeNumber = pe.EmployeeNumber;
END
GO
    4. CREATE FUNCTION GetSales()
RETURNS TABLE
AS
RETURN
    SELECT pe.LastName + N', ' + pe.FirstName AS [Full Name], 
           cs.SaleDate, cs.Amount
    FROM Commercial.Sales cs
    INNER JOIN Personnel.Employees pe
    ON cs.EmployeeNumber = pe.EmployeeNumber;
GO
    5. CREATE FUNCTION GetSales()
BEGIN
    SELECT pe.LastName + N', ' + pe.FirstName AS [Full Name], 
           cs.SaleDate, cs.Amount
    FROM Commercial.Sales cs
    INNER JOIN Personnel.Employees pe
    ON cs.EmployeeNumber = pe.EmployeeNumber;
END
RETURN TABLE
GO
  92. While managing a furniture store, Elise has a table of employees and their sales as follows: 
    CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Commercial;
GO

CREATE TABLE Personnel.Employees
(
    EmployeeNumber nchar(7) not null primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    EmploymentStatus smallint,
    HourlySalary money
);
GO
INSERT Personnel.Employees
VALUES(N'284-680', N'Anselme', N'Bongos', 2, 18.62),
      (N'730-704', N'June', N'Malea', 1, 9.95),
      (N'735-407', N'Frank', N'Monson', 3, 14.58),
      (N'281-730', N'Jerry', N'Beaulieu', 1, 16.65);
GO

CREATE TABLE Commercial.Sales
(
    SaleID int identity(1, 1),
    EmployeeNumber nchar(7) not null,
    SaleDate date,
    Amount money
);
GO
INSERT INTO Commercial.Sales(EmployeeNumber, SaleDate, Amount)
VALUES(N'284-680', N'2011-02-14', 4250),
      (N'735-407', N'2011-02-14', 5300),
      (N'730-704', N'2011-02-14', 2880),
      (N'281-730', N'2011-02-14', 4640),
      (N'284-680', N'2011-02-15', 4250),
      (N'281-730', N'2011-02-15', 3675);
GO
    Since views don't allow parameterized queries, she wants to create an inline table-valued function that takes an employee number as argument and produces the sales made by that employee. Each record will include the employee's full name (the last name followed by a comma and followed by the first name), the date a sale was made, and the amount of the sale. How can she write code to produce that result? 
    1. CREATE FUNCTION Commercial.GetSales(@EmplNbr nchar(7))
RETURNS TABLE
AS
RETURN
    SELECT pe.LastName + N', ' + pe.FirstName AS [Full Name],
           cs.SaleDate, cs.Amount
    FROM Commercial.Sales cs
    INNER JOIN Personnel.Employees pe
    ON cs.EmployeeNumber = pe.EmployeeNumber
    WHERE pe.EmployeeNumber = @EmplNbr;
GO
    2. CREATE FUNCTION Commercial.GetSales(@EmplNbr nchar(7))
RETURN
    SELECT pe.LastName + N', ' + pe.FirstName AS [Full Name],
           cs.SaleDate, cs.Amount
    FROM Commercial.Sales cs
    INNER JOIN Personnel.Employees pe
    ON cs.EmployeeNumber = pe.EmployeeNumber
    WHERE pe.EmployeeNumber = @EmplNbr
    RETURNS TABLE
END
GO
    3. CREATE INLINE FUNCTION Commercial.GetSales(@EmplNbr nchar(7)) AS TABLE
BEGIN
    SELECT pe.LastName + N', ' + pe.FirstName AS [Full Name],
           cs.SaleDate, cs.Amount
    FROM Commercial.Sales cs
    INNER JOIN Personnel.Employees pe
    ON cs.EmployeeNumber = pe.EmployeeNumber
    WHERE pe.EmployeeNumber = @EmplNbr;
END
GO
    4. CREATE FUNCTION Commercial.GetSales(@EmplNbr nchar(7))
RETURNS TABLE
BEGIN
    SELECT pe.LastName + N', ' + pe.FirstName,
           cs.SaleDate, cs.Amount
    FROM Commercial.Sales cs
    INNER JOIN Personnel.Employees pe
    ON cs.EmployeeNumber = pe.EmployeeNumber
    WHERE pe.EmployeeNumber = @EmplNbr;
END
GO
    5. CREATE FUNCTION Commercial.GetSales(@EmplNbr nchar(7))
BEGIN
    SELECT pe.LastName + N', ' + pe.FirstName AS [Full Name],
           cs.SaleDate, cs.Amount
    FROM Commercial.Sales cs
    INNER JOIN Personnel.Employees pe
    ON cs.EmployeeNumber = pe.EmployeeNumber
    WHERE pe.EmployeeNumber = @EmplNbr
    AS TABLE;
GO
  93. Evelyne works for a department store. She has created a table of items sold in the store and filled it with some records as follows: 
    CREATE SCHEMA Inventory;
GO
CREATE TABLE Inventory.StoreItems
(
    ItemCode int identity(1, 1) not null PRIMARY KEY,
    DateAcquired date,
    Name nvarchar(50),
    Size nvarchar(32),
    UnitPrice money,
    DiscountRate decimal
);
GO
INSERT Inventory.StoreItems(DateAcquired, Name, Size, UnitPrice, DiscountRate)
VALUES(N'2011-12-06', N'Mid Lady Bag - Lizard', N'12', 228, NULL),
      (N'2010-02-22', N'Short-Sleeved Bush Shirt', N'Medium', 59.95, 20),
      (N'2011-10-12', N'Zip Front Sheath Dress', N'8', 138, NULL),
      (N'2010-05-24', N'Holly Gladiator Heel Shoes', N'7.5', 198, 40),
      (N'2011-02-16', N'Short Black Skirt', N'14', 55.85, 0.00),
      (N'2011-04-18', N'Color-Block Chambray Shirt', N'10', 85.25, 20);
GO
    To make easy to show the records, Evelyne wants to create a stored produce that would produce the item code, the name, the size, and the unit price. How can she write code to create that procedure? 
    1. CREATE OBJECT::Inventory.GetItems
AS PROCEDURE
    SELECT ItemCode, Name, Size, UnitPrice
    FROM Inventory.StoreItems;
GO
    2. CREATE PROCEDURE Inventory.GetItems
AS
    SELECT ItemCode, Name, Size, UnitPrice
    FROM Inventory.StoreItems;
GO
    3. CREATE OBJECT::Inventory.GetItems AS PROCEDURE
BEGIN
    SELECT ItemCode, Name, Size, UnitPrice
    FROM Inventory.StoreItems;
END
GO
    4. CREATE PROCEDURE OBJECT::Inventory.GetItems
RETURN
    SELECT ItemCode, Name, Size, UnitPrice
    FROM Inventory.StoreItems;
GO
    5. CREATE OBJECT::Inventory.GetItems
RETURN PROCEDURE
BEGIN
    SELECT ItemCode, Name, Size, UnitPrice
    FROM Inventory.StoreItems;
END
GO
  94. Evelyne works for a department store. She created a stored named GetItems that belongs to a scheman named Inventory. What code can she write to see the results of that stored procedure? 
    1. SELECT * FROM Inventory.GetItems;
GO
    2. WITH Inventory.GetItems
EXECUTE;
GO
    3. EXECUTE Inventory.GetItems;
GO
    4. USE Inventory.GetItems;
GO
    5. SET Inventory.GetItems ON;
GO
  95. Martha is working for an apartment building. She inherited the following table: 
    CREATE SCHEMA Listing;
GO
CREATE TABLE Listing.Apartment
(
	UnitNumber nchar(8) not null,
	Bedrooms int,
	Bathrooms real,
	Price money,
	Deposit money,
	Available bit
);
    To protect the table, Martha wants to create a stored procedure named GetUnits that other employees can use to see a list derived from the table. The list will include the unit number, the number of bedrooms, the number of bathrooms, and pricce. Because other people cannot directly execute the new stored procedure, she wants to allow them to use the marthag account. How should she create the stored procedure to make this possible? 
    1. CREATE OBJECT::PROCEDURE Registration.GetUnits
AS
    SET NOCOUNT ON
    SELECT UnitNumber, Bedrooms, Bathrooms, Price
    FROM Listing.Apartments
    WITH EXECUTE AS N'marthag';    
GO
    2. CREATE Registration.GetUnits
AS PROCEDURE
    SET NOCOUNT ON
    SELECT UnitNumber, Bedrooms, Bathrooms, Price
    FROM Listing.Apartments
    WITH EXECUTE AS N'marthag';    
GO
    3. CREATE PROCEDURE Registration.GetUnits
SET USER = N'marthag'
AS
    SET NOCOUNT ON
    SELECT UnitNumber, Bedrooms, Bathrooms, Price
    FROM Listing.Apartments;    
GO
    4. CREATE PROCEDURE Registration.GetUnits
AS
    SET NOCOUNT ON
    SELECT UnitNumber, Bedrooms, Bathrooms, Price
    FROM Listing.Apartments
    SET USER = N'marthag';    
GO
    5. CREATE PROCEDURE Registration.GetUnits
WITH EXECUTE AS N'marthag'
AS
    SET NOCOUNT ON
    SELECT UnitNumber, Bedrooms, Bathrooms, Price
    FROM Listing.Apartments;    
GO
  96. Martha is working for an apartment building. She inherited the following table and its records: 
    CREATE SCHEMA Listing;
GO
CREATE TABLE Listing.Apartment
(
	UnitNumber nchar(8) not null,
	Bedrooms int,
	Bathrooms real,
	Price money,
	Deposit money,
	Available bit
);
INSERT Listing.Apartments
VALUES('104', 2, 1.00, 1050.00, 300.00, 0),
      ('306', 3, 2.00, 1350.00, 425.00, 1),
      ('105', 1, 1.00, 885.00,  250.00, 1),
      ('202', 1, 1.00, 950.00,  325.00, 0),
      ('304', 2, 2.00, 1250.00, 300.00, 0),
      ('106', 3, 2.00, 1350.00, 425.00, 1),
      ('308', 0, 1.00, 875.00,  225.00, 1),
      ('203', 1, 1.00, 885.00,  250.00, 1),
      ('204', 2, 2.00, 1125.00, 425.00, 1),
      ('205', 1, 1.00, 1055.00, 350.00, 0);
GO
    Martha wants to create a stored procedure named ShowUnit that takes a unit number as argument and produces the record of the corresponding apartment. How can she write code for that stored procedure? 
    1. CREATE OBJECT::Listing.ShowUnit @UnitNbr  nchar(8)
RETURN PROCEDURE
AS
    SET NOCOUNT ON
    SELECT UnitNumber, Bedrooms, Bathrooms, Price, Deposit, Available
    FROM   Listing.Apartments
    WHERE  UnitNumber = @UnitNbr;
GO
    2. CREATE OBJECT::Listing.ShowUnit @UnitNbr  nchar(8)
RETURNS PROCEDURE
AS
    SET NOCOUNT ON
    RETURN SELECT UnitNumber, Bedrooms, Bathrooms,
                  Price, Deposit, Available
           FROM   Listing.Apartments
           WHERE  UnitNumber = @UnitNbr;
GO
    3. CREATE PROCEDURE Listing.ShowUnit @UnitNbr  nchar(8)
AS
    SET NOCOUNT ON
    SELECT UnitNumber, Bedrooms, Bathrooms,
           Price, Deposit, Available
    FROM   Listing.Apartments
    WHERE  UnitNumber = @UnitNbr;
GO
    4. CREATE PROCEDURE Listing.ShowUnit
AS
    DECLARE @UnitNbr  nchar(8)

    SET NOCOUNT ON
    RETURN SELECT UnitNumber, Bedrooms, Bathrooms,
                  Price, Deposit, Available
           FROM   Listing.Apartments
           WHERE  UnitNumber = @UnitNbr;
GO
    5. CREATE PROCEDURE OBJECT::Listing.ShowUnit
                        @UnitNbr  nchar(8)
BEGIN
    SET NOCOUNT ON
    RETURN SELECT UnitNumber, Bedrooms, Bathrooms,
                  Price, Deposit, Available
           FROM   Listing.Apartments
           WHERE  UnitNumber = @UnitNbr;
END
GO
  97. James is working for a department store. He has created two tables  and added afew records follows: 
    CREATE TABLE Inventory.Categories
(
    CategoryID int identity(1, 1) primary key,
    Category nvarchar(20) not null
);
GO
CREATE TABLE Inventory.StoreItems
(
    ItemNumber nvarchar(10) primary key,
    CategoryID int foreign key
        references Inventory.Categories(CategoryID),
    ItemName nvarchar(60) not null,
    Size nvarchar(20),
    UnitPrice money
);
GO

INSERT INTO Inventory.Categories(Category)
VALUES(N'Men'), (N'Women'), (N'Boys'), (N'Girls'),(N'Miscellaneous');
GO
INSERT INTO Inventory.StoreItems
VALUES(N'264850', 2, N'Long-Sleeve Jersey Dress', N'Petite', 39.95),
      (N'930405', 4, N'Solid Crewneck Tee', N'Medium', 12.95),
      (N'924515', 1, N'Hooded Full-Zip Sweatshirt', N'S', 69.95),
      (N'294936', 2, N'Cool-Dry Soft Cup Bra', N'36D', 15.55);
GO
    The employees of the company are in charge of creating and changing products records. To keep track of the records added or changed from the StoredItems table, James creates a table as follows: 
    CREATE TABLE Inventory.DatabaseOperations
(
    OperationID int identity(1,1) NOT NULL,
    ObjectType nchar(20) default N'Table',
    ObjectName nvarchar(40),
    PerformedBy nvarchar(50),
    ActionPerformed nvarchar(max),
    TimePerformed datetime,
    CONSTRAINT PK_Operations PRIMARY KEY(OperationID)
);
GO
    When the record of an item has been changed in the StoreItems table, James would like the DatabaseOperations table to receive a notification. How can James create a trigger to perform that operation? 
    1. CREATE TRIGGER OBJECT::Inventory.ProductUpdated
ON Inventory.StoreItems
FOR UPDATE
AS
BEGIN
    INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'Product was updated', GETDATE())
END
GO
    2. CREATE TRIGGER OBJECT::Inventory.ProductUpdated
ON Inventory.StoreItems
FOR UPDATE
AS
    RETURN
	INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'Product was updated', GETDATE()),
GO
    3. CREATE OBJECT::Inventory.ProductUpdated
ON Inventory.StoreItems
RETURNS TRIGGER
WITH UPDATE
AS
	INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'Product was updated', GETDATE()),
GO
    4. CREATE TRIGGER Inventory.ProductUpdated
ON Inventory.StoreItems 
AFTER UPDATE
AS
	INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'Product was updated', GETDATE());
GO
    5. CREATE OBJECT::Inventory.ProductUpdated
ON Inventory.StoreItems
AFTER UPDATE
AS TRIGGER
BEGIN
    INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'Product was updated', GETDATE())
END
GO
  98. James is working for a department store. He has created two tables  and added afew records follows: 
    CREATE TABLE Inventory.Categories
(
    CategoryID int identity(1, 1) primary key,
    Category nvarchar(20) not null
);
GO
CREATE TABLE Inventory.StoreItems
(
    ItemNumber nvarchar(10) primary key,
    CategoryID int foreign key
        references Inventory.Categories(CategoryID),
    ItemName nvarchar(60) not null,
    Size nvarchar(20),
    UnitPrice money
);
GO

INSERT INTO Inventory.Categories(Category)
VALUES(N'Men'), (N'Women'), (N'Boys'), (N'Girls'),(N'Miscellaneous');
GO
INSERT INTO Inventory.StoreItems
VALUES(N'264850', 2, N'Long-Sleeve Jersey Dress', N'Petite', 39.95),
      (N'930405', 4, N'Solid Crewneck Tee', N'Medium', 12.95),
      (N'924515', 1, N'Hooded Full-Zip Sweatshirt', N'S', 69.95),
      (N'294936', 2, N'Cool-Dry Soft Cup Bra', N'36D', 15.55);
GO
    The employees regularly create, change, and delete records from the tables. To keep track of operations in the StoredItems table, James creates a table as follows: 
    CREATE TABLE Inventory.DatabaseOperations
(
    OperationID int identity(1,1) NOT NULL,
    ObjectType nchar(20) default N'Table',
    ObjectName nvarchar(40),
    PerformedBy nvarchar(50),
    ActionPerformed nvarchar(max),
    TimePerformed datetime,
    CONSTRAINT PK_Operations PRIMARY KEY(OperationID)
);
GO
    To know when a record has been deleted from the StoredItems table, James wants a new record to be added to the DatabaseOperations table. How can James create a trigger to get those notifications? 
    1. CREATE TRIGGER Inventory.ProductRemoved
AFTER DELETE
ON Inventory.StoreItems
AS
    RETURN
	INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'Existing product deleted', GETDATE());
END
GO
    2. CREATE TRIGGER Inventory.ProductRemoved
ON Inventory.StoreItems 
AFTER DELETE
AS
	INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'Existing product deleted', GETDATE());
GO
    3. CREATE OBJECT::Inventory.ProductRemoved
RETURNS TRIGGER
AFTER DELETE
ON Inventory.StoreItems
BEGIN
    RETURN
	INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'Existing product deleted', GETDATE());
END
GO
    4. CREATE OBJECT::Inventory.ProductRemoved
AS TRIGGER
AFTER DELETE
ON Inventory.StoreItems
BEGIN
	INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'Existing product deleted', GETDATE());
END
GO
    5. CREATE TRIGGER Inventory.ProductRemoved
AFTER DELETE
ON Inventory.StoreItems
BEGIN
	INSERT INTO Inventory.DatabaseOperations(ObjectType,
				ObjectName, PerformedBy,
				ActionPerformed, TimePerformed)
	VALUES(DEFAULT, N'StoreItems', SUSER_SNAME(),
	       N'Existing product deleted', GETDATE());
END
GO
  99. Richard is a member of a team of programmers who have to create a database for a bank. To keep track of who creates tables in the project, he creates a table as follows: 
    CREATE SCHEMA Management;
GO
CREATE TABLE Management.Operations
(
    OperationID int identity(1,1) NOT NULL,
    ObjectType nchar(20) default N'Table',
    Employee nvarchar(50),
    ActionPerformed nvarchar(max),
    OccurredOn datetime,
    CONSTRAINT PK_Operations PRIMARY KEY(OperationID)
);
GO
    How can he create a trigger that creates a notification in the above table every time a table is created? 
    1. CREATE TRIGGER ObjectAdded
WHEN CREATE_TABLE
RETURN TABLE
AS
BEGIN
    INSERT INTO Management.Operations(ObjectType, Employee,
				ActionPerformed, OccurredOn)
	VALUES(DEFAULT, SUSER_SNAME(), 
	   N'Created a new table', GETDATE());
END
GO
    2. CREATE TRIGGER ObjectAdded
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    INSERT INTO Management.Operations(ObjectType, Employee,
				ActionPerformed, OccurredOn)
	VALUES(DEFAULT, SUSER_SNAME(), 
	   N'Created a new table', GETDATE());
END
GO
    3. CREATE TRIGGER ObjectAdded
TARGET DATABASE
WHEN CREATE_TABLE
AS
    INSERT INTO Management.Operations(ObjectType, Employee,
				ActionPerformed, OccurredOn)
	VALUES(DEFAULT, SUSER_SNAME(), 
	   N'Created a new table', GETDATE());
END
GO
    4. CREATE OBJECT::ObjectAdded
RETURN TRIGGER
WHEN CREATE_TABLE
AS TABLE
BEGIN
    SELECT INTO Management.Operations
    SET ObjectType = N'Table',
        Employee = SUSER_SNAME(),
        ActionPerformed =  N'Created a new table',
        OccurredOn = GETDATE();
END
GO
    5. CREATE OBJECT::ObjectAdded
RETURN TRIGGER
WHEN CREATE_TABLE
AS TABLE
BEGIN
    INSERT INTO Management.Operations(ObjectType, Employee,
				ActionPerformed, OccurredOn)
	VALUES(DEFAULT, SUSER_SNAME(), 
	   N'Created a new table', GETDATE());
END
GO
  100. Florence is a member of a team of database developers who work for a bank. The database already contains many objects such as tables, views, functions, etc. To find out when somebody creates, changes, or deletes an object, she creates a table as follows: 
    CREATE SCHEMA Management;
GO
CREATE TABLE Management.Operations
(
    OperationID int identity(1,1) NOT NULL,
    ObjectType nchar(20) default N'Table',
    Employee nvarchar(50),
    ActionPerformed nvarchar(max),
    OccurredOn datetime,
    CONSTRAINT PK_Operations PRIMARY KEY(OperationID)
);
GO
    Now she wants to create a trigger that gets a notification if an employees deletes one of the existing tables of the project. How can she create that trigger? 
    1. CREATE TRIGGER TabledRemoved
WHEN DROP_TABLE
AS
    INSERT INTO Management.Operations(ObjectType, Employee,
				ActionPerformed, OccurredOn)
	VALUES(DEFAULT, SUSER_SNAME(), 
	   N'An existing table was deleted', GETDATE());
GO
    2. CREATE OBJECT::TabledRemoved
AS TRIGGER
WHEN DROP_TABLE
BEGIN
    INSERT INTO Management.Operations(ObjectType, Employee,
				ActionPerformed, OccurredOn)
	VALUES(DEFAULT, SUSER_SNAME(), 
	   N'An existing table was deleted', GETDATE());
END
GO
    3. CREATE TRIGGER::TabledRemoved
ON DATABASE 
WHEN DROP_TABLE
BEGIN
    INSERT INTO Management.Operations(ObjectType, Employee,
				ActionPerformed, OccurredOn)
	VALUES(DEFAULT, SUSER_SNAME(), 
	   N'An existing table was deleted', GETDATE());
END
GO
    4. CREATE TabledRemoved
RETURNS TRIGGER
ON DATABASE 
WHEN DROP_TABLE
BEGIN
    INSERT INTO Management.Operations(ObjectType, Employee,
				ActionPerformed, OccurredOn)
	VALUES(DEFAULT, SUSER_SNAME(), 
	   N'An existing table was deleted', GETDATE());
END
GO
    5. CREATE TRIGGER TabledRemoved
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    INSERT INTO Management.Operations(ObjectType, Employee,
				ActionPerformed, OccurredOn)
	VALUES(DEFAULT, SUSER_SNAME(), 
	   N'An existing table was deleted', GETDATE());
END
GO
  101. What's the name of the stored procedure used to send a notification email when something happens in Microsoft SQL Server?
    1. sp_sendmail
    2. sp_dbsend_mail
    3. sp_send_dbmail
    4. sp_senddb_mail
    5. sp_send_mail
  102. Hank has the following tables of employees and sales they made during a certain period: 
    CREATE TABLE Personnel.Employees
(
    EmployeeNumber nchar(7) not null primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    EmploymentStatus smallint,
    HourlySalary money
);
GO
CREATE TABLE Commercial.Sales
(
    SaleID int identity(1, 1),
    EmployeeNumber nchar(7) not null,
    SaleDate date,
    Amount money
);
GO

INSERT Personnel.Employees
VALUES(N'284-680', N'Anselme', N'Bongos', 2, 18.62),
      (N'730-704', N'June', N'Malea', 1, 9.95),
      (N'735-407', N'Frank', N'Monson', 3, 14.58),
      (N'281-730', N'Jerry', N'Beaulieu', 1, 16.65);
GO
INSERT INTO Commercial.Sales(EmployeeNumber, SaleDate, Amount)
VALUES(N'284-680', N'2011-02-14', 4250),
      (N'735-407', N'2011-02-14', 5300),
      (N'730-704', N'2011-02-14', 2880),
      (N'281-730', N'2011-02-14', 4640),
      (N'284-680', N'2011-02-15', 4250),
      (N'281-730', N'2011-02-15', 3675);
GO
    Hank must produce a summary list that shows the employees and the average sale they made during that period. How can he write code to get that result? 
    1. SELECT EmployeeNumber, AVG(Amount)
FROM Sales
HAVING EmployeeNumber;
    2. SELECT EmployeeNumber, AVG(Amount)
FROM Sales
GROUP BY EmployeeNumber;
    3. SELECT DISTINCT EmployeeNumber, AVG(Amount)
FROM Sales;
GO
    4. SELECT EmployeeNumber, AVG(Amount)
FROM Sales
WHERE EmployeeNumber IS NOT NULL;
GO
    5. SELECT EmployeeNumber, AVG(Amount)
FROM Sales
GROUP BY Amount;
GO
  103. Hank has the following tables of employees and sales they made during a certain period: 
    CREATE TABLE Personnel.Employees
(
    EmployeeNumber nchar(7) not null primary key,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    EmploymentStatus smallint,
    HourlySalary money
);
GO
CREATE TABLE Commercial.Sales
(
    SaleID int identity(1, 1),
    EmployeeNumber nchar(7) not null,
    SaleDate date,
    Amount money
);
GO

INSERT Personnel.Employees
VALUES(N'284-680', N'Anselme', N'Bongos', 2, 18.62),
      (N'730-704', N'June', N'Malea', 1, 9.95),
      (N'735-407', N'Frank', N'Monson', 3, 14.58),
      (N'281-730', N'Jerry', N'Beaulieu', 1, 16.65);
GO
INSERT INTO Commercial.Sales(EmployeeNumber, SaleDate, Amount)
VALUES(N'284-680', N'2011-02-14', 4250),
      (N'735-407', N'2011-02-14', 5300),
      (N'730-704', N'2011-02-14', 2880),
      (N'281-730', N'2011-02-14', 4640),
      (N'284-680', N'2011-02-15', 4250),
      (N'281-730', N'2011-02-15', 3675);
GO
    Hank is asked to show a summary list of employees and the average sale they made during that period. The list must contain the employee's full name (made of last name followed by a comma and the first name) and the average sale. How can he write code to get that result? 
    1. SELECT e.LastName + N', ' + e.FirstName AS [Full Name],
       AVG(s.Amount) AS [Average Sales]
FROM Sales s INNER JOIN Employees e
ON s.EmployeeNumber = e.EmployeeNumber
GROUP BY e.LastName + N', ' + e.FirstName;
GO
    2. SELECT e.LastName + N', ' + e.FirstName AS [Full Name],
       AVG(s.Amount) AS [Average Sales]
FROM Sales s INNER JOIN Employees e
ON s.EmployeeNumber = e.EmployeeNumber
GROUP BY s.EmployeeNumber;
GO
    3. SELECT e.EmployeeNumber, AVG(s.Amount) AS [Average Sales]
FROM Sales s INNER JOIN Employees e
ON s.EmployeeNumber = e.EmployeeNumber
GROUP BY e.LastName + N', ' + e.FirstName;
GO
    4. SELECT e.LastName + N', ' + e.FirstName AS [Full Name],
       AVG(s.Amount) AS [Average Sales]
FROM Sales s INNER JOIN Employees e
GROUP BY e.EmployeeNumber 
ON s.EmployeeNumber = e.EmployeeNumber;
GO
    5. SELECT e.LastName + N', ' + e.FirstName AS [Full Name],
       AVG(s.Amount) AS [Average Sales]
FROM Sales s INNER JOIN Employees e
ON s.EmployeeNumber = e.EmployeeNumber
HAVING e.EmployeeNumber;
GO
  104. Martha has the following list of apartments and the records where the first digit of the unit number indicates its floor: 
    CREATE SCHEMA Listing;
GO
CREATE TABLE Listing.Apartments
(
	UnitNumber int not null,
	Bedrooms int,
	Bathrooms real,
	Price money,
	Deposit money,
	Available bit
);
GO
INSERT Listing.Apartments
VALUES(104, 2, 1.00, 1050.00, 300.00, 0),
      (306, 3, 2.00, 1350.00, 425.00, 1),
      (105, 1, 1.00, 885.00,  250.00, 1),
      (202, 1, 1.00, 950.00,  325.00, 0),
      (304, 2, 2.00, 1250.00, 300.00, 0),
      (106, 3, 2.00, 1350.00, 425.00, 1),
      (308, 0, 1.00, 875.00,  225.00, 1),
      (203, 1, 1.00, 885.00,  250.00, 1),
      (204, 2, 2.00, 1125.00, 425.00, 1),
      (205, 1, 1.00, 1055.00, 350.00, 0);
GO
    What codes can Martha write to get a list of available apartments from the second floor (Select 2)? 
    1. SELECT * FROM Listing.Apartments
WHERE UnitNumber IN(200, 299) AND (Available = 1);
GO
    2. SELECT * FROM Listing.Apartments
WHERE (UnitNumber >= 200) AND (UnitNumber <= 299) AND (Available = 1);
GO
    3. SELECT * FROM Listing.Apartments
WHERE (UnitNumber BETWEEN 200 AND 299) AND (Available = 1);
GO
    4. SELECT * FROM Listing.Apartments
WHERE UnitNumber IN(200, 299) OR (Available = TRUE);
GO
    5. SELECT * FROM Listing.Apartments
WHERE UnitNumber >= 200 AND Available IS NOT NULL;
GO
  105. Jimmy has a database that contains employees records and their time sheets as follows: 
    CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Payroll;
GO
CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(7) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO
CREATE TABLE Payroll.TimeSheets
(
	TimeSheetID int identity(1, 1) not null primary key,
	EmployeeNumber nchar(7) not null
	    foreign key references Personnel.Employees(EmployeeNumber),
	DateWorked date,
	TimeWorked decimal(6, 2)
);
GO
INSERT Personnel.Employees
VALUES(N'795-074', N'Steve', N'Leland', 16.46),
      (N'240-157', N'Alex', N'Randt', 10.55),
      (N'482-259', N'Janice', N'Lane', 8.64),
      (N'628-113', N'Jimmy', N'Walters', 20.24);
GO
INSERT INTO Payroll.TimeSheets(EmployeeNumber, DateWorked, TimeWorked)
VALUES(N'240-157', N'2011-04-04', 8.00),
      (N'628-113', N'2011-04-04', 9.50),
      (N'795-074', N'2011-04-04', 8.00),
      (N'482-259', N'2011-04-04', 6.50),
      (N'795-074', N'2011-04-05', 8.50),
      (N'482-259', N'2011-04-05', 8.00),
      (N'240-157', N'2011-04-05', 9.50),
      (N'628-113', N'2011-04-05', 7.50),
      (N'795-074', N'2011-04-06', 8.00),
      (N'240-157', N'2011-04-06', 8.50),
      (N'795-074', N'2011-04-07', 10.00),
      (N'628-113', N'2011-04-07', 8.00);
GO
    How can he write code to get a list that contains the employee number, the date he or she worked, and the time spent at work, without repeating records? 
    1. SELECT empl.EmployeeNumber [Empl #],
       pts.DateWorked [Date Worked],
       pts.TimeWorked [Time Worked] 
FROM   Personnel.Employees empl INNER JOIN Payroll.TimeSheets pts;
GO
    2. SELECT empl.FirstName [First Name], empl.LastName [Last Name],
       pts.DateWorked [Date Worked],
       pts.TimeWorked [Time Worked] 
FROM   Personnel.Employees empl OUTER JOIN Payroll.TimeSheets pts;
GO
    3. SELECT empl.EmployeeNumber [Empl #],
       pts.DateWorked [Date Worked],
       pts.TimeWorked [Time Worked] 
FROM   Personnel.Employees empl, Payroll.TimeSheets pts;
GO
    4. SELECT pts.EmployeeNumber [Empl #],
       pts.DateWorked [Date Worked],
       pts.TimeWorked [Time Worked] 
FROM   Payroll.TimeSheets pts;
GO
    5. SELECT DISTINCT empl.EmployeeNumber [Empl #],
       pts.DateWorked [Date Worked],
       pts.TimeWorked [Time Worked] 
FROM   Personnel.Employees empl, Payroll.TimeSheets pts;
GO
  106. Jimmy has a database that contains employees records and their time sheets as follows: 
    CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Payroll;
GO
CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(7) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO
CREATE TABLE Payroll.TimeSheets
(
	TimeSheetID int identity(1, 1) not null primary key,
	EmployeeNumber nchar(7) not null
	    foreign key references Personnel.Employees(EmployeeNumber),
	DateWorked date,
	TimeWorked decimal(6, 2)
);
GO
INSERT Personnel.Employees
VALUES(N'795-074', N'Steve', N'Leland', 16.46),
      (N'240-157', N'Alex', N'Randt', 10.55),
      (N'482-259', N'Janice', N'Lane', 8.64),
      (N'628-113', N'Jimmy', N'Walters', 20.24);
GO
INSERT INTO Payroll.TimeSheets(EmployeeNumber, DateWorked, TimeWorked)
VALUES(N'240-157', N'2011-04-04', 8.00),
      (N'628-113', N'2011-04-04', 9.50),
      (N'795-074', N'2011-04-04', 8.00),
      (N'482-259', N'2011-04-04', 6.50),
      (N'795-074', N'2011-04-05', 8.50),
      (N'482-259', N'2011-04-05', 8.00),
      (N'240-157', N'2011-04-05', 9.50),
      (N'628-113', N'2011-04-05', 7.50),
      (N'795-074', N'2011-04-06', 8.00),
      (N'240-157', N'2011-04-06', 8.50),
      (N'795-074', N'2011-04-07', 10.00),
      (N'628-113', N'2011-04-07', 8.00);
GO
    He want to get a list that contains the employee number, the first name, the last name, the date worked, and the time worked, without repeating records. What code can he write to get that list? 
    1. SELECT empl.EmployeeNumber [Empl #],
       empl.FirstName [First Name],
       empl.LastName [Last Name],
       pts.DateWorked [Date Worked],
       pts.TimeWorked [Time Worked] 
FROM   Personnel.Employees empl INNER JOIN Payroll.TimeSheets pts
ON     empl.EmployeeNumber = pts.EmployeeNumber;
GO
    2. SELECT empl.EmployeeNumber [Empl #],
       empl.FirstName [First Name],
       empl.LastName [Last Name],
       pts.DateWorked [Date Worked],
       pts.TimeWorked [Time Worked] 
FROM   Personnel.Employees empl CROSS JOIN Payroll.TimeSheets pts
ON     empl.EmployeeNumber = pts.EmployeeNumber;
GO
    3. SELECT empl.EmployeeNumber [Empl #],
       empl.FirstName + N' ' + empl.LastName [Full Name],
       pts.DateWorked [Date Worked],
       pts.TimeWorked [Time Worked] 
FROM   Personnel.Employees empl LEFT INNER JOIN Payroll.TimeSheets pts
ON     empl.EmployeeNumber = pts.EmployeeNumber;
GO

    4. SELECT DISTINCT(empl.EmployeeNumber,
       empl.FirstName,
       empl.LastName),
       pts.DateWorked [Date Worked],
       pts.TimeWorked [Time Worked] 
FROM   Personnel.Employees empl LEFT INNER JOIN Payroll.TimeSheets pts
ON     empl.EmployeeNumber = pts.EmployeeNumber;
GO

    5. SELECT empl.EmployeeNumber [Empl #],
       empl.FirstName [First Name],
       empl.LastName [Last Name],
       pts.DateWorked [Date Worked],
       pts.TimeWorked [Time Worked] 
FROM   Personnel.Employees empl LEFT JOIN Payroll.TimeSheets pts
ON     empl.EmployeeNumber = pts.EmployeeNumber
GROUP BY pts.EmployeeNumber;
GO
  107. Leslie has a database that contains employees records and their time sheets as follows: 
    CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Payroll;
GO
CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(7) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO
CREATE TABLE Payroll.TimeSheets
(
	TimeSheetID int identity(1, 1) not null primary key,
	EmployeeNumber nchar(7) not null
	    foreign key references Personnel.Employees(EmployeeNumber),
	DateWorked date,
	TimeWorked decimal(6, 2)
);
GO
INSERT Personnel.Employees
VALUES(N'795-074', N'Steve', N'Leland', 16.46),
      (N'240-157', N'Alex', N'Randt', 10.55),
      (N'482-259', N'Janice', N'Lane', 8.64),
      (N'628-113', N'Jimmy', N'Walters', 20.24);
GO
INSERT INTO Payroll.TimeSheets(EmployeeNumber, DateWorked, TimeWorked)
VALUES(N'240-157', N'2011-04-04', 8.00),
      (N'628-113', N'2011-04-04', 9.50),
      (N'795-074', N'2011-04-04', 8.00),
      (N'482-259', N'2011-04-04', 6.50),
      (N'795-074', N'2011-04-05', 8.50),
      (N'482-259', N'2011-04-05', 8.00),
      (N'240-157', N'2011-04-05', 9.50),
      (N'628-113', N'2011-04-05', 7.50),
      (N'795-074', N'2011-04-06', 8.00),
      (N'240-157', N'2011-04-06', 8.50),
      (N'795-074', N'2011-04-07', 10.00),
      (N'628-113', N'2011-04-07', 8.00);
GO
    What code can Leslie write to get a list that contains each employee number and the total time the employee worked during that time frame? 
    1. SELECT pts.EmployeeNumber [Empl #],
       SUM(pts.TimeWorked [Time Worked])
FROM   Payroll.TimeSheets pts
GROUP  BY pts.EmployeeNumber
WHERE  pts.EmployeeNumber IS NOT NULL;
GO

    2. SELECT pts.EmployeeNumber [Empl #],
       SUM(pts.TimeWorked [Time Worked])
FROM   Payroll.TimeSheets pts
GROUP  BY pts.EmployeeNumber
HAVING pts.EmployeeNumber IS NOT NULL;
GO

    3. SELECT pts.EmployeeNumber [Empl #],
       SUM(pts.TimeWorked) [Time Worked] 
FROM   Payroll.TimeSheets pts
GROUP  BY pts.EmployeeNumber;
GO
    4. SELECT pts.EmployeeNumber [Empl #],
       SUM(pts.TimeWorked) [Time Worked]
GROUP  BY pts.EmployeeNumber
FROM   Payroll.TimeSheets pts;
GO
    5. SELECT pts.EmployeeNumber [Empl #],
       SUM(pts.TimeWorked) [Time Worked]
FROM   Payroll.TimeSheets pts
GROUP  BY pts.TimeWorked;
GO
  108. Jimmy has a database that contains employees records and their time sheets as follows: 
    CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Payroll;
GO
CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(7) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO
CREATE TABLE Payroll.TimeSheets
(
	TimeSheetID int identity(1, 1) not null primary key,
	EmployeeNumber nchar(7) not null
	    foreign key references Personnel.Employees(EmployeeNumber),
	DateWorked date,
	TimeWorked decimal(6, 2)
);
GO
INSERT Personnel.Employees
VALUES(N'795-074', N'Steve', N'Leland', 16.46),
      (N'240-157', N'Alex', N'Randt', 10.55),
      (N'482-259', N'Janice', N'Lane', 8.64),
      (N'628-113', N'Jimmy', N'Walters', 20.24);
GO
INSERT INTO Payroll.TimeSheets(EmployeeNumber, DateWorked, TimeWorked)
VALUES(N'240-157', N'2011-04-04', 8.00),
      (N'628-113', N'2011-04-04', 9.50),
      (N'795-074', N'2011-04-04', 8.00),
      (N'482-259', N'2011-04-04', 6.50),
      (N'795-074', N'2011-04-05', 8.50),
      (N'482-259', N'2011-04-05', 8.00),
      (N'240-157', N'2011-04-05', 9.50),
      (N'628-113', N'2011-04-05', 7.50),
      (N'795-074', N'2011-04-06', 8.00),
      (N'240-157', N'2011-04-06', 8.50),
      (N'795-074', N'2011-04-07', 10.00),
      (N'628-113', N'2011-04-07', 8.00);
GO
    He want to get a list that contains the employee's name (made of the first name followed by space and the last name) and the time worked, without repeating records. What code can he write to get that list? 
    1. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked]
FROM   Payroll.TimeSheets pts JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName;
GO
    2. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked]
FROM   Payroll.TimeSheets pts LEFT OUTER JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.EmployeeNumber;
GO
    3. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked]
FROM   Payroll.TimeSheets pts LEFT OUTER JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY pts.TimeWorked;
GO
    4. SELECT DISTINCT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked]
FROM   Payroll.TimeSheets pts CROSS JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName;
GO
    5. SELECT DISTINCT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked]
ON pts.EmployeeNumber = empls.EmployeeNumber
FROM   Payroll.TimeSheets pts CROSS JOIN Personnel.Employees empls
GROUP BY pts.EmployeeNumber;
GO
  109. Bill has a database that contains employees records and their time sheets as follows: 
    CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Payroll;
GO
CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(7) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO
CREATE TABLE Payroll.TimeSheets
(
	TimeSheetID int identity(1, 1) not null primary key,
	EmployeeNumber nchar(7) not null
	    foreign key references Personnel.Employees(EmployeeNumber),
	DateWorked date,
	TimeWorked decimal(6, 2)
);
GO
INSERT Personnel.Employees
VALUES(N'795-074', N'Steve', N'Leland', 16.46),
      (N'240-157', N'Alex', N'Randt', 10.55),
      (N'482-259', N'Janice', N'Lane', 8.64),
      (N'628-113', N'Jimmy', N'Walters', 20.24);
GO
INSERT INTO Payroll.TimeSheets(EmployeeNumber, DateWorked, TimeWorked)
VALUES(N'240-157', N'2011-04-04', 8.00),
      (N'628-113', N'2011-04-04', 9.50),
      (N'795-074', N'2011-04-04', 8.00),
      (N'482-259', N'2011-04-04', 6.50),
      (N'795-074', N'2011-04-05', 8.50),
      (N'482-259', N'2011-04-05', 8.00),
      (N'240-157', N'2011-04-05', 9.50),
      (N'628-113', N'2011-04-05', 7.50),
      (N'795-074', N'2011-04-06', 8.00),
      (N'240-157', N'2011-04-06', 8.50),
      (N'795-074', N'2011-04-07', 10.00),
      (N'628-113', N'2011-04-07', 8.00);
GO
    He want to create a list that shows the employee's name (made of the first name followed a space and the last name), the time worked, and the weekly salary that mulitplies the time worked by the employee's hourly salary. What code can he write to get that list? 
    1. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked],
       SUM(pts.TimeWorked * empls.HourlySalary) [Weekly Salary]
FROM   Payroll.TimeSheets pts JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName;
GO
    2. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked],
       pts.TimeWorked * empls.HourlySalary AS [Weekly Salary]
FROM   Payroll.TimeSheets pts JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName;
GO
    3. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked],
       SUM(pts.TimeWorked) * SUM(empls.HourlySalary) AS [Weekly Salary]
FROM   Payroll.TimeSheets pts JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName;
GO
    4. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked],
       MAX(pts.TimeWorked * empls.HourlySalary) AS [Weekly Salary]
FROM   Payroll.TimeSheets pts JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName;
GO
    5. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked],
       VAL(pts.TimeWorked) * VAL(empls.HourlySalary) AS [Weekly Salary]
FROM   Payroll.TimeSheets pts JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName;
GO
  110. Daouda has a table of employees and another table for their time sheets as follows: 
    CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Payroll;
GO

CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(7) not null primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO
CREATE TABLE Payroll.TimeSheets
(
	TimeSheetID int identity(1, 1) not null primary key,
	EmployeeNumber nchar(7) not null
	    foreign key references Personnel.Employees(EmployeeNumber),
	DateWorked date,
	TimeWorked decimal(6, 2)
);
GO
INSERT Personnel.Employees
VALUES(N'795-074', N'Steve', N'Leland', 16.46),
      (N'240-157', N'Alex', N'Randt', 10.55),
      (N'482-259', N'Janice', N'Lane', 8.64),
      (N'628-113', N'Jimmy', N'Walters', 20.24);
GO
INSERT INTO Payroll.TimeSheets(EmployeeNumber, DateWorked, TimeWorked)
VALUES(N'240-157', N'2011-04-04', 8.00),
      (N'628-113', N'2011-04-04', 9.50),
      (N'795-074', N'2011-04-04', 8.00),
      (N'482-259', N'2011-04-04', 6.50),
      (N'795-074', N'2011-04-05', 8.50),
      (N'482-259', N'2011-04-05', 8.00),
      (N'240-157', N'2011-04-05', 9.50),
      (N'628-113', N'2011-04-05', 7.50),
      (N'795-074', N'2011-04-06', 8.50),
      (N'482-259', N'2011-04-04', 6.50),
      (N'240-157', N'2011-04-06', 9.50),
      (N'795-074', N'2011-04-07', 10.00),
      (N'628-113', N'2011-04-07', 8.00),
      (N'240-157', N'2011-04-07', 10.00),
      (N'240-157', N'2011-04-08', 8.50),
      (N'482-259', N'2011-04-08', 6.00),
      (N'628-113', N'2011-04-08', 8.00),
      (N'795-074', N'2011-04-08', 7.50);
GO
    To evaluate the overtime of employees, Daouda wants to get a list that contains the employee's name (made of the first name followed by space and the last name) and the time worked, only for employees who worked over 40 hours. What code can he write to get that list? 
    1. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked]
FROM   Payroll.TimeSheets pts JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName
WHERE SUM(pts.TimeWorked) >= 40.00;
GO
    2. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked]
FROM   Payroll.TimeSheets pts INNER JOIN Personnel.Employees empls
WHERE SUM(pts.TimeWorked) >= 40.00;
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName
GO
    3. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked]
FROM   Payroll.TimeSheets pts JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName
HAVING SUM(pts.TimeWorked) >= 40.00;
GO
    4. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked]
FROM   Payroll.TimeSheets pts INNER JOIN Personnel.Employees empls
HAVING SUM(pts.TimeWorked) >= 40.00;
ON pts.EmployeeNumber = empls.EmployeeNumber
GROUP BY empls.FirstName + N' ' + empls.LastName
GO
    5. SELECT empls.FirstName + N' ' + empls.LastName [Full Name],
       SUM(pts.TimeWorked) [Time Worked]
FROM   Payroll.TimeSheets pts INNER JOIN Personnel.Employees empls
ON pts.EmployeeNumber = empls.EmployeeNumber
HAVING SUM(pts.TimeWorked) >= 40.00
GROUP BY empls.FirstName + N' ' + empls.LastName;
GO
  111. Judith is working on a large database for a commercial bank. The database will be named KoloBank. It will have a primary and secondary log files. Judith  will store the records of the database in two hard drives: C: and D:. To prepare to eventually load the records, she creates a folder on the C: drive and names it Kolo Bank Primary. She creates another folder in the D: drive and names it Kolo Bank Secondary. To prepare to store the log files, she creates a folder named Kolo Bank Logs on the C: drive. What code can she use to create the database? 
    1. CREATE OBJECT DATABASE KoloBank
ON PRIMARY
WITH FILEGROUP = KoloBank1
  ( NAME = N'KoloBankMain',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankMain.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB),
FILEGROUP KoloBankPrimary
  ( NAME = N'KoloBankFirst',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankFirst.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB),
FILEGROUP = KoloBankSecondary
  ( NAME = N'KoloBankSecond',
    FILENAME = N'D:\Kolo Bank Secondary\KoloBankSecondady.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB)
LOG ON FILEGROUP = KoloBankLog
  ( NAME = N'KoloBankLog',
    FILENAME = N'C:\Kolo Bank Logs\KoloBankLogger.ldf',
    SIZE = 10MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 2MB);
GO
    2. CREATE DATABASE KoloBank
ON PRIMARY
  ( NAME = N'KoloBankMain',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankMain.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB),
FILEGROUP KoloBankPrimary
  ( NAME = N'KoloBankFirst',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankFirst.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB),
FILEGROUP KoloBankSecondary
  ( NAME = N'KoloBankSecond',
    FILENAME = N'D:\Kolo Bank Secondary\KoloBankSecondady.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB)
LOG ON
  ( NAME = N'KoloBankLog',
    FILENAME = N'C:\Kolo Bank Logs\KoloBankLogger.ldf',
    SIZE = 10MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 2MB);
GO
    3. CREATE OBJECT::KoloBank
AS DATABASE
ON PRIMARY
WITH FILEGROUP = KoloBank1
  ( NAME = N'KoloBankMain',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankMain.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB),
WITH SECONDARY FILEGROUP = KoloBankPrimary
  ( NAME = N'KoloBankFirst',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankFirst.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB),
WITH SECONDARY FILEGROUP = KoloBankSecondary
  ( NAME = N'KoloBankSecond',
    FILENAME = N'D:\Kolo Bank Secondary\KoloBankSecondady.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB)
WITH LOG FILEGROUP = KoloBankLog
  ( NAME = N'KoloBankLog',
    FILENAME = N'C:\Kolo Bank Logs\KoloBankLogger.ldf',
    SIZE = 10MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 2MB);
GO
    4. CREATE DATABASE KoloBank
ON PRIMARY
WITH PRIMARY FILEGROUP KoloBank1
  ( NAME = N'KoloBankMain',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankMain.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB),
WITH SECONDARY FILEGROUP KoloBankPrimary
  ( NAME = N'KoloBankFirst',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankFirst.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB),
WITH SECONDARY FILEGROUP ADD KoloBankSecondary
  ( NAME = N'KoloBankSecond',
    FILENAME = N'D:\Kolo Bank Secondary\KoloBankSecondady.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB)
ON LOG FILEGROUP KoloBankLog
  ( NAME = N'KoloBankLog',
    FILENAME = N'C:\Kolo Bank Logs\KoloBankLogger.ldf',
    SIZE = 10MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 2MB);
GO
    5. CREATE DATABASE KoloBank
PRIMARY FILEGROUP = KoloBank1
  ( NAME = N'KoloBankMain',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankMain.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB),
SECONDARY FILEGROUP = KoloBankPrimary
  ( NAME = N'KoloBankFirst',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankFirst.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB),
SECONDARY FILEGROUP = KoloBankSecondary
  ( NAME = N'KoloBankSecond',
    FILENAME = N'D:\Kolo Bank Secondary\KoloBankSecondady.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB)
ON LOG FILEGROUP = KoloBankLog
  ( NAME = N'KoloBankLog',
    FILENAME = N'C:\Kolo Bank Logs\KoloBankLogger.ldf',
    SIZE = 10MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 2MB);
GO
  112. Brian is working on a large database for a commercial bank. His colleague Judith has already created the database as follows: 
    CREATE DATABASE KoloBank
ON PRIMARY
  ( NAME = N'KoloBankMain',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankMain.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB),
FILEGROUP KoloBankPrimary
  ( NAME = N'KoloBankFirst',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankFirst.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB),
FILEGROUP KoloBankSecondary
  ( NAME = N'KoloBankSecond',
    FILENAME = N'D:\Kolo Bank Secondary\KoloBankSecondady.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB)
LOG ON
  ( NAME = N'KoloBankLog',
    FILENAME = N'C:\Kolo Bank Logs\KoloBankLogger.ldf',
    SIZE = 10MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 2MB);
GO
    Because of the large volume of records that the project will have, the database must be partitioned. To start, Brian has been asked to create a function that will partition records from left to right. The column used to manage the partitions will be of type int. What code can Brian use to create the partitions? 
    1. USE KoloBank;
GO
CREATE PARTITION FUNCTION KoloBankPartitions(int)
WITH VALUES(1)
IN RANGE LEFT;
GO
    2. USE KoloBank;
GO
CREATE FUNCTION KoloBankPartitions(int)
RETURNS PARTITION
FOR VALUES(1)
AS RANGE LEFT;
GO
    3. USE KoloBank;
GO
CREATE OBJECT::FUNCTION KoloBankPartitions(int)
AS PARTITION
FOR VALUES(1)
AS RANGE LEFT;
GO
    4. USE KoloBank;
GO
CREATE PARTITION FUNCTION KoloBankPartitions(int)
AS RANGE LEFT
FOR VALUES(1);
GO
    5. USE KoloBank;
GO
CREATE PARTITION FUNCTION KoloBankPartitions(int)
FOR VALUES(1)
AS RANGE LEFT;
GO
  113. Brian is working on a large database for a commercial bank. His colleague Judith has already created the database as follows: 
    CREATE DATABASE KoloBank
ON PRIMARY
  ( NAME = N'KoloBankMain',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankMain.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB),
FILEGROUP KoloBankPrimary
  ( NAME = N'KoloBankFirst',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankFirst.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB),
FILEGROUP KoloBankSecondary
  ( NAME = N'KoloBankSecond',
    FILENAME = N'D:\Kolo Bank Secondary\KoloBankSecondady.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB)
LOG ON
  ( NAME = N'KoloBankLog',
    FILENAME = N'C:\Kolo Bank Logs\KoloBankLogger.ldf',
    SIZE = 10MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 2MB);
GO
    Based on the large volume of records that the project will have, the database must be partitioned. A partition function was already created as follows: 
    USE KoloBank;
GO
CREATE PARTITION FUNCTION KoloBankPartitions(int)
AS RANGE LEFT
FOR VALUES(1);
GO
    To tie the file groups to the scheme, Brian must create a partition function. How can he write code to create the appropriate partition scheme? 
    1. USE KoloBank;
GO
CREATE PARTITION SCHEME KoloBankScheme
AS PARTITION KoloBankPartitions
TO (KoloBankPrimary, KoloBankSecondary);
GO
    2. CREATE FUNCTION KoloBankScheme
AS PARTITION SCHEME
WITH PARTITION KoloBankPartitions
FOR (KoloBankPrimary, KoloBankSecondary);
GO
    3. CREATE OBJECT::KoloBankScheme
RETURNS PARTITION SCHEME
FOR PARTITION KoloBankPartitions
ON (KoloBankPrimary, KoloBankSecondary);
GO
    4. CREATE PARTITION SCHEME KoloBankScheme
FOR PARTITION KoloBankPartitions
ON (KoloBankPrimary, KoloBankSecondary);
GO
    5. CREATE OBJECT::KoloBankScheme
AS PARTITION SCHEME
ON PARTITION KoloBankPartitions
TO (KoloBankPrimary, KoloBankSecondary);
GO
  114. Julia is working on a large database for a commercial bank. A colleague has already created the database as follows: 
    CREATE DATABASE KoloBank
ON PRIMARY
  ( NAME = N'KoloBankMain',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankMain.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB),
FILEGROUP KoloBankPrimary
  ( NAME = N'KoloBankFirst',
    FILENAME = N'C:\Kolo Bank Primary\KoloBankFirst.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB),
FILEGROUP KoloBankSecondary
  ( NAME = N'KoloBankSecond',
    FILENAME = N'D:\Kolo Bank Secondary\KoloBankSecondady.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 2MB)
LOG ON
  ( NAME = N'KoloBankLog',
    FILENAME = N'C:\Kolo Bank Logs\KoloBankLogger.ldf',
    SIZE = 10MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 2MB);
GO
    Another colleague created a partition function and a partition scheme: 
    USE KoloBank;
GO
CREATE PARTITION FUNCTION KoloBankPartitions(int)
AS RANGE LEFT
FOR VALUES(1);
GO

CREATE PARTITION SCHEME KoloBankScheme
AS PARTITION KoloBankPartitions
TO (KoloBankPrimary, KoloBankSecondary);
GO
    As the main database developer, Julia has to create a table that will store customers records. Among others, the table will have a column named CustomerID of type int. The above partition scheme will decide what file group must hold the records. How should the table be created? 
    1. WITH KoloBankScheme
CREATE TABLE Customers
(
    CustomerID int identity(1, 1) primary key,
    AccountNumber nchar(10) not null,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null
)
USE CustomerID AS PARTITION;
GO
    2. CREATE TABLE Customers
(
    CustomerID int identity(1, 1) primary key
	WITH KoloBankScheme,
    AccountNumber nchar(10) not null,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null
);
GO
    3. CREATE TABLE Customers
(
    CustomerID int identity(1, 1) primary key,
    AccountNumber nchar(10) not null,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null
) ON KoloBankScheme(CustomerID);
GO
    4. CREATE TABLE Customers1
(
    CustomerID int identity(1, 1) primary key,
    AccountNumber nchar(10) not null,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null
);
SET PARTITION::KoloBankScheme FOR CustomerID;
GO
    5. USING KoloBankScheme(CustomerID)
CREATE TABLE Customers
(
    CustomerID int identity(1, 1) primary key,
    AccountNumber nchar(10) not null,
    FirstName nvarchar(20),
    LastName nvarchar(20) not null
);
GO
  115. Marc is creating a table to hold the employees of his organization. He has started the table as follows: 
    CREATE TABLE Employees
(
    EmployeeNumber nchar(60),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    FullName AS LastName + N', ' + FirstName,
);
GO
    He wants the value of the FullName column to be saved in memory as an actual value. If it is possible, how should Marc modify code to make it happen?
    1. The value of computed column cannot be saved
    2. CREATE TABLE Employees
(
    EmployeeNumber nchar(60),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    FullName AS LastName + N', ' + FirstName PERSISTED,
);
GO
    3. CREATE TABLE Employees
(
    EmployeeNumber nchar(60),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    PERSIST FullName AS LastName + N', ' + FirstName,
);
GO
    4. CREATE TABLE Employees
(
    EmployeeNumber nchar(60),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    FullName AS LastName + N', ' + FirstName CONSTRAINT PERSITENT,
);
GO
    5. CREATE TABLE Employees
(
    EmployeeNumber nchar(60),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    FullName AS LastName + N', ' + FirstName,
    CONSTRAINT P_FullName PERSISTANCE(FullName)
);
GO

Answers
  1. Answers
    1. Right Answer: That's the right answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  2. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer: That's the right answer
    4. Wrong Answer
    5. Wrong Answer
  3. Answers
    1. Wrong Answer: There is no sp_autogenerate stored procedure
    2. Wrong Answer: Adding the ProductID in the INSERT statement will only continue with the error
    3. Wrong Answer: Calling the MAX() aggregate function may produce the highest product number but would not lead to a solution
    4. Right Answer: Setting IDENTITY_INSERT to OFF will make it possible to recover from a previous IDENTITY_INSERT set to ON. The code to do this is: 
      SET IDENTITY_INSERT Products OFF;
GO
    5. Wrong Answer: There is no AUTOINCREMENT keyword in Transact-SQL
  4. Answers
    1. Wrong Answer: The SET operator is not used to create an index
    2. Wrong Answer: The AS keyword will cause an error
    3. Right Answer: That's a good way to create an index
    4. Wrong Answer: There is no selection to make when creating an index
    5. Right Answer: The CREATE INDEX expression is not followed by EXEC
  5. Answers
    1. Wrong Answer: CREATE PRIMARY KEY WITH is not a valid expression
    2. Wrong Answer: That's one way to create a primary key
    3. Right Answer: That's another way to create a primary key
    4. Wrong Answer: The REFERENCES keyword is not used to create a primary key
    5. Right Answer: That's one more way to create a primary key
  6. Answers
    1. Wrong Answer: The code will produce an error because the COUNT() function doesn't define what employee number it is referring to
    2. Right Answer: That code will produce the list of employee with the number of sales each made
    3. Wrong Answer: The position of the GROUP BY clause will cause an error
    4. Wrong Answer: The "HAVING Amount NOT NULL" expression has no meaning
    5. Wronog Answer: The WHERE clause will cause an error
  7. Answers
    1. Wrong Answer: That is the wrong position to include the check option clause
    2. Wrong Answer: That is the wrong position to include the check option clause
    3. Wrong Answer: There is no SET CHECK OPTION ON expression
    4. Right Answer: That code will make sure any new record added through the view obeys the condition
    5. Wronog Answer: That's a wrong to create a check option
  8. Answers
    1. Wrong Answer: Yes there is something wrong with that code. It will produce an error
    2. Wrong Answer: There is no such a limit on the nchar data type
    3. Right Answer: You can use either UNIQUE or PRIMARY KEY, but not both
    4. Wrong Answer: Yes it can, as long as you follow some rules
    5. Wronog Answer: That's a nice suggestion but it is not a rule
  9. Answers
    1. Wrong Answer: The UPDATE keyword is not used to modify a view
    2. Right Answer: That code will change the view
    3. Wrong Answer: There is no stored procedure named sp_change
    4. Wrong Answer: If you want to change a view, you must indicate the complete list of columns
    5. Wrong Answer: The ADD keyword does not add a new column to a view
  10. Answers
    1. Wrong Answer: ALL and the asterisk will caus an error
    2. Wrong Answer: That code will remove members who are not suspended
    3. Wrong Answer: There is no REMOVE keyword in Transact-SQL
    4. Wrong Answer: There no stored procedure named sp_remove
    5. Right Answer: That code will delete all suspended members
  11. Answers
    1. Wrong Answer: That code will update the records of human resources employees to change them to the personnel department
    2. Right Answer: That code will update the records of employees from the personnel department, change them to the human resources department, and show the list of records that were changed
    3. Wrong Answer: The DELETED word will produce an error
    4. Wrong Answer: The OUTPUT clause must come after the UDATE expression
    5. Wrong Answer: The expressions are misplaced in that formula
  12. Answers
    1. Wrong Answer: The ItemDescription doesn't have a default value. Therefore, omitting it would cause an error
    2. Right Answer:
    3. Wrong Answer: The number of fields in the INSERT code is lower than the actual number of fields in the table
    4. Right Answer:
  13. Answers
    1. Wrong Answer: There is no difference writing the INSERT INTO code before or after the CREATE USER code
    2. Wrong Answer: The primary key doesn't control whether records can be updated or not on a table
    3. Right Answer: In order to be able to update records, a user must be granted at least both SELECT and UPDATE permissions
    4. Wrong Answer: That whole answer doesn't make sense
    5. Wrong Answer: In Transact-SQL, you specify whatever name you want to use when creating a user. It can even be a fictitious name
  14. Answers
    1. Wrong Answer: The option to grant permissions is missing
    2. Wrong Answer: There is no CONNECT permission on tables
    3. Right Answer:
    4. Wrong Answer: GRANT OPTION = ALL is not a valid Transact-SQL statement
    5. Wrong Answer: WITH OPTION GRANT ALL is not a valid Transact-SQL statement
  15. Answers
    1. Wrong Answer: The statement is fine and Teachers.CourseID is valid
    2. Wrong Answer: The result will include CourseID since it is in the SELECT statement
    3. Right Answer: In order to be able to update records, a user must be granted at least both SELECT and UPDATE permissions
    4. That answer doesn't make sense: A SELECT statement doesn't specify what a primary key is
    5. Wrong Answer: The result will show all three fields
  16. Answers
    1. Wrong Answer: [CMST 320] is not valid
    2. Right Answer: This will show all records of the Teachers table
    3. Wrong Answer: The WHEN keyword is not valid in this context
    4. Right Answer: This will show all records that match the name of the teacher
    5. Right Answer: This will show all teachers whose CourseID match the criterion
  17. Discussions
    1. Wrong Answer: Either CourseID or Teachers.CourseID is valid
    2. Wrong Answer: The teachers will not appear in the result
    3. Wrong Answer: The teachers will not appear in the result
    4. Right Answer:
    5. Wrong Answer: The other columns must not be included in the statement
  18. Discusions
    1. Wrong Answer: Only one of those fields can be used. If both are used with the GROUP BY clause, the statement produces an error
    2. Wrong Answer: The statement produces an error because there is no aggregate function to validate the GROUP BY clause
    3. Right Answer: This produces the list of courses and the number of teachers who teach that course
    4. Right Answer: This produces the list of teachers and the number of courses that each teacher teaches
    5. Wrong Answer: The field i the SELECT section must be the same in the GROUP BY clause
    6. Wrong Answer: The field i the SELECT section must be the same in the GROUP BY clause
  19. Discussions
    1. Right Answer:
    2. Wrong Answer:
    3. Wrong Answer:
    4. Wrong Answer:
    5. Wrong Answer:
  20. Answers
    1. Wrong Answer: If/Since the user is able to see the table (she didn't say that she could not connect to the database; she said she was denied the ability to create records), then she is able to connect to the database. The CONNECT permission is  not an issue
    2. Wrong Answer: If you were able to execute the script, it means there is no problem with the username. Besides, that username is fine
    3. Right Answer: Since the script included the permissions, you must make sure you grant the right ones. Even though you granted the INSERT permission, it is not enough. The following script should solve the problem: 
      USE SuperMarket1;
GO
GRANT SELECT
ON OBJECT::Personnel.Employees
TO [Hermine];
GO
    4. Wrong Answer: That is not an issue. We assume that you are the database administrator/developer and system administrator
    5. Wrong Answer: Who marked the table as read-only and why? That was not an issue
  21. Answers
    1. Right Answer: A view must not include ORDER BY, unless the SELECT statement has a TOP clause
    2. Wrong Answer: The empl name is an alias for the table and it doesn't have to be in square brackets. Since the names of fields probably include spaces, they can also include space in this context
    3. Wrong Answer: The names of columns can contain space is they contain space in the original table
    4. Wrong Answer: If the ORDER BY were possible, it would be written after the FROM expression
    5. Wrong Answer: The name of a view can/must be preceded by a schema
  22. Answers
    1. Right Answer: You cannot create a view from a temporary table
    2. Wrong Answer: Since you cannot create a temporary view, the name of a view cannot start with #
    3. Wrong Answer: Both SQL and Transact-SQL allow names of objects to contain space
    4. Wrong Answer: The name of a temporary table must start with #
    5. Wrong Answer: That answer doesn't make sense. A primary key has nothing to do with the type of table: temporary or not
  23. Answers
    1. Wrong Answer: The result will show the list of teachers and the courses they teach. That code is the same as: 
      SELECT TeacherID, FullName, Teachers.CourseID
FROM Teachers
    2. Wrong Answer: That code will produce an error because of the position of OUTER
    3. Wrong Answer: The result will show the list of teachers and the courses they teach
    4. Wrong Answer: That code will show a list of courses: their codes and names
    5. Right Answer: This will produce the list of all Teachers whether they have a matching CourseID record in the Courses table or not
  24. Answers
    1. Right Answer: The result is the list of all employees and the department each employee belongs to
    2. Wrong Answer: This would have been possible with a CROSS join, but that's not the type of join in the code
    3. Wrong Answer: The database engine is able to recognize the relationship based on the types of fields
    4. Wrong Answer: The code will not produce any error
    5. Wrong Answer: That answer doesn't make sense. That answer is too vague
  25. Answers
    1. Wrong Answer: There is no error in either code
    2. Right Answer: Both codes produce the same error
    3. Wrong Answer: There is no error in either code
    4. Wrong Answer: There is no error in either code
    5. Wrong Answer: Both codes are the same
  26. Answers
    1. Wrong Answer: There is no error in the code. The WHERE expression is always written after the FROM clause
    2. Wrong Answer: Including or omitting the Departments.DepartmentCode field depends on the intended result but has no effect on the final result
    3. Right Answer: The result will consist of a list of employees who belong to the Research & Development department
    4. Wrong Answer: That answer doesn't makle sense
    5. Wrong Answer: The list will not include employees not in the RSDV department
  27. Answers
    1. Wrong Answer: Yes you can delete a view
    2. Wrong advice: It appears that Maggie wants to delete a view, nothing to do with a table
    3. Wrong advice: There is no reason to worry about the schema
    4. Wrong Answer: There is no reason to worry about the records. The records are stored in the table(s), not the view
    5. Right Answer: When you decide the delete a view, the main concern is about the permissions because they would be lost. If you decide to re-create the view, you must grant the same permissions to the users who had access to the old view
  28. Answers
    1. Wrong Answer: Yes triggers use or need a schema
    2. Wrong Answer: We need neither a user nor a login. It appears that Frank just wants to tes code and chose not to specify a user or login. Besides, since the original code executes successfully, the user name or login are not an issue
    3. Wrong Answer: That will not solve the problem
    4. Right Answer: Both the Customers table and the ForCustomers triger must use the same schema
    5. Wrong Answer: The presence or absence of a primary key will not determine whether Frank can create a record or not. In fact, the error has nothing to do with any of the tables in the database. The error is related to the trigger
  29. Answers
    1. Wrong Answer: That's not how the IN operator is used
    2. Right Anwer: That code will produce the list of apartments from the second floor
    3. Wrong Answer: There is no FROM ... TO condition in SQL
    4. Right Anwer: That code will produce the list of apartments from the second floor
    5. Wrong Answer: The FROM 200 TO 300 expression is invalid
  30. Answers
    1. Right Answer: That will work
    2. Wrong Answer: That code will produce an erro because of the presence of CONSTRAINT
    3. Right Answer: That's another valid way to create a check constraint
    4. Wrong Answer: That code will produce an error because of ON
    5. Wrong Answer: That code uses an invalid formula to create a check constraint
  31. Answers
    1. Wrong Answer: 20.15 is not the highest salary in the Research & Development department
    2. Wrong Answer: The value seems to come from a department different than the one in the WHERE condition
    3. Right Answer: The code produces the highest paid salary in the Research & Development department
    4. Wrong Answer: That value is from the Accounting department
    5. Wrong Answer: That's not the highest salary in the Research & Development department
  32. Answers
    1. Right Answer: That's the right sequence of keywords to produce a number of records
    2. Wrong Answer: The AS clause must be included after a column
    3. Wrong Answer: The FROM keyword must not follow SELECT
    4. Wrong Answer: When a WHERE condition is used, it must be included after the FROM statement
    5. Wrong Answer: The WHERE condition is not added immediately after SELECT
  33. Answers
    1. Wrong Answer: FROM cannot follow SELECT
    2. Wrong Answer: FROM cannot follow SELECT
    3. Right Answer: The MAX() function is used to get the highest value of a column
    4. Wrong Answer: There is no HIGH function in Transact-SQL
    5. Wrong Answer: If used, the AS clause must be included (immediately) after the column
  34. Answers
    1. Wrong Answer: That code will produce the highest salary in the research & developement department
    2. Right Answer: That code will produce the highest salary in the human resources department
    3. Wrong Answer: The presence of OUTER will produce an error in that code
    4. Wrong Answer: There is no aggregate function named HIGH in Transact-SQL
    5. Wrong Answer: There is no function or stored procedure named sp_high
  35. Answers
    1. Right Answer: That code will produce the list of employees and their manager name, if any, in the last column. If an employee does not have a manager, the last column shows NULL
    2. Wrong Answer: A subquery can include only one column
    3. Wrong Answer: A subquery must produce only one value
    4. Wrong Answer: That code will produce the list of employees but the last column shows the salaries
    5. Wrong Answer: That code will produce the list of employees but the last column shows the name of the employee from the first column
  36. Answers
    1. Right Answer: That code will produce the list of employees who have a manager and will show the name of that manager on the last column
    2. Wrong Answer: That code will produce the list of employees and their manager's name, but the list includes the employees who don't have a manager
    3. Wrong Answer: You don't use an IS or IS NO condition after an AS alias
    4. Wrong Answer: That code will produce the list of employees and their manager's employee number. The list also includes the employees who don't have a manager
    5. Wrong Answer: The NOT NULL expression is misplaced
  37. Answers
    1. Wrong Answer: A sub-query must include only one column
    2. Right Answer: That code will produce the list of employees, their salaries, and their manager's name
    3. Wrong Answer: The parts of a WHERE statement must be on the same type. Those in empl.EmployeeNumber = sal.SalaryID are not
    4. Wrong Answer: There no SalaryID in the Employees table
    5. Wrong Answer: Although that code will work fine, it shows the employee number twice, in the last two columns
  38. Answers
    1. Wrong Answer: A subquery must include a condition
    2. Wrong Answer: The value provided by the subquery is a different type than the one expected by the WHERE condition
    3. Right Answer: That code will produce the necessary information
    4. Wrong Answer: That will simply give a list of all employees and show a common salary for all of them
    5. Wrong Answer: That code produces all employees
  39. Answers
    1. Wrong Answer: That code will move the employees from the human resources department to the personnel department
    2. Right Answer: That code will move the employees from the personnel department to the human resources department
    3. Wrong Answer: The FROM keyword will produce an error
    4. Wrong Answer: The SET and the UDATE keywords are inversed
    5. Wrong Answer: The SET and the UDATE keywords are inversed
  40. Answers
    1. Wrong Answer: That code will delete the table, including all of its records
    2. Wrong Answer: There is no REMOVE operator in Transact-SQL
    3. Wrong Answer: The asterisk is not used in a DELETE operation
    4. Right Answer: That code will delete all records from the table but keep the table itself
    5. Wrong Answer: There is no stored procedure with that name
  41. Answers
    1. Wrong Answer: BEGIN and END are not used when creating a view
    2. Right Answer: That code creates a view that includes the employee number, the first name, and the last name
    3. Wrong Answer: The RETURN TABLE expression has no meaning here
    4. Wrong Answer: WITH and END have no meaning here
    5. Wrong Answer: The EXECUTE keyword is not used to create a view
  42. Answers
    1. Wrong Answer: The UPDATE keyword is not used to modify a table
    2. Right Answer: That code will change the table and add the new column
    3. Wrong Answer: There is no EXECUTE MODIFY expresion in Transact-SQL
    4. Wrong Answer: There is stored procedure named sp_change
    5. Wrong Answer: The AS keyword must not be used
  43. Answers
    1. Wrong Answer: The WITH keyword will create an error
    2. Right Answer: That code adds a new column that will act as a primary key
    3. Right Answer: That code adds a new column that will act as a primary key
    4. Wrong Answer: The UPDATE keyword is not used to change a table
    5. Wrong Answer: That formula will produce an error
  44. Answers
    1. Right Answer: That code will produce the list of employees whose last names end with a
    2. Wrong Answer: The underscore would not produce the needed result
    3. Wrong Answer: Including a in square brackets would not accomplish anything
    4. Wrong Answer: Preceding a with an accent would not do anything
    5. Wrong Answer: The exclamation point is not a valid wildcard in Transact-SQL
  45. Answers
    1. Wrong Answer: Considering that 0 is a value, if a product has receive 0 as its discount, then it actually has a discount
    2. Wrong Answer: That code will not produce anything
    3. Wrong Answer: That code will show the list of products whose discount is not 0
    4. Wrong Answer: That code will produce an error because there is no operator before NULL
    5. Right Answer: That code will show a list of products that have a discount
  46. Answers
    1. Wrong Answer: There is no such an expression as SET ENCRYPTION ON
    2. Wrong Answer: There is no ENCRYPT WHEN DONE valid expression
    3. Right Answer: To encrypt the entry code of a view in a database, use WITH ENCRYPTION after the name of the view
    4. Wrong Answer: The place to specify encryptio is wrong
    5. Wrong Answer: There is no such a thing as SET ENCRYPTION ON
  47. Answers
    1. Wrong Answer: There is no reson to select anything when granting a permission
    2. Right Answer: That code will grant the ALTER permission to Peter
    3. Wrong Answer: There is no stored procedure named sp_grant
    4. Wrong Answer: There is PERMISSION keyword to be used when granting a permission
    5. Wrong Answer: There is no such a thing as SET PERMISSION GRANT
  48. Answers
    1. Wrong Answer: There is no value DO SCHEMA BINDING expression in Transact-SQL
    2. Wrong Answer: The SCHEMA BINDING expression will create an error
    3. Wrong Answer: The FOR word will cause an error
    4. Right Answer: To bind a schema to its parent table, add a WITH SCHEMABINDING clause when creating the view
    5. Wrong Answer: There is no valid SET SCHEMA BINDING expression
  49. Answers
    1. Wrong Answer: The order of HAVING and GROUP BY is not right
    2. Wrong Answer: The presence of SUM will make this code show all employees
    3. Right Answer: That code will show the employees who made sales over 4750
    4. Wrong Answer: The WHERE clause will cause that code to produce an error
    5. Wrong Answer: The WHERE condition creates an error
  50. Answers
    1. Right Answer: That code will show the list of employees. Each record will show the first name, the last name and the number of sales the employee made. The number of sales are ordered in ascending order
    2. Wrong Answer: Since the Amount is not represented in the SELECT statement, the code will produce an error
    3. Wrong Answer: The presences of ORDER BY, HAVING, and ORDER BY as misplaced
    4. Wrong Answer: The FROM expression must come immediately after the SELECT statement
    5. Wrong Answer: The GROUP BY expression must be the last
  51. Answers
    1. Wrong Answer: There is no valid RENAME COLUMN expression in Transact-SQL
    2. Wrong Answer: There is no RENAME operator to rename a column
    3. Right Answer: You use the sp_rename stored procedure to rename a columne and you should add N'COLUMN'
    4. Wrong Answer: There in stored procedure named sp_changename
    5. Wrong Answer: There is no RENAME operator to rename a column
  52. Answers
    1. Wrong Answer: The DELETE keyword is not used to remove a column
    2. Wrong Answer: The SET keyword is not used to remove a column
    3. Wrong Answer: The UPDATE keyword is not used to remove a column
    4. Right Answer: That code will delete the column
    5. Wrong Answer: The UPDATE keyword is not used to remove a column and the SET keyword cannot be used as in this code
  53. Answers
    1. Right Answer: That code will show the records from the Employees table. Each record will show each of the records from the second table. The result will include only products that cost more than 50.00
    2. Wrong Answer: There is no reason to use DISTINCT here
    3. Wrong Answer: You cannot create a Boolean operation between a string-based column ([Empl #] and a number-based column (Number)
    4. Wrong Answer: You can't use GROUP BY since there is no aggregate function in the statement
    5. Wrong Answer: Since the unit price is not used in an aggregate function in the SELECT statement, this code will produce an error
  54. Answers
    1. Wrong Answer: Unlike the primary key that can be created separate from its column, a foreign key must be created in the statement of its column
    2. Right Answer: That code will create a foreign key that references a column from the Employees table
    3. Right Answer: That's another way to create a primary key
    4. Wrong Answer: If you decide to use the CONSTRAINT keyword, you must create a name for the foreign key
    5. Wrong Answer: CREATE FOREIGN KEY WITH is not a valid expression
  55. Answers
    1. Wrong Answer: You don't specify the name of the column when deleting an index
    2. Wrong Answer: The DELETE keyword is not valid in this context
    3. Wrong Answer: The FROM keyword will cause an error
    4. Right Answer: That's the code to delete an index
    5. Wrong Answer: The DELETE keyword cannot be used like that
  56. Answers
    1. Wrong Answer: The combination of word after ALTER TABLE is not valid
    2. Wrong Answer: You don't use UPDATE to add a new column
    3. Wrong Answer:The AS keyword is not used when adding a new column
    4. Wrong Answer: There is no CHANGE keyword to be used here
    5. Right Answer: That code will add a new column that is a foreign key
  57. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer: That's the right formula
  58. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer: That's the right formula
    4. Wrong Answer
    5. Wrong Answer
  59. Answers
    1. Right Answer: That's the right answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  60. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer: That's the right answer
    5. Wrong Answer
  61. Answers
    1. Wrong Answer
    2. Right Answer: That's the right answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  62. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer: That's the right answer
    4. Wrong Answer
    5. Wrong Answer
  63. Answers
    1. Wrong Answer: There is no AUTONUMBER in Transact-SQL
    2. Wrong Answer: The COUNTER word will produce an error
    3. Wrong Answer: There is no AUTOINCREMENT in Transact-SQL
    4. Right Answer: That's the right answer
    5. Wrong Answer
  64. Answers
    1. Right Answer: That's the right answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  65. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer: That's the right answer
    5. Wrong Answer
  66. Answers
    1. Wrong Answer
    2. Right Answer: That's the right answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  67. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer: That's the right answer
  68. Answers
    1. Right Answer: That's the right answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  69. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer: That's the right answer
    4. Wrong Answer
    5. Wrong Answer
  70. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer: That's the right answer
    5. Wrong Answer
  71. Answers
    1. Wrong Answer
    2. Right Answer: That's the right answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  72. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer: That's the right answer
  73. Answers
    1. Right Answer: That's the right answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  74. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  75. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  76. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  77. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  78. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  79. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  80. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  81. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer
  82. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  83. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  84. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  85. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  86. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  87. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  88. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer
  89. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  90. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  91. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  92. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  93. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  94. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  95. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer
  96. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  97. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  98. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  99. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  100. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer
  101. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  102. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  103. Answers
    1. Right Answer
      2.
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  104. Answers
    1. Wrong Answer
    2. Right Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  105. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  106. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  107. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  108. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  109. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  110. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  111. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  112. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  113. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  114. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  115. Answers
    1. Wrong Answer: Yes, it is possible to persist the value of a computed column
    2. Right Answer: The ask the database engine to store the value of a computed column, you must add the PERSISTED keyword
    3. Wrong Answer: There is no PERSIST keyword in Transact-SQL
    4. Wrong Answer: Persistence is not created as a constraint
    5. Wrong Answer: Persistence is not created as a constraint

 
Previous Copyright © 2009-2011 FunctionX.com Next