MIS 315 Final Exam

DDL statements are used to

create and modify the structure of the database

Examples of DDL commands

CREATE, ALTER, DROP

DML statements are used to

manipulate the data within the database

DML statements include

INSERT INTO, UPDATE, DELETE, SELECT

DML statements also include the following command for data retrieval

SELECT

What would be used to populate a table with data

INSERT INTO

What statement would be used to change a value in a record of a table

UPDATE

Observe the table STUDENT:

STUDENT

StudentID           SSN                            Name              Class
D111                 111-11-1111           Connor          Freshman
D222                 222-22-2222           Trevor           Freshman
D333                 333-33-3333           Connor          Sophomore
D444                 444-44-4444           Parker           Sophomore
D555                 555-55-5555           Connor          Sophomore

 

Which of the following queries on table STUDENT will return three values? 

SELECT DISTINCT name FROM student;

Observe the table STUDENT:

STUDENT

StudentID           SSN                             Name              Class

D111                 111-11-1111           Connor          Freshman
D222                 222-22-2222           Trevor           Freshman
D333                 333-33-3333           Connor         Sophomore
D444                 444-44-4444           Parker           Sophomore
D555                 555-55-5555           Connor         Sophomore

 

How many records will the following query on table STUDENT return? 

SELECT * 
FROM student 
WHERE name LIKE '%or%';

4

Observe the table STUDENT:

 

STUDENT

StudentID           SSN                            Name              Class
D111                 111-11-1111           Connor         Freshman
D222                 222-22-2222           Trevor           Freshman
D333                 333-33-3333           Connor         Sophomore
D444                 444-44-4444           Parker           Sophomore
D555                 555-55-5555           Connor          Sophomore

 

What will be the result of the following query on table STUDENT?

SELECT COUNT( *) 
FROM student 
GROUP BY class 
HAVING COUNT(*) <3;

There will be two groups of classes

Observe the DOCTORS AND SPECIALTIES database:

DOCTOR

DocID	DocName	NoOfPatients	SpecID
111	Jill	20	SUR
222	Linda	20	SUR
333	Lisa	30	RAD
444	Sue	15	ANE
555	Lola	15

 

SPECIALTY

SpecID	SpecName
SUR	Surgery
RAD	Radiology
ANE	Anesthesiology

 

How many rows will the following query return?

SELECT * 
FROM doctor JOIN specialty
  ON doctor.specid = specialty.specid;

4

Explain this code

SELECT * 
FROM doctor JOIN specialty
  ON doctor.specid = specialty.specid;

The join operation produces a table that combines rows from both tables where SPECID values match the DOCTOR values

Observe the DOCTORS AND SPECIALTIES database:

DOCTOR

DocID	DocName	NoOfPatients	SpecID
111	Jill	20	SUR
222	Linda	20	SUR
333	Lisa	30	RAD
444	Sue	15	ANE
555	Lola	15

 

SPECIALTY

SpecID	SpecName
SUR	Surgery
RAD	Radiology
ANE	Anesthesiology

How many rows will the following query return?

SELECT *
FROM doctor LEFT JOIN specialty
  ON doctor.specid = specialty.specid;

5

Explain this code

SELECT *
FROM doctor LEFT JOIN specialty
  ON doctor.specid = specialty.specid;

The LEFT JOIN statement ensures that all rows from the DOCTOR table are included in the result regardless of whether there is a match to the SPECIALTY table

Assume that Table B has a foreign key referring to a primary key in Table A.  Which of the following options may result in the following: deleting a row in Table A will cause a deletion of a row in Table B?

DELETE CASCADE

Observe the HOMETOWN REALESTATE database:

SELLER

SellerID	SellerName	RealtorID
S111	Paul	R1
S222	Carly	R1
S333	Ana	R2
S444	Charlotte	R2
S555	Aidan	R3
C666	Eric	R1
C777	Christina	R1

 

REALTOR

RealtorID	RealtorName
R1	Claire
R2	Kate
R3	Cliff
R4	William

 

Suppose a DBMS enforces a DELETE RESTRICT option on the database's referential integrity constraint between SELLER and REALTOR. What will be the outcome after a user tries to delete the first record (R1, Claire) from REALTOR?

SELLER will have 7 records, REALTOR will have 4 records

Delete restrict

Does not allow a record to be deleted if its primary key value is referred to by a foreign key

Observe the HOMETOWN REALESTATE database:

 

SELLER

SellerID	SellerName	RealtorID
S111	Paul	R1
S222	Carly	R1
S333	Ana	R2
S444	Charlotte	R2
S555	Aidan	R3
C666	Eric	R1
C777	Christina	R1

 

REALTOR

RealtorID	RealtorName
R1	Claire
R2	Kate
R3	Cliff
R4	William

 

Suppose a DBMS enforces an UPDATE SET-TO-NULL option on the database's referential integrity constraint between SELLER and REALTOR. What will be the outcome after a user tries to update the RealtorID value in the table REALTOR from R3 to R5?

Value R5 appears once in column RealtorID in REALTOR, and value R5 does not appear in column RealtorID in table SELLER