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 Signup and view all the answers

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

JOIN 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 Signup and view all the answers

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

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

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

To modify existing rows in a table 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 Signup and view all the answers

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

3 NF Signup and view all the answers

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

Application Design Signup and view all the answers

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

Transaction data usability 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 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 Signup and view all the answers

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

Inability to handle complex queries Signup and view all the answers

Which normal form helps to prevent redundancy in database design?

3 NF Signup and view all the answers

Which management activity enhances the efficiency of database application stages?

Requirements collection and analysis 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. 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 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. Signup and view all the answers

Which method effectively disables remote connections to a MySQL server?

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

Which scenario correctly describes referential integrity?

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

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

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

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

Regularly updating index statistics. 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 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 Signup and view all the answers

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

Master.info Signup and view all the answers

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

Database security 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 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); Signup and view all the answers

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

Physical database design 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 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 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 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 Signup and view all the answers

Which command is NOT used to terminate a transaction?

TRUNCATE Signup and view all the answers

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

Database security Signup and view all the answers

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

ROLLBACK Signup and view all the answers

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

AFTER UPDATE trigger Signup and view all the answers

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

COMMIT 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%'; 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. 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; 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 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. 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. 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'; 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'; Signup and view all the answers

Study Notes

Database management systems (DBMS) use structures like bitmaps, b-trees, and hashes for data storage and retrieval.
Indexes are used to speed up data retrieval, particularly for SELECT statements, by reducing the amount of data that needs to be searched.
Normalization reduces data redundancy and improves data integrity by organizing data into separate tables.
A primary key uniquely identifies a row in a table.
A composite key is a combination of multiple columns that uniquely identifies a row in a table.
Stored procedures are sets of SQL statements that perform a specific task.
Stored procedures can improve performance and security.
GRANT SELECT gives user permission to read data in a table.
Database backups are crucial for data recovery.
Data types handle different kinds of data and are crucial for managing data storage space and ensuring data integrity.
Data is stored in rows and columns in tables.
A row in a table is called a tuple or a record.
A column in a table is called an attribute or a field.
Data manipulation language (DML) commands manipulate data within a database (e.g., INSERT, UPDATE, DELETE, SELECT)
Indexes can improve query performance.
Appropriate data types should be used for columns to minimize storage space.
Data integrity in relational databases is maintained by primary and foreign keys which link tables.
Data manipulation language (DML) commands may include INSERT, UPDATE, and DELETE to change data in a table.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge on SQL fundamentals with this quiz. Explore concepts such as commands, normalization, and database planning. Perfect for beginner to intermediate learners in database management.