PLEASE DO NOT USE AI TO SOLVE THIS

carefully read all instructions and complete the questions. Be sure to complete the readings and ePortfolio before proceeding to the questions.

Instructions

Use the school database to answer the questions. The database creation file is available on ELMS.

Submit your answers as one SQL file via ELMS. Name your file in the following format: yourlastname$\_$A$1\_$query.sql$,$ for example, D$\_$Smith$\_$A$1\_$query.sql$.$

State your SQL file answer$($s$)$ clearly by numbering them with comments. For example:

sql

Copy code

$--$ Q$1$

SELECT $*$ FROM vendors;

Ensure that your query results replicate the sample result sets shown in the figures exactly matching their column headers and format aspects.

You are not allowed to use exact match conditions such as:

WHERE last$\_$name $=$ 'Smith'

WHERE last$\_$name $<>$ 'Smith'

You are allowed to use inequality conditions such as:

WHERE last$\_$name $>$ 'Smith'

WHERE last$\_$name $<$ 'Smith'

WHERE last$\_$name $>=$ 'Smith'

WHERE last$\_$name $<=$ 'Smith'

You may not use them to filter primary key values.

You are allowed to use filtering conditions to include or exclude NULL values. For example:

WHERE date IS NOT NULL

WHERE address IS NULL

Contact the instructors immediately if you think there is an error in any of the questions, or if more clarification is needed.

Ensure that your queries replicate the sample result sets shown in the figures exactly matching their column headers and format aspects.

For this assignment, you are not allowed to use the following keywords: BETWEEN, LIKE, LIMIT, IFNULL.

This transcription captures the instructions and information provided in the images.

Q$1)$ Simple SELECT Query with Sorting $(30$ points$)$

Write a SELECT query that returns the address columns $($street$,$ city, state, country, zip code$),$ main phone number, and local phone number from the addresses table. Filtering is not used, so all $140$ rows should be returned. The query must replicate the result set shown in the figure below.

NOTE: The result set is sorted on $3$ columns. Unsorted columns may be displayed in an order different from the result set shown below. For more information, read the "Indeterminate Sorting in SQL$"$ page in the Week $1$ module.

Hint: Check the bottom "Action Output" panel in Workbench under the "Response" tab to see how many rows were returned. This is also where any syntax errors will be shown.

yaml

Copy code

street $|$ city $|$ state $|$ country $|$ zipcode $|$ main$\_$phone $|$ local$\_$phone

$-----------------------------------------------------------------------------------------------------$

$087$ North Crossing $|$ Lekas $|$ NULL $|$ Albania $|5001|424-555-0464|699-555-7489$

$6797$ Lien Way $|$ Batna $|$ NULL $|$ Algeria $|5006|940-555-6137|873-555-4430$

$3707$ Fallview Alley $|$ Camabatela $|$ NULL $|$ Angola $|$ NULL $|940-555-2356|899-555-8395$

$7806$ Monument Trail $|$ Alta Gracia $|$ NULL $|$ Argentina $|5186|619-555-1778|477-555-5487$

$39$ Warrior Lane $|$ Carlos Tejedor$|$ NULL $|$ Argentina $|$ B$6455|605-555-2304|586-555-8010$

$93392$ Dennis Alley $|$ Sylhet $|$ NULL $|$ Bangladesh $|3100|489-555-4437|170-555-4848$

$379$ Anhalt Center $|$ Cachoeira do Sul$|$ NULL $|$ Brazil $|96500|946-555-3952|511-555-4347$

$4$ Rockefeller Way $|$ Rio de Janeiro$|$ NULL $|$ Brazil $|20000|946-555-1780|810-555-3431$

$61960$ Center Junction$|$ Mutengene $|$ NULL $|$ Cameroon $|$ NULL $|594-555-7795|754-555-9637$

$80$ Knutson Crossing $|$ Cowansville $|$ QC $|$ Canada $|$ J$2$K $|242-555-9504|548-555-4427$

$29821$ Manitowish Trail$|$ Fuqing $|$ NULL $|$ China $|350300|830-555-6868|572-555-7149$

$4$ Bellgrove Street $|$ Urban $|$ NULL $|$ China $|300000|743-555-0402|228-555-4497$

$0$ Melrose Alley $|$ Xingtai $|$ NULL $|$ China $|540000|0|0$

$3626$ Riverside Parkway$|$ Bogota $|$ NULL $|$ Colombia $|111711|0|0$

$311$ Texas Pass $|$ Guatape $|$ NULL $|$ Colombia $|53847|0|0$

$34$ Vernon Avenue $|$ Haraida $|$ NULL $|$ Japan $|879-0602|0|0$

$057$ Spohn Pass $|$ Ibadan $|$ NULL $|$ Nigeria $|200211|0|0$

$92$ Cody Hill $|$ Yanac $|$ NULL $|$ Peru $|2209|0|0$

$858$ Rigney Way $|$ Afrin $|$ NULL $|$ Syria $|0|0|0$

$16787$ Warbler Circle $|$ Si Wilai $|$ NULL $|$ Thailand $|43210|0|0$

$740$ Oneill Point $|$ Ankara $|$ NULL $|$ Turkey $|6500|0|0$

$26804$ Prairie Rose Circle$|$ Ashgabat $|$ NULL $|$ Turkmenistan$|744000|0|0$

$---$

$145$ rows returned. Only samples from the beginning and end of the result set are shown.

Image $2$ Transcription

Q$2)$ SELECT Query with Column Aliases and Formatting $(30$ points$)$

Using the addresses table, write a SELECT query that replicates the result set shown in the figure below.