Question 1 There are many different types of database management systems. A few

Question

Question 1

There are many different types of database management systems. A few of the names for these different types are Document Databases, Multi Dimensional Databases, Flat File Databases, and Relational Databases. What type of database is Microsoft SQL Server?

Document Database

Multi Dimensional Database

Flat File Database

Relational Database

Question 2

Relational Databases are great for transactional databases. In this class, we have focused on transactional databases. These databases have the data they store changed often by inserts, updates, and deletes. Following relational database design principles is very important for transactional databases because there can be significant performance problems with a bad design. Select the database design principles you should follow when designing a transactional relational database.

Every table describes only one thing.

Every table can have a row uniquely identified.

There is very little duplication of data within the database.

Every table describes only one thing.

Every table can have a row uniquely identified.

All tables are related to each other.

Every table describes only one thing.

Every table can have a row uniquely identified.

Data can be found in multiple tables.

Every table describes multiple things.

Every table can have a row uniquely identified.

All tables are related to each other.

Question 3

One of the advantages of a relational database over a flat file database, is

Everything can be found in one place.

Data is duplicated to make it easy to find information without joins.

Data is more accurate because instead of duplication, the data is in 1 place and can be linked from other tables.

Multiple tables give you more options.

Question 4

When you write a query joining 2 tables, you can choose from several different types of joins. When you use the keyword join by itself, it is equivalent to another type of join. Select the statement that is equal to this statement.

Select * from tableName1

join tableName2

on tableName1.col1 = tableName2.col4;

Select * from tableName1

outer join tableName2

on tableName1.col1 = tableName2.col4;

Select * from tableName1

inner join tableName2

on tableName1.col1 = tableName2.col4;

Select * from tableName1

left inner join tableName2

on tableName1.col1 = tableName2.col4;

Select * from tableName1

right outer join tableName2

on tableName1.col1 = tableName2.col4;

Question 5

Select all of the valid SQL Server Data Types.

number

varchar(n)

nchar

varbinary

Question 6

When designing a database, you have to choose the data type for all of your columns based on the data you will store in that column. If you have a column which will store the day and time a person ate a meal, what data type is best?

smalldatetime

date

datetime

datetime2

Question 7

Database inserts are important to add data to the database. Select the insert that is correct based on the following database table definition.

create table person(id bigint, first_name varchar(100), last_name varchar(100), street_address varchar(200), city varchar(100), state varchar(50), zip int);

insert into person(id bigint, first_name varchar(100), last_name varchar(100), street_address varchar(200), city varchar(100), state varchar(50), zip int)values(1,'Billy','Meyers','1001 Charleston','Cincinnati','OHIO',451001);

insert into person(id, first_name, last_name, street_address, city, state, zip)

values(1,'Billy','Meyers','1001 Charleston','Cincinnati','OHIO',451001);

insert into person(id, first_name, last_name, street_address, city, state, zip)

values(1,Billy,Meyers,1001 Charleston,Cincinnati,OHIO,451001);

insert into person(id, first_name, last_name, street_address, city, state, zip)

values(1,[Billy],[Meyers],[1001 Charleston],[Cincinnati],[OHIO],451001);

Question 8

What clause of a Select statement is used to limit the number of rows returned in a result set?

only

order by

group by

where

Question 9

We are going to try to find Jackie Joyner-Kersee via the LastName column. You know the name ends with Kersee and the surname is hyphenated. Select the where clause that will search all of the customer rows looking for anything prefixing -Kersee.

WHERE CustomerLastName LIKE '%-Kersee'

WHERE CustomerLastName LIKE '_-Kersee'

WHERE CustomerLastName LIKE '*-Kersee'

WHERE CustomerLastName = '%-Kersee'

Question 10

When you are querying data from a database table(s), you often want to sort the data by one or more columns. Choose the clause you will use in a select statement to order the results by one or more columns.

group by

order by

order

group

Question 11

Choose the statement that will find the records in the customer table that have a value in the first_name column.

select * from customer where first_name is null;

select * from customer where first_name <> null;

select * from customer where first_name like '%not null';

select * from customer where first_name is not null;

Question 12

If you want to relate a table to another table, you create a ___________ to relate one column in the table to another column in the other table.

primary key

unique key

foreign key

composite key

Question 13

If no default database is specified, _____________ is the default database.

master

system

key

northwind

Question 14

If you want to delete a row or rows from a table, customer, the following statement will NOT work.

delete from customer;

delete from customer where first_name = 'Bekah';

delete from customer where first_name in (select first_name from person where last_name = 'Michael');

delete from customer where first_name in (select * from person where last_name = 'Michael');

Question 15

This update statement is written correctly.

update customer set first_name = 'Rebekah' where first_name = 'rebekah';

True

False

Question 16

Choose the statement that will create a table in the database sales_system.

create table person (

id bigint,

first_name varchar(100),

last_name varchar(100),

age smallint);

create table person (

id bigint

first_name varchar(100)

last_name varchar(100)

age smallint);

create person (

id bigint,

first_name varchar(100),

last_name varchar(100),

age smallint);

create table person

id bigint,

first_name varchar(100),

last_name varchar(100),

age smallint

Question 17

If I have the following join statement, logically I want to get what kind of results?

select * from customer

outer join account on

customer.id = account.person;

The customers that don't have an account and the accounts that don't have a customer

The customers that have an account

The accounts that don't have a customer

The customers that don't have an account

Question 18

Relational Databases are very powerful and are used all the time in your daily life. Select all of the applications that use a relational database to store the information you might see everyday.

Banking Applications showing your transactions such as PNC Bank Mobile Applications

Consumer Find Business applications like Yelp

Business Applications such as Order Management software

Directory applications such as your phone directory

Question 19

Write a statement to create a table named Book. There are 5 columns - title, author_first_name, author_last_name, isbn, and cost. Create a primary key and appropriate data types. This must be syntactically correct so I recommend you try it out in SQL Server Management Studio first.

Question 20

According to the book, what is the first step in designing any database?

Create the database in SQL Server.

Identify the objects that need to be represented in the database.

Create the tables.

Identify the tables needed in the database.

Document Database

Multi Dimensional Database

Flat File Database

Relational Database

Explanation / Answer

1)

Since Microsoft SQL server uses relational model , it is a Relational data base .

Hence the correct option is “Relational data base”.

2)

Therefore according to good data base design principles, the following design principle is good to select:

Every table describes only one thing.

Every table can have a row uniquely identified.

All tables are related to each other.

3)

Advantages of relational data base over flat files system:

Relational data base

flat files data base

Data can be shred, because all tables are available in one place and they are related each other.

Same flat files are maintained in different locations.

Data consistency exists

Data updating should be done in all files resides in different places.

More secure

No security assurance

No duplication

Duplication exists

Easy to access and maintain

Hard to structure and access.

No limitation for size

Limited number of records can be stored in a flat file

Hence the correct option is “Data is more accurate because instead of duplication, the data is in 1 place and can be linked from other tables.” , which includes “Everything can be found in one place.” also.

4)

Hence the correct option is “Select * from tableName1 inner join tableName2 on tableName1.col1 = tableName2.col4;”

5)

All data types given except ‘number’, are the valid SQL server data types.

Hence the correct options are “ varchar(n), nchar, varbinary”.

6)

If we want less storage, we can select smalldatetime

If we want high accuracy, we can select datetime2

If we want optimized storage and accuracy, we can select datetime.

But to store the day and time a person ate a meal, smalldatetime is best, regarding storage.

7)

Or

INSERT INTO table-name (col-name,col2-name,col3-name,...) VALUES (value1,value2,value3, value4...);

The given query:

create table person(id bigint, first_name varchar(100), last_name varchar(100), street_address varchar(200), city varchar(100), state varchar(50), zip int);

Hence the correct option is “insert into person(id, first_name, last_name, street_address, city, state, zip)

values(1,'Billy','Meyers','1001 Charleston','Cincinnati','OHIO',451001);”

8)

Hence the correct options are “Where”.

9)

Hence the correct option is “WHERE CustomerLastName LIKE '%-Kersee'”

10)

Hence the correct options are “Order by”.

Relational data base

flat files data base

Data can be shred, because all tables are available in one place and they are related each other.

Same flat files are maintained in different locations.

Data consistency exists

Data updating should be done in all files resides in different places.

More secure

No security assurance

No duplication

Duplication exists

Easy to access and maintain

Hard to structure and access.

No limitation for size

Limited number of records can be stored in a flat file

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.