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.