Databases：練習題

In a database, what is the correct term for a single column that contains one specific category of information for all records?

Which of the following statements correctly describes a requirement for a field to be used as a primary key in a database table?

Given the table DELIVERIES:

PackageID | Weight | Destination | Urgent
P1 | \( 2.5 \) | Paris | True
P2 | \( 5.0 \) | London | False
P3 | \( 1.5 \) | Paris | False
P4 | \( 10.0 \) | Paris | True

What value is returned by the query: SELECT SUM(Weight) FROM DELIVERIES WHERE Destination = 'Paris' AND Urgent = True;

In a database management system, which of the following best describes the primary purpose of a primary key?

Why is a NationalID field usually a more suitable choice for a primary key than a FirstName field?

Which basic data type would be most suitable for a field named PriceEach that stores values such as \(19.99\) and \(5.50\)?

Write an SQL statement to select the Model and Year of all cars in a VEHICLES table where the Color is 'Red' and the Price is less than \(15000\).

Identify the result of the following query based on the data provided:

SELECT Name FROM PRODUCTS WHERE (Stock < 10 AND Price > 5) OR Category = 'Gift' ORDER BY Name ASCENDING;

Name | Stock | Price | Category
Pen | \(20\) | \(2\) | Office
Box | \(5\) | \(12\) | Storage
Card | \(50\) | \(1\) | Gift
Mug | \(8\) | \(15\) | Gift

A company uses a database table, Staff, to store employee details. The fields in the table are: StaffID, FirstName, LastName, Department, Salary, and DateJoined.

(a) Define the term primary key and explain why StaffID would be a more suitable primary key than LastName.
(b) Suggest the most appropriate data type for the Salary and DateJoined fields.
(c) Write a Structured Query Language (SQL) script to display the FirstName, LastName, and Salary of all staff members in the 'Sales' department who have a salary greater than 30000.

A fitness center uses a single-table database named GYM_MEMBERS to manage its subscriptions. Consider the following data stored in the table:

MemberID | Name | Category | MonthlyFee | Active
M101 | Sarah | Premium | \(45.00\) | True
M102 | James | Basic | \(25.00\) | True
M103 | Anya | Premium | \(45.00\) | False
M104 | Robert | Basic | \(25.00\) | True
M105 | Chloe | Student | \(20.00\) | True
M106 | David | Premium | \(45.00\) | True

(a) State the output that would be returned by the following SQL query:
SELECT Name FROM GYM_MEMBERS WHERE Category = 'Premium' AND Active = True ORDER BY Name DESCENDING;
(b) Write an SQL script to calculate the total SUM of MonthlyFee for all members who belong to the 'Basic' or 'Student' categories.
(c) The gym manager wants to prevent data entry errors in the MonthlyFee field, specifically ensuring values are not negative. Identify a suitable validation check and explain how it would be implemented for this field.