Topic 3.3: SQL Questions

A clinic holds a single view, APPOINTMENTS, with the fields ClientID, ClientName, StaffID, StaffName, VisitedOn and Service, where Service holds values such as Eye test, Hearing test and Hearing aid fitting. Construct an SQL SELECT query that lists the names of clients whose service includes the word hearing. A client's name should appear only once.

Explain the difference between an INNER JOIN and a LEFT JOIN, with an example of when each is appropriate.

Construct an SQL statement to return the name and email of all customers in the city of Lisbon, sorted by name.

Describe the effect of omitting the WHERE clause from an UPDATE statement.

Describe the difference between DELETE, TRUNCATE and DROP.

Choose the statement that changes values in rows that already exist.

A cinema's booking database has been designed as an ERD. Explain how a data definition language (DDL) is used to implement this design.

Describe how SQL is used to record a new order in an online shop's ORDERS table.

An online shop's developers use both DDL and DML. Explain the difference between them in this context.

A shop runs UPDATE PRODUCT SET Price = Price * 1.1 WHERE Category = 'Toys';. Describe what this statement does.

Explain why an UPDATE to one customer's address should use the primary key in its WHERE clause.

A club runs DELETE FROM MEMBER WHERE LastVisit < '2024-01-01';. Describe the effect of this statement.

Identify the three parts of an SQL UPDATE statement that changes one pupil's form group.

State the SQL keyword used to add a new row to a table.

Explain why a DELETE on a CUSTOMER row can be refused when ORDER rows refer to it.

Identify three SQL statements used to change the data held in a school's tables.

Describe how SQL updates a warehouse's stock level after a delivery.

Describe how SQL removes expired coupons from a COUPON table.

Explain how one SQL UPDATE can change many records at once in a shop's price list.

Identify four SQL commands a database developer uses to change data or structure in a sports club's database.