Question

As you glance through the database, you note that Houston is spelled with a lower case ‘h’in at least one case. First of all, write the SQL code to list all values for Houston, capitalized or not capitalized. Begin with SELECT * FROM sales to list all fields.

Then, write the SQL script to update all lower case instances of houston to Houston. NOTE: use of WHERE id=is not permitted. This method is used in the tutorial but is not practical if you had a very large database.

Answer 1

To list all records containing any variation of 'Houston' in a sales database, use SELECT with a LIKE clause. To update lowercase 'houston' to 'Houston', an UPDATE statement with REPLACE and LIKE is used.

Step-by-step explanation:

To list all values for Houston, whether capitalized or not, you can use the following SQL code:

SELECT * FROM sales WHERE city LIKE '%houston%';

To update lowercase instances of 'houston' to 'Houston', the SQL script would be:

UPDATE sales SET city = REPLACE(city, 'houston', 'Houston') WHERE city LIKE '%houston%';

It's important to note that the use of wildcards(%) in the LIKE clause allows you to find any occurrence of 'houston', even if it is part of a larger string or if there are characters before or after it.

The SQL code to list all values for Houston, capitalized or not capitalized, would be:

SELECT * FROM sales WHERE city = 'Houston' OR city = 'houston';

To update all lowercase instances of 'houston' to 'Houston', you can use the UPDATE statement:

UPDATE sales SET city = 'Houston' WHERE city = 'houston';

It's important to note that the use of WHERE id=is not permitted in this case, so we are using the city column directly instead.