Write a SQL query that works in MySQL to find the following information: 1) Show

Question

Write a SQL query that works in MySQL to find the following information:

1) Show the names and IDs of all artists who have not recorded titles but do have web addresses listed in the database.

2) List each salesperson by first name with his or her supervisor's salesID. Use "AS" clauses to show the fields as "Subordinate" and "Supervisor" respectively. Do not include any salespeople who do not have supervisors. The output should look like this:

+-------------+------------+
| Subordinate | Supervisor |
+-------------+------------+
| Bob | 4 |
| Lisa | 4 |
| Clint | 1 |
+-------------+------------+
3 rows in set (0.00 sec)

3) a.(6 pts) List each salesperson who has at least one subordinate listed in the salespeople table.
b. (2 pts) Why do some salespeople appear in the results from this query who did not appear in the left hand column (subordinates) of the results from the last query?
c. (2 pts) Why do some salespeople appear in the results of the last query but not in the results from this one?

4) Show every salesperson's SalesID and the members, if any, to whom he or she is assigned. If a salesperson is not assigned to any
members, show his/her SalesID anyway with the message "No Members Assigned." Label the column showing members as "Clients." The output
should look like this:

+---------+---------------------+
| SalesID | Clients |
+---------+---------------------+
| 1 | 9 |
| 1 | 13 |
| 1 | 18 |
| 1 | 29 |
| 1 | 30 |
| 1 | 31 |
| 2 | 3 |
| 2 | 10 |
| 2 | 15 |
| 2 | 20 |
| 2 | 21 |
| 2 | 26 |
| 2 | 28 |
| 2 | 34 |
| 2 | 35 |
| 2 | 36 |
| 3 | 7 |
| 3 | 8 |
| 3 | 14 |
| 3 | 19 |
| 3 | 22 |
| 3 | 32 |
| 3 | 33 |
| 4 | No Members Assigned |
+---------+---------------------+

5) List all sales people who represent members who are not located in California.

6) a) (6 pts) List all salespeople who do not represent any members who are located in California.
b) (4 pts) Why are the results of the last query different from the results of this one

EIDT: Please leave a comment for the SQL dtatabase!

Write a SQL query that works in MySQL to find the following information: Show the names and IDs of all artists who have not recorded titles but do have web addresses listed in the database. List each salesperson by first name with his or her supervisor's salesID. Use "AS" clauses to show the fields as "Subordinate" and "Supervisor" respectively. Do not include any salespeople who do not have supervisors. The output should look like this: 3 rows in set (0.00 sec) List each salesperson who has at least one subordinate listed in the salespeople table. Why do some salespeople appear in the results from this query who did not appear in the left hand column (subordinates) of the results from the last query? Why do some salespeople appear in the results of the last query but not in the results from this one? Show every salesperson's SalesID and the members, if any, to whom he or she is assigned. If a salesperson is not assigned to any members, show his/her SalesID anyway with the message "No Members Assigned." Label the column showing members as "Clients." The output should look like this:

Explanation / Answer

1. Select ArtistId, ArtistName from Artists where WebAddress is NOT NULL and ArtistId not in(Select ArtistId from Titles);

2. Select FirstName as Subordinate, Supervisor from SalesPeople where Supervisor is NOT NULL;

3. a) Select SalesId, FirstName, LastName from SalesPeople where SalesId in (Select Supervisor from SalesPeople);

b) because all the subordinates are not supervisors

4. Select S.SalesId, nvl(M.MembersId,'No Members Assigned')as Clients from SalesPeople S right outer join Members M on S.SalesId=M.SalesId;

5.Select S.SalesId, S.FirstName, S.LastName from SalesPeople S, Members M where S.SalesId=M.SalesId and M where city !='California';

6.Select S.SalesId, S.FirstName, S.LastName from SalesPeople S where SalesId not in (select M.SalesId from Members M where city='California');

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.