Databases

Structured Query Language

What does SQL stand for?

Database Management Systems

What are IBM DB2, SQL server, Postgre SQL, MySQL, and Oracle examples of?

A Database is an organized collection of data so as to minimize data redundancy and ensure data consistency. It also provides for efficient data retrieval.

OR

A database contains records of connected information organized for efficient access

What is a database?

An entity

What is table otherwise known as in a database?

A field

What is a column otherwise known as in a database?

A cell

What is the intersection of a row and a column is called?

A database management system (DBMS) is software that enables users to create, define, manage, and manipulate databases. It provides a structured way to store, organize, retrieve, and modify large amounts of data efficiently and securely. The DBMS acts as an interface between the database itself and users or application programs, ensuring that data remains consistently organized and easily accessible

.

Key functions of a DBMS include:

• Creating and managing databases and their structures

Storing, retrieving, updating, and deleting data

Managing data security, integrity, and access control

Supporting concurrent data access by multiple users

Providing backup, recovery, and data administration tools

DBMSs can be categorized into types such as relational (RDBMS), NoSQL, object-oriented, and hierarchical systems, depending on how they structure and manage data.

What is a Database Management System?

— double dash starts a single line comment

Enclose block comments using /* comment */

What is the syntax of comments in SQL?

Text and Dates are enclosed in single quotes ‘ ‘

How is Text and Dates indicated in SQL?

Enclose the name in double quotes (“ “) or square brackets ([ ]).

ex. select [First Name]
from students

If a table or column name uses a reserved word or contains spaces what must be done for the DBMS to accept it?

Each field describes an attribute or property of the entity.

What does each column/field describe?

A row is also known as a record

What is a row of an entity otherwise known as?

Each row of a table describes one particular occurrence or instance of an entity.

What does each record describe?

Schema

What term refers to the organization of data as a blueprint of how the database is constructed?

A primary key in the context of databases is a column or a set of columns in a table whose values uniquely identify each row or record in that table. The primary key ensures that no two rows can have the same value(s) in the primary key column(s), and it cannot contain null values. Each table can have only one primary key, and it plays a crucial role in maintaining data integrity, preventing duplicate records, and establishing relationships between tables in a relational database

What is a primary key?

A natural key is a field or a combination of multiple fields that already exist in the entity, that creates a unique identifier of the record and it did not have to be artificially created for the purpose of the table having a primary key. But can be used as a primary key.

What is a natural key?

A surrogate key is a unique identifier for a record in a database table that is artificially created by the system, not derived from the actual data in the table. It has no business or semantic meaning, is typically auto-generated (such as an incrementing number or a UUID), and is used primarily to ensure each row is uniquely identified, especially when no suitable natural key exists

What is a surrogate key?

A composite key is a combination of two or more columns in a database table that together uniquely identify a record, even though none of the individual columns can uniquely identify the record on their own.

What is a composite key?

the “use” statement

What statement specifies which database to access for the execution of a query script, or portion thereof

go

What term causes the current group of statements to be executed immediately by the query processor, rather than grouping all queries into a single batch before execution

DatabaseName.SchemaName.TableName[.ColumnName]

Column name is optional.
Schema name is often “dbo” especially for database that predate the introduction of schemas.

What is the syntax of an absolute name of a table?

select

What command is used to retrieve a result set?

select ColumnName as AliasName

OR

select ColumnName as ‘Alias Name’

What is the syntax of aliasing a column name?

What is the syntax of sorting a query by FirstName in ascending order within HireDate in descending order?

What is the syntax of only retrieving the first 5 records returned from a query?

What is the syntax of only retrieving the first one percent of records returned?

The database engine will do its best to follow your wishes when returning a result set but may ignore ties.

Why is it important to consider ties when using the “top” modifier in queries?

If the ordering is left out, then your result set will correspond to the first n records in the unrestricted result set.

The result set may be unpredictable.

Why is it always best to include an “order by” clause when using the “top” clause?

What is the syntax of querying a result set where any duplicate data is removed?

What is the syntax of aliasing a table name?

What is the syntax of a query in which the price is not 19.99? Both versions, use NOT for one and != for the other.

What is the syntax of using between?

IN - true if the comparison value is equivalent to one value in the comma separated list. For example, if unitPrice= 45, unitPrice in (1, 45, 90) will return true.

Basically this operator is a nicer way of writing

unitPrice= 1 or unitPrice= 45 or unitPrice= 90

What is the syntax of using the “in” operator for which the number of units in stock is anyone of the following values: 5,10,15,20,25,30?