Write a query to display the author ID, first and last name for all authors who have never written a book with the subject Programming. Sort the results by author last name.
时间: 2024-03-13 11:43:27 浏览: 14
Sure, here's the SQL query to display the author ID, first and last name for all authors who have never written a book with the subject Programming and sort the results by author last name:
```
SELECT authors.au_id, authors.au_fname, authors.au_lname
FROM authors
LEFT JOIN titleauthor ON authors.au_id = titleauthor.au_id
LEFT JOIN titles ON titleauthor.title_id = titles.title_id
LEFT JOIN titlesubject ON titles.title_id = titlesubject.title_id
LEFT JOIN subjects ON titlesubject.subject_id = subjects.subject_id
WHERE subjects.subject LIKE '%Programming%'
AND titleauthor.au_id IS NULL
ORDER BY authors.au_lname;
```
This query uses a left join to join the `authors`, `titleauthor`, `titles`, `titlesubject`, and `subjects` tables together. It filters the results by selecting only authors who have never written a book with the subject `Programming` by checking for a `NULL` value in the `titleauthor.au_id` column. Finally, the results are sorted by the author last name in ascending order using the `ORDER BY` clause.