SQL Fundamentals Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

Which SQL keyword is used to add rows to a database table?

JOIN
UPDATE
INSERT (correct)
CREATE

What is the purpose of the INTERSECT command in SQL?

Combine results from two queries with all unique rows.
Return only rows that appear in both result sets. (correct)
Remove rows from a table.
Join two tables based on a common column.

Which two columns should be selected to create a composite primary key for the ChapterLanguage table?

City
ChapterId (correct)
Country
LanguageId (correct)

In order to normalize data to third normal form, how many tables should you create from a given recipe data?

3 (D) Signup and view all the answers

Which command should be used to retrieve data from two related database tables?

JOIN (A) Signup and view all the answers

Which keyword would you use to combine the results of two queries and return only the unique rows?

UNION (C) Signup and view all the answers

To evaluate changes in database objects, which SQL statements should you use?

Data definition language (DDL) statements (D) Signup and view all the answers

What is the primary function of the UPDATE command in SQL?

To modify existing rows in a table (D) Signup and view all the answers

What is the appropriate term to fill in the blank: The _______________ model for database management is based on first-order predicate logic?

relational (C) Signup and view all the answers

Which normal form should be applied to avoid many-to-many relationships in the StudentInformation table?

3 NF (A) Signup and view all the answers

Which of the following are steps in the database planning life cycle? Select three.

Application Design (A), Conceptual database design (B), Operational Maintenance (D) Signup and view all the answers

What are the characteristics of effective application design? Select three.

Transaction data usability (A), Output of the transaction (C), Anticipated utilization rate (D) Signup and view all the answers

Which design should John implement to create a description of a database on secondary storage media?

Physical database design (A) Signup and view all the answers

What would be the correct action to resolve a SELECT permission error on the CurrentEmp table?

Grant permissions to the employee (C) Signup and view all the answers

Which of the following is NOT a characteristic of relational databases?

Inability to handle complex queries (B) Signup and view all the answers

Which normal form helps to prevent redundancy in database design?

3 NF (C) Signup and view all the answers

Which management activity enhances the efficiency of database application stages?

Requirements collection and analysis (D) Signup and view all the answers

What action can resolve an issue with a failed DML statement on the Sales table? (Choose two.)

Provide an appropriate privilege or create views on the Sales table. (A), Clean up all the uncommitted transactions on the Sales table. (B) Signup and view all the answers

What is the process of extracting trails for transfer to a non-accessible security system by database administrators?

Native auditing (D) Signup and view all the answers

How can you reduce fragmentation caused by page splits in the OrderDetails table?

Change the fillfactor for the indexes to 60. (D) Signup and view all the answers

Which method effectively disables remote connections to a MySQL server?

Start the server with the --skip-networking option. (C) Signup and view all the answers

Which scenario correctly describes referential integrity?

Enforcing relationships between tables through foreign keys. (B) Signup and view all the answers

What happens when page splitting occurs in a heavily transacted table?

Performance may degrade due to fragmentation. (C) Signup and view all the answers

Which of the following actions can improve data retrieval performance in a database?

Regularly updating index statistics. (C) Signup and view all the answers

Which clause should be included in usp_GetEmp to ensure it executes under the context of the owner?

With Execute As Owner (B) Signup and view all the answers

What type of database design is used to create a high-level representation of the data structure?

Conceptual database design (B) Signup and view all the answers

Which of the following files should you copy for a backup related to MySQL replication?

Master.info (D) Signup and view all the answers

What is the best approach to prevent unauthorized changes to a SQL Server database?

Database security (C) Signup and view all the answers

Which two files contain essential configuration information that must be restored after a MySQL database server crash?

My.cnf (A), My.ini (B) Signup and view all the answers

Which SQL query correctly ensures that the lastname column in the employees table will not accept null values?

CREATE TABLE employees (id INTEGER PRIMARY KEY, firstname CHAR(50) NULL, lastname CHAR(75) NOT NULL, dateofbirth DATE NULL); (B) Signup and view all the answers

What type of database design involves how data is physically stored?

Physical database design (B) Signup and view all the answers

Which action is necessary to give an employee access to the CurrentEmp table?

Grant the employee the SELECT permission on the CurrentEmp table (D) Signup and view all the answers

What type of trigger should be implemented to ensure the ExamModifiedDate is updated only if no constraint violation occurs?

INSTEAD OF trigger (B) Signup and view all the answers

Which option will successfully create a trigger that updates ExamModifiedDate with the current date and time on the ExamQuestions table?

CREATE TRIGGER trgExamQuestionsModified ON dbo.ExamQuestions INSTEAD OF UPDATE NOT FOR REPLICATION AS UPDATE Exams SET ExamModifiedDate = GetDate() FROM inserted WHERE inserted.ExamID = Exams.ExamID (B) Signup and view all the answers

To restrict a MySQL server to only accept clients with new-format passwords, which option is correct?

Start the server with the --secure-auth option (D) Signup and view all the answers

Which command is NOT used to terminate a transaction?

TRUNCATE (D) Signup and view all the answers

What mechanism protects a database from unintended activities such as misuse and attacks?

Database security (A) Signup and view all the answers

Which command effectively rolls back the previous operations in a transaction?

ROLLBACK (C) Signup and view all the answers

Which of the following triggers would activate upon an UPDATE action on exam entries?

AFTER UPDATE trigger (A) Signup and view all the answers

Which command would you use to finalize a successful transaction in MySQL?

COMMIT (D) Signup and view all the answers

Which query will retrieve ItemName and Price when chocolate appears in the ItemDescription column?

SELECT ItemName, Price FROM products WHERE ItemDescription LIKE '%chocolate%'; (A) Signup and view all the answers

What could likely cause a stored procedure to return all null values when verifying that there is data in the Person table?

All columns being concatenated contain null values, including Prefix. (A) Signup and view all the answers

Which SQL statement will return the country and number of orders in each country where the number of orders is less than 50?

SELECT Country, COUNT(orderID) AS Orders FROM orders GROUP BY Country HAVING COUNT(orderID) < 50; (D) Signup and view all the answers

Which feature does a relational database use to ensure that data entered into a column is valid?

Data Types (C) Signup and view all the answers

Which condition is likely to prevent a table from being in third normal form?

A column is dependent on a non-key attribute. (D) Signup and view all the answers

Which of these statements about functions and stored procedures is correct?

Functions may be used within a SQL statement, whereas procedures cannot. (C) Signup and view all the answers

Which query will return a result set of orders placed after January 2023 in all states except California?

SELECT * FROM orders WHERE orderDate > '2023-01-01' AND State != 'CA'; (B) Signup and view all the answers

What is the correct way to create a report of data from the students table based on specific requirements?

SELECT enrollment_date, graduation_date, academic_status FROM students WHERE academic_status = 'Graduated'; (C) Signup and view all the answers

Flashcards

Data Definition Language (DDL)

A set of commands used to define and modify the structure of a database, including creating, altering, and deleting tables and other database objects.

Data Manipulation Language (DML)

Commands used to manipulate data within a database, including adding, deleting, and updating rows.