Chapter 5 - Database Table Design & SQL Data Types

SELECT CAST(vacation_accrual as Float(4,3));

A database stores a value for vacation accrual. A sample value is 1.2533458. Which SQL statement will show a value of 1.253? 1. SELECT CAST(vacation_accrual (4,3)); 2. SELECT CAST(vacation_accrual as Real(0)); 3. SELECT CAST(vacation_accrual as Float(4,3)); 4. SELECT CAST(vacation_accrual as Int(3));

DROP Explanation The DROP statement can be used to drop a table (DROP TABLE Artists); or a constraint (ALTER TABLE Artist DROP CONSTRAINT artist_fk).

In data definition language (DDL), which of the following statements can be used to remove a table or constraint? 1. CREATE 2. DELETE 3. DROP 4. UNDO

Approximate

Real, float, and double are considered _____ numbers 1. Decimal 2. Real 3. Approximate 4. True

They should not be used together Explanation Because SQL does not recognize them as the same, choose one or the other and stick with it!

How should DECIMAL and NUMERIC be used in the same database? 1. Alternatingly 2. Interchangeably 3. On different parent-child levels 4. They should not be used together

Float(5, 4)

Using the float keyword, which statement will return all digits for the value 3.5872? 1. Float(0,5) 2. Float(3,7) 3. Float(5, 4) 4. Float(7,0)

UNIQUE Explanation Unique makes sure that each entry is different from all others.

What SQL constraint would be useful to make sure that an ID number is not issued twice? 1. DEFAULT 2. NOT NULL 3. LENGTH 4. UNIQUE

The column zipCode will be removed. Explanation Not only will the column be removed, but the data with it! Be careful when removing columns; if it is okay to lose the data, go for it!

2. Look at the following code. What will be the result of the query? ALTER TABLE Customers DROP COLUMN zipCode; 1. A copy of the zipCode column will be created. 2. The table will be deleted. 3. The column zipCode will be removed. 4. The column zipCode will be added.

Require the length to be more than 150 Explanation The constraint requires that the length be greater than 150 characters.

3. What best describes what the following code would do in SQL? 1. Require the length to be exactly 150 2. Require the length to be more than 150 3. Require the length to be less than 150 4. Require a numerical answer

Object Explanation You can query a numeric or character-based field; not so much with a BLOB. They are truly objects, and the only thing you can query against is the reference/pointer to that object.

BLOBS are _____ data types 1. Neutral 2. Primitive 3. Object 4. Inherited

Any of these answers is correct Explanation There are a number of reasons that a user may want to specify the size of integer for a given field.

In using Structured Query Language, why might a user want to limit the size of integers? 1. Reduced memory requirements 2. Any of these answers is correct 3. Increased speed 4. Accuracy

DML Explanation Insert, delete, and update are examples of the DML language. DCL and DML include other examples.

Insert, delete, and update are examples of the _____ language. 1. None of the other answers are correct. 2. DML. 3. DCL. 4. DDL.

CREATE Explanation The CREATE TABLE commands creates a new table.

To build a new table from scratch, use the _____ command. 1. ALTER 2. CREATE 3. INSERT 4. DROP

enrollment_date DATE, Explanation The order is column name, data type, then a comma if not the last column in a table.

Assuming it is not the last column in a database, what is the correct formatting for a DATE data type for the column 'enrollment_date'? 1. enrollment_date DATE, 2. DATE enrollment_date, 3. DATE enrollment_date 4. enrollment_date DATE

A database administrator. Explanation DCL is normally used by a database administrator because it deals with privileges, and security. General users and managers do not normally have access to this language.

DCL is normally used by _____. 1. A general user. 2. All of the other answers are correct. 3. A database administrator. 4. A manager.

2 Explanation Only True or False are acceptable values.

How many possible choices can be used in a BOOLEAN data type? 1. 10 2. 1 3. 2 4. Infinite

22.001

If you used the statement Float(5, 3) for the value 22.0009, what would SQL return? 1. 23 2. 22 3. 22.001 4. 22.10

LONGBLOB Explanation MySQL divided these up for performance reasons; the MySQL LONGBLOG equals BLOB in any other database management system.

In MySQL, the _____ can hold 4 gigabytes. 1. BLOB 2. LONGBLOB 3. TINYBLOB 4. MEDIUMBLOB

More than 4 billion Explanation INT holds four bytes, therefore 2^32 values. There are 8 bits in a byte, and each bit is either one or zero (two options). Therefore 2^32 = 4.2 billion.

In Structured Query Language (SQL), about how many values can INT hold? 1. More than 4 billion 2. A quintillion 3. 256 4. More than 2 million

2 Explanation SMALLINT records a range of up to 65,536 values. There are 8 bits in a byte, and each bit is either one or zero (two options). Therefore 2^16 = 65,536.

In Structured Query Language (SQL), how many bytes of information does SMALLINT hold? 1. 3 2. 2 3. 1 4. 4

2^8 Explanation TINYINT only holds one byte of data, or eight bits. There are 8 bits in a byte, and each bit is either one or zero (two options). Therefore 2^8, or 256.

In Structured Query Language (SQL), how many potential values can be held by TINYINT? 1. 2^16 2. 2^32 3. 2^64 4. 2^8

8 Explanation This offers 18 quintillion different possibilities. There are 8 bits in a byte, and each bit is either one or zero (two options). Therefore 2^64 = 18.4 quintillion.

In Structured Query Language, how many bytes does each BIGINT entry permit? 1. 2 2. 3 3. 4 4. 8

Structured Query Language. Explanation SQL stands for Structure Query Language. Standard and question are not discussed in the lesson.

SQL stands for _____. 1. Structured Query Language. 2. Standard Query Language. 3. None of the answers is correct. 4. Structured Question Language

DROP Explanation All data is lost as well with a DROP command! This command should be used only for temporary tables or when first building the structure.

The _____ command is used to completely remove a table from the database. 1. DROP 2. ALTER 3. CREATE 4. INSERT

ALTER Explanation Use ALTER TABLE to modify column(s) in a table. It is also used to rename tables or columns.

The _____ command is used to modify columns in a table 1. CREATE 2. ALTER 3. INSERT 4. DROP

Structured Query Language Explanation The acronym, SQL, stands for Structured Query Language. Sequential Query Language, Structured Quality Language, and Simple Quality Language, are all bogus.

The acronym, SQL, stands for? 1. Sequential Query Language 2. Simple Quality Language 3. Structured Quality Language 4. Structured Query Language

SQL Explanation SQL made DML a common skill.

What common tool made DML common for humans to learn? 1. CSS 2. C 3. SQL 4. HTML

Data Manipulation Language Explanation DML stands for Data Manipulation Language

What does DML stand for? 1. Database Manipulation Language 2. Data Management Language 3. Database Management Language 4. Data Manipulation Language

Precision Explanation It tells how many digits will be in the entire value.

What does the 'p' stand for in a DECIMAL or NUMERIC value? 1. Precision 2. Politics 3. Partial 4. Portable

Scale Explanation This explains how many numbers will follow the decimal.

What does the 's' stand for in a DECIMAL or NUMERIC value? 1. Sequence 2. Size 3. Sort 4. Scale

It is versatile. Explanation With only two answers, BOOLEAN is not versatile.

What is NOT an advantage of using BOOLEAN as a data type? 1. It involves fewer errors. 2. It is versatile. 3. It is faster. 4. It is unambiguous.

HH:MM:SS(.nnnnnnn) Explanation Hours, minutes, and seconds all have two spots, while the optional places for fractions of a second have 7.

What is the correct format for TIME? 1. HH:MM:SS(.nnnnnnn) 2. HH:MM:S 3. nnnnnnn:SS:MM:HH 4. MM:HH:SS

YYYY:MM:DD Explanation A four place year, then the month, then the day. This makes it easier for SQL to sort in chronological order.

What is the proper format for DATE? 1. MM:DD:YYYY 2. MM:DD:YY 3. YY:MM:DD 4. YYYY:MM:DD

Brackets Explanation Brackets are considered the best practice way to set apart constraints.

What often sets apart SQL constraints in the code? 1. Brackets 2. Colons 3. Semi colons 4. Commas

True or false Explanation BOOLEAN is a data type that holds a true or false answer, or essentially a yes or no.

What type of data can BOOLEAN hold? 1. It doesn't hold data 2. True or false 3. A number 4. A 7 letter word

Real

Which data type stores 4 bytes, or 7 digits after the decimal? 1. Double 2. Real 3. Long 4. Float

Result Explanation Result does NOT describe a SQL schema. A result is what you get back when you access a database. A schema is a template, framework, or pattern.

Which of the following does NOT describe a SQL schema? 1. Database Key 2. Pattern 3. Result 4. Template

All of these can be stored in either type of data. Explanation Only the character count matters, not the type of character.

Which of these CANNOT be stored in VARCHAR or CHAR records? 1. Numbers 2. Letters 3. All of these can be stored in either type of data. 4. Spaces

Data itself Explanation The rest are parts of the database itself.

Which of these can be created or destroyed using DML? 1. Data itself 2. Tables 3. Databases 4. Indices

NUMERIC Explanation Only NUMERIC and DECIMAL allow for decimals.

Which of these data types allow for decimals? 1. SMALLINT 2. NUMERIC 3. BIGINT 4. INTEGER

SELECT * FROM Explanation The others are from the command line, HTML and C

Which of these is a command in DML? 1. $ ls 2. < body/> 3. SELECT * FROM 4. 1. include <stdio.h>

book_id CHAR(5) Explanation The class comes first, then the data type, then the limit.

Which of these is a properly formatted CHAR example? 1. drivers_id (10)CHAR 2. CHAR(13) college_code 3. CHAR college_code(13) 4. book_id CHAR(5)

All of the answers are correct. Explanation A schema describes the size, organization, and type of information. In other words, all of the answers are correct.

Which of the following is a characteristic that a schema describes about information? 1. Organization 2. Type 3. All of the answers are correct. 4. Size

Massive amounts of data would crash a spreadsheet Explanation Databases and DML allow that information to be held and accessed only when needed.

Why do we use databases and DML instead of spreadsheets? 1. Massive amounts of data would crash a spreadsheet 2. Laws require it 3. No real reason 4. Easy to use DML

All of the other answers are correct. Explanation MySQL is all of the other answers. It is a RDBMS that is freely available on the Internet.

MySQL is _____. 1. Open Source. 2. A RDBMS. 3. All of the other answers are correct. 4. Free on the Internet.

Data definition language. Explanation DDL stands for data definition language. The other answers are not mentioned in the lesson.

DDL stands for _____. 1. Data definition language. 2. None of the other answers is correct. 3. Data description language. 4. Definition data language.

structure Explanation Data Definition Language (DDL) commands are used to modify the structure, by creating tables, relationships, etc.

Data Definition Language commands are used to modify the _____ of a database. 1. data 2. security 3. structure 4. users

Structure Explanation DDL statements create and modify the structure of the database objects, including tables, indexes, views, and triggers.

Data definition language (DDL) includes statements that modify the _____ of a database. 1. Structure 2. Size 3. Users 4. Data

All of the answers are correct Explanation Apart from creating, altering, and dropping tables and constraints, you can use DDL statements to add/drop triggers (automated processes that occur based on an event, such as a modification to a table), views (a picture of the table presented to users), or indexes (used to create a sort on a column or columns).

Data definition language (DDL) statements also allow you to create: 1. All of the answers are correct 2. Triggers 3. Indexes 4. Views

Database Type, Intended Use Explanation Database schemas depend on database type and intended use. Size and composition are related, but not dependent upon.

Database schemas depend on _____ and _____. 1. Composition, Intended Use 2. None of the answers are correct. 3. Database Type, Intended Use 4. Database Type, Size

ALTER TABLE Artists ADD CONSTRAINT 'artist_pk' PRIMARY KEY (artistID) Explanation You first specify the table, use the ADD CONSTRAINT option with the constraint name, and the field being used for the primary key. Notice that the primary key constraint is named with a 'pk'; this makes it easier to maintain the database and to update future fields/keys.

In data definition language (DDL), which of the following statements can be used to add a primary key to a table? 1. ADD CONSTRAINT 'artist_pk' PRIMARY KEY (artistID) 2. ALTER TABLE Artists PRIMARY KEY (artistID) 3. ALTER TABLE Artists ADD CONSTRAINT 'artist_pk' PRIMARY KEY (artistID) 4. CREATE TABLE Artists ADD 'artist_pk' AS PRIMARY KEY (artistID)

4 gigabytes Explanation This supports multimedia such as videos, images, etc. Remember that it is an object data type: A pointer/reference to the object in the table. You can't query the contents of an image file!

In most database management systems, a BLOB can hold up to _____ 1. 8 bytes 2. 32 megabytes 3. 256 bytes 4. 4 gigabytes

varbinary(MAX); Explanation You can specify how big the BLOB should be by putting a value where the MAX is; but if you're dealing with big files (especially video or audio), keep it at the MAX!

To declare a BLOB in SQL Server, the following statement is used. 1. BLOB = new BLOB(); 2. declare BLOB; 3. var myBlob = new binary BLOB; 4. varbinary(MAX);

P and S Explanation These two tell SQL the size of the value.

What values need to be entered after selecting a data type (either NUMERIC or DECIMAL)? 1. P and Q 2. Q and S 3. P and S 4. N and P

TIMESTAMP must be manually entered like TIME and DATE. Explanation While TIME and DATE are both manually entered, TIMESTAMP is a command and is used to create a record of any sort of change that happens to a record. To do this, simply include TIMESTAMP as you are creating a table

Which of the following is FALSE about TIMESTAMP? 1. TIMESTAMP is a command. 2. TIMESTAMP must be manually entered like TIME and DATE. 3. To create a TIMESTAMP, simply include the word TIMESTAMP as you are creating a table. 4. TIMESTAMP is used to create a record of any sort of change that happens to a record.

CHANGE Explanation CHANGE is NOT a SQL command. UPDATE is the command used to change information.

Which of the following is NOT a SQL command? 1. INSERT 2. CHANGE 3. DELETE 4. CREATE

SELECT Explanation Data definition language (DDL) works with the structure of the database, not the data itself. CREATE, DROP, and ALTER are DDL statements.

Which of the following statements is NOT considered a part of data definition language (DDL)? 1. SELECT 2. ALTER 3. CREATE 4. DROP

first_name VARCHAR(10) Explanation The class comes first, then the data type, then the limit.

Which of these is a properly formatted example of VARCHAR? 1. school_name CHAR(15) 2. first_name CHAR(10) 3. first_name VARCHAR(10) 4. school_code (VARCHAR)6

id CHAR(6) [UNIQUE], Explanation The constraint comes after the data type, and a comma follows.

Which of these is properly formatted, assuming it is not the last column in a table in SQL? 1. id CHAR(6) [UNIQUE] 2. id UNIQUE [CHAR(6)], 3. id UNIQUE [CHAR(6)] 4. id CHAR(6) [UNIQUE],

friend BOOLEAN; Explanation The BOOLEAN data type keyword follows the name of the column. Don't forget the semicolon, if it is the last data type.

Which of these is properly formatted? 1. BOOLEAN friend, 2. BOOLEAN, friend 3. friend, BOOLEAN 4. friend BOOLEAN;

Phone numbers (including area codes) Explanation Phone numbers have the same length, so it is better to store them using CHAR.

Which of these would NOT likely be stored using VARCHAR in SQL? 1. Phone numbers (including area codes) 2. Names 3. Addresses 4. e-mail addresses

College graduate? Explanation Only college graduate is a boolean question - the others require a INTEGER or TEXT answer.

Which of these would be a useful BOOLEAN question? 1. College graduate? 2. Graduation major? 3. Hometown? 4. Graduation year?

NOT NULL Explanation NOT NULL makes sure than an entry exists.

Which of these would be useful in making sure that every SQL record has a name? 1. UNIQUE 2. NOT NULL 3. DEFAULT 4. None of these would work

Names Explanation Names have variable lengths, while the others have standard numbers of characters.

Which of these would likely NOT be stored using CHAR in SQL? 1. Social security numbers 2. Names 3. Phone numbers 4. ID codes

game_time TIME, Explanation The data type comes after the column and a comma should follow.

Which shows how the column 'game_time' should be formatted using TIME, assuming it is not the last column in a database? 1. TIME game_time, 2. game_time TIME, 3. TIME game_time 4. game_time TIME

Video, Audio, Images Explanation Due to their size, use BLOB for large files. They are stored in the database, and you can reference them (just not query them).

Which types of data would be a good candidate for a Binary Large Object (BLOB)? 1. Dates, times 2. Phone numbers, fax numbers 3. Integers, characters, decimal values 4. Video, Audio, Images