ilideinfo-it-spesialist-database-7-januari-2022-p_240603_094546.pdf

QUESTION 1 You are structuring a table relational database. For each statement, select True or False. Note : you will receive partial credit for each correct selection. True False Each value in a field in a table must be...

QUESTION 1 You are structuring a table relational database. For each statement, select True or False. Note : you will receive partial credit for each correct selection. True False Each value in a field in a table must be unique (F) Each row in a table must be unique (F) Each column name in a table must be unique (T) QUESTION 2 You have a table named Product. The product table has column for productDescription and ProductCategory. You need to change the ProductCategory value for all the spoons in the Product to 43. A ProductDescription of spoon indicates that the item is a spoon. Which statement should you use? A. UPDATE Product WHERE ProductDescription = ‘spoon’ SET ProductCategory = 43 B. UPDATE Product SET ProductCategory = 43 WHERE ProductDescription = ‘spoon’ C. UPDATE Product WHERE ProductDescription = ‘spoon’ TO ProductCategory = 43 D. UPDATE Product TO ProductCategory = 43 WHERE ProductDescription = ‘spoon’ QUESTION 3 You accept an IT internship at a local charity. The charity ask 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 list 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 EQUALS ‘Tia’ D. DELETE FROM Volunteer WHERE GivenName IS ‘Tia’ QUESTION 4 You delete row in a table named Order. The corresponding row in the OrderItem table are automatically deleted This process is an example of a/an : A. Domino delete B. Waterfall delete C. Functional delete D. Cascade delete E. Inherited delete QUESTION 5 A table named PlayerStat contain following fields : Field Data Type Allow Nulls PlayerID INT FALSE TeamID INT FALSE GameDate DATETIME TRUE Point INT TRUE You need to display the total number of point 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. Note : you will receive partial credit for each correct selection. QUESTION 6 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, if return a syntax error You need to modify the query to run without error and return accurate result What should you do? A. Remove the group by clause B. Change the having clause to having count(title) = 1 C. Remove the order by clause D. Change the having clause to having count(1) = 1 QUESTION 7 Which statement creates an index ? A. CREATE TABLE Employee (EmployeeID INTEGER DISTINCT) B. CREATE TABLE Employee (EmployeeID INTEGER INDEX) C. CREATE TABLE Employee (EmployeeID INTEGER PRIMARY KEY) D. CREATE TABLE Employee (EmployeeID INTEGER NULL) QUESTION 8 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 table B. A syntax error C. An error stating that null values are not allowed D. An error stating that duplicate ids are not allowed QUESTION 9 A stored procedure contains the following query SELECT ‘Greetings’ + Prefix + ‘ ‘ + Firstname FROM Person; The stored procedure return all null values. You verify that there is data in the person table What is likely the cause of the problem A. You must specify the NULLIF keyword in the query B. The plus (+) operator cannot be used to append character data C. The Prefix Of FirstName columns have null values D. You must specify the join keyword in the select statement QUESTION 10 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. Like B. In C. Between D. Fetch QUESTION 11 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 12 The customers table includes the following data ID FirstName Lastname PhoneNumber Extension 1 Hope Ragabash (123)555-0111 NULL 2 Luna Faltor (123)555-0112 NULL 3 Mickey Sassafras (123)555-0113 12 4 Minne Hemingway (123)555-0114 77 5 Sherlock Steam (123)555-0115 NULL You need to create a query that returns a result set that contains the lastname, phonenumber and extension 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 QUESTION 13 You have a table product that contains one million row. 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 clusterd index on the Price column C. A non-clustered index on the Category column D. A non-clustered on the Price column QUESTION 14 You have a Customer table that store 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 the selects the following data. All customers and their orders Costumers who have no orders Which type of join do these results represent ? A. Full join B. Partial join C. Inner join D. Complete join QUESTION 15 You need to create a report of data in the students table. The report must meet the following requirements: Display all student who enrolled on or after June 1, 2020 Display all student who graduated in 2020 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 student WHERE enrollment_date >= ‘2020-06-01’ OR academic_status =’graduated’ AND graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date DESC B. SELECT * FROM student WHERE enrollment_date >= ‘2020-06-01’ AND academic_status =’graduated’ AND graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date ASC C. SELECT * FROM student Where enrollment_date >= ‘2020-06-01’ AND academic_status =’graduated’ OR graduation_date >= ‘2020-01-01’  ORDER BY enrollment_date D. SELECT * FROM student WHERE enrollment_date >= ‘2020-06-01’ OR academic_status =’graduated’ OR graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date DESC QUESTION 16 Which statement should you use to remove a foreign key? a. Delete table b. Alter table c. Alter foreign key d. Delete foreign key QUESTION 17 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 named ChapterLanguage to relate the chapter table and the Language Table. You need to select columns from the Chapter and Language tables ti create a composite primary key for the ChapterLanguage table. Which two columns should you select ? (select 2) A. Chapterid B. City C. Region D. Languagename E. Country F. Languageid QUESTION 18 You write a series of SQL statements that perform a long, complex query. The statements then send the result in an email. You want to be able to manually call the code any time you need to. Which database object can you use to store the code ? A. Stored procedure B. Function C. Trigger D. View QUESTION 19 The products table contains the following data. ItemNumber ItemName ItemDescription Price 1 Bonbon Chocolate Truffles, Black Forest Truffles, and 24.95 Box Cherry Bonbons 2 Brownie Caramel Nut, German Chocolate, Chocolate Swirl, 14.95 Mini Bites and Double Dutch Brownies 3 Cappuccino Tasty treats to accompany your favorite java, 21.50 Companion Includes Strawberry Thumbprints, Chocoate Chip Collection Cookies, and Biscotti. 4 Citrus Refreshing citrus cookies, including Orange 19.99 Cooler Gift Chiffon Meltaways, Key Lime Bars, Lemon Box Coolers, and Tangerine Coconut Smoothies 5 Fruit Jewels Fruity Favorites, including Apricot Jewels, Cherry 29.99 Assortment Bonbons, Coconut Patties, and Strawberry Thumbprints Which query will increase the price of item 1 by 6 percent ? A. USE Products SET Price = Price * 1.06 WHERE ItemNumber = 1; B. SET Price = Price * 1.06 FROM Products WHERE ItemNumber = 1; C. ALTER Products SET Price = Price * 1.06 WHERE ItemNumber = 1; D. UPDATE Products SET Price = Price * 1.06 WHERE ItemNumber = 1; QUESTION 20 You need to delete a database table. Which data definition language (DDL) keyword should you use ? A. Alter B. Delete C. Drop D. Truncate QUESTION 21 You execute the following query. SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department This type of operation is called a/an A. Intersection B. Outer join C. Cartesian product D. Equi-join QUESTION 22 You need to normalized a database to first normal form. Which two requirement must you meet? (Choose 2) A. Exclude repeating groups B. Exclude foreign key C. Exclude composite key D. Exclude duplicate rows QUESTION 23 A database table stores information about school attendance. You have a data set 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 whole number Dayabsent can have one number after the decimal Move each appropriate data type from the list on the left to the correct column name on the right. You may use each data type once, more than once, or not at all. QUESTION 24 Which statement deletes the rows where the employee’s phone number is not entered A. DELETE FROM employee WHERE phone = NULL B. DELETE FROM employee WHERE phone IS NULL C. DELETE FROM employee WHERE phone IS NOT NULL D. DELETE FROM employee WHERE phone = NULLABLE QUESTION 25 IYou 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 chronological order by DateJoined B. In the order in which the rows were inserted C. In no predictable order D. In alphabetical order by FirstName QUESTION 26 You need to remove a view named EmployeeView from your database. Which statement should you use ? a. DROP VIEW EmployeeView b. DELETE EmployeeView c. DELETE View EmployeeView d. DROP EmployeeView QUESTION 27 Which SQL statement will return the country and number or orders in each country where the number or orders is less than 50? OrderID represent an individual order, Country represent the country, and Orders represents the number of orders. A. SELECT COUNT (OrderId), Country FROM Orders HAVING COUNT(OrderId) < 50; GROUP BY Country B. SELECT Country, OrderId FROM Orders GROUP BY Country WHERE COUNT(OrderId) < 50; C. SELECT COUNT (OrderId), Country FROM Orders GROUP BY Country HAVING COUNT(OrderId) < 50; D. SELECT Country, OrderId FROM Orders WHERE Count(OrderId) < 50; GROUP BY Country QUESTION 28 You have a table that contains the following data. ProductID ProductCategory 32 Books 25 Books 67 Movies 89 Movies Which database term is used to describe the relationship between productid and productcategory. A. Deterministic B. Cohort C. Compositional D. Functionally dependent E. Relationally dependent QUESTION 29 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. The table will be corrupted B. 40 rows will be deleted from the table C. No rows will be deleted from the Table D. The transaction will restart QUESTION 30 Your company stores customer social security numbers in a column named SSN in a table named customer. New compliance laws prohibit your companyfrom 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 it removes the SSN column from the customers table ? A. ALTER TABLE Customers DELETE SSN; B. ALTER TABLE Customers DELETE COLUMN SSN; C. ALTER TABLE Customers DROP SSN; D. ALTER TABLE Customers DROP COLUMN SSN; QUESTION 31 You have two tables named cars and colors as defined below. the two tables are related by colorid. Cars table Car Type Car Model Color ID Sedan 2-Door 2 Truck 4-Door NULL Minivan 3-Door 1 Colors table Color Colorid Red 1 gold 2 You run the following SQL statement SELECT * FROM Cars LEFT OUTER JOIN Colors ON Cars.ColorID = Color.ColorID How many rows does the SQL statement return? A. 0 B. 2 C. 3 D. 6 QUESTION 32 You are developing SQL statement to create a table. Which two SQL keywords are valid to use in a create table statement? (Choose 2) A. Order By B. Primary Key C. Insert Into D. Constraint QUESTION 33 The following table named Building stores data about building and their most recent inspection dates. Field Data Type Allow Nulls BuildingID INT FALSE Address VARCHAR(100) FALSE InspectorID CHAR(3) TRUE InspectionDate DATETIME TRUE The address field stores the building’s 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. You need to display the address of the earliest 10 buildings that have been inspected. Complete the query by moving the appropriate keywords from the list on the left to the correct locations on the right. You may use each keyword once, more than once, or not at all. QUESTION 34 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’ AND Ship_State NOT = ‘AZ’ ; D. SELECT * FROM Orders WHERE Ship_State NOT = ‘TX’ OR Ship_State NOT = ‘AZ’ ; QUESTION 35 For each statement, select true or false. Note : you will receive partal credit for each correct selection True False A full database backup is a copy of all of the data in the entire database A transaction log backup backs up all the data in the database A differential backup copies only data that was changed before the last full backup A file of filegroup restore specifies a portion of the database to recover QUESTION 36 To create a table named 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 Mygames As SELECT Name FROM Games B. CREATE VIEW Mygames As SELECT * FROM Games C. CREATE VIEW Mygames As SELECT Name From Games ORDER BY Name D. CREATE VIEW Mygames As SELECT * From Games Where Name BETWEEN ‘A’ AND ‘Z’ QUESTION 37 One reason to create a stored procedure is to : A. Minimized storage space B. Improve performance C. Give the user control of the query logic D. Bypass case sensitify requirements QUESTION 38 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. A foreign key B. A primary key C. An index D. A unique constraint E. A data type QUESTION 39 You need to populate a table named employeecopy with data from an excisting table named employee. Which statement should you use? A. Copy * Into Employee Select * From Employee B. Select * Into Employeecopy Select * From Employee C. Insert * From Employee Into Employeecopy D. Insert Into Employeecopy Select * From Employee QUESTION 40 The ItemsOnOrder table contains the following data. ID ItemNumber Quantity UnitPrice LineItemTotal 100 1 10 24.94 249.50 100 2 25 14.95 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 display 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 COUNT (ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder HAVING ItemNumber, Quantity, UnitPrice B. SELECT COUNT (ID), AVG(Unitprice+Quantity), MAX(UnitPrice+Quantity), SUM(UnitPrice+Quantity) FROM ItemOnOrder GROUP By ItemNumber, LineItemTotal C. SELECT SUM(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemOnOrder D. SELECT COUNT(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM (LineItemTotal) FROM ItemOnOrder QUESTION 41 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. DELETE CustomerID FORM Customer WHERE CustomerID = 12345 B. DELETE FROM Customer WHERE CustomerID = 12345 C. UPDATE Customer DELETE * WHERE CustomerID = 12345 D. UPDATE CustomerID FROM Customer DELETE * WHERE CustomerID = 12345 QUESTION 42 You have a table named Product that contains the following data. ProductID ProductName CategoryID 3296 Spoon 2222 1114 Chair 4444 The ProductID column is the primary key. The CategoryID column is a foreign key to a separate table named Category. You execute the following statement. INSERT INTO Product VALUES (3296, ‘Table’, 4444) What is the result? A. A new row in the Product table B. A syntax error C. A foreign key constraint violation D. A new row in the Category table E. A primary key constraint violation QUESTION 43 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. waterfall delete B. inherited delete C. cascade delete D. domino delete E. functional delete QUESTION 44 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 45 You create the following table named LoanedBooks, which lists how many books you have on loan to your friends. ID Name City Books 1 Harry New York 2 2 Sally San Fransisco 3 3 Sue Chicago 0 4 Harry San Fransisco 3 5 William Detroit 1 Harry in San Fransisco returns your books. Which statement will correctly update your table? A. UPDATE LoanedBooks SET Books = 0 WHERE (Name = ‘Harry’ OR City = ‘San Fransisco’) B. UPDATE LoanedBooks SET Books = 0 WHERE (Name In ‘Harry’, ‘San Fransisco’) C. UPDATE LoanedBooks SET Books = 0 WHERE (Name ‘Harry’ AND City = ‘San Fransisco’) D. INSERT INTO LoanedBooks SET Books = 0 WHERE ID = 4 QUESTION 46 You have a table named Product. You create a view that includes all the products from the Product table Product table that are in the Furnitur 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. empty B. deleted C. unchanged D. archived QUESTION 47 You have two tables. Each table has three rows. You create a SQL query that uses a cross join. The query does not include a WHERE clause. How many rows will be included in the Cartesian product of thess two tables? A. 0 B. 3 C. 6 D. 9 QUESTION 48 You have a table named Customer. You need to create a new column named District. Which statement should you use? A. ALTER TABLE Customer MODIFY (District INTEGER) B. ALTER TABLE Customer ADD (INTEGER District) C. ALTER TABLE Customer ADD (District INTEGER) D. MODIFY TABLE Customer ADD (INTEGER District) QUESTION 49 One difference between a function and a stored procedure is that a function. A. cannot accept parameters B. cannot contain a transaction C. must be called from a trigger D. must return a value QUESTION 50 Which SQL statement is a data manipulation languange statement? A. ALTER TABLE Employee  ADD EmployeeName Varchar; B. SELECT EmployeeName FROM Employee WHERE EmployeeName = ‘Jack Smith’; C. SELECT * INTO Employee FROM NewHires INSERT INTO Employee D. VALUES (‘Jack Smith’); QUESTION 51 You create the following table, which displays winners of past pet shows. Pet Competition Winners ID Event Year PetID Birthdate City 1 Cutest Pet Fest 2009 RG459 June 23, 2008 Chicago 2 Pet Babies Invitational 2010 SP372 August 12, 2009 Montreal 3 Adorable Friends 2011 BD116 May 1, 2010 Atlanta 4 AWHH Fest 2012 2012 FD228 August 12, 2009 Detroit Which column prevents the table from being in third normal form? A. PetID B. Birthdate C. ID D. Year QUESTION 52 For each statement, select True or False. Note: You will receive partial credit for each correct selection. True False You can delete data by using a stored procedure. A function must have a return value. A stored procedure must have a return value. QUESTION 53 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 TABLE Student( ID INT,  Name VARCHAR (100), Age INT) B. CREATE Student( ID INT, Name VARCHAR (100), Age INT) C. CREATE TABLE ( ID INT, Name VARCHAR (100), Age INT) D. CREATE ( TABLE Student ID INT, Name VARCHAR (100), QUESTION 54 You need to disable User1’s access to view the data in the Customer table. Which statement should you use? A. REVOKE User1 FROM Customer B. REVOKE SELECT ON Customer FROM User1 C. REMOVE SELECT ON Customer FROM User1 D. REMOVE User1 FROM Customer QUESTION 55 You have a database table that contains the following columns: You must insert the following record into the table: StreetAddress City State PostalCode 1234 main Street Dallas TX 75201 Which two SQL statement can you use? (Choose 2) A. INSERT INTO AddressInfo (‘1234 Main Street’, ‘Dallas’, ‘TX’, ‘75201’) VALUES ([StreetAddress], [City], [State], [PostalCode]) B. INSERT INTO AddressInfo VALUES (‘1234 Main Street’, ‘Dallas’, ‘TX’, ‘75201’) C. UPDATE AddressInfo SET [StreetAddress] = ‘123 Main Street’, [City] = ‘Dallas’, [State] = ‘TX’, [PostalCode] = ‘75201’ D. INSERT INTO AddressInfo ([StreetAddress], [City], [State], [PostalCode]) VALUES (‘1234 Main Street’, ‘Dallas’, ‘TX’, ‘75201’) QUESTION 56 You’re the database admin for a university that stores student information in a table named student. This table has a foreign key named courseID that links to the primary key in a table named courses. You need to return the student name and course name using both tables where there is a match on courseID. You execute the following query: SELECT students.name, courses.name FROM students OUTER JOIN courses WHERE student.courseID = courses.courseID The query returns an error. How should you correct the query? A. SELECT students.name, courses.name FROM students INNER JOIN courses WHERE students.courseID = courses.courseID B. SELECT students.name, courses.name FROM students INNER JOIN courses ON students.courseID = courses.coursesID C. SELECT students.name,courses.name FROM students INNER JOIN ON students.courseID = courses.coursesID D. SELECT students.name, courses.name FROM students INNER JOIN On courses WHERE students.courseID = courses.courseID QUESTION 57 Another developer is trying to add 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 Person table is empty. B. You should run the DROP CONSTRAINT query before running the ALTER TABLE query. C. The varchar(4) data types is invalid for the Person table. D. The DEFAULT keyword should be used to specify a default value. QUESTION 58 You have a table named Student that contains 100 rows. Some of the rows have a NULL value in the FirstName column. You execute the following statement: DELETE FROM Student What is the result? A. All rows and the table definition will be deleted B. All rows containing a NULL value in the FirstName column will be deleted C. All rows in the table will be deleted D. You will receive an error message QUESTION 59 You have a table that contains the following data. ProductID ProductCategory 32 Books 25 Books 67 Movies 89 Movies Which database term is used to describe the relationship between ProductID and ProductCategory? A. compositional B. cohort C. deterministic D. relationally dependent E. functionally dependent QUESTION 60 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. Clustered index B. Primary key C. Foreign key D. Nonclustered index QUESTION 61 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 * FROM Employee B. SELECT COUNT(*) FROM Employee C. SELECT COUNT(rows) FROM Employee D. SELECT SUM(*) FROM Employee QUESTION 62 The products table contains the following data. ItemNumber ItemName ItemDescription Price 1 Bonbon Chocolate Truffles, Black Forest Truffles, and 24.95 Box Cherry Bonbons 2 Brownie Caramel Nut, German Chocolate, Chocolate Swirl, 14.95 Mini Bites and Double Dutch Brownies 3 Cappuccino Tasty treats to accompany your favorite java, 21.50 Companion Includes Strawberry Thumbprints, Chocoate Chip Collection Cookies, and Biscotti. 4 Citrus Refreshing citrus cookies, including Orange 19.99 Cooler Gift Chiffon Meltaways, Key Lime Bars, Lemon Box Coolers, and Tangerine Coconut Smoothies 5 Fruit Jewels Fruity Favorites, including Apricot Jewels, Cherry 29.99 Assortment 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 LIKE ‘%chocolate%’; C. SELECT ItemName, Price FROM Products WHERE ItemDEscription LIKE ‘chocolate’; D. SELECT ItemName, Price FROM Products WHERE ItemDescription IN ‘%chocolate%’; QUESTION 63 You need to combine the results of two queries into a single result that contains all of the rows from both queries. Which SQL statement should you use? A. UNION B. JOIN C. EXCEPT D. TRUNCATE QUESTION 65 You need to set up a database to provide a view of North American mammals (NorthAmericanMammals_View). 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. Note: You will receive partial credit for each correct response. QUESTION 66 Which statement creates a composite key? A. CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY OrderID, PRIMARY KEY OrderItemID) B. CREATE TABLE Order (OrderID INTEGER PRIMARY KEY, OrderItemID INTEGER PRIMARY KEY) C. CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY) D. CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY(OrderID, OrderItemID)) QUESTION 67 A table has a 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. QUESTION 68 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. Binary C. Bit D. Varchar QUESTION 69 Which statement removes all rows from a table without logging the individual row deletions? A. ALTER TABLE B. DROP TABLE C. TRUNCATE TABLE D. CREATE TABLE QUESTION 70 A database table named Cars is defined as shown. Type Color Origin Sedan Red Japan Truck Red USA Minivan Silver Japan Hatchback Red Japan Compact Black Japan SUV Silver Gemany 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 71 You execute the following query: SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department This type of operation is called a/an: A.Equi-join B. Cartesian product C. Intersection D. Outer join QUESTION 72 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. QUESTION 73 Which query returns a result set of orders placed after January 2017 in all states except California (CA)? A. SELECT * FROM orders WHERE order_date > ‘2017-01-01’ AND ship_state < > ‘CA’ B. SELECT * FROM orders WHERE order_date > ‘2017-01-01’ OR ship_state < > ‘CA’ C. SELECT * FROM orders WHERE order_date ? ‘2017-01-01’ AND ship_state LIKE ‘CA’ D. SELECT * FROM orders WHERE order_date > ‘2017-01-01’ OR ship_state LIKE ‘CA’ QUESTION 74 QUESTION 75