Database Technologies Final

False

When adding a column to an existing table, data in that table may be lost.

- Set the datatype of the new column in the table on the "many side" to match the primary key column's datatype of the table on the "one side". - DO NOT set the UNIQUE column property of the new column in the table on the "many side". - Add a foreign key constraint on the new column in the table of the "many side". Have this foreign key reference the primary key column in the table on the "one side".

When adding a one to many relationship to two existing tables...which of the following steps should happen...

DQL

When retrieving data from a database, which category of SQL statement is used?

INSERT

Which SQL statement is used to add a new row of data to a table in a database? (one word)

UPDATE

Which SQL statement is used to change data within existing rows in a database? (one word)

select price from books where title='data';

Which of the following SQL statements is syntactically correct? select price where title='data from books; select title='data' from books where price; select price from books where title='data'; from books select price where title='data';

False

The following is a correlated subquery. select * from BOOKS where AUTHOR_ID=( select AUTHOR_ID from AUTHORS where FIRST_NAME='BOB' and LAST_NAME='SMITH' );

True

These two sql statements equivalent.select * from table_a where col_a in ('A','B');select * from table_a where (col_a='A' or col_a='B');

ALTER TABLE

Which SQL statement is used to change, such as adding a new column to, an existing table in a database? (two words)

CREATE TABLE

Which SQL statement is used to make a new table in a database? (two words) ____

DROP TABLE

Which SQL statement is used to remove a database? (two words)

DELETE

Which SQL statement is used to remove an existing row of a table in a database? (one word)

USE

Which SQL statement will select a default database schema to use? (one word)

- A value is not provided for a field with the property NOT NULL. - A value is provided for a field which is a foreign key, such that it does not exist as a value in any row of the field and table the foreign key refers to. - A value is provided for a primary key field which already exists as a value for that same field in an existing row. - A value is provided for a field for a table which does not have that field. - A value is provided for a UNIQUE field which already exists as a value for that same field in an existing row.

Adding a row of data into table may be disallowed given which of the following...

MAX(Population)

Which aggregate function will return the largest value of a column called "population" in a table? (write just the aggregate function and it's argument...do not write the entire SQL statement)

1

A "scalar" subquery should return "exactly"... x rows of data and x columns

Cross Join

A cartesian product is also called...

select letter from alphabet order by letter desc;

All lower case letters in the english language exist in the "alphabet" table as a char data type. What query will guarantee to output a list of letters in the alphabet sorted from "z" through "a".

YYYY-MM-DD

In MySQL what is the default format for a DATE datatype?

DISTINCT

What syntax should be added to an SQL select statement to only return unique rows for the columns requested? (one word)

- A column called PERSON_ID should be add to the PETS table. - The column PETS.PERSON_ID should have the data type INT. - The column PETS.PERSON_ID should have the "NOT NULL" property set. -The PETS tables should have a foreign key constraint added as such "constraint PETS_FK_OWNED_BY foreign key (PERSON_ID) references PEOPLE.PERSON.ID"

Which of the following must exist to establish a relationship between an entity called PEOPLE with the primary key PERSON_ID (of type INT) and and entity called PETS with the primary key PET_ID (of type INT). A person may own many pets but doesn't have to own any. A pet must be owned by one and only one person. (choose all that apply).

same number of columns, columns of compatible data types

With the UNION SQL statement all data sets must have...

nothing, not, NOT, cannot

For the following SQL statement...select title from books where title like '%not';...which "may" be possible values return assuming they exist as a "title" the "books" table. "nothing" "not" "NOT" "another" "cannot"

"person_id", "first_name" and "last_name"

For the table...people(person_id,first_name,last_name)...which columns will be shown when executing the following SQL statement...SELECT * FROM people;

select NAME, 100 * POPULATION/(select sum(POPULATION) from STATES) as PERCENT_POPULATION from STATES;

Given the following table and data exists... STATES NAME POPULATION NJ 8.9 NY 8.6 PA 12.8 Which of these SQL statements will display each State's Name and it's Population as a percentage of all states included. - select NAME, 100 * POPULATION/count(POPULATION) as PERCENT_POPULATION from STATES; - select NAME, 100 * POPULATION/(select sum(POPULATION) from STATES) as PERCENT_POPULATION from STATES; - select NAME, 100 * POPULATION/(select count(POPULATION) from STATES) as PERCENT_POPULATION from STATES; - select NAME, 100 * POPULATION/sum(POPULATION) as PERCENT_POPULATION from STATES;

select COUNT(*), STATE from PEOPLE group by STATE;

Given this table exists. PEOPLE NAME STATE John NJ Ann NY Bob NJ Which SQL Statement will result in the output below? COUNT(*) STATE 2 NJ 1 NY

SHOW TABLES

In MySQL, which SQL statement will list the tables (in the current default database) available to you? (two words)

Atomicity, Isolation

In a relational database a transaction has which of the following properties?

True

Surrogate (also called Artificial) Keys are frequently given the AUTO_INCREMENT column option?

- Remove the row STUDENT_enrolled_in_CLASS with the STUDENT_ID of "1" and the CLASS_ID of "A". - First, Remove the row STUDENT_enrolled_in_CLASS with the STUDENT_ID of "1" and the CLASS_ID of "A". And then second, Remove the row in CLASSES with the CLASS_ID of "A".

A STUDENTS table exists with the primary key STUDENT_ID. This table has a row with the STUDENT_D of "1". A CLASSES table exists with the primary key CLASS_ID. This table has a row with the CLASS_ID of "A". A STUDENT_enrolled_in_CLASS table exists with the composite primary key STUDENT_ID and CLASS_ID. This table also has two foreign keys for STUDENT_ID which refers to STUDENTS.STUDENT_ID and for CLASS_ID which refers to CLASSES.CLASS_ID. This table has a row with the STUDENT_ID of "1" and the CLASS_ID of "A". Which of the following independent actions are possible without requiring the use of transactions?

15

The Cartesian product including one table with 3 rows and another table with 5 tables will result in a output table of how many rows?