SQL and Database Final Exam Guide
SQL and Database Design Flashcards
Q:
What is a DBMS?
A:
A Database Management System (DBMS) is software that enables users to create, update, and manage databases efficiently.
Q:
Define a relational database.
A:
A relational database organizes data into tables (relations) consisting of rows and columns, enabling relationships between the data to be established.
Q:
What is a primary key?
A:
A primary key is a column (or set of columns) in a table that uniquely identifies each row in the table.
Q:
What is a composite primary key?
A:
A composite primary key is a primary key that consists of two or more columns, used to uniquely identify rows in a table.
Q:
Define a foreign key.
A:
A foreign key is a column in one table that refers to the primary key in another table, establishing a relationship between the two tables.
Q:
What is a data type in SQL?
A:
A data type specifies the type of data that a column can hold, such as integers, strings, or dates.
Q:
What is a default value in SQL?
A:
A default value is the value automatically assigned to a column when no explicit value is provided during insertion.
Q:
What is Standard SQL?
A:
Standard SQL is a standardized language for querying and manipulating relational databases.
Q:
Define Transact-SQL.
A:
Transact-SQL (T-SQL) is a proprietary extension of SQL used in Microsoft SQL Server.
Q:
What are tables, rows, and columns in a database?
A:
A table is a collection of related data, rows are individual records, and columns are the fields defining data attributes.
Q:
What is a null value?
A:
A null value represents missing or unknown data in a database.
Q:
Explain the concept of an identity column.
A:
An identity column automatically generates a unique number for each new row in a table.
Q:
What is a result set in SQL?
A:
A result set is the collection of rows returned by a query.
Q:
Differentiate between an inner join and an outer join.
A:
An inner join retrieves rows with matching values in both tables, while an outer join includes all rows from one table and matching rows from the other, with nulls for non-matches.
Q:
What is a schema in a database?
A:
A schema is the structure that defines how data is organized in a database, including tables, columns, and relationships.
Q:
Define a database diagram.
A:
A database diagram visually represents the structure of a database, including its tables and relationships.
Q:
What is a cascading update in SQL?
A:
A cascading update automatically updates the foreign key values in related tables when the primary key value is updated.
Q:
What is a Boolean expression?
A:
A Boolean expression is a logical statement that evaluates to true or false.
Q:
What is a column alias?
A:
A column alias is a temporary name assigned to a column in a query result for better readability.
Q:
Define a scalar function in SQL.
A:
A scalar function operates on a single value and returns a single value, such as LEN() or GETDATE().
Q:
What are aggregate functions?
A:
Aggregate functions perform calculations on a group of values and return a single value, such as SUM(), AVG(), and COUNT().
Q:
What is a subquery?
A:
A subquery is a query nested inside another query, used to retrieve data for the main query.
Q:
What are string data types?
A:
String data types store alphanumeric characters, including fixed-length (CHAR) and variable-length (VARCHAR) strings.
Q:
Explain implicit and explicit data type conversions.
A:
Implicit conversion is automatically performed by SQL, while explicit conversion requires functions like CAST() or CONVERT().
Q:
What is a view in SQL?
A:
A view is a virtual table based on the result of a query, used for simplifying complex queries and enhancing security.
Q:
What is a stored procedure?
A:
A stored procedure is a precompiled collection of SQL statements stored in the database, used to perform repetitive tasks.
Q:
What are the benefits of normalization?
A:
Normalization reduces redundancy and improves data integrity by organizing data into efficient structures.
Q:
What is the first normal form?
A:
A table is in the first normal form if each column contains atomic (indivisible) values and each row is unique.
Q:
What is referential integrity?
A:
Referential integrity ensures that relationships between tables remain consistent, typically enforced using foreign key constraints.
Q:
Define an index in SQL.
A:
An index improves query performance by providing a quick way to look up data in a table.
Q:
What are scalar and string functions?
A:
Scalar functions operate on single values, while string functions manipulate text, such as CONCAT() and SUBSTRING().
Q:
What is a Cartesian product in SQL?
A:
A Cartesian product is the result of a cross join, combining all rows from two tables.
Q:
What is the difference between a fully-qualified and partially-qualified object name?
A:
A fully-qualified name includes the database, schema, and object, while a partially-qualified name omits some of these components.
Q:
What is the purpose of a linking table?
A:
A linking table resolves many-to-many relationships by storing primary keys from the related tables as foreign keys.
Q:
What is data redundancy?
A:
Data redundancy occurs when the same data is stored in multiple places, leading to potential inconsistencies.
Q:
What are the benefits of using indexes?
A:
Indexes speed up data retrieval and improve query performance, especially on large datasets.
Q:
What is a logical operator in SQL?
A:
Logical operators, such as AND, OR, and NOT, are used to combine or negate Boolean expressions.
Q:
What is a nested sort?
A:
A nested sort specifies multiple levels of sorting in a query, using ORDER BY with multiple columns.
Q:
What are the types of joins in SQL?
A:
Types of joins include inner join, left outer join, right outer join, full outer join, and cross join.
Q:
What is the purpose of a calculated value?
A:
A calculated value is derived from existing data using expressions or functions in a query.
Q:
What are system stored procedures?
A:
System stored procedures are built-in procedures provided by SQL Server to perform administrative tasks.
Q:
What is a return value in a stored procedure?
A:
A return value is a single value returned by a stored procedure to indicate success, failure, or other statuses.
Q:
What are the steps in data structure design?
A:
Steps include identifying entities, defining attributes, establishing relationships, and normalizing the data structure.
Q:
What is the second normal form?
A:
A table is in the second normal form if it is in the first normal form and all non-key attributes are fully dependent on the primary key.
Q:
What is the third normal form?
A:
A table is in the third normal form if it is in the second normal form and all attributes are only dependent on the primary key.
Q:
What is a fully-qualified object name?
A:
A fully-qualified object name specifies the database, schema, and object name, ensuring clarity in references.
Q:
What is a correlation name?
A:
A correlation name is a table alias used in queries to simplify references to table names.
Q:
What is an entity in database design?
A:
An entity represents an object or concept in a database, with attributes defining its properties.
Q:
What is a composite key?
A:
A composite key is a combination of two or more columns that uniquely identify a row in a table.
Q:
Define the term 'unnormalized data structure.'
A:
An unnormalized data structure contains data that has not been organized to eliminate redundancy.
Q:
What is a wildcard in SQL?
A:
A wildcard is a character used in search patterns, such as % for multiple characters and _ for a single character.
Q:
What is a concatenation operator?
A:
A concatenation operator combines two or more strings into a single string, such as using '+' in T-SQL.
Q:
What are the benefits of views in SQL?
A:
Views simplify complex queries, enhance security by restricting data access, and improve query organization.
Q:
What is a script in SQL Server?
A:
A script is a collection of SQL statements written in a file or batch for execution.
Q:
What is normalization in database design?
A:
Normalization organizes data to reduce redundancy, improve consistency, and simplify maintenance.