QL – Basic Query 1. Show the first name, last name, city and e-mail of each cust
ID: 3700712 • Letter: Q
Question
QL – Basic Query 1. Show the first name, last name, city and e-mail of each customer in New Jersey 2. Show all book titles ordered on or after December 31, 1999. [Do not list any title more than once.] 3. Show the title, author name, publisher name, publication year, and number on hand for all books of which we have at least 10 copies on hand, and which were published by Knopf or published after 1985 (or both). Sort the output by the number of copies on hand, with the largest number of books on hand coming first. Books with the same number of copies hand should appear in alphabetical order by title. 4. For each author with any books with a publication year before 1940, give the author's name and the number of such books. Sort the results in order of the number of books, with the largest number of books appearing first. The number of books column should have the heading "number_of_books". 5. Show the number of titles and total number of copies on hand of books that have a retail price less than $20. These results should respectively be labeled "number_of_titles" and "number_of_copies". 6. Show the first name, last name, and number of physical books ordered (labeled "num_ordered") for all customers who have ordered at least 11 physical books. Sort the results alphabetically by last name, with customers having the same last name sorted alphabetically by first name.
Explanation / Answer
If you have any doubts, please give me comment...
-- 1. Show the first name, last name, city and e-mail of each customer in New Jersey
SELECT first_name, last_name, city, email
FROM customer
WHERE state = 'New Jersey';
-- 2. Show all book titles ordered on or after December 31, 1999. [Do not list any title more than once.]
SELECT DISTINCT title
FROM book
WHERE order_date >='1999-12-31';
-- 3. Show the title, author name, publisher name, publication year, and number on hand for all books of which we have at least 10 copies on hand, and which were published by Knopf or published after 1985 (or both). Sort the output by the number of copies on hand, with the largest number of books on hand coming first. Books with the same number of copies hand should appear in alphabetical order by title.
SELECT title, author_name, publisher_name, publication_year, number_of_copies
FROM book
WHERE number_of_copies >=10 AND (publisher_name = 'Knopf' || publication_year >=1985)
ORDER BY number_of_copies DESC, title;
-- 4. For each author with any books with a publication year before 1940, give the author's name and the number of such books. Sort the results in order of the number of books, with the largest number of books appearing first. The number of books column should have the heading "number_of_books".
SELECT author_name, COUNT(*) AS 'number of books'
FROM book
WHERE publication_year <1940
GROUP BY author_name
ORDER BY number_of_books DESC;
-- 5. Show the number of titles and total number of copies on hand of books that have a retail price less than $20. These results should respectively be labeled "number_of_titles" and "number_of_copies".
SELECT COUNT(*) AS number_of_titles, SUM(number_of_copies) AS number_of_copies
FROM book
WHERE price<20;
-- 6. Show the first name, last name, and number of physical books ordered (labeled "num_ordered") for all customers who have ordered at least 11 physical books. Sort the results alphabetically by last name, with customers having the same last name sorted alphabetically by first name.
SELECT first_name, last_name, COUNT(*) AS num_ordered
FROM customer C, orders O
WHERE C.customer_id =O.customer_id
GROUP BY customer_id
HAVING COUNT(*)>=11;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.