MIST 4610 Final Exam

True

A full join is not directly supported by MySQL. To be able to execute such a join you must use a union of left and right joins a. True b. False

True

A recursive relationship denotes a relationship of an entity with itself and needs a label. a. True b. False

False

A table has 1000 rows. However, it does not have a primary key. Are all the rows assured to be uniquely identifiable ? a. True b. False

False

ALTER TABLE Offices MODIFY office_city VARCHAR(45); From this SQL code, it can be inferred that... [Answer (T)RUE or (F)ALSE]: The name of a column in the Offices table is being changed a. True b. False

True

An entity is something in the organizational environment we want to keep track of. a. True b. False

False

Attributes are the unique identifier used to distinguish one instance of an entity from another. a. True b. False

False

Based on the above SQL code, a procedure is being created with three inputs of different data types. a. True b. False

True

Based on the above diagram, a flight can have multiple codeshare flights. a. True b. False

False

Based on the above diagram, a flight can have no flight bookings. a. True b. False

False

Based on the above diagram, a flyer can take the same flight multiple times. a. True b. False

a. SELECT employeeFName, employeeLname, projectName, payRate FROM Employee, Tasks WHERE Employee.employeeID = Tasks.employeeID AND payRate < (SELECT AVG(payRate) FROM Tasks);

Based on the above model, which query lists out the employee name, the project name and the pay rate if the pay rate of the employee is less than the average pay rate of all tasks? a. SELECT employeeFName, employeeLname, projectName, payRate FROM Employee, Tasks WHERE Employee.employeeID = Tasks.employeeID AND payRate < (SELECT AVG(payRate) FROM Tasks); b. SELECT employeeFName, employeeLname, projectName, payRate FROM Employee, Tasks WHERE payRate < AVG(payRate); c. SELECT employeeFName, employeeLname, projectName, payRate FROM Employee, Tasks WHERE Employee.departmentID = Tasks.employeeID AND payRate < (SELECT AVG(payRate) FROM Tasks); d. SELECT employeeFName, employeeLname, projectName, payRate FROM Employee, Tasks WHERE Employee.employeeID = Tasks.employeeID AND payRate < AVG(payRate);

a. SELECT departmentName, employeeFName FROM Department JOIN Employees ON Department.departmentID = Employees.departmentID;

Based on the above model, which query lists the department name and the first name of employees in each department? a. SELECT departmentName, employeeFName FROM Department JOIN Employees ON Department.departmentID = Employees.departmentID; b. SELECT departmentName, employeeFName FROM Department FROM Employees ON Department.departmentID = Employees.departmentID; c. SELECT departmentName, employeeFName FROM Department JOIN Employees ON Department.departmentID = Employees.employeeID; d. SELECT departmentName, employeeFName FROM Department JOIN Employees;

d. SELECT departmentName, employeeFName, employeeLname FROM Employee JOIN Department ON Employee.departmentID = Department.departmentID WHERE employeeLname REGEXP '^W';

Based on the above model, which query lists the names of the department and the names of employees whose last name starts with a W? a. SELECT departmentName, employeeFName, employeeLname FROM Employee JOIN Department ON Employee.employeeID = Department.departmentID WHERE employeeLname REGEXP '^W'; b. SELECT departmentName, employeeFName, employeeLname FROM Employee JOIN Department ON Employee.departmentID = Department.departmentID WHERE employeeLname = '^W'; c. SELECT departmentName, employeeFName, employeeLname FROM Employee JOIN Department WHERE employeeLname REGEXP '^W'; d. SELECT departmentName, employeeFName, employeeLname FROM Employee JOIN Department ON Employee.departmentID = Department.departmentID WHERE employeeLname REGEXP '^W';

c. SELECT SUM(hours) FROM Tasks;

Based on the above model, which query provides the total number of hours assigned for all projects? a. SELECT AVG(hours) FROM Tasks; b. SELECT SUM(hours*payRate) FROM Tasks; c. SELECT SUM(hours) FROM Tasks; d. SELECT COUNT(hours) FROM Employees, Tasks WHERE Employee.employeeID = Tasks.employeeID;

d. SELECT busNumber FROM Buses WHERE mileage > 50000 AND capacity < 30;

Based on the data model below, pick the right answer to the following question Which query returns the bus number for buses that have a mileage greater than 50000 and a capacity of less than 30? a. FROM Buses SELECT busNumber WHERE mileage > 50000 AND capacity < 30; b. SELECT busNumber FROM Buses WHERE mileage < 50000 AND capacity > 30; c. SELECT busNumber FROM Buses WHERE mileage < 50000 OR capacity > 30; d. SELECT busNumber FROM Buses WHERE mileage > 50000 AND capacity < 30;

a. There is no column called "StudentAge"

Based on the data model below, pick the right answer to the following question: A user wrote the following query: SELECT * FROM Students WHERE StudentAge > 20 Why did they get an error? a. There is no column called "StudentAge" b. There is no row called "StudentAge" c. They should not have gotten an error d. There is no StudentID called "StudentAge"

False

Based on the data model below, pick the right answer to the following question: Is Birthday a Primary Key in this table? a. True b. False

d. SELECT * FROM Students WHERE Age > 18

Based on the data model below, pick the right answer to the following question: Which of the following queries will give all the details of the students with age greater than 18? a. SELECT * FROM Students WHERE Age <>= 18 b. SELECT * FROM Students WHERE Age GREATER THAN 18 c. SELECT * FROM Students WHERE Age => 18 d. SELECT * FROM Students WHERE Age > 18

a. 2

Based on the following data model How many foreign keys are in the ProjectComposition table? a. 2 b. 1 c. 3 d. 0

c. A combination of projectID and idEmployee

Based on the following data model What is the primary key of the ProjectTasks entity? a. idEmployee b. A combination of projectID, idEmployee, and hours c. A combination of projectID and idEmployee d. projectID

c. SELECT boss.jobTitle FROM Employees as boss JOIN Employees as worker ON boss.employeeNumber = worker.reportsTo WHERE worker.lastName = "Whitton" AND worker.firstName = "Andrew";

Based on the following data model What is the query lists the the job title of Andrew's boss (Andrew's last name is Whitton, by the way). a. SELECT boss.jobTitle FROM Employees as boss JOIN Employees as worker ON worker.employeeNumber = boss.reportsTo WHERE worker.lastName = "Whitton" AND worker.firstName = "Andrew"; b. SELECT boss.jobTitle FROM Employees as boss JOIN Employees as worker ON worker.employeeNumber = boss.reportsTo WHERE boss.lastName = "Whitton" AND boss.firstName = "Andrew"; c. SELECT boss.jobTitle FROM Employees as boss JOIN Employees as worker ON boss.employeeNumber = worker.reportsTo WHERE worker.lastName = "Whitton" AND worker.firstName = "Andrew"; d. SELECT boss.jobTitle FROM Employees as boss JOIN Employees as worker ON boss.employeeNumber = worker.reportsTo WHERE boss.lastName = "Whitton" AND boss.firstName = "Andrew";

c. SELECT prereq.course_name FROM COURSE AS course JOIN COURSE AS prereq ON course.prereq_id = prereq.course_id WHERE course.course_name = "Body Conditioning";

Based on the following data model What is the query that identifies the name of the course that is a pre-requisite course for "Body Conditioning"? You can assume prereq_id is the FK referencing course_id. a. SELECT prereq.course_name FROM COURSE AS course JOIN COURSE AS prereq ON prereq.prereq_id = course.course_id WHERE prereq.course_name = "Body Conditioning"; b. SELECT course.course_name FROM COURSE AS course JOIN COURSE AS prereq ON prereq.prereq_id = course.course_id WHERE course.course_name = "Body Conditioning"; c. SELECT prereq.course_name FROM COURSE AS course JOIN COURSE AS prereq ON course.prereq_id = prereq.course_id WHERE course.course_name = "Body Conditioning"; d. SELECT prereq.course_name FROM COURSE AS course JOIN COURSE AS prereq ON course.prereq_id = prereq.course_id WHERE prereq.course_name = "Body Conditioning";

b. select comp.proddesc, prod.proddesc from product as prod join assembly on prod.prodid = assembly.prodid join product as comp on assembly.subprodid = comp.prodid;

Based on the following data model What is the query that lists the description of the subproduct as well as the description of all the products that subproduct is a part of? a. select proddesc, subprodid from product; b. select comp.proddesc, prod.proddesc from product as prod join assembly on prod.prodid = assembly.prodid join product as comp on assembly.subprodid = comp.prodid; c. select comp.proddesc, prod.proddesc from product as prod join assembly on assembly.subprodid = prod.prodid join product as comp on comp.prodid = assembly.prodid; d. select proddesc, subprodid from product join assembly on product.prodid = assembly.prodid;

c. SELECT projectName, COUNT(idEmployee) FROM Project JOIN ProjectComposition ON Project.projectID = ProjectComposition.projectID GROUP BY projectName;

Based on the following data model What is the query that lists the project name and the number of employees on the project? a. SELECT projectName, COUNT(ProjectComposition.idEmployee) FROM Employee JOIN ProjectComposition ON ProjectComposition.idEmployee = Employee.idEmployee GROUP BY projectName; b. SELECT projectName, SUM(ProjectComposition.idEmployee) FROM Employee JOIN ProjectComposition ON ProjectComposition.idEmployee = Employee.idEmployee GROUP BY projectName; c. SELECT projectName, COUNT(idEmployee) FROM Project JOIN ProjectComposition ON Project.projectID = ProjectComposition.projectID GROUP BY projectName; d. SELECT projectName, SUM(idEmployee) FROM Project JOIN ProjectComposition ON Project.projectID = ProjectComposition.projectID GROUP BY projectName;

True

Stored routines, such as functions and procedures, are a set of SQL statements stored in a server (instead of someone's personal computer) which can be used or called for in queries a. True b. False

True

Subordinate entities are helpful when the more abstract entity (person) has different instances of it (for example, professor and student) with different attributes (for example, salary and graduating year) a. True b. False

True

The EXISTS clause checks if a certain condition is TRUE or not. If TRUE, the query should return at least 1 row of data. a. True b. False

True

The associative entity created as part of the M-M relationship may have its own primary key. a. True b. False

True

The many to many recursive relationship requires the creation of a new entity with multiple columns for the foreign key. a. True b. False

True

The rule for when you have a M-M relationship is to create a third entity (also called a weak entity) to keep track of the interaction between the two entities. a. True b. False

True

To delete a set of records from a table one should use DELETE FROM tableName WHERE specifiedRecordsToDelete; whereas to get rid of all records from a table one can simply use TRUNCATE tableName; a. True b. False

True

True or False: A foreign key must always reference a primary key in another table.

True

True or False: A relational database uses tables to organize data into rows and columns.

False

True or False: A subquery can only return a single value.

False

True or False: A table in a relational database can have multiple primary keys.

True

True or False: A view can include data from multiple tables.

True

True or False: In SQL, the GROUP BY clause is used to group rows that have the same values in specified columns.

True

True or False: In Tableau, a dimension is a categorical variable used to slice data.

True

True or False: In Tableau, a measure is typically a numeric variable that can be aggregated.

False

True or False: In a one-to-many relationship, the 'one' side typically has the foreign key.

True

True or False: Knowing your audience is critical when creating data visualizations.

True

True or False: Tableau is used for data visualization and business intelligence.

True

True or False: The following query calculates the average number of orders per customer. SELECT AVG(OrderCount) AS AvgOrdersPerCustomer FROM ( SELECT COUNT(OrderID) AS OrderCount FROM Orders GROUP BY CustomerID) Subquery;

True

True or False: The following query ensures all customers with no orders are included in the result. SELECT c.Name, COUNT(o.OrderID) AS OrderCount FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.Name;

True

True or False: The following query retrieves all customers who have placed more than 5 orders. SELECT c.CustomerID, c.Name FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.Name HAVING COUNT(o.OrderID) > 5;

True

True or False: The following query retrieves the second highest order total. SELECT MAX(TotalAmount) AS SecondHighest FROM Orders WHERE TotalAmount < (SELECT MAX(TotalAmount) FROM Orders);

True

True or false? Pie charts are often less effective than bar charts for comparison.

B. Structured Query Language

What does SQL stand for? · A. Simple Query Language · B. Structured Query Language · C. Sequential Query Language · D. Systematic Query Language

A. Retrieves all customers, sorted by the number of orders they have placed, in descending order.

What does the following query do? SELECT c.CustomerID, c.Name, COUNT(o.OrderID) AS OrderCount FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.Name ORDER BY OrderCount DESC; · A. Retrieves all customers, sorted by the number of orders they have placed, in descending order. · B. Retrieves customers who have placed orders, sorted alphabetically by name. · C. Retrieves only customers who have not placed any orders. · D. Retrieves all customers, sorted by the total amount they spent.

A. Retrieves customers and their total spending in 2023, sorted by spending in descending order.

What does the following query do? SELECT c.Name, SUM(o.TotalAmount) AS TotalSpent FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderDate >= '2023-01-01' GROUP BY c.Name ORDER BY TotalSpent DESC; · A. Retrieves customers and their total spending in 2023, sorted by spending in descending order. · B. Retrieves customers and their order counts in 2023, sorted by the total amount spent. · C. Retrieves all orders in 2023, sorted by customer name. · D. Retrieves customers and the number of orders they placed in 2023.

B. A method to combine rows from two or more tables based on a related column

What is a JOIN operation in SQL? · A. A command to add new rows to a table · B. A method to combine rows from two or more tables based on a related column · C. A function to aggregate data in a table · D. A way to remove duplicates in a table

B. A sheet containing visualizations like charts and graphs

What is a Tableau worksheet? · A. A single dashboard view · B. A sheet containing visualizations like charts and graphs · C. A set of data filters · D. A data connection interface

A. A key made of multiple columns to uniquely identify a row

What is a composite key in a relational database? · A. A key made of multiple columns to uniquely identify a row · B. A key used for encrypting database tables · C. A primary key in multiple tables · D. A foreign key that links two tables

B. A unique identifier for a row in a table

What is a primary key in a relational database? · A. A key used to encrypt data · B. A unique identifier for a row in a table · C. A foreign key in another table · D. A column used for indexing data

B. A virtual table based on a SELECT statement.

What is a view in a relational database? · A. A stored procedure for querying data. · B. A virtual table based on a SELECT statement. · C. A physical copy of a table used for performance optimization. · D. A special type of index to speed up queries.

A. Removing unnecessary data points, lines, or elements that don't add value

What is meant by 'eliminating clutter' in visualizations? · A. Removing unnecessary data points, lines, or elements that don't add value · B. Adding more annotations for clarity · C. Using multiple chart types in a single visualization · D. Replacing text with images

B) Unintended loss of additional data when a record is deleted.

What is the consequence of a delete anomaly in a database? · A) Inability to delete data due to existing foreign key constraints. · B) Unintended loss of additional data when a record is deleted. · C) Deletion of data that violates a primary key constraint. · D) Deletion of data without proper authorization.

A. INNER JOIN

What is the default join type in SQL? · A. INNER JOIN · B. LEFT JOIN · C. FULL OUTER JOIN · D. RIGHT JOIN

B. To improve query performance by speeding up data retrieval.

What is the primary purpose of an index in a database? · A. To create a backup of a table. · B. To improve query performance by speeding up data retrieval. · C. To enforce data integrity constraints. · D. To enable automatic partitioning of data.

B. To display multiple visualizations together for analysis

What is the purpose of a dashboard in Tableau? · A. To store data · B. To display multiple visualizations together for analysis · C. To connect to external data sources · D. To apply filters to data

B. To reference a primary key in another table

What is the purpose of a foreign key in a relational database? · A. To create indexes on a table · B. To reference a primary key in another table · C. To encrypt table data · D. To specify a unique identifier for a table

B. To reduce data redundancy and improve integrity

What is the purpose of normalization in database design? · A. To create backups of data · B. To reduce data redundancy and improve integrity · C. To optimize query performance · D. To encrypt sensitive data

B. To rename a column or table temporarily for a query

What is the role of an alias in SQL? · A. To create a backup of a table · B. To rename a column or table temporarily for a query · C. To encrypt sensitive data · D. To improve query performance

A) Self-referential foreign keys.

What key concept is fundamental to maintaining referential integrity in recursive relationships? · A) Self-referential foreign keys. · B) Relationship constraints. · C) Composite keys. · D) Cardinality limits.

B. HAVING

Which SQL clause is used to filter aggregate functions like SUM or COUNT? · A. WHERE · B. HAVING · C. GROUP BY · D. FILTER

B. ALTER TABLE

Which SQL command is used to modify the structure of an existing table? · A. MODIFY TABLE · B. ALTER TABLE · C. UPDATE TABLE · D. CHANGE TABLE

A. CREATE TABLE

Which SQL statement is used to create a new table? · A. CREATE TABLE · B. ADD TABLE · C. NEW TABLE · D. MAKE TABLE

B. DELETE

Which SQL statement is used to remove rows from a table? · A. DROP · B. DELETE · C. REMOVE · D. TRUNCATE

B. DROP INDEX

Which of the following SQL statements can be used to get rid of an index? · A. REMOVE INDEX · B. DROP INDEX · C. DELETE INDEX · D. TRUNCATE INDEX

A. Overuse of colors

Which of the following does is recommended avoiding in visualizations? · A. Overuse of colors · B. Data labeling · C. Annotations · D. Consistent formatting

B. Using pre-attentive attributes to focus attention

Which of the following is a recommended approach for designing good visuals? · A. Using 3D charts for added depth · B. Using pre-attentive attributes to focus attention · C. Using highly detailed legends · D. Adding as many data points as possible

a. Blueprint for a Database

Which of the following is the purpose of data modeling? a. Blueprint for a Database b. Removing Records c. Creating Transactions d. Cost Estimates

b. WHERE

Which part of the SELECT clause is used to filter records in a table ? a. GROUP BY b. WHERE c. FROM d. SELECT

c. List the total payment amount received from customers in Australia

Which question is the below query answering? SELECT SUM(amount) FROM Payments, Customers WHERE Customers.customerNumber = Payments.customerNumber AND country = 'Australia'; a. List the payment amount received from all customers b. Report the number of Australian customers with payments c. List the total payment amount received from customers in Australia d. Report the number of payments received from Australian customers

d. List, for each type of skill other than Electric, the average hourly rate of the workers with that skill, along with the number of workers with that skill

Which question is the below query answering? SELECT skill_type, AVG(hrly_rate), COUNT(*) FROM WORKER WHERE skill_type <> 'Electric' GROUP BY skill_type; a. List the average hourly rate of workers with Electric skill b. List the number and average hourly rate of all workers c. List the number and average hourly rate of workers with Electric skill d. List, for each type of skill other than Electric, the average hourly rate of the workers with that skill, along with the number of workers with that skill

d. SELECT prereq.course_id, prereq.course_number FROM COURSE AS course JOIN COURSE AS prereq ON course.prereq_id = prereq.course_id WHERE course.unit_id REGEXP 'MIST' AND course.course_number = 4610;

Based on the following data model What is the query that shows which course (id and number) is the pre-requisite course of MIST 4610? You can assume prereq_id is the FK referencing course_id. a. SELECT prereq.course_id, prereq.course_number FROM COURSE AS course JOIN COURSE AS prereq ON prereq.prereq_id = course.course_id WHERE prereq.unit_id REGEXP 'MIST' AND prereq.course_number = 4610; b. SELECT prereq.course_id, prereq.course_number FROM COURSE AS course JOIN COURSE AS prereq ON course.prereq_id = prereq.course_id WHERE prereq.unit_id REGEXP 'MIST' AND prereq.course_number = 4610; c. SELECT prereq.course_id, prereq.course_number FROM COURSE AS course JOIN COURSE AS prereq ON prereq.prereq_id = course.course_id WHERE course.unit_id REGEXP 'MIST' AND course.course_number = 4610; d. SELECT prereq.course_id, prereq.course_number FROM COURSE AS course JOIN COURSE AS prereq ON course.prereq_id = prereq.course_id WHERE course.unit_id REGEXP 'MIST' AND course.course_number = 4610;

a. select employeeName from Employee where not exists (select * from ProjectTasks where ProjectTasks.idEmployee = Employee.idEmployee);

Based on the following data model What query lists the name of employees who were not assigned any project tasks? a. select employeeName from Employee where not exists (select * from ProjectTasks where ProjectTasks.idEmployee = Employee.idEmployee); b. select employeeName from Employee where exists (select * from ProjectTasks where ProjectTasks.idEmployee = Employee.idEmployee); c. select employeeName from Employee, ProjectTasks where ProjectTasks.idEmployee = Employee.idEmployee; d. select employeeName from Employee where idEmployee in (select idEmployee in ProjectTasks);

c. SELECT course.course_name, course.course_number FROM COURSE AS course JOIN COURSE AS prereq ON course.prereq_id = prereq.course_id WHERE course.course_level = prereq.course_level;

Based on the following data model Which query lists the course name and number of the course with the same level as its pre-requisite course? a. SELECT prereq.course_name, prereq.course_number FROM COURSE AS course JOIN COURSE AS prereq ON prereq.prereq_id = course.course_id WHERE course.course_level = prereq.course_level; b. SELECT course.course_name, course.course_number FROM COURSE AS course JOIN COURSE AS prereq ON prereq.prereq_id = course.course_id WHERE course.course_level = prereq.course_level; c. SELECT course.course_name, course.course_number FROM COURSE AS course JOIN COURSE AS prereq ON course.prereq_id = prereq.course_id WHERE course.course_level = prereq.course_level; d. SELECT prereq.course_name, prereq.course_number FROM COURSE AS course JOIN COURSE AS prereq ON course.prereq_id = prereq.course_id WHERE course.course_level = prereq.course_level;

c. SELECT COUNT(idEmployee) FROM Project JOIN ProjectComposition ON Project.projectID = ProjectComposition.projectID WHERE projectName = "Rails";

Based on the following data model What is the query that identifies the number of people working on of the "Rails" project? a. SELECT SUM(idEmployee) FROM Project JOIN ProjectComposition ON Project.projectID = ProjectComposition.projectID GROUP BY idEmployee HAVING projectName = "Rails"; b. SELECT SUM(idEmployee) FROM Project JOIN ProjectComposition ON Project.projectID = ProjectComposition.projectID WHERE projectName = "Rails"; c. SELECT COUNT(idEmployee) FROM Project JOIN ProjectComposition ON Project.projectID = ProjectComposition.projectID WHERE projectName = "Rails"; d. SELECT COUNT(idEmployee) FROM Project JOIN ProjectComposition ON Project.projectID = ProjectComposition.projectID WHERE projectName = "Rails" GROUP BY idEmployee;

False

Cardinality captures whether a relationship is optional or mandatory a. True b. False

False

MySQL is not a relational database a. True b. False

False

Question 1 In a 1-M or 1-1 Recursive relationship, the foreign key needs to have the same name as that of the primary key in the same table. a. True b. False

attribute

becomes a column

entity

becomes a table

identifier

becomes the primary key

False

From this SQL code, it can be inferred that... [Answer (T)RUE or (F)ALSE]: We will get a list of all customers along with their orders (even customers who haven't placed any orders) a. True b. False

True

From this SQL code, it can be inferred that... [Answer (T)RUE or (F)ALSE]: get the total sales amount for each city within each country, each country as a whole, and a grand total of all sales.. a. True b. False

False

From this data model, it can be inferred that... [Answer (T)RUE or (F)ALSE]: It is possible for a particular visit to include many services (e.g., a haircut & moustache trim). a. True b. False

False

From this data model, it can be inferred that... [Answer (T)RUE or (F)ALSE]: The table BARBER, once created, will have 5 columns. a. True b. False

True

From this data model, it can be inferred that... [Answer (T)RUE or (F)ALSE]: The table VISIT, once created, will have three FKs. a. True b. False

a. SELECT lastName, firstName, country FROM Employees, Offices WHERE Offices.officeCode = Employees.officeCode AND country REGEXP 'USA|UK' AND jobTitle REGEXP 'Sales Rep' ORDER BY lastName, firstName, country;

Given the above data model, which query would list sales representatives ('Sales Rep') working in either American ('USA') or British ('UK') offices sorted by employee name and country? a. SELECT lastName, firstName, country FROM Employees, Offices WHERE Offices.officeCode = Employees.officeCode AND country REGEXP 'USA|UK' AND jobTitle REGEXP 'Sales Rep' ORDER BY lastName, firstName, country; b. SELECT lastName, firstName, country FROM Employees, Offices WHERE Offices.officeCode = Employees.officeCode AND country REGEXP 'USA|UK' ORDER BY lastName, firstName, country; c. SELECT lastName, firstName, country FROM Employees, Offices WHERE Offices.officeCode = Employees.officeCode AND country = 'USA|UK' AND jobTitle = 'Sales Rep' ORDER BY lastName, firstName, country; d. SELECT lastName, firstName, country FROM Employees, Offices WHERE Offices.officeCode = Employees.officeCode AND country REGEXP 'USA|UK' AND jobTitle REGEXP 'Sales Rep';

A. A list of all customers and the total number of orders each has placed.

Given the following schema: Table: Customers - CustomerID (Primary Key) - Name - Email Table: Orders - OrderID (Primary Key) - CustomerID (Foreign Key) - OrderDate What does the following query return? SELECT Name, COUNT(OrderID) AS OrderCount FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY Name; · A. A list of all customers and the total number of orders each has placed. · B. A list of all orders and their associated customers. · C. The total number of orders placed by all customers. · D. A list of customers who have placed more than one order.

C. Both A and B

Given the following schema: Table: Customers - CustomerID (Primary Key) - Name - Email Table: Orders - OrderID (Primary Key) - CustomerID (Foreign Key) - OrderDate Which query retrieves the names of customers who have never placed an order? · A. SELECT NameFROM Customers cWHERE NOT EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID); · B. SELECT NameFROM Customers cLEFT JOIN Orders o ON c.CustomerID = o.CustomerIDWHERE o.OrderID IS NULL; · C. Both A and B · D. Neither A or B.

B. It retrieves the total number of orders placed on each date in 2023.

Given the following schema: Table: Customers - CustomerID (Primary Key) - Name - Email Table: Orders - OrderID (Primary Key) - CustomerID (Foreign Key) - OrderDate What does the following query return? SELECT OrderDate, COUNT(*) AS TotalOrders FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01' GROUP BY OrderDate ORDER BY OrderDate; · A. It retrieves the total number of orders for each customer in 2023. · B. It retrieves the total number of orders placed on each date in 2023. · C. It retrieves all orders placed by customers in 2023. · D. It retrieves orders placed in January 2023 only.

B. DISTINCT

In SQL, which keyword is used to remove duplicate rows in the result set? · A. UNIQUE · B. DISTINCT · C. DELETE · D. WHERE

True

In a relational database, each row must be uniquely identified a. True b. False

False

In regular expression, ^ means ends with. True False

True

In regular expression, | means alternation (or). a. True b. False

False

drop view totalValueforEachCustomer; Based on the above SQL code, a stored routine is being dropped from the database. a. True b. False

A. The order with the highest total amount.

1. What does the following query return? SELECT o.OrderID, o.TotalAmount FROM Orders o WHERE o.TotalAmount = (SELECT MAX(TotalAmount) FROM Orders); · A. The order with the highest total amount. · B. All orders with a total amount greater than the average. · C. The total revenue for all orders. · D. The total number of orders.