Table of Contents
Enroll Here: SQL and Relational Databases 101 Cognitive Class Exam Quiz Answers
Introduction to SQL and Relational Databases 101
SQL is a specialized programming language used for managing and manipulating relational databases. It allows users to perform various operations on data stored in these databases, such as querying data, inserting new records, updating existing records, and deleting records. SQL is crucial for interacting with relational database management systems (RDBMS) like MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle.
Relational Databases: A relational database is a type of database that stores and organizes data into tables consisting of rows and columns. These tables are related to each other based on common fields across different tables. This relational model allows for efficient organization, retrieval, and manipulation of data.
Key Concepts in SQL:
- Tables: In SQL, data is stored in tables. Each table consists of rows (records) and columns (fields).
- Queries: SQL allows you to retrieve data from a database using SELECT statements. Queries can be simple (selecting all rows and columns) or complex (involving conditions, joins, aggregations, etc.).
- Inserting and Updating Data: SQL provides commands like INSERT for adding new data and UPDATE for modifying existing data within tables.
- Deleting Data: The DELETE command is used to remove records from a table based on specified conditions.
- Joins: SQL supports various types of joins (e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN) to combine data from multiple tables based on related columns.
- Constraints: Constraints ensure data integrity by enforcing rules on columns. Examples include UNIQUE (ensuring values are unique), NOT NULL (ensuring a column cannot have NULL values), and FOREIGN KEY (enforcing referential integrity between tables).
- Indexes: Indexes are used to speed up data retrieval operations by providing quick access to rows based on indexed columns.
- Transactions: SQL supports transactions, which are sequences of SQL operations that are executed as a single unit. This ensures data consistency and integrity.
Benefits of SQL and Relational Databases:
- Data Integrity: Relational databases enforce constraints to maintain data accuracy and consistency.
- Flexibility: SQL provides a flexible and powerful way to manipulate and retrieve data, supporting a wide range of operations.
- Scalability: Relational databases can handle large volumes of data and support multiple concurrent users.
- Security: They offer robust security features to protect sensitive data.
Conclusion: SQL and relational databases play a fundamental role in managing structured data efficiently. Understanding SQL allows developers and analysts to interact effectively with databases, perform complex data manipulations, and ensure data integrity. This capability is essential for applications ranging from web development to enterprise systems where structured data management is critical.
SQL and Relational Databases 101 Cognitive Class Certification Answers
Module 1 – SQL and Relational Databases 101 Quiz Answers
Question 1: What is a Database?
- A program that stores data
- Stores data in tabular form
- A repository of data
- All of the above
Question 2: Advantages of the relational model include:
- Provides logical and physical data independence
- Data is stored in simple data structures
- It is the most used data model
- All of the above
Question 3: In an Entity-Relationship diagram, the Entity Name maps to the Table name, the attributes map to the …
- Table rows and columns
- Table columns
- Table rows
- None of the above
Module 2 – Relational Model Constraints and Data Objects Quiz Answers
Question 1: Which of the following statements is true?
- A table can have a primary key and a foreign key
- A Foreign Key is a set of columns referring to a primary key of another table
- A primary key uniquely identifies each row in a table
- All of the above
Question 2: Which Relational Constraint prevents duplicate values in a table?
- Entity Integrity constraint
- Null constraint
- Check constraint
- All of the above
Question 3: The Semantic Integrity Constraint defines the relationships between tables. (T/F)
- True
- False
Module 3 – Data Definition Language (DDL) and Data Manipulation Language (DML) Quiz Answers
Question 1: The Primary Key of a relational table uniquely identifies each _____ in a table.
- column
- row
- Both of the above
- Neither of the above
Question 2: The INSERT statement cannot be used to insert multiple rows in a single statement. (T/F)
- True
- False
Question 3: The SELECT statement is called a Query, and the output we get from executing the query is called a Result Set. (T/F)
- True
- False
Module 4 – Advanced DDL and DML Quiz Answers
Question 1: You want to select an author’s name from a table, but you only remember the author’s last name starts with the letter B, which string pattern can you use?
- SELECT lastname from author where lastname like ‘B$’
- SELECT lastname from author where lastname like ‘B%’
- SELECT lastname from author where lastname like ‘B#’
- None of the above
Question 2: In a SELECT statement, which SQL clause controls how the result set is displayed?
- ORDER BY clause
- ORDER IN clause
- ORDER WITH clause
Question 3: Which SELECT statement eliminates duplicates in the result set?
- SELECT country from author ORDER BY 1
- SELECT distinct(country) from author
- SELECT unique(country) from author
- None of the above
Module 5 – Working with Multiple Tables Quiz Answers
Question 1: An INNER JOIN returns only the rows that match. (T/F)
- True
- False
Question 2: A LEFT OUTER JOIN displays all the rows from the right table, and combines matching rows from the left table. (T/F)
- True
- False
Question 3: When using an OUTER JOIN, you must explicitly state that you want either a LEFT JOIN or a RIGHT JOIN. (T/F)
- True
- False
SQL and Relational Databases 101 Final Exam Answers
Question 1: The 5 basic SQL commands are…
- CREATE, SELECT, INSERT, UPDATE, DELETE
- CREATE, ALTER, INSERT, UPDATE, DELETE
- CREATE, SELECT, INSERT, MODIFY, DELETE
- All of the above
Question 2: The blueprint of any database system is the …
- Data model
- Information model
- Both of the above
Question 3: Attributes help clarify relationship diagrams (T/F)
- True
- False
Question 4: A table containing one or more foreign keys is called a Parent table. (T/F)
- True
- False
Question 5: The Referential Integrity Constraint ensures the validity of the data using a combination of Primary Keys and Foreign Keys.
- True
- False
Question 6: What are the basic categories of the SQL language based on functionality?
- Data Manipulation Language
- Data Definition Language
- Both of the above
- Neither of the above
Question 7: The CREATE TABLE statement is a….
- DDL statement
- DML statement
- Both of the above
Question 8: When using the UPDATE statement, if you do not specify the WHERE clause, all the rows in the table are updated.
- True
- False
Question 9: You want to select a list of books whose number of pages is between 100 and 200 . Select the correct query from the following options.
- SELECT title, pages from book where pages BETWEEN 100 AND 200
- SELECT title, pages from book where pages RANGE 100 AND 200
- SELECT title, pages from book where pages less than or equal to 100 AND pages greater than or equal to 200
- A or B
- All the options above are correct
Question 10: What is the default sorting mode of the ORDER BY clause?
- Ascending
- Descending
- Randomly selected order
- None of the above
- All of the above
Question 11: Which of the following can be used in a SELECT statement to restrict a result set?
- HAVING
- GROUP BY
- DISTINCT
- All of the above
Question 12: The HAVING clause works only with the GROUP BY clause. (T/F)
- True
- False
Question 13: Which of the following are valid types of OUTER JOIN?
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- A and B only
- All of the above
Question 14: A FULL JOIN returns only the rows that match. (T/F)
- True
- False
Question 15: To combine tables in relational databases, we use:
- Table names
- Matching values/Primary key
- Tables cannot be combined
- None of the above options are correct
Question 16: >> True or False: Semantic integrity ensures that data entered into a row reflects an allowable value for that row.
- True
- False
Question 17: Which of the following statements are correct about databases:
- A database represents some aspect of the real world
- A database is a logically coherent collection of data with some inherent meaning
- A database is designed, built and populated with data for a specific purpose
- All the above options are correct
Question 18: Which of the following statements are correct about primary keys
- The value of the Primary Key must be unique for each instance of the entity.
- There can be no missing values (i.e., Not Null) for Primary Keys.
- The Primary Key is immutable .i.e., once created the value of the Primary Key cannot be changed.
- All the above options are correct
Question 19: The ____________ keyword is used to eliminate duplicate tuples from the result set of an SQL query.
- COMMON
- DISTINCT
- ALL
- EXISTS
Question 20: Select the correct statements about the join operator
- Primary Key- Foreign Key is the common join operator
- Is used to combine more than one table
- You have to know the relationship between the tables
- None of the Statements are correct