Terms To Remember

Question 1

What is a row called in a database?

Answer 1

A Record

Question 2

What is a column called in a database?

Answer 2

Field

Question 3

Databases: what function do they provide?

Answer 3

They organize the information we receive so that we can use it

Question 4

Tables

Answer 4

A collection of rows and columns. A database can contain more than one table.

Question 5

The structure of tables in the database?

Answer 5

Schema. The database’s schema includes the information about the layout of tables and other information about the database itself.

Question 6

Relational Database

Answer 6

Tables are made up of rows and columns. Columns are the relation.

Question 7

What are common column data types?

Answer 7

Number, text, date, true/false value, binary data

Question 8

Composite Key

Answer 8

Two or more fields taken together to act as a unique identifier

Question 9

Foreign Key

Answer 9

Primary key from one table when referencing another table

Question 10

What are the three types of Database Relationships?

Answer 10

One-to-many
Many-to-many
One-to-one

Question 11

Transaction

Answer 11

A set of operations that all must be completed

Question 12

What does ACID stand for in relation to transactions?

Answer 12

Atomic
Consistent
Isolated
Durable

Question 13

What does Atomic mean?

Answer 13

Means that the transaction is indivisible, that pieces of it can’t be separated out.

Question 14

What does consistent mean?

Answer 14

Whatever the transaction does, it needs to leave the database in a valid or consistent state. Can’t violate integrity.

Question 15

What does Isolated mean?

Answer 15

While the activities in the transaction are being completed, nothing else can make changes to the data involved

Question 16

What does Durable mean?

Answer 16

Information actually gets written to the database.

Question 17

SQL

Answer 17

Structured Query Language

Question 18

What does SQL do?

Answer 18

Allows statements to be written for DBMS to interpret how to interact with data (DML)

Offers feature to manage the database itself, such as creating or modifying tables and controlling access to tables. (DDL and DCL)

Question 19

What does CRUD stand for?

Answer 19

Create
Read
Update
Delete

Question 20

A relationship connects two pieces of data in different _____ in the same _____.

Answer 20

tables; database

Question 21

Which is a good example of a candidate key?

Answer 21

an employee ID number. Any piece of data that uniquely represents a row is a candidate key, and if you have a value that occurs in the data naturally, that’s a natural key.

Question 22

How many SQL clauses are in this query?

SELECT Width,Height FROM Shapes;

Answer 22

two. Each keyword, SELECT and FROM, defines a single clause.

Question 23

In a database, what is a relation?

Answer 23

a set of attributes (columns) that describe information about specific instances (rows) of an entity
This was the correct answer

Question 24

When is an associative table useful?

Answer 24

when records need to be related in a many-to-many relationship. An associative (or linking) table relates foreign keys from different tables to associate their records.

Question 25

What does the term transaction mean?

Answer 25

A transaction is a collection of steps that must all be completed in order for a change to be made to the database.

Question 26

If you reference a key from Table A in Table B, what is that value in Table B?

Answer 26

a foreign key. Because it refers to a key in another table, in this context, the value is called a foreign key.

Question 27

In a database where you keep track of records for a school, what tables should you expect to find?

Answer 27

Each of these tables holds a different kind of information, and because the tables can store more than one record, the table names are pluralized.

Question 28

When talking about data types, what do you call the group of types that represent text?

Answer 28

string types. There are various kinds of string types to accommodate text of different lengths.

Question 29

To store the value 4:32PM, December 27, 2019, which data type would you use?

Answer 29

DATETIME. This is the correct type, because it includes both a date component and a time component. The other options here do not.

Question 30

Which condition represents a NULL value?

Answer 30

a date cell containing no data. A cell, regardless of its data type, is NULL when it has no value.

Question 31

If you don’t use a number type to store numeric data, _____.

Answer 31

you need to take additional steps to process the data as a number whenever you use it. Storing numeric data in numeric data types gives you the ability to work with numbers directly, as you might do when you use mathematical operations in queries.

Question 32

When planning a database, what do you start with?

Answer 32

an Entity Relationship (ER) diagram. Using an ER diagram, you can plan out what fields will appear on which tables, and how they’re related.

Question 33

Three Relationships?

Answer 33

one to one
one to many
many to many

Question 34

Referential Integrity

Answer 34

Databases are aware of relationships and won’t allow a user to modify data in a way that violates those relationships.

Question 35

Which is an example of referential integrity?

Answer 35

preventing the user from entering a record that refers to nonexistent data

Question 36

Defining relationships helps you to do what?

Answer 36

reduce the repetition of data across tables

model real-world scenarios and requirements

understand how your data should be stored

Question 37

What is it called if you delete a record and the database goes on and deletes other records associated with that record?

Answer 37

a cascading delete

Question 38

In a one-to-many relationship, the value representing the ‘many’ side is what?

Answer 38

foreign key

Question 39

When modeling a many-to-many relationship, how should you name the linking table?

Answer 39

with a combination of the names of the tables it’s linking

Question 40

When you need to create a many-to-many relationship, what do you need to generate?

Answer 40

a linking table that has a one-to-many relationship with two or more tables

Question 41

Which scenario represents a one-to-many relationship?

Answer 41

bank customers linked to their bank accounts. A certain customer can have multiple accounts, but a certain account can belong to only one customer.

Question 42

A one-to-one relationship _____.

Answer 42

allows only one record to be connected to only one other record

Question 43

First Normal Form (1NF) Normalization Rule?

Answer 43

Values in each cell should be atomic and tables should have no repeating groups.

Question 44

Second Normal Form (2NF) Normalization Rule?

Answer 44

No value in a table should depend on only part of a key that can be used to unique identify a row

Question 45

Third Normal Form (3NF) Normalization Rule?

Answer 45

Values should not be stored if they can be calculated from another non-key field

Question 46

Denormalization

Answer 46

The process of intentionally duplicating information in a table, in violation of normalization rules

Question 47

If you can figure out the value of one non-key field in a row by looking at another non-key field in that same row, what do you violate?

Answer 47

Third Normal Form

Question 48

In order to put a database into Third Normal Form, _____.

Answer 48

it must also be in First and Second Normal Form

Question 49

First Normal Form tells you to do what?

Answer 49

remove repeating groups

Question 50

When might you choose to denormalize a table?

Answer 50

Retrieving the data upon request would be slow or burdensome, and you are able to pre-calculate or store a copy of the data somewhere it can be retrieved faster.

Question 51

A table has two rows with the same values in all columns. Which step can you take to have this table meet the first normal form (1NF) requirements?

Answer 51

Add a primary key to the table. The primary key will add a unique value for each row, and thus eliminate the repeating duplicate rows issue.

Question 52

What is the foreign key in the table created after this command?

CREATE TABLE Models (
ModelID INT(6) NOT NULL AUTO_INCREMENT,
Color INT(6) REFERENCES Colors(ColorID),
PRIMARY KEY(ModelID)
);

Answer 52

Color

Question 53

Which SQL command will you use to create a new database called “mydb”?

Answer 53

CREATE DATABASE mydb;

Question 54

Which of these is not an example of a time when you would use an aggregate function?

Answer 54

looking up customers with the name Rafael Montresso. Aggregate functions are used to tell you about certain characteristics of multiple records. Requesting a record by specific field values is not an aggregate operation.

Question 55

Which WHERE condition can you use to find all records containing a first name starting with the letter “A”?

Answer 55

WHERE FirstName LIKE “A%”;

Question 56

When using an aggregate function, how many results do you expect?

Answer 56

Aggregate functions return one value that describes a set of data.

Question 57

What should come instead of the ??? placeholder for this query to return all fields and records in the table?

SELECT ??? FROM mytable;

Answer 57

*
The asterisk is used as a wildcard to retrieve everything from the given table.

Question 58

True or False? In any given query, you can only join together a maximum of two tables.

Answer 58

FALSE. You can join together many tables as long as you tell the database which pairs of values on the tables are intended to match.

Question 59

In order to use records from more than one table in a query, you need to _____ the tables based on some matching criteria.

Answer 59

join

Joining tables allows you to match rows from one table with rows on another table.

Question 60

If a table is set to auto-increment the primary key, you’ll need to know the next value and set it manually when you enter a record. True or false?

Answer 60

FALSE.

When the database automatically increments a key field, you don’t need to worry about setting the value. The database will provide the next value in the sequence automatically.

Question 61

When modifying a record, it’s a good idea to specify the record _____.

Answer 61

as precisely as possible, ideally using the primary key

Question 62

True or false? In order to sort results based on a field, that field needs to appear in the final output.

Answer 62

FALSE

A field used for sorting doesn’t need to appear in the ‘SELECT’ clause of the query.

Question 63

A SQL statement that returns requested records from the database is called:

Answer 63

a SQL query

Question 64

Where can you write SQL?

Answer 64

in database management software

in an app’s source code

at a command-line console

Question 65

You can narrow down the results that a query returns by only asking for results where a _____ matches a given value.

Answer 65

field

Question 66

Which of these tasks can you accomplish using SQL as a DML?

Answer 66

inserting a record into a table

Question 67

When first defining a table, what should you specify?

Answer 67

A. the table’s name

C. the fields and type of data they contain

E. the primary key and any referential constraints

Question 68

Which sorting option shows dates from latest to earliest?

Answer 68

ORDER BY Date DESC

Question 69

When you use SQL statements to create or modify the structure of a database, what is SQL being used as?

Answer 69

a Data Definition Language (DDL)

As a Data Definition Language, SQL can be used to create and modify the structure of database tables.

Question 70

What is the correct SQL syntax to use when joining tables A and B on their “ID” field?

Answer 70

SELECT * FROM A
JOIN B ON A.ID=B.ID

Question 71

When telling the database that a certain field must not contain an empty value, you say that it is:

Answer 71

not null

Question 72

For a table that holds the purchase amounts in a grocery store over time, which query will likely return the highest value?

Answer 72

SELECT SUM(amount) FROM purchases;

Question 73

What is the possible issue with this query?

UPDATE mytable SET price=5;

Answer 73

It may update undesired records.

Without a WHERE condition, this query will update all records including possibly undesired ones.

Question 74

Which database is often used in a big data context?

Answer 74

Hadoop and Spark are often used for big data applications.

Question 75

Microsoft Access is generally considered a(n) _____ database platform.

Answer 75

desktop

Desktop databases are typically hosted on a workstation rather than a dedicated server, and they’re designed to support a few to a few hundred users.

Question 76

Relational databases can store all of these except what?

Answer 76

graph data

Some NoSQL databases are designed to store data arranged as graphs rather than in relations.

Question 77

A stored procedure is _____.

Answer 77

a predefined query or statement

Stored procedures are queries that are stored on the server, and they can be called by developers or users in their queries.

Question 78

An index _____.

Answer 78

helps to increase the speed of lookups using a particular column at the cost of speed while modifying records

As with denormalization, indexes offer a trade off.

Question 79

What is it called when a malicious user tries to change the way a SQL statement works by entering their own SQL?

Answer 79

injection

This kind of attack involves someone injecting their own code into an application.

Question 80

If you store certain kinds of information, your database may be subject to certain compliance regulations. True or false?

Answer 80

TRUE

If you store personally identifiable information (PII), health information, or some other kinds of information, your database may be subject to various regulations. Be sure to do your research and stay in compliance!

Question 81

What is a database transaction?

Answer 81

a group of statements that runs or fails as a whole

Transactions combine multiple statements into a single logical block, where one failing statement rolls back the entire action.

Question 82

What is a table?

Answer 82

A table stores and displays data in a structured format consisting of columns and rows that are similar to those seen in Excel spreadsheets.

Question 83

What does SQL do?

Answer 83

SQL is used to access and manipulate a database.

It can be used to select & filter, add and remove data and tables, and much more.

Question 84

The SELECT statement

Answer 84

used to select data from a table.

Question 85

*

Answer 85

selects all columns of the table.

Question 86

in order to select everything from the Customers table, we use the following:

Answer 86

SELECT * FROM Customers

Question 87

Instead of *, we can specify a column that we want to select.

For example, let’s select only the City column:

Answer 87

SELECT City FROM Customers

Question 88

We can specify multiple columns in the SELECT statement by separating them with commas.

For example:

Answer 88

SELECT firstname, lastname FROM Customers

Question 89

You can run multiple SQL queries, by separating them with semicolons.

For example:

Answer 89

SELECT firstname, lastname
FROM Customers;

SELECT firstname, city, age
FROM Customers;

Question 90

SQL is case insensitive.

The following statements are equivalent and will produce the same result:

Answer 90

select city from Customers;
SELECT City FROM Customers;
sElEct ciTY From Customers;

Common practice is all upper case

Question 91

The ORDER BY command

Answer 91

used to sort the data in a SELECT statement.

Question 92

By default, ORDER BY sorts the results in

Answer 92

ascending order. (A to Z)

Question 93

We can specify the ordering using the

Answer 93

ASC and DESC keywords.

SELECT * FROM Customers
ORDER BY firstname DESC

Question 94

lets sort our Customers data using the city column, followed by the lastname column:

Answer 94

SELECT * FROM Customers
ORDER BY city ASC, lastname DESC

Question 95

The SELECT statement returns all the rows in the table.

In case we only need a specific number of rows from the table, we can use

Answer 95

the LIMIT keyword.

Question 96

let’s select only the top 3 rows from our table:

Answer 96

SELECT * FROM Customers
LIMIT 3

Question 97

We can combine the LIMIT with ORDER BY.

For example:

Answer 97

SELECT * FROM Customers
ORDER BY city DESC
LIMIT 3

Remember, the LIMIT keyword has to come after the ORDER BY in the SELECT statement.

Question 98

We can also select rows starting from an offset.

For example, let’s select 3 rows starting from the 3rd:

Answer 98

SELECT * FROM Customers
ORDER BY city DESC
LIMIT 3 OFFSET 2

The first row has the offset 0, which is why OFFSET 2 will start from the 3rd row in the result.

Question 99

We can remove the duplicates using the

Answer 99

DISTINCT keyword.

Question 100

To select only specific rows that satisfy a condition

Answer 100

WHERE command can be used.

Question 101

For example, let’s select the rows that have the age column greater than 30:

Answer 101

SELECT * FROM Customers
WHERE age > 30

Question 102

The following comparison operators can be used in the WHERE condition:

Answer 102

> greater than
< less than
= greater or equal
<= less or equal

Question 103

o check for equality, the

Answer 103

equal to operator = can be used:

WHERE age = 42

Question 104

To compare with a text value, put the

Answer 104

text value in single quotes.

WHERE firstname = ‘James’

Question 105

The not equal operator is

Answer 105

<>

Question 106

The BETWEEN operator can be used to

Answer 106

Select rows that are in a range of values.

SELECT * FROM Customers
WHERE age
BETWEEN 30 AND 50

Question 107

The AND operator

Answer 107

combines two conditions and is satisfied if both conditions are satisfied.

SELECT * FROM Customers
WHERE age >= 42 AND age <= 60

Question 108

the OR operator

Answer 108

satisfied if any one of its conditions holds.

SELECT * FROM Customers
WHERE city = ‘New York’ OR city = ‘Chicago’

Question 109

the IN operator

Answer 109

Instead of multiple OR conditions for a single column, the IN operator can be used.

SELECT * FROM Customers
WHERE city IN (‘New York’, ‘Chicago’)

Question 110

The NOT IN operator

Answer 110

allows you to exclude a list of specific values from the result set.

SELECT * FROM Customers
WHERE city NOT IN (‘New York’, ‘Chicago’)

Question 111

When combining conditions, it is important to

Answer 111

use parentheses, so that the order to evaluate each condition is known.

Question 112

Any text values in SQL statements need to be enclosed in

Answer 112

single quotes.

Question 113

SQL supports filtering text based on a search condition.

This can be done using

Answer 113

the LIKE keyword, by specifying a pattern for the text.

SELECT * FROM Customers
WHERE lastname LIKE ‘%s’

For example, let’s select all Customers where the lastname column value ends with an ‘s’:

Question 114

To match only a single character

Answer 114

the _ underscore symbol can be used.

SELECT * FROM Customers
WHERE firstname LIKE ‘_ames’

This will match all the Customer firstnames that start with any letter, then end with ‘ames’.

Question 115

NULL is

Answer 115

the absence of value.

This means that any field in our table with no value with have the value NULL.

Question 116

To check for NULL values

Answer 116

the IS NULL operator can be used.

SELECT * FROM Customers
WHERE age IS NULL

Question 117

Similarly, we can check for non-null values using

Answer 117

IS NOT NULL

SELECT * FROM Customers
WHERE age IS NOT NULL

Question 118

the COUNT() function

Answer 118

is used to return the number of records of a select statement

SELECT COUNT(*)
FROM Customers

This will return the number of rows in the table.

Question 119

SUM()

Answer 119

It returns the sum of the values in a column.

SELECT SUM(salary)
FROM Customers

Question 120

The AVG() function

Answer 120

returns the average value of a numeric column.

SELECT AVG(salary)
FROM Customers

Question 121

MIN() and MAX() are used to

Answer 121

return the minimum and maximum value of a column

SELECT MIN(salary)
FROM Customers;

SELECT MAX(salary)
FROM Customers;

Question 122

the UPPER() function

Answer 122

can be used to convert text to uppercase.

SELECT UPPER(firstname)
FROM Customers

Question 123

We can provide a new name for the column with the

Answer 123

AS keyword

SELECT UPPER(firstname) AS Name
FROM Customers

Question 124

SQL allows to group records using the

Answer 124

GROUP BY clause

Question 125

SELECT city, COUNT(*)
FROM Customers
GROUP BY city

Answer 125

This query will return the number of customers in each city, as well as the city name.

Question 126

We can filter groups using the

Answer 126

HAVING clause

SELECT city, COUNT() AS c
FROM Customers
GROUP BY city
HAVING COUNT() > 1
ORDER BY c DESC

Question 127

Creating a basic table involves

Answer 127

naming the table and defining its columns and each column’s data type.

Question 128

INT

Answer 128

whole number

Question 129

FLOAT

Answer 129

floating point number

Question 130

DOUBLE

Answer 130

A double precision floating-point number

Question 131

DATE

Answer 131

A date in YYYY-MM-DD format

Question 132

DATETIME

Answer 132

A date and time combination in YYYY-MM-DD HH:MM:SS format

Question 133

TIMESTAMP

Answer 133

A timestamp, calculated from midnight, January 1, 1970

Question 134

TIME

Answer 134

Stores the time in HH:MM:SS format

Question 135

VARCHAR(M)

Answer 135

Variable-length character string. Max size is specified in parenthesis

Question 136

TEXT

Answer 136

Large amount of text data

Question 137

The CREATE TABLE command

Answer 137

defined the table name and the columns list in parentheses.

Question 138

A column can also have a default value, which is applied to the rows when no value is provided for it. It is defined using the

Answer 138

DEFAULT keyword

CREATE TABLE Customers (
id INT,
firstname VARCHAR(128),
lastname VARCHAR(128),
salary INT DEFAULT 0,
city VARCHAR(128)
);

Question 139

We can also specify if a column can or cannot have NULL values.

For example

Answer 139

CREATE TABLE Customers (
id INT,
firstname VARCHAR(128) NOT NULL,
lastname VARCHAR(128),
salary INT DEFAULT 0,
city VARCHAR(128)
);

Now, when inserting data into the table, the firstname column has to have a value.

Question 140

When the table is created, it is empty.

We can insert data using the

Answer 140

INSERT command

INSERT INTO Customers VALUES
(1, ‘John’, ‘Smith’, ‘New York’, 5000)

Question 141

The UPDATE statement allows you to

Answer 141

change values in a table.

UPDATE Customers
SET salary = 9900
WHERE id = 2

Question 142

It is also possible to UPDATE multiple columns at the same time by comma-separating them:

For example:

Answer 142

UPDATE Customers
SET salary = 9900,
city = ‘New York’
WHERE ID = 2

Question 143

Similar to UPDATE, the DELETE command is used to

Answer 143

delete rows from a table

DELETE FROM Customers
WHERE ID = 4

Question 144

The ALTER TABLE command is used to

Answer 144

add, delete, or modify columns in an existing table

For example, let’s add a new column to our Customers table:

ALTER TABLE Customers
ADD age INT

Question 145

we can delete an entire column with

Answer 145

DROP COLUMN

Question 146

The ALTER TABLE command can also be used to rename a column

Answer 146

ALTER TABLE Customers
RENAME city TO location

Question 147

We can rename an existing table using the following

Answer 147

ALTER TABLE Customers
RENAME TO People

Question 148

To delete an entire table, the following command is used

Answer 148

DROP TABLE Customers

Question 149

What is a Relational Database?

Answer 149

Stores data in tables

Question 150

Normalization

Answer 150

The process of building the best database design

Question 151

Keys

Answer 151

How to make everything unique in a table

Question 152

Record, Rows, Tuple

Answer 152

All talking about the rows in a table

Question 153

Columns, Field, Attributes

Answer 153

All talking about the columns in a table

Question 154

Frontend

Answer 154

What the user sees

Question 155

Backend

Answer 155

What’s going on behind the scenes

Question 156

Joins

Answer 156

Connects data from multiple tables

Question 157

Atomic Value

Answer 157

The value stores one thing

Question 158

Entity

Answer 158

This would be like a column header “name”

Question 159

Attributes

Answer 159

This is the stuff that is under the heading, like the actual names themselves; this is the actual information

Question 160

Foreign Key

Answer 160

The child in the parent to child relationship. It is a reference to a primary key.

Question 161

Explain the Child and Parent relationship concept in Database Design

Answer 161

Parent would be something like the user name

Child would be the comments that this user makes.

Children inherit the values from their parents

Question 162

What rules are followed with Keys?

Answer 162

Unique, never changing, never null

Question 163

Superkey

Answer 163

Any number of columns that create unique rows

Question 164

Candidate Key

Answer 164

The least number of columns needed to force every row to be unique

Question 165

Natural Key

Answer 165

Something that you naturally want to store (username and password, for example)

Question 166

Surrogate Key

Answer 166

Giving an ID to every row in every table

Question 167

Simple Key

Answer 167

Key consists of one column

Question 168

Composite Key

Answer 168

Key consists of two or more columns. Most common with natural keys.

Question 169

Compound Key

Answer 169

Key that has multiple columns, and they’re all keys.

Question 170

Entity Relationship Modeling

Answer 170

It’s when you draw out your database before you make it

Question 171

Cardinality

Answer 171

Relationship type between rows (ex: Cardholder and card)

Question 172

Modality

Answer 172

Whether or not the relationship is required. (ex: not null)

Question 173

Normalization

Answer 173

When we go through our database and start correcting things that may case problems (integrity, repeating, etc.)

Question 174

What are the three steps in normalization?

Answer 174

1st Normal Form, 2nd Normal Form, 3rd Normal Form. Can’t do the next one until you’ve done the one before it.

Question 175

What is second normal form?

Answer 175

Deals with a partial dependency. That’s when a column only depends on part of the primary key. In order for that to happen, you need to have a compound or composite key.

Question 176

What is third normal form?

Answer 176

Deals with what is called a transitive dependency. Which is when a column depends on a column that depends upon the primary key.

Question 177

What is an index?

Answer 177

It’s a list of where certain data points are.

Question 178

What is a nonclustered index?

Answer 178

Just a separate reference that points to the data

Question 179

What is a clustered index?

Answer 179

organizes the actual data. You can only have one of these. (like a phonebook)

Question 180

Three main categories of Data Types?

Answer 180

Date, Numeric, and String

Question 181

What is a string?

Answer 181

it is a combination of characters

Question 182

What is numeric in data types?

Answer 182

Numbers, no quotes

Question 183

What is date in data types?

Answer 183

stores a date. Think DATE, TIME, or DATETIME. Usually, colons separate.

Question 184

Join?

Answer 184

Taking something complex in a database and presenting it in a user friendly way.

Question 185

What is an inner join?

Answer 185

Going to take rows that are the same from one table and rows that are the same from another table and join them together to make a new table

Question 186

What is an outer join?

Answer 186

Returning all values; left outer join, right outer join and full outer join

Question 187

What is an alias?

Answer 187

It’s when you rename something to make it easier to understand using the “AS” command

Question 188

What are the four components of a database system?

Answer 188

Users

Database Applications

Database Management System (DBMS)

Database

Question 189

What is a database?

Answer 189

A self-describing collection of related records

Question 190

What is metadata?

Answer 190

Data describing the structure of the data in a database

Question 191

What is a DBMS?

Answer 191

Serves as an intermediary between database applications and the database

Question 192

What is normalization?

Answer 192

It’s a process where we go through our table and try to eliminate the null values

Question 193

What is a functional dependency?

Answer 193

ex: box price and cookie price; they are dependent upon one another

Question 194

What is first normal form?

Answer 194

each cell within the table has one value (atomic)

Question 195

What is second normal form?

Answer 195

Beyond all requirements of the first normal form, it must be fully functionally dependent on the entire primary key. No partial dependencies.

Question 196

What is the most important concept to remember when it comes to normalization?

Answer 196

All your tables should have to do with one business concept.

Question 197

Who originally developed SQL?

Answer 197

IBM

Question 198

SQL is comprised of three components. What are they?

Answer 198

data definition language (DDL), data manipulation language (DML), and data control language (DCL)

Question 199

What is DDL?

Answer 199

used to define and manage database structures

Question 200

What is DML?

Answer 200

data definition and updating, and data retrieval (queries)

Question 201

What is DCL?

Answer 201

used for creating user accounts, managing permissions, etc.

Question 202

what do you end all SQL statements with?

Answer 202

semicolon

Question 203

What does the INSERT INTO statement do?

Answer 203

Will add a new row to a table

Question 204

What does the UPDATE statement do?

Answer 204

Will update the rows in a table which match the specified criteria

Question 205

What does the DELETE FROM statement do?

Answer 205

Will delete the rows in a table which match the specified criteria

Question 206

What does the SELECT statement do?

Answer 206

Extracts information you specify from the database.

Question 207

The DISTINCT keyword may be added to the SELECT statement to

Answer 207

Suppress the display of duplicate rows

Question 208

The WHERE clause

Answer 208

specifies the matching or filtering criteria for the records (rows) that are to be displayed

Question 209

The multiple character wildcard character is a

Answer 209

percent sign

Question 210

The single character wildcard character is

Answer 210

an underscore

Question 211

Queried data can be categorized by what clause?

Answer 211

GROUP BY

Question 212

HAVING

Answer 212

filters the categorized results that you get from the select statement

Question 213

OUTER JOIN

Answer 213

Can be used to obtain data that exists in one table without matching data in the other table

Question 214

What are the three stages of database development?

Answer 214

Requirements analysis stage, component design stage, implementation stage

Question 215

Entity Class

Answer 215

this is like the recipe that someone follows for a cake, while the entity instance is actual cake that comes about as a result

Question 216

What is an attribute?

Answer 216

they describe a specific entity. example are projectName, startDate, projectType, projectDescription

Question 217

What is an identifier or key?

Answer 217

It identifies a specific instance in the entity class, for example, socialSecurityNumver, studentID, employeeID, emailAddress, departmentId

Question 218

Unary Relationship

Answer 218

an entity is related to itself (Marriage)

Question 219

Binary Relationship

Answer 219

one entity is related to another entity (most common) (employee and parking space)

Question 220

ternary relationship

Answer 220

three entities involved in the relationship (doctor, patient, drug; intersection of those would be a prescription)

Question 221

Relationships are named and classified by their cardinalities, which is a word that means

Answer 221

Count (as in the number of items in a set)

Question 222

Minimum cardinality

Answer 222

minimum number of entities that need to participate in a relationship

Question 223

What is concurrency?

Answer 223

People or applications may try to update the same information at the same time

Question 224

What is interdependency?

Answer 224

Changes requested by one user may impact others

Question 225

Atomic Transaction

Answer 225

all of the database actions occur or none of them does. Each step must be successful for the transaction to be saved

Question 226

Dynamic Cursor

Answer 226

All changes made to the rows in the result set are visible when scrolling through the cursor

Question 227

What do processing rights define?

Answer 227

Who is permitted to perform certain actions

When certain actions are allowed to be performed

Question 228

Index

Answer 228

a data structure that contains a copy of some of the data from one or more existing data tables

Question 229

Indexes are created on one or more ______ in a table

Answer 229

columns

Question 230

B-Tree Index

Answer 230

Use pointers and several layers of nodes in order to quickly locate desired data

Question 231

Bitmap Index

Answer 231

A table is created with the values of one attribute along the horizontal axis and the values of another attribute along the vertical axis. Can be really fast.

Question 232

Hashed Index

Answer 232

a hashing algorithm is used in order to convert an input value into a location within an index (such as a B-tree index), which in turn contains or points to the actual data row.

Question 233

What are the two categories of Business Intelligence systems?

Answer 233

Reporting systems

data mining applications