VERSI 1-Latihan [PDF]

Citation preview

1. You have a table that contains the following data. ProductID ProductName Color1 4545 Shirt Blue



Color2 Green



Color3 Peurple



You break the table into the following two tables. ProductID ProductName 4545 Shirt ProductID 4545 4545 4545



Color Blue Green Purple



This Process is referred to as : A. Normalization B. Fragmentation C. Denormalization D. Defragmentation 2. You combine data from three tables into one table. The new table includes redundancy to optimize read performance The data in the new table has been denormalized Instructions. Review the underlined text. If it makes the statement correct, select “ No Change is needed” if the statement incorrect, select the answer choice that makes the statement correct A. B. C. D.



Indexed Truncated Normalized No change is needed



3. Which statement creates a composite key ? A. CREATE TABLE Order ( OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY) B. CREATE TABLE Order ( OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY OrderID, PRIMARY KEY OrderItemID) C. CREATE TABLE Order ( OrderID INTEGER PRIMARY KEY, OrderItemID INTEGER PRIMARY KEY) D. CREATE TABLE Order



( OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY(OrderID, OrderItemID)) 4. First normal form requires that a database excludes repeating groups. Instructions : Review the underlined text. If it makes the statement correct, select “ No Change is needed” if the statement incorrect, select the answer choice that makes the statement correct A. B. C. D.



No change is needed Foreign keys Duplicate rows Composite keys



5. Which two keys establish a relationship between two tables ? choose two A. B. C. D. E.



Foreign Primary Local Candidate Superkey



6. You have the following table: FlightNumber 1 2 3



Airline Blue yonder airlines Margie’s travel Northwind trades



Instructions : use the drop-down menus to select the answers choice that completes each statement based on the information presented in the graphic. Each correct selection is worth one point A. Adding a [answer choice] on the FlightNumber column physically sorts rows in the table by FlightNumber . Clustered index B. Adding a [answer choice] on the Airline column retrieves data faster, but will not physically sort rows in the table by Airline. Foreign Key



7. Denormalization is performed in order to : A. Improve query performance B. Reduce redundancy



C. Eliminate repeating groups D. Create smaller tables



8. Which two element are required to define a column ? choose two A. A Data type B. An index C. A name D. A key 9. One difference between a function and a stored procedure is that a functions : A. Must return a value B. Cannot contain a transaction C. Cannot accept parameters D. Must be called from a trigger 10. You are developing a SQL statement to create table Which two SQL keyword are valid to use in a CREATE TABLE statement? Choose two A. B. C. D.



PRIMARY KEY INSERT INTO ORDER BY CONSTRAINT



11. Which keyword must be included in a CREATE VIEW statement? A. ORDER BY B. WHERE C. SELECT D. UPDATE 12. Which permissions does a user need in order to run a stored procedure? A. RUN B. EXECUTE C. ALLOW D. CALL 13. You need to create a view to filter rows of data from an underlying table. Which type of clause must be included in the CREATE VIEW statement? A. B. C. D.



CONSTARINT WHERE JOIN FILTER



14. You create a table of recently released video games and their review scores. To create a table recently a view that returns a list of game names that is alphabetically sorted the completed SQL statement should be the following create view MyGames As select Name from Games. Instructions : Review the underlined text. If it makes the statement correct, select “ No Change is needed” if the statement incorrect, select the answer choice that makes the statement correct A. B. C. D.



No change is needed. Select * from Games WHERE name BETWEEN ‘A’ AND ‘Z’ Select* from Games Select name from games order by name



15. Views are database objects that contain all of the data in a database Instructions : Review the underlined text. If it makes the statement correct, select “ No Change is needed” if the statement incorrect, select the answer choice that makes the statement correct A. B. C. D.



Quires No change is needed Stored procedures Tables



16. Which command should you use A. CREATE USER B. ADD USER C. INSERT USER D. ALLOW USER E. ALTER USER 17. Which command should you use to add a column to an exiting table? A. MODIFY B. INSERT C. CHANGE D. ALTER E. UPDATE 18. The component that holds information for a single entry in a table is called a : A. Data type B. Column C. Row D. View



19. 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. B. C. D.



All row in the table will be deleted All rows containing a NULL value in the First Name Column will be deleted. All rows and the table definition will be deleted You will receive an error message



20. Which category of SQL statements is used to add, remove and modify database structure? A. Data manipulation language (DML) B. Data control language (DCL) C. Data definition language (DDL) D. Data acces language (DAL) 21. You are writing an SQL Statement to retrieve rows from a table. Which data manipulation language (DML) command should you use? A. B. C. D.



GET READ OUTPUT SELECT



22. In SQL an INSERT statement is used to add a : A. Row of data to a table. B. Column to a table definition C. Use to a databse D. Table to a database 23. Use indexing to create, remove , or change databes objects Instructions : Review the underlined text. If it makes the statement correct, select “ No Change is needed” if the statement incorrect, select the answer choice that makes the statement correct A. B. C. D.



A unique constraint Data definition language (DDL) statement Data manipulation Languange (DML) statement No change is needed



24. Data in a database is stored in : A. Stored procedures B. Tables C. Data types D. Queries 25. Which command should you use in a query? A. Union B. Truncate C. Join D. Intersect 26.26. ID 1 2 3



GivenName Tia Susana Joey



When volunteer information changes, you must update the table. You need to change Tia’s name to Kimberly Which statement should you choose? A. UPDATE volunteer SET GivenName = ‘Kimberly’ WHERE GivenName = ‘Tia’ B. SET volunteer TO GivenName = ‘Kimberly’ WHERE GivenName = ‘Tia’ C. SET GivenName = ‘Kimberly’ FROM volunteer WHERE GivenName = ‘Tia’ D. UPDATE GivenName =’Kimberly’ FROM volunteer WHERE GivenName = ‘Tia’ 27. 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. The table wil be computed C. Forty (40) rows will be deleted from the table D. The transaction will restart



28. You accept an IT intership at a local charity. The charitu asks you to keep a record of its volunteers by using a database table named Volunteer. When volunteer ask to be removed from mailing lists, the table must be updated You need to use a transaction to ensure that the database has data integrity and referential integrity which statement should you use ? BEGIN TRANSACTION VolunteerDelete; DELETE FROM Volunteer WHERE Id = 13; COMMIT TRANSACTION VolunteerDelete; WHILE TRANSACTION VolunteerDelete; DELETE FROM Volunteer WHERE Id = 13; COMMIT TRANSACTION VolunteerDelete; OPEN TRANSACTION VolunteerDelete; DELETE FROM Volunteer WHERE Id = 13; COMMIT TRANSACTION VolunteerDelete; UPDATE TRANSACTION VolunteerDelete; DELETE FROM Volunteer WHERE Id = 13; COMMIT TRANSACTION VolunteerDelete; 29. A database contains two tables named customer and order. You execute the following statement : DELETE FROM Order WHERE CustomerID = 209 What is the result ? A. All orders for CustomerID 209 are deleted from the Order table, and CustomerID 209 is deleted from the customer table B. CustomerID 209 is deleted from the Customer Table C. The first order for customerID 209 is deleted from the order table D. All orders for CustomerID 209 are deleted from the order table