Final Exam

The options to sort ascending and descending order in mongodb are:

1, -1

+----+----------+-----+-----------+-----------+| ID| NAME | AGE | ADDRESS | SALARY |+--+----------+-----+-----------+-------------+| 1 | Ronald | 35 | Columbia | 2000.00 || 2 | Kristy | 25 | St. Louis | 1500.00 || 3 | Danny | 23 | KC | 2000.00 || 4 | Charly | 25 | Columbia | 6500.00 || 5 | Harry | 27 | St. Louis | 8500.00 || 6 | Dale | 22 | St. Louis | 4500.00 || 7 | Maria | 24 | KC | 10000.00 |+----+----------+-----+-----------+------------+ What will be returned by the following query? SELECT NAMEFROM EMPLOYEESWHERE NAME LIKE '[DCA]%';

3 results: Danny, Charly, Dale

+----+----------+-----+-----------+-----------+| ID| NAME | AGE | ADDRESS | SALARY |+--+----------+-----+-----------+-------------+| 1 | Ronald | 35 | Columbia | 2000.00 || 2 | Kristy | 25 | St. Louis | 1500.00 || 3 | Danny | 23 | KC | 2000.00 || 4 | Charly | 25 | Columbia | 6500.00 || 5 | Harry | 27 | St. Louis | 8500.00 || 6 | Dale | 22 | St. Louis | 4500.00 || 7 | Maria | 24 | KC | 10000.00 |+----+----------+-----+-----------+------------+ How many results will be returned by the following query? SELECT NAMEFROM EMPLOYEESWHERE NAME LIKE '[!M-Z]%';

5

Which of the following is true about a FOREIGN KEY?

A FOREIGN KEY is a key used to link two tables together.

Candidate Key

A minimal set of attributes which uniquely identifies a record in a database table.

Foreign Key

A set of attributes in one table that uniquely identifies a row of another table or the same table. This key is defined in a second table, but it refers to the primary key or a unique key in the first table.

Consistent

A transaction will leave the database in a consistent state

In SQL, what is the default ordering of records using the ORDER BY clause?

ASC (ascending order)

Which SQL operator is used to search for a specified string pattern in a column?

LIKE

Which of the following is used in MySQL to indicate that only a certain number of records (rows) should be returned from a query?

LIMIT

Complete the following statement. Views in SQL make queries _______

Less Complex

Which SQL function returns the current DATETIME?

NOW()

What basic data types does JSON support?

Number, String, Boolean, Array, Object, null

Which of the following is used to sort the result set of a SQL query?

ORDER BY

Primary Key

One key that is chosen from among the candidate keys.

With SQL, how do you select all the records from a table named "Contracts" where the "amount" is greater than 150000 and the "contractLength" is less than 3?

SELECT * FROM Contracts WHERE amount > 150000 AND contractLength < 3

With SQL, how do you select all the records from a table named "Countries" where the "name" is alphabetically between (and including) "Canada" and "Japan"?

SELECT * FROM Countries WHERE name BETWEEN 'Canada' AND 'Japan'

The table "Posts" contains a column called "content" that holds a string that is the title of a social media post. With SQL, how do you select all the records from the table "Posts" where the content contains within it the string "murder"?

SELECT * FROM Posts WHERE content LIKE '%murder%'

With SQL, how can you return all the records from a table named "tasks" sorted in descending order by "addDate"?

SELECT * FROM tasks ORDER BY addDate DESC

With SQL, how can you return the number of records in the "Students" table?

SELECT COUNT(*) FROM Students

Which SQL statement is used to return only different values so no duplicates are returned? Group of answer choices

SELECT DISTINCT

Given a mongodb salesreps collection with the following fields: _idfirstNamelastNamesalesofficeemailmanager Select the equivalent SQL query for the mongodb query db.salesreps.find({"sales": {$gte: 500}}, {"_id" : 1, "firstName" : 1, "lastName" : 1})

SELECT _id, firstName, lastName FROM salesreps WHERE sales >= 500

Given the following employee table: CREATE TABLE employees (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255),gender ENUM('m', 'f'),salary FLOAT); Which SQL statement returns the average salary by each gender?

SELECT gender, AVG(salary) FROM employees GROUP BY gender;

Given the following table: CREATE TABLE employees (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255),gender ENUM('m', 'f'),ethnicity VARCHAR(64),salary FLOAT); Which SQL statement returns the following table that provides average salary for each combination of gender and ethnicity as shown in the following table? genderethnicityAVG(salary)masian60000mblack112000mwhite78000fblack90000fwhite85000

SELECT gender, ethnicity, AVG(salary) FROM employees GROUP BY gender, ethnicity;

Consider the following relations: customer(id, firstName, lastName, address, email)orders(id, customerID, orderDate, shipDate) Which SQL statement retrieves orders for the customer with a customer id of 1205 where the records only contain the order id, orderDate, shipDate, customer firstName, customer lastName, and customer email and the records are ordered by orderDate ascending? Group of answer choices

SELECT orders.id, orders.orderDate, orders.shipDate, customer.firstName, customer.lastName, customer.email FROM orders INNER JOIN customer ON orders.customerID = customer.id WHERE orders.customerID = 1205 ORDER BY orders.orderDate ASC;

Superkey

Set of attributes that uniquely identify a record. Many may exist and may not be comprised of a minimal set of attributes.

Update Anomaly

Storing the same information expressed on multiple rows can lead to inconsistencies. This exists when one or more instances of duplicated data is modified, but not all.

Match the MySQL terms on the left to the related MongoDB terms on the right.

Table = Collection Row = Document Database = Database Column = Field

Which SQL statement is used to add, modify or drop columns in a database table?

The ALTER TABLE statement.

Durable

The results of the transaction are permanent

What is the result of the following update operation in mongodb? db.inventory.update({ _id: 14 },{$inc: { inStock: 9 }})

The update increments the inStock field by 9 for the document with _id 14

Atomic

Transactions are "all or nothing"

Isolation

Transactions execute as if they had occurred in some serial order isolated from each other

A primary key may not contain NULLS.

True

Based on the following Employees Table. The following queries are equivalent. They will return the same results. +----+----------+-----+-----------+-----------+| ID| NAME | AGE | ADDRESS | SALARY |+--+----------+-----+-----------+-------------+| 1 | Ronald | 35 | Columbia | 2000.00 || 2 | Kristy | 25 | St. Louis | 1500.00 || 3 | Danny | 23 | KC | 2000.00 || 4 | Charly | 25 | Columbia | 6500.00 || 5 | Harry | 27 | St. Louis | 8500.00 || 6 | Dale | 22 | St. Louis | 4500.00 || 7 | Maria | 24 | KC | 10000.00 |+----+----------+-----+-----------+------------+ a) SELECT *FROM EMPLOYEESWHERE ID IN (SELECT IDFROM EMPLOYEESWHERE SALARY < 7000) ; b) SELECT *FROM EMPLOYEESWHERE WHERE SALARY < 7000;

True

Given a mongodb collection called pets the following two queries are equivalent, i.e. they return the same results. db.pets.find() db.pets.find({},{})

True

In the following query the customers table is the left table. True or False? SELECT c.customerNumber, c.customerName, e.employeeNumber, e.firstName, e.lastNameFROM customers cJOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber;

True

JOIN and INNER JOIN have the same effect on an SQL query? Group of answer choices

True

Normalization reduces data duplication.

True

One of the reasons Views are helpful in an SQL database is because the enhance its security.

True

Suppose we have a mongodb database of exams and we want to find out how many students had passing grades in exam1. Passing grades are 70 and above. The following mongdb queries are equivalent queries for returning the number of students who passed exam1? They return the same number? db.exam1.find({"score": {$gte: 70}}).count() db.exam1.aggregate([{$match: {score: {$gte: 70}}},{$count: "passingStudents"},])

True

The "_" wildcard operator is used to represent any character? True or False

True

The HAVING keyword in SQL is used with aggregate functions because the WHERE clause will not work with aggregate functions.

True

The database schema of a database system is its structure described in a formal language supported by the database management system (DBMS).

True

The following query in MongoDB will delete the entire document where "Jane Smith" is the name. db.students.remove({'name':'Jane Smith'})

True

The ultimate goal of normalization is to minify modification anomalies.

True

To perform SQL queries that provides summary information (averages, counts, totals, etc.) based on data that belongs together in some grouping, the GROUP BY clause is used. Group of answer choices

True

You need to change a value in a record in a database. Which SQL statement is used to make a change to a record?

UPDATE

Deletion Anomaly

When the deletion of unwanted information causes wanted information to get deleted as well. This exists when certain attributes are lost because of the deletion of other attributes.

Which type of query should you use if you wanted to find the unique category values in a collection?

db.collection.distinct

Choose the equivalent mongodb query for the following MySQL query SELECT id, salesRepNameFROM salesWHERE category = "home" AND amount >350

db.sales.find({"category": "home", "amount": {$gt: 350}}, {"_id" : 1, "salesRepName" : 1})

A database schema contains the follow relation:Countries(id, name, population, gdp) Which attribute forms the primary key?

id

The SQL wildcards are ____ and ____ .

percent sign (%); underscore (_)

$count

returns the number of documents

$add

totals values in different fields

$sum

totals values in the same field

Which of the following is used to represent an object in JSON?

{ }

The $set function can be used in the db.collection.aggregate and db.collection.update pipelines

False

The NOT NULL constraint enforces a column to accept null values.

False

The OR operator displays a record if ALL conditions listed are true. The AND operator displays a record if ANY of the conditions listed are true.

False

The following query returns the total sales for Truman Tiger db.sales.aggregate([{$match: {salesRepName: "Truman Tiger"}},{$add: {totalSales: "$amount"}},])

False

Which SQL statement is used to add a new record to a table in a database?

INSERT INTO

With SQL, how can you insert 0.25 as the "precipitation" in the "Weather" table?

INSERT INTO Weather (precipitation) VALUES (0.25)

+----+----------+-----+-----------+-----------+| ID| NAME | AGE | ADDRESS | SALARY |+--+----------+-----+-----------+-------------+| 1 | Ronald | 35 | Columbia | 2000.00 || 2 | Kristy | 25 | St. Louis | 1500.00 || 3 | Danny | 23 | KC | 2000.00 || 4 | Charly | 25 | Columbia | 6500.00 || 5 | Harry | 27 | St. Louis | 8500.00 || 6 | Dale | 22 | St. Louis | 4500.00 || 7 | Maria | 24 | KC | 10000.00 |+----+----------+-----+-----------+------------+ Based on the table the following query will? UPDATE EMPLOYEESSET SALARY = SALARY * 1.20WHERE SALARY IN (SELECT SALARY FROM EMPLOYEESWHERE SALARY < 4000 );

Increase salaries less than 4000 by 20%

In a mongodb collection called crime the following query will return values from the year field only. db.crime.find({}{"year":1})

False

In the following MongoDB query if there are no "Male" present in the collection the query will return 0 results? db.student.find({$and:[{"gender":"Male"}, {"age":{ $gte: 21 }}.pretty();

False

Which SQL statement is used to delete records from a database?

DELETE

In the following query if there is a customer who has not been assigned to any employees the customer's customerNumber and customerName will appear in the results with NULL values for e.employeeNumber, e.firstName, e.lastName SELECT c.customerNumber, c.customerName, e.employeeNumber, e.firstName, e.lastNameFROM customers cRIGHT JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber;

False

In MongoDB the following is called a? {id:xyz123name: "John Smith"email:"[email protected]"}

Document

A View in SQL is a physical table just like the other tables in the database?

False

Sub queries cannot be used in INSERT and DELETE queries? Group of answer choices

False

A foreign key is the column or set of columns used to uniquely identify the items in a table. A primary key is used to uniquely identify the items in a different table, allowing join operations to happen. Group of answer choices

False

For the ACID properties of transactions, the "I" stands for idempotency- that is, the multiple executions of the same transaction should result in the same correct effect.

False

ACID refers to which of the following with regard to database management systems?

Atomicity, Consistency, Isolation, and Durability

Insert Anomaly

Certain information cannot be recorded in the database due to incomplete data.