ITS Database reviewer.pdf

Question 1 Another developer is trying to adtd a column named Prefix in a table by using the following query: ALTER TABLE Person ADD Prefix varchar(4) NOT NULL; The developer receives an error message after running the query. The developer is unable to remember the exact error message. What is l...

Question 1 Another developer is trying to adtd a column named Prefix in a table by using the following query: ALTER TABLE Person ADD Prefix varchar(4) NOT NULL; The developer receives an error message after running the query. The developer is unable to remember the exact error message. What is likely the cause of this problem? A. The varchar(4) data type is invalid for the Person table B. The Person table is empty C. The DEFAULT keyword should be used to specify a default value D. You should run the DROP CONSTRAINT query before running the ALTER TABLE query Question 2 You need to create a report of data in the students table. The report must meet the following requirements: Display all students who enrolled on or after June 1, 2020 Display all students who graduated in 2022. Return the result set in order of enrollment, with the most recent enrollment date first. enrollment_date specifies the enrollment date. graduation _date specifies the graduation date. An academic _status of Graduated indicates that a student has graduated. Which query should you use? A SELECT * FROM students WHERE enrollment_date >= ‘2020-06-01’ AND academic_status = ‘Graduated’ OR graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date B SELECT * FROM students WHERE enrollment_date >= ‘2020-06-01’ AND academic_status = ‘Graduated’ AND graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date ASC C SELECT * FROM students WHERE enrollment_date >= ‘2020-06-01’ OR academic_status = ‘Graduated’ AND graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date DESC D SELECT * FROM students WHERE enrollment_date >= ‘2020-06-01’ OR academic_status = ‘Graduated’ OR graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date DESC Question 3 Given is the table below: CarName Color Origin Sedan Red Japan Truck Red USA Minivan Silver Japan Hatchback Red Japan Compact Black Japan SUV Silver Germany Convertible Black USA Hybrid Black Germany You have the following SQL statement: SELECT * FROM Cars c WHERE c.Origin ‘USA’ AND c.Color ‘Black’ How many rows are returned by the SQL statement? a. 4 b. 5 c. 6 d. 7 Question 4 Your database contains a table named Customer. You need to delete the record from the Customer table that has a CustomerID of 12345. Which statement should you use? A UPDATE CustomerID FROM Customer DELETE * WHERE CustomerID = 12345 B DELETE CustomerID FROM Customer WHERE CustomerID = 12345 C UPDATE Customer DELETE * WHERE CustomerID = 12345 D DELETE FROM Customer WHERE CustomerID = 12345 Question 5 Which SQL statement is a data manipulation language (DML) statement? A SELECT EmployeeName FROM Employee WHERE EmployeeName = ‘Jack Smith’; B SELECT * INTO Employee FROM NewHires C INSERT INTO Employee VALUES (‘Jack Smith’); D ALTER TABLE Employee ADD EmployeeName Varchar Question 6 The Products table contains the following data: ItemNumber ItemName ItemDescription Price 1 Bonbon Box Chocolate Truffles, Black 24.95 Forest Truffles, and Cherry Bonbons 2 Brownie Mini Caramel Nut, German 14.95 Bites Chocolate, Chocolate Swirl, and Double Dutch Brownies 3 Cappuccino Tasty treats to accompany 21.50 Companion your favorite java. Includes Collection Strawberry Thumbprints,  Chocolate Chip Cookies, and Biscotti 4 Citrus Cooler Refreshing citrus cookies, 19.99 Gilt Box including Orange Chiffon Mellaways, Key Lime Bars, Lemon Collers and Tangerine Coconut Smoothies 5 Fruit Jewels Fruity Favorites, including 29.99 Assortment Apricot Jewels, Cherry Bonbons, Coconut Patties, and Strawberry Thumbprints Which query will retrieve ItemName and Price when chocolate appears in the ItemDescription column? A SELECT ItemName, Price FROM Products WHERE ItemDescription = ‘chocolate’; B SELECT ItemName, Price FROM Products WHERE ItemDescription IN ‘%chocolate%’; C SELECT ItemName, Price FROM Products WHERE ItemDescription LIKE ‘chocolate’; D SELECT ItemName, Price FROM Products WHERE ItemDescription LIKE ‘%chocolate%’; Question 7 Which statement creates a composite key? A CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY (OrderID, OrderItemID)) B CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY OrderID, PRIMARY KEY OrderItemID)) C CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY) D CREATE TABLE Order (OrderID INTEGER PRIMARY KEY, OrderItemID INTEGER PRIMARY KEY) Question 8 Structuring a table in relational database. In each statement select True or False. Each item will receive partial credit for each correct selection. Each value in a field in a table must be unique - False Each row in a table must be unique - True Each column name in a table must be unique – True Question 9 You work at a coffee shop. They ask you to set up a website that stores charges on purchases. You need to recommend a data type in a database table to run financial functions against the charged amounts. Which data type should you recommend? a. Decimal b. Bit c. Binary d. Varchar Question 10 You have a table named LoanedBooks that contains the following records 1 Harry New York 2 2 Sally San Francisco 3 3 Sue Chicago 0 4 Harry San Francisco 3 5 William Detroit 1 Harry in San Francisco returns your books. Which statement will correctly update your table? A INSERT INTO LoanedBooks SET Books = 0 WHERE ID = 4 B UPDATE LoanedBooks SET Books = 0 WHERE (NAME = ‘Harry’ or City = ‘San Francisco’) C UPDATE LoanedBooks SET Books = 0 WHERE (NAME In ‘Harry’, ‘San Francisco’) D UPDATE LoanedBooks SET Books = 0 WHERE (NAME = ‘Harry’ AND City = ‘San Francisco’) Question 11 You have a table named Employee that includes the following columns: EmployeeID EmployeeName Which statement should you use to return the number of rows in the table? A SELECT SUM(*) FROM Employee B SELECT * FROM Employee C SELECT COUNT(rows) FROM Employee D SELECT COUNT(*) FROM Employee Question 12 You have a table named PlayerStat contains the following fields: Field Data Type Allow Nulls PlayerID INT FALSE TeamID INT FALSE GameDate DATETIME TRUE Points INT TRUE You need to display the total number of points per player on the team whose ID is 1. Move the appropriate code segments from the list on the left to the correct locations on the right. You may use each code segment once, more than once, or not at all. Code Segments Answer Area PlayerID SELECT PlayerID, TeamID SUM(Points) SUM(Points) WHERE FROM PlayerStat GROUP BY TeamID = 1 WHERE COUNT HAVING GROUP BY PlayeID Question 13 You have two tables named SalesPerson and Sales. You need to ensure that each record in the Sales table has a valid associated salesperson record in the SalesPerson table. Which database object should you add to the Sales table? A. nonclustered index B. Foreign key C. Clustered index D. Primary key Question 14 You need to create a database to provide a view of North America mammals (NorthAmericanMammals_View). Drag the code segments from the list on the left to the correct locations on the right. You may use each code segment once, more than once, or not at all. Code Segments CREATE VIEW [dbo].[NorthAmericanMammals_View] UPDATE VIEW [dbo].[NorthAmericanMammals_View] AS SELECT a.Id, a.Name AS SELECT VIEW a.Id, a.Name FROM Animal a FROM Animal b Answer Segments CREATE VIEW [dbo].[NorthAmericanMammals_View] AS SELECT a.Id, a.Name  FROM Animal a WHERE a.Class = ‘Mammals’ AND a.InNorthAmerica = 1 Question 15 You have a table name Product. You create a view that includes all the products from the Product table that are in the Furniture category. You execute a statement on the Product table that deletes all the products in the Furniture category. After you execute the statement, the result set of the view is: a. Unchanged b. Deleted c. Archived d. Empty Question 16 Which keyword combines the results of two queries and returns only rows that appear in both result sets? a. UNION b. INTERSECT c. ALL d. JOIN Question 17 One reason to create a stored procedure is to: a. Give the user control of the query logic b. Bypass case sensitivity requirements c. Improve performance d. Minimize storage space Question 18 You need to normalize a database to first normal form. Which two requirements must you meet? (Choose two) a. Exclude duplicate rows b. Exclude composite keys c. Exclude repeating groups d. Exclude foreign keys Question 19 You have a Customer table that stores information about customers and an Order table that stores information about orders placed. Each table contains a CustomerID column. You join the two tables by using the CustomerID column in each table. You run a query that selects the following data: All customers and their orders Customers who have no orders Which type of join do these results represent? a. Full join  b. Complete join c. Partial join d. Inner join Question 20 The following table name Building stores data about buildings and their most recent inspection dates. Field Data Type Allow Nulls BuildingID INT FALSE Address VARCHAR(100) FALSE InspectionID CHAR(3) TRUE InspectionDate DATETIME TRUE The Address field store the buildings address. The InspectionDate field stores the most recent inspection date. A value of NULL in the InspectionDate field means that the building has not yet been inspected. Keyword ANSWER AREA: TOP 10 COUNT SELECT Address GROUP BY 10 IS NOT NULL FROM Building IS NULL ORDER BY WHERE InspectionDateIS NOT NULL TOP 10 ORDER BY InspectionDate Question 21 A database table stores information about school attendance. You have a data that is defined as follows: StudentName GradeLevel DaysAbsent John 12 2.5 Holly 12 0.0 David 12 3.0 You have the following requirements StudentName must consist of a string of characters GradeLevel must be a whole number BIT DATETIME DECIMAL INT VARCHAR CHAR Drag the data type to the answer section appropriate to each field StudentName VARCHAR GradeLevel INT DaysAbsent DECIMAL Question 22 You have a table named Product. The Product table has columns for ProductDescription and ProductCategory. You need to change the ProductCategory value for all the spoons in the Product table to 43. A ProductDescription of spoon indicates that the item is a spoon. Which statement should you use? A UPDATE Product SET ProductCategory = 43 WHERE ProductDescription = ‘spoon’ B UPDATE Product WHERE ProductDescription = ‘spoon’ SET ProductCategory = 43 C SET Product WHERE ProductDescription = ‘spoon’ TO ProductCategory = 43 D SET Product TO ProductCategory = 43 WHERE ProductDescription = ‘spoon’  Question 23 You have a table that contains the following data: ProductID ProductCategory 32 books 25 books 67 movies 89 movies What database term is used to describe the relationship between ProductID and ProductCategory? a. deterministic b. relationally dependent c. cohort d. compositional e. functionality dependent Question 24 You execute the following query: SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department This type of operation is called a/an: a. outer join b. equi-join c. intersection d. cartesian product Question 25 Which statement creates an index? A CREATE TABLE Employee (EmployeeID INTEGER PRIMARY KEY) B CREATE TABLE Employee (EmployeeID INTEGER INDEX) C CREATE TABLE Employee (EmployeeID INTEGER NULL) D CREATE TABLE Employee (EmployeeID INTEGER DISTINCT) Question 26 You are developing a SQL statement to create a table. Which two SQL keywords are valid to use in a CREATE TABLE statement? (Choose 2) A. PRIMARY KEY B. CONSTRAINT C. INSERT INTO D. ORDER BY Question 27 You accept an IT internship at a local charity. The charity asks you to keep a record of its volunteers by using a database table named Volunteer. The table has the following columns and rows. ID GivenName DateOfBirth 1 Tia 1976-05-30 2 Susana 1952-11-04 3 Joey 1963-02-17 You must update the table when volunteers ask to be removed from mailing lists. You need to delete all records with the GivenName Tia. Which SQL statement should you use? a. DELETE FROM Volunteer WHERE GivenName == ‘Tia’ b. DELETE FROM Volunteer WHERE GivenName = ‘Tia’ c. DELETE FROM Volunteer WHERE GivenName IS ‘Tia’ d. DELETE FROM Volunteer WHERE GivenName EQUALS ‘Tia’ Question 28 You have a table named Customer that has columns named CustomerID, Firstname, and DateJoined CustomerID is a primary key. You execute the following query: SELECT CustomerID, FirstName, DateJoined FROM Customer How are the rows organized in the result set? a. In alphabetical order by FirstName b. In chronological order by DateJoined c. In no predictable order d. In the order in which the rows were inserted. Question 29 Given is the Customers table below: ID FirstName LastName PhoneNumber Extension 1 Hope Ragabash (123) 555- NULL 0111 2 Luna Faltor (123) 555- NULL 0112 3 Mickey Sassafras (123) 555- 12 0113 4 Minnie Hemingway (123) 555- 77 0114 5 Sherlock Steam (123) 555- NULL 0115 You need to create a query that returns a result set that contains the LastName, PhoneNumber and Extension for extensions that are valid numbers. The result set should be sorted by the customer ‘s last name. Complete the code by selecting the correct option from each drop- down list. Note: You will receive partial credit for each correct selection. SELECT LastName, PhoneNumber, Extension FROM Customers WHERE PhoneNumber IS NOT NULL ORDER BY LastName; Question 30 Given is the ItemOnOrder table below: ItemNumber ID Quantity UnitPrice LineItemTotal 100 1 10 24.95 249.50 100 2 25 14.94 373.75 100 3 25 19.99 499.75 101 4 10 25.00 250.00 102 5 10 29.99 299.00 You need to create a query that displays the total number of orders, the average line item total, the highest line item total, and the grand total of all items ordered. LineItemTotal represents the line item total Which query should you use? A SELECT SUM(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder B SELECT COUNT(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder HAVING ItemNumber, Quantity, UnitPrice C SELECT COUNT(ID), AVG(UnitPrice+Quantity), MAX(UnitPrice+Quantity), SUM(UnitPrice+Quantity) FROM ItemsOnOrder GROUP BY ItemNumber, LineItemTotal D SELECT COUNT(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder Question 31 You need to populate a table named EmployeeCopy with data from an existing table named Employee. Which statement should you use? A COPY * INTO Employee SELECT * FROM Employee B SELECT * INTO EmployeeCopy SELECT * FROM Employee C INSERT INTO EmployeeCopy SELECT * FROM Employee D INSERT * FROM Employee INTO EmployeeCopy Question 32 You execute a statement inside a transaction to delete 100 rows from a table. The transaction fails after only 40 rows are deleted. What is the result in the database? a. No rows will be deleted from the table b. 40 rows will be deleted from the table c. The transaction will restart d. The table will be corrupted. Question 33 Which SQL statement will return the country and number of orders in each country where the number of orders is less than 50? orderID represents an individual order, Country represents the country, and Orders represent the number of orders. A SELECT COUNT(OrderID), Country FROM Orders HAVING COUNT(orderID) < 50 GROUP BY Country B SELECT COUNT(OrderID), Country FROM Orders GROUP BY Country HAVING COUNT(orderID) < 50 C SELECT Country, orderID FROM Orders GROUP BY Country WHERE COUNT(orderID) < 50 D SELECT Country, orderID FROM Orders HAVING COUNT(orderID) < 50 GROUP BY Country Question 34 You have the following table definition: CREATE TABLE Road (RoadID INTEGER NOT NULL, Distance INTEGER NOT NULL) The Road table contains the following data. RoadID Distance 1234 22 1384 34 You execute the following statement. INSERT INTO Road VALUES (1234, 36) What is the result? a. A new row in the table b. An error stating that NULL values are not allowed c. A syntax error d. An error stating that duplicate IDs are not allowed Question 35 You delete rows in a table named Order. The corresponding rows in the OrderItem table are automatically deleted. This process is an example of a/an: a. domino delete b. cascade delete c. functional delete d. waterfall delete e. inherited delete Question 36 The Products table contains the following data: ItemNumber ItemName ItemDescription Price 1 Bonbon Box Chocolate Truffles, Black 24.95 Forest Truffles, and Cherry Bonbons 2 Brownie Mini Caramel Nut, German 14.95 Bites Chocolate, Chocolate Swirl, and Double Dutch Brownies 3 Cappuccino Tasty treats to accompany 21.50 Companion your favorite java. Includes Collection Strawberry Thumbprints, Chocolate Chip Cookies, and Biscotti 4 Citrus Cooler Refreshing citrus cookies, 19.99 Gilt Box including Orange Chiffon Mellaways, Key Lime Bars, Lemon Collers and Tangerine Coconut Smoothies 5 Fruit Jewels Fruity Favorites, including 29.99 Assortment Apricot Jewels, Cherry Bonbons, Coconut Patties, and Strawberry Thumbprints Which query will increase the price of item 1 by 6 percent? A SET Price = Price * 1.06 FROM Products WHERE ItemNumber = 1; B UPDATE Products SET Price = Price * 1.06 WHERE ItemNumber = 1; C ALTER Products SET Price = Price * 1.06 WHERE ItemNumber = 1; D USE Products SET Price = Price * 1.06 WHERE ItemNumber = 1; Question 37 Instructions: For each of the following statements, select True if the statement is true. Otherwise, select False. Each correct selection is worth a partial point. A full database backup is a copy of all the data in the entire database – True A transaction log backup backs up all the data in the database – False A differential backup copies only data that was changed before the last full backup – False A file or filegroup restore specified a portion of the database to recover – True Question 38 Which statement deletes the rows where the employer’s phone number is not entered? A DELETE FROM Employee WHERE Phone = NULL B DELETE FROM Employee WHERE Phone = NULLABLE C DELETE FROM Employee WHERE Phone IS NULL D DELETE FROM Employee WHERE Phone IS NOT NULL Question 39 Which query correctly returns a result set for all orders where the ship_state excludes Texas (TX) and Arizona (AZ)? A SELECT * FROM Orders WHERE NOT ship_state = ‘TX’ AND NOT ship_state = ‘AZ’ B SELECT * FROM Orders WHERE NOT ship_state = ‘TX’ OR NOT ship_state = ‘AZ’ C SELECT * FROM Orders WHERE ship_state NOT = ‘TX’ OR ship_state NOT = ‘AZ’ D SELECT * FROM Orders WHERE ship_state NOT = ‘TX’ AND ship_state NOT = ‘AZ’ Question 40 Your company stores customer social security numbers in a column named SSN in a table named Customers. New compliance laws prohibit your company from storing this information. Running the query below causes an error to occur. ALTER TABLE Customers REMOVE SSN; What changes are needed to the query above so that if removes the SSN column from the Customers table? A ALTER TABLE Customers DELETE COLUMN SSN; B ALTER TABLE Customers DROP COLUMN SSN; C ALTER TABLE Customers DROP SSN; D ALTER TABLE Customers DELETE SSN; Question 41 You create the following query to determine whether Sample Movie appears only once in the Movie table. SELECT Title FROM Movie WHERE Title = ‘Sample Movie’ ORDER BY Title GROUP BY Title HAVING COUNT(*) = 1 When you run this query, it returns a syntax error. You need to modify the query to run without error and return accurate results. What should you do? A. Change the HAVING clause to HAVING COUNT(Title) = 1 B. Remove the GROUP BY clause C. Change the HAVING clause to HAVING COUNT(1) = 1 D. Remove the ORDER BY clause Question 42 You need to remove a view named EmployeeView from your database. Which statement should you use? a. DELETE EmployeeView b. DROP VIEW EmployeeView c. DROP EmployeeView d. DELETE VIEW EmployeeView Question 43 Which statement should you use to remove a foreign key? a. DELETE FOREIGN KEY b. DELETE TABLE c. ALTER FOREIGN KEY d. ALTER TABLE Question 44 You have a Department table and an Employee table in your database. You need to ensure that an employee can be assigned to only an existing department. What should you apply to the Employee table? a. An index b. A foreign key c. A data type d. A primary key e. A unique constraint Question 45 You have a table named Product that contains one million rows. You need to search for product information using the following query: SELECT ProductName, Price FROM Product WHERE Category = ‘Science Books’ What will make this type of search more efficient?  a. A clustered index on the ProductName column b. A clustered index on the Price column c. A non-clustered index on the Category column d. A non-clustered index on the Price column Question 46 You are writing a SELECT statement to find every product whose name contains a specific character. Which keyword should you use in your WHERE clause? a. BETWEEN b. IN c. FETCH d. LIKE Question 47 A stored procedure contains the following query: SELECT ‘Greetings’ + Prefix + ‘ ‘ + FirstName FROM Person; The stored procedure returns all null value. You verify that there is data in the Person table. What is like the cause of this problem? a. You must specify the JOIN keyword in the SELECT statement b. The plus (+) operator cannot be used to append character data c. The Prefix or firstName columns have null values d. You must specify the NULLIF keyword in the query Question 48 You accept an IT internship at a local charity. The charity has two tables in their data model named Chapter and Language, as shown: Chapter table ChapterID City Region Country 1 Chicago Illinois USA 2 Los Angeles California USA 3 New York City New York USA 4 Toronto Ontario Canada Language Table LanguageID LanguageName Locale 1 English USA 2 English Canada 3 Spanish Spain 4 Spanish Mexico 5 French France 5 French Canada 6 German Germany You create a third table name ChapterLanguage to relate the Chapter table and Language table You need to select columns from the Chapter and Language tables to create a composite primary key for the ChapterLanguage table. Which two columns should you select? (Choose 2) A. City B. Country  C. LanguageName D. Region E. ChapterID F. LanguageID Question 49 Which feature does a relational database use to ensure that data entered into a column is valid? a. An attribute b. A primary key c. A constraint d. An index Question 50 You create a table names Games that contains the review scores of recently released video games. You need to create a view that returns an alphabetical list of game names. Name represents the game name. Which query should you use? A. CREATE VIEW MyGame AS SELECT Name FROM Games B. CREATE VIEW MyGame AS SELECT * FROM Games  C. CREATE VIEW MyGame AS SELECT Name FROM Games ORDER BY Name D. CREATE VIEW MyGame AS SELECT * from Games WHERE Name BETWEEN ‘A’ AND ‘I’ Question 51 You need to disable User1’s access to view the data in the Customer table. Which statement should you use? A. REMOVE SELECT ON Customer FROM User1 B. REVOKE User1 FROM Customer C. REMOVE User1 FROM Customer D. REVOKE SELECT ON Customer FROM User1 Question 52 You are creating a database object named Student to store the following data. ID Name Age 1 Rene 18 2 Tia 22 3 Oliver 25 Which syntax should you use to create the object? A. CREATE Student ( ID INT, Name VARCHAR (100), Age INT) B. CREATE ( TABLE Student ID INT, Name VARCHAR (100), Age INT) C. CREATE TABLE Student ( ID INT, Name VARCHAR (100), Age INT) D. CREATE TABLE ( ID INT, Name VARCHAR (100), Age INT) Question 53 A table named Flight contains the following fields. Field Data Type Allow Nulls ID INT FALSE FlightNumber VARCHAR(8) FALSE OriginAirport CHAR(3) FALSE DestinationAirport CHAR(3) FALSE DepartureTime DATETIME FALSE ArrivalTime DATETIME FALSE You need to display the flight numbers of all flights that arrive at LaGuardia Airport (LGA) later today. The results should be sorted by the most recent arrival time. Which query should you use? Complete the code by selecting the correct option from each drop-down list. Note: You will receive partial credit for each correct selection. Answer Area: SELECT FlightNumber FROM Flight WHERE DestinationAirport = ‘LGA’ AND ArrivalTime > GETDATE() ORDER BY ArrivalTime DESC Question 54 A table has clustered index. Complete the statements by selecting the correct option from each drop-down list. Note: You will receive partial credit for each correct selection. A clustered index improves the performance of queries that Return large results sets A clustered index improves the performance of queries on columns that are accessed randomly