SQL and Database Final Exam Guide

DBMS

Database Management System, software that enables users to create, update, and manage databases.

Relational database

Organizes data into tables (relations) consisting of rows and columns.

Primary key

A column (or set of columns) that uniquely identifies each row in a table.

Composite primary key

A primary key that consists of two or more columns.

Foreign key

A column in one table that refers to the primary key in another table.

Data type

Specifies the type of data a column can hold, such as integers, strings, or dates.

Default value

The value assigned to a column when no explicit value is provided.

Standard SQL

A standardized language for querying and manipulating relational databases.

Transact-SQL

A proprietary extension of SQL used in Microsoft SQL Server.

Table

A collection of related data organized into rows and columns.

Row

An individual record in a table.

Column

The field defining data attributes in a table.

Null value

Represents missing or unknown data.

Identity column

Automatically generates a unique number for each new row.

Result set

The collection of rows returned by a query.

Inner join

Retrieves rows with matching values in both tables.

Outer join

Includes all rows from one table and matching rows from another, with nulls for non-matches.

Schema

The structure that defines how data is organized, including tables and relationships.

Database diagram

Visually represents the structure of a database, including tables and relationships.

Cascading update

Automatically updates foreign key values in related tables when the primary key value is updated.

Boolean expression

A logical statement that evaluates to true or false.

Column alias

A temporary name assigned to a column in a query result.

Scalar function

Operates on a single value and returns a single value.

Aggregate functions

Perform calculations on a group of values and return a single value.

Subquery

A query nested inside another query.

String data types

Store alphanumeric characters, including CHAR and VARCHAR.

Implicit conversion

Automatically performed by SQL to change data types.

Explicit conversion

Requires functions like CAST() or CONVERT() to change data types.

View

A virtual table based on a query result.

Stored procedure

A precompiled collection of SQL statements stored in the database.

Normalization

Reduces redundancy and improves data integrity by organizing data.

First normal form (1NF)

Each column contains atomic values and each row is unique.

Referential integrity

Ensures relationships between tables remain consistent.

Index

Improves query performance by providing quick data lookups.

Logical operators

AND, OR, NOT operators used to combine or negate Boolean expressions.

Nested sort

Specifies multiple levels of sorting using ORDER BY with multiple columns.

Types of joins

Include inner join, outer join, and cross join.

Calculated value

Derived from existing data using expressions or functions.

System stored procedures

Built-in procedures provided by SQL Server.

Return value

A single value returned by a stored procedure.

Data structure design steps

Include identifying entities, defining attributes, and establishing relationships.

Second normal form (2NF)

In 1NF and all non-key attributes are fully dependent on the primary key.

Third normal form (3NF)

In 2NF and all attributes are dependent only on the primary key.

Fully-qualified object name

Specifies the database, schema, and object name.

Correlation name

A table alias used in queries.

Entity

An object or concept in a database, with attributes defining its properties.

Composite key

A combination of two or more columns that uniquely identify a row in a table.

Unnormalized data structure

Contains data that has not been organized to eliminate redundancy.

Wildcard

A character used in search patterns, such as % or _.

Concatenation operator

Combines two or more strings into a single string.

Benefits of views

Simplify complex queries and enhance security by restricting data access.

Script

A collection of SQL statements written in a file or batch for execution.

Normalization in database design

Organizes data to reduce redundancy, improve consistency, and simplify maintenance.