Question:
Show a listing of all consultants who live in Linlithgow and Whitecross (towns).
- Re-write the previous query so that the tables are joined in a different order. For example, if your query had “FROM consultant JOIN address” then change it to “FROM address JOIN consultant”.
Does the query work both ways?
Change the ON criteria as well. If you had “ON c.address_id = a.address_id” then change it to “ON a.address_id = c.address_id”. Does the query work both ways?
- List all clients in the towns of Falkirk and Armadale.
- Show a list of all consultants, the names of the clients they worked with, and the comment made about the assignment.
- Create a listing of consultants (name and town) and the clients (name, and town) for the jobs that they performed in their own home town.
- Show a listing which shows consultants, clients and towns where the consultant lives in the town where the client’s office is. (Disregard whether the consultant has performed an assignment at the client site. The ONLY issue is whether the consultant lives in the town.)
- Make a listing to show all clients and the assignment ids, start dates and end dates for assignments for the client. Sort the output to show all client records in order of client name and show all clients even if there have been no assignments for the client.
- The consultants want to car pool. Make a listing which shows consultants who live in the same town and go to the same client. (Simply sort the records so we can see who should carpool. Do not worry about eliminating the cases where there is only one consultant travelling to a client site. Don’t worry about displaying more than one consultant on the same line in the result set.)
- List all assignment ids and the last name of the consultant if one has been assigned. If no consultant has been assigned then use ‘None Assigned’ as the name.
- List all consultants who are older than their boss.
- Write a query which shows the number of assignment records.
- Count the number of assignment records where consultants have been allocated. (Reminder: assignments can have null consultant_ids)
- Display consultant’s names and the count of assignment records he or she is named on.
- Name of each town and number of consultants who live in each town. (Show zero if no consultants live in the town.)
- Number of clients in each town but show only if the count is greater than 2.
- Number of employees who report to each consultant. Please give the consultant’s full name and the number of people who report to this person. (Show zero if no one reports to this person.)
- (Level 2) Find all consultants who do not have anyone reporting to them.
- (Level 2) Count all the consultants who do not have anyone reporting to them.
3a. (Level 1) List the highest pay that any consultant received for any single assignment.
3b. (Level 3) List the consultant, the client_name, and amount paid for the single highest paying assignment. (Could be several tied for first place.)
4a. (Level 1) List each consultant and their personal highest amount they were paid for any single assignment.
4b. (Level 3) List each consultant, the client_name of the assignment for which they earned their personal highest amount and the amount they were paid for that assignment.
4c. (Level 3) Rewrite the previous query. If you used a correlated subquery then put your subquery in the FROM or the JOIN clause. If you used a subquery in the FROM or JOIN then turn your query into a correlated subquery in your WHERE clause.
5a. (Level 1) List each client and their sum of pay.
5b. (Level 1) Use SQL Server’s TOP to write a query that will list the TOP 1 client who has paid the most.
5c. (Level 3) Rewrite the previous query so that you do not use TOP. (Hint: You will need a subquery in the HAVING clause.)
Answer:
- QUERY
SELECT consultant.* FROM consultant INNER JOIN address ON consultant.ADDRESS_ID=address.ADDRESS_ID WHERE address.TOWN=’Whitecorss’ OR address.TOWN=’Linlithgow’;
RESULTS
- QUERY
SELECT consultant.* FROM address INNER JOIN consultant ON consultant.ADDRESS_ID=address.ADDRESS_ID WHERE address.TOWN=’Whitecorss’ OR address.TOWN=’Linlithgow’;
RESULTS
The result of the query did not change from the changes gotten in question 1.
QUERY
SELECT consultant.* FROM consultant INNER JOIN address ON address.ADDRESS_ID=consultant.ADDRESS_ID WHERE address.TOWN=’Whitecorss’ OR address.TOWN=’Linlithgow’;
RESULTS
Changing the on clause did not bring any changes to the results of the query.
- QUERY
SELECT consultant.* FROM consultant
INNER JOIN address ON address.ADDRESS_ID=consultant.ADDRESS_ID
WHERE address.TOWN=’Falkirk ‘ OR address.TOWN=’Armadale’;
RESULTS
- List of all consultants, the names of the clients they worked with, and the comment made about the assignment.
QUERY
SELECT consultant.FIRST_NAME AS ‘consultant first name’, consultant.LAST_NAME as ‘consultant last name’,client.CLIENT_NAME,assignment.COMMENTS FROM assignment
INNER JOIN client ON assignment.CLIENT_ID=client.CLIENT_ID
INNER JOIN consultant ON assignment.CONSULTANT_ID=consultant.CONSULTANT_ID;
RESULTS
- Create a listing of consultants (name and town) and the clients (name, and town) for the jobs that they performed in their own home town
QUERY
SELECT consultant.FIRST_NAME AS ‘consultant first name’, consultant.LAST_NAME as ‘consultant last name’,addressA.TOWN,client.CLIENT_NAME,addressB.TOWN FROM assignment INNER JOIN client ON assignment.CLIENT_ID=client.CLIENT_ID INNER JOIN consultant ON assignment.CONSULTANT_ID=consultant.CONSULTANT_ID INNER JOIN address addressB ON client.ADDRESS_ID=addressB.ADDRESS_ID INNER JOIN address addressA ON consultant.ADDRESS_ID=addressA.ADDRESS_ID WHERE consultant.ADDRESS_ID=client.ADDRESS_ID;
OUTPUT
The query returned zero rows meaning that no consultant worked in their home town.
- Listing which shows consultants, clients and towns where the consultant lives in the town where the client’s office is. (Disregard whether the consultant has performed an assignment at the client site. The ONLY issue is whether the consultant lives in the town.)
QUERY
SELECT consultant.FIRST_NAME AS ‘consultant first name’, consultant.LAST_NAME as ‘consultant last name’,client.CLIENT_NAME,address.TOWN FROM address INNER JOIN consultant ON address.ADDRESS_ID=consultant.ADDRESS_ID INNER JOIN client ON address.ADDRESS_ID=client.ADDRESS_ID WHERE client.ADDRESS_ID=consultant.ADDRESS_ID;
OUTPUT
The query returned 0 rows
- Listing to show all clients and the assignment ids, start dates and end dates for assignments for the client. Sort the output to show all client records in order of client name and show all clients even if there have been no assignments for the client.
QUERY
SELECT client.CLIENT_NAME,assignment.ASSIGNMENT_ID,assignment.START_DATE,assignment.END_DATE FROM client
LEFT OUTER JOIN assignment ON client.CLIENT_ID=assignment.CLIENT_ID;
OUTPUT
- Shows consultants who live in the same town and go to the same client.
QUERY
SELECT consultant.CONSULTANT_ID,consultant.FIRST_NAME,consultant.LAST_NAME
FROM consultant
LEFT OUTER JOIN assignment ON consultant.CONSULTANT_ID=assignment.CONSULTANT_ID
GROUP BY consultant.CONSULTANT_ID
HAVING COUNT(consultant.ADDRESS_ID) >= 2;
RESULTS
- List all assignment ids and the last name of the consultant if one has been assigned. If no consultant has been assigned then use ‘None Assigned’ as the name.
QUERY
SELECT assignment.ASSIGNMENT_ID,COALESCE(consultant.LAST_NAME,’None Assigned’) AS LAST_NAME FROM assignment
LEFT JOIN consultant ON assignment.CONSULTANT_ID=assignment.CONSULTANT_ID;
OUTPUT
- List all consultants who are older than their boss.
Query
SELECT cons.CONSULTANT_ID,cons.LAST_NAME FROM consultant
INNER JOIN consultant cons ON consultant.CONSULTANT_ID=cons.CONSULTANT_ID
INNER JOIN consultant boss ON consultant.CONSULTANT_ID=boss.CONSULTANT_ID
WHERE cons.BIRTHDATE)<boss.BIRTHDATE;
OUTPUT
PART 2
- number of assignment records.
QUERY
SELECT COUNT(ASSIGNMENT_ID) FROM assignment;
OUTPUT
- Count the number of assignment records where consultants have been allocated
QUERY
SELECT COUNT(ASSIGNMENT_ID) FROM assignment
WHERE CONSULTANT_ID IS NOT NULL;
OUTPUT
- Display consultant’s names and the count of assignment records he or she is named on.
QUERY
SELECT COALESCE(consultant.CONSULTANT_ID,’NOT ASSIGNED’),COUNT(assignment.ASSIGNMENT_ID) FROM assignment
LEFT JOIN consultant ON assignment.CONSULTANT_ID=consultant.CONSULTANT_ID
GROUP BY consultant.CONSULTANT_ID;
OUTPUT
- Name of each town and number of consultants who live in each town
QUERY
SELECT address.TOWN,
CASE WHEN COUNT(consultant.CONSULTANT_ID)=’0′ THEN ‘NONE’ ELSE COUNT(consultant.CONSULTANT_ID) END AS COUNT(consultant.CONSULTANT_ID)
FROM address
INNER JOIN consultant ON address.ADDRESS_ID=consultant.ADDRESS_ID
GROUP BY address.ADDRESS_ID;
OUTPUT
- Number of clients in each town but show only if the count is greater than 2
QUERY
SELECT address.TOWN, COUNT(client.CLIENT_ID)
FROM address
INNER JOIN client ON address.ADDRESS_ID=client.ADDRESS_ID
GROUP BY address.ADDRESS_ID
HAVING COUNT(client.CLIENT_ID)>2;
OUTPUT
The query returned 0 rows
- Number of employees who report to each consultant. Please give the consultant’s full name and the number of people who report to this person. (Show zero if no one reports to this person.)
QUERY
SELECT boss.LAST_NAME,COUNT(cons.CONSULTANT_ID) FROM consultant
INNER JOIN consultant cons ON consultant.CONSULTANT_ID=cons.CONSULTANT_ID
INNER JOIN consultant boss ON consultant.CONSULTANT_ID=boss.BOSS_ID
GROUP BY boss.BOSS_ID;
RESULTS
Part 3
- Find all consultants who do not have anyone reporting to them.
QUERY
SELECT CONSULTANT_ID,FIRST_NAME FROM consultant WHERE CONSULTANT_ID NOT IN (SELECT BOSS_ID FROM consultant WHERE BOSS_ID IS NOT NULL);
OUTPUT
- Count all the consultants who do not have anyone reporting to them.
QUERY
SELECT COUNT(CONSULTANT_ID) FROM consultant WHERE CONSULTANT_ID NOT IN (SELECT BOSS_ID FROM consultant WHERE BOSS_ID IS NOT NULL);
RESULTS
- List the highest pay that any consultant received for any single assignment
QUERY
SELECT MAX(assignment.PAY) FROM ASSIGNMENT;
OUTPUT
- List the consultant, the client_name, and amount paid for the single highest paying assignment. (Could be several tied for first place.)
QUERY
SELECT consultant.LAST_NAME,client.CLIENT_NAME,assignment.PAY FROM assignment
INNER JOIN consultant ON assignment.CONSULTANT_ID=consultant.CONSULTANT_ID
INNER JOIN client ON assignment.CLIENT_ID=client.CLIENT_ID
HAVING MAX(assignment.PAY);
RESULTS
- List each consultant and their personal highest amount they were paid for any single assignment.
QUERY
SELECT consultant.LAST_NAME,assignment.PAY FROM assignment
INNER JOIN consultant ON assignment.CONSULTANT_ID=consultant.CONSULTANT_ID
GROUP BY consultant.CONSULTANT_ID;
OUTPUT
- List each consultant, the client_name of the assignment for which they earned their personal highest amount and the amount they were paid for that assignment.
QUERY
SELECT consultant.LAST_NAME,client.CLIENT_NAME,assignment.PAY FROM assignment
INNER JOIN consultant ON assignment.CONSULTANT_ID=consultant.CONSULTANT_ID
INNER JOIN client ON assignment.CLIENT_ID=client.CLIENT_ID
GROUP BY consultant.CONSULTANT_ID
HAVING MAX(assignment.PAY);
OUTPUT
- Rewrite the previous query. If you used a correlated subquery then put your subquery in the FROM or the JOIN clause. If you used a subquery in the FROM or JOIN then turn your query into a correlated subquery in your WHERE clause.
QUERY
SELECT consultant.LAST_NAME,client.CLIENT_NAME,assignment.PAY FROM assignment
INNER JOIN consultant ON assignment.CONSULTANT_ID=consultant.CONSULTANT_ID
INNER JOIN client ON assignment.CLIENT_ID=client.CLIENT_ID
WHERE consultant.CONSULTANT_ID IN (SELECT assignment.CONSULTANT_ID FROM assignment GROUP BY assignment.CONSULTANT_ID HAVING MAX(assignment.PAY));
OUTPUT
5a. (Level 1) List each client and their sum of pay.
QUERY
SELECT client.CLIENT_NAME,SUM(assignment.PAY) FROM assignment
INNER JOIN client ON assignment.CLIENT_ID=client.CLIENT_ID
WHERE assignment.PAY IS NOT NULL
GROUP BY client.CLIENT_NAME;
OUTPUT
5b. (Level 1) Use SQL Server’s TOP to write a query that will list the TOP 1 client who has paid the most.
QUERY
SELECT TOP 1 client.CLIENT_NAME,SUM(assignment.PAY) FROM assignment
INNER JOIN client ON assignment.CLIENT_ID=client.CLIENT_ID
WHERE assignment.PAY IS NOT NULL
GROUP BY client.CLIENT_NAME;
OUTPUT
5c. (Level 3) Rewrite the previous query so that you do not use TOP. (Hint: You will need a subquery in the HAVING clause.)
QUERY
SELECT client.CLIENT_NAME,SUM(assignment.PAY) FROM assignment
INNER JOIN client ON assignment.CLIENT_ID=client.CLIENT_ID
WHERE assignment.CLIENT_ID IN (SELECT assignment.CLIENT_ID FROM assignment HAVING MAX(assignment.PAY)));
OUTPUT
