Database - Mastery Exam (1-3)

What does the following statement return? SELECT DATEDIFF('2020-11-04', '2020-11-09');

-5

Evaluate the SQL statement and data below. What is correct result? SELECT SUM(Cost) + SUM(Markup) FROM Profit;

485

A view table provides which benefit when created in a database with multiple tables?

A consolidated view of specific data without changing the underlying database structure

What links a host programming language to a database system?

API

In the following ER diagram, what does 'AlbumTitle' represent?

Attribute

The following tables describe parts and companies that supply parts: CREATE TABLE Part ( PartId SMALLINT, PartName VARCHAR(30), PartSupplierCode CHAR(3), PRIMARY KEY (PartID), FOREIGN KEY (PartSupplierCode) REFERENCES Supplier (SupplierCode) ); CREATE TABLE Supplier ( SupplierCode CHAR(3), SupplierName VARCHAR(30), PostalCode CHAR(5), PRIMARY KEY (SupplierCode) ); The following query selects all possible combinations of part names and supplier names. What XXX completes the query?

CROSS JOIN Supplier

A __________ constraint specifies a column value that is used when no value is provided in an INSERT statement

DEFAULT

ShipPartCode is a foreign key in the Shipment table. ShipPartCode refers to the primary key PartCode of the Part table. What replaces XXX in the following statement? CREATE TABLE Shipment ( ShipNumber INT UNSIGNED, ShipPartCode CHAR(3) NOT NULL, Quantity SMALLINT UNSIGNED, PRIMARY KEY (ShipNumber), XXX );

FOREIGN KEY (ShipPartCode) REFERENCES Part (PartCode)

In the SQL query below, the Teacher table is the _____ SELECT LastName, FirstName, CourseTitle FROM Teacher INNER JOIN Class ON Teacher.TeacherID = Class.TeacherID;

Left table

Which concept relates to software that reads and writes data in a database?

Management system

NONE

NONE

In MySQL, what is the result of TRUE AND NULL?

NULL

Which type of database system is optimized for big data?

NoSQL

Refer to the Product table. Which products are selected by the query below? SELECT * FROM Product WHERE Quantity > 5 AND UnitPrice <= 15.00;

Onesies set, Pajama set, Shorts set

Which principle defines data independence?

Physical design never affects query results

Complete the ORDER BY clause to sort the Products by ProductName alphabetically, then in decreasing Quantity SELECT ProductName, Size, Quantity, SupplierId FROM Product ORDER BY _______;

ProductName, Quantity DESC

Which role focuses on creating software that interacts with a database?

Programmer

What does a user that interacts with a database use to read and write data with?

Query language

A database designer installs MySQL Community Edition to create a database. Which account does the designer use to gain full control of MySQL?

Root

A database administrator uses which two SQL statements to view and then modify existing customer balances with a late fee?

SELECT, UPDATE

What design consideration would apply to a database that has special performance requirements?

Structuring data properly on storage media

What is the result of a relational operation?

Table

An Inventory view table is defined with a WHERE clause that specifies ShelfNumber = 25. An INSERT adds a new item to ShelfNumber 20. What happens when the WITH CHECK OPTION is not specified in the CREATE VIEW statement?

The item appears in the base but not in the view table

SQLAlchemy is a library that facilitates the communication between Python programs and databases. Most of the times, the library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements

True

What should be added so NULL values are not allowed in ProductName? CREATE TABLE Product ( ProductID INT, ProductName _______, UnitPrice DECIMAL(5,2), SupplierId INT );

VARCHAR(50) NOT NULL

In a relational model, a column is _______

a name and a data type

A/An ______ is a rule enforced on a table's data

constraint

A database management system reads and writes data in a database, and _____

ensures consistency and availability

In the relational algebra, compatible tables have the same _______

number of columns and data types

A value that is used in a computation is known as a/an _____

operand

A column, or group of columns, that serves as the unique identifier in a relational identifier in a relational database table is called a/an ________

primary key

A ______ is a collection of values with no inherent order

set