SQL：練習問題

You need to add a new student record into the Students table, which has columns StudentID, FirstName, LastName, and Major. The new student's details are: ID 'S001', FirstName 'Alice', LastName 'Smith', Major 'Computer Science'. Which SQL statement correctly inserts this data?

Consider a table named Customers with a column CustomerName. Which SQL query will retrieve all customers whose names start with the letter 'M'?

You are managing a database for an e-commerce platform. The Customers table currently has columns CustomerID, CustomerName, and Email. You need to add a new column named LastLoginDate to this table. This column should store dates and have a default value of '2023-01-01' for any existing or new records if no specific date is provided. Which SQL statement correctly performs this operation?

An existing table named Suppliers has columns SupplierID, SupplierName, ContactPerson, and FaxNumber. Due to a policy change, the FaxNumber column is no longer needed. Which SQL statement correctly removes the FaxNumber column from the Suppliers table?

Given a table named Products with columns ProductID, ProductName, Category, Price, and StockQuantity. Which SQL query will retrieve the ProductName and Price of all products whose price is between $$50$$ and $$100$$ (inclusive)?

Which specific SQL Data Manipulation Language (DML) command is used to add new rows (records) of data into an existing database table?

Given a table Employees (EmployeeID, Name, Salary, DepartmentID) and Departments (DepartmentID, DepartmentName). Write an SQL query to list the Name of employees and their corresponding DepartmentName for all employees, and if a department name is not available, display 'No Department'.

Given the tables: Orders (OrderID, CustomerID, OrderDate) and Customers (CustomerID, CustomerName, City). Write an SQL query to retrieve the CustomerName of all customers who have placed at least one order in the year $$2023$$.

A company needs to set up a simple relational database to manage its IT assets and maintenance history.

You are tasked with defining the structure of the maintenance record table.

Table: Assets

• AssetID (Primary Key, INTEGER)
• AssetName (VARCHAR)
• Location (VARCHAR)

(a) Write the SQL statement to create a new table named MaintenanceLog with the following specifications:

• LogID (Primary Key, auto-incrementing INTEGER)
• AssetID (INTEGER, linked to Assets table)
• MaintenanceDate (DATE)
• TechnicianName (VARCHAR, cannot be NULL)

(b) Write an SQL statement to insert a record into the MaintenanceLog table, assuming the AssetID is 101, the maintenance was done on '2024-05-15', and the technician was 'Sarah Lee'. (Assume LogID is handled automatically.)

(c) Later, the company decides that all maintenance records must include a unique service report number (up to 20 characters) which should be recorded in a new column called ReportCode. Write the SQL statement to add this new column to the MaintenanceLog table and ensure that every entry must have a unique code.

An online retailer tracks product information in the Products table and sales transactions in the Transactions table:

Table: Transactions

• TransactionID (INTEGER)
• ProductID (INTEGER)
• SaleDate (DATE)
• UnitsSold (INTEGER)

(a) Write an SQL query to determine the total UnitsSold and the number of distinct transactions (COUNT(TransactionID)) for each month in the year \(2024\). Assume a built-in function MONTH() or similar is available to extract the month from SaleDate.

(b) Write an SQL query to list the ProductID and the total UnitsSold for products where the total units sold is less than \(500\) units. The results must be grouped by ProductID.