Database Administration Online Test

App usage data are kept in the following table:

TABLE sessions
  id INTEGER PRIMARY KEY,
  userId INTEGER NOT NULL,
  duration DECIMAL NOT NULL

Write a query that returns, for each user who has more than one session:

• The userId.
• The average session duration.

See the example case for more details.

In the diagram below, each type of backup is taken at the specified time. Due to a missed scenario during the testing phase, a development error in setting wrong query conditions resulted in the loss of all records from a table at a time marked by the red cross.

Select all the ways to restore the database until the point of failure.

The following data definition defines an organization's employee hierarchy.

An employee is a manager if any other employee has their managerId set to this employee's id. That means John is a manager if at least one other employee has their managerId set to John's id.

TABLE employees
  id INTEGER NOT NULL PRIMARY KEY
  managerId INTEGER
  name VARCHAR(30) NOT NULL
  FOREIGN KEY (managerId) REFERENCES employees(id)

Write a query that selects only the names of employees who are not managers.

See the example case for more details.

For an autocomplete feature in an app, English words are stored in a table with the following data definition:

TABLE dictionary
  id INTEGER NOT NULL PRIMARY KEY
  word VARCHAR(100) NOT NULL

To evaluate the user-friendliness of the feature, you need to measure how many word suggestions are returned based on the user’s input.

For letters 'b', 'i' and 'd' typed in succession, write a query that returns the count of the suggestions as shown in the video below: All words in the table will be in lowercase.

See the example case for more details.

The following table contains students enrolled in a yearly course:

TABLE enrollments
  id INTEGER NOT NULL PRIMARY KEY
  year INTEGER NOT NULL
  studentId INTEGER NOT NULL

Records with IDs between 20 and 100 (inclusive) contain incorrect data.

Write a query that updates the 'year' field of every faulty record to 2015.

Given the following data definition, write a query that returns the number of students whose first name is John.

TABLE students
   id INTEGER PRIMARY KEY,
   firstName VARCHAR(30) NOT NULL,
   lastName VARCHAR(30) NOT NULL

An insurance company maintains records of sales made by its employees. Each employee is assigned to a state. States are grouped under regions. The following tables contain the data:

TABLE regions
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL

TABLE states
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL
  regionId INTEGER NOT NULL
  FOREIGN KEY (regionId) REFERENCES regions(id)

TABLE employees
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL
  stateId INTEGER NOT NULL
  FOREIGN KEY (stateId) REFERENCES states(id)

TABLE sales
  id INTEGER PRIMARY KEY
  amount INTEGER NOT NULL
  employeeId INTEGER NOT NULL
  FOREIGN KEY (employeeId) REFERENCES employees(id)  

Management requires a comparative region sales analysis report.

Write a query that returns:

• The region name.
• Average sales per employee for the region (Average sales = Total sales made for the region / Number of employees in the region).
• The difference between the average sales of the region with the highest average sales, and the average sales per employee for the region (average sales to be calculated as explained above).

Employees can have multiple sales. A region with no sales should be also returned. Use 0 for average sales per employee for such a region when calculating the 2nd and the 3rd column.

See the example case for more details.

The following two tables are used to define users and their respective roles:

TABLE users
  id INTEGER NOT NULL PRIMARY KEY,
  userName VARCHAR(50) NOT NULL

TABLE roles
  id INTEGER NOT NULL PRIMARY KEY,
  role VARCHAR(20) NOT NULL

The usersRoles table should contain the mapping between each user and their roles. Each user can have many roles, and each role can have many users.

Modify the provided SQL create table statement so that:

• Only users from the users table can exist within usersRoles.
• Only roles from the roles table can exist within usersRoles.
• A user can only have a specific role once.

See the example case for more details.

Each item in a web shop belongs to a seller. To ensure service quality, each seller has a rating.

The data are kept in the following two tables:

TABLE sellers
  id INTEGER PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  rating INTEGER NOT NULL

TABLE items
  id INTEGER PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  sellerId INTEGER
  FOREIGN KEY (sellerId) REFERENCES sellers(id)

Write a query that selects the item name and the name of its seller for each item that belongs to a seller with a rating greater than 4. The query should return the name of the item as the first column and name of the seller as the second column.

See the example case for more details.

Information about pets is kept in two separate tables:

TABLE dogs
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL

TABLE cats
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL

Write a query that select all distinct pet names.

See the example case for more details.

You are working with an e-commerce database that contains the following two tables:

TABLE customers
  id INT PRIMARY KEY
  firstName VARCHAR(50) NOT NULL
  email VARCHAR(50) NOT NULL
    
TABLE orders
  id INT PRIMARY KEY
  customerId INT
  orderDate DATE NOT NULL
  totalAmount INT NOT NULL
  FOREIGN KEY (customerId) REFERENCES customers (id)

Over the years, some rows in the orders table still reference id's from customers table that have been deleted (for example, due to GDPR “right-to-be-forgotten” requests). Management has asked you for a report that has all the columns from both tables. The report must meet both of the following requirements:

• Every customer currently in the customers table should appear in the output, even if they have never placed an order (their order-related columns should be NULL).
• Every order in the orders table should appear in the output, even if its customer's id no longer exists in the customers table (the customer-related columns should be NULL).

On your initial attempt to create a query, you see that INNER JOIN does not satisfy the requirements. Explain why and describe which type of SQL JOIN is the most appropriate to satisfy both requirements?

(An answer up to 40 words is sufficient)

A new social network site has the following data tables:

Select data that will be returned by the following SQL query:

SELECT users.name, COUNT(friends.user1) as count FROM users
LEFT JOIN friends
ON users.id = friends.user1 OR users.id = friends.user2
WHERE users.sex = 'f'
GROUP BY users.id, users.name;

A new cloud service provider plans to launch its services in the near future. The database cluster for keeping user data will consist of database nodes with each node placed in a different region.

Users can choose the regional node their data will reside on. User data is kept in a table along with the region id. The region id and the region name are kept in another table which is redundant on every region node.

Select all answers that are true for the given scenario.