Intro to SQL: Module Five Exercise 1 Guidelines

Up until now, you have written queries against a single table; however, you can hardly find a relational database with just one tablethat contradicts the whole idea of RDBMS and normalization rules. To achieve meaningful results, you usually have to retrieve information from multiple tables within a single query.

Your Assignment

You need to write the select statements (with comments) to retrieve the following data from your personal profile account database using inner and outer joins. Make sure you test them against the SQL Server database before submitting them in a Word document:

Query 1:

Retrieve a list of the mechanics who work at an airport in California or Texas.

Query 2:

Retrieve the mechanics name, skill name, and year qualified for each mechanic.

Query 3:

Retrieve the mechanics name for each mechanic that has a skill in the skill category of Wing skill.

Query 4:

Retrieve the mechanics name and the city and state of the airport they work in for each mechanic that has a skill named Tire repair.

Query 5: Retrieve a list of the mechanics who work at an airport in California or Texas. Sort this by the mechanics name alphabetically from A to Z. Query 6: Retrieve the mechanics name, skill name, and year qualified for each mechanic. Sort this by the year qualified descending, and secondly by the skill name ascending. Query 7: Retrieve each mechanics name and their associated skill name for each mechanic who works in California. Sort this by mechanic name, AZ, first, and by skill name, ZA, second. Query 8: Retrieve the list of airport names that have a mechanic who has a skill name of Fan blade diagnose or Fan blade replacement. Sort the list by airport name, AZ.

Each query should include the following critical elements:

1. All statements should be present.

2. All rows should be displayed and in the order requested.

3. Each statement should have an explanation why it returns what it should.

TABLES FOR DATABASE:

TABLE AIRPORT - AIRNAME VARCHAR(20), CITY VARCHAR(20), STATE VARCHAR(20), SIZE FLOAT, YEAROPENED VARCHAR(4), PRIMARY KEY (AIRNAME)

TABLE MECHANIC - MECHNUM VARCHAR(5), MECHNAME VARCHAR(20), TELEPHONE VARCHAR(8), SALARY FLOAT, AIRNAME VARCHAR(20), PRIMARY KEY (MECHNAME), FOREIGN KEY (AIRNAME) REFERENCES AIRPORT (AIRNAME)

TABLE SKILL - SKILLNUM VARCHAR(3), SKILLNAME VARCHAR(25), SKILLCAT VARCHAR(20), PRIMARY KEY (SKILLNUM)

TABLE QUALIFICATION - MECHNUM VARCHAR(5), SKILLNUM VARCHAR(3), YEARQUAL VARCHAR(4), PROFRATE VARCHAR(50), PRIMARY KEY (MECHNUM), FOREIGN KEY (MECHNUM)REFERENCES MECHANIC (MECHNUM), FOREIGN KEY (SKILLNUM)REFERENCES SKILL (SKILLNUM)