1) Based upon the contents of the BOOKS table, write a query which will retrieve
ID: 3554127 • Letter: 1
Question
1) Based upon the contents of the BOOKS table, write a query which will retrieve all books published in 2001. Run the query and include the returned results.
2)Based upon the BOOKS table, write a query which will retrieve all book titles that are in the Business or Computer category and have a retail price of more than $35.00. Run the query and include the returned results.
3) Based upon the BOOKS table, write a query that will retrieve all books stored in the BOOKS table with Pubid 1 or 2 or that have a retail price of atleast $42.00. Run the query and include the returned results.
4) Based upon the contents of the ORDERS table, write a query which will display only those orders shipped to the zipcode zone that begins with 323. Run the query and include the returned results.
5) Based upon the CUSTOMERS table, write a query which will display the name of each customer in lower case characters. Run the query and include the returned results.
6) Based upon the contents of the BOOKS table, write a query that will return the number of different publishers represented in the table. Run the query and include the returned results.
7) Based upon the BOOKS table, write a query which will display the retail price of the most expensive book provided by publisher 3. Run the query and include the returned results.
Explanation / Answer
--1) Based upon the contents of the BOOKS table, write a query which will retrieve all books published in 2001. Run the query and include the returned results.
SELECT *
FROM books
WHERE pubdate BETWEEN '01-JAN-2001' AND '31-DEC-2001';
--2)Based upon the BOOKS table, write a query which will retrieve all book titles that are in the Business or Computer category and have a retail price of more than $35.00. Run the query and include the returned results.
SELECT distinct title
FROM books
WHERE (UPPER(category) = 'BUSINESS'
OR UPPER(category) = 'COMPUTER')
AND retail > 35;
--3) Based upon the BOOKS table, write a query that will retrieve all books stored in the BOOKS table with Pubid 1 or 2 or that have a retail price of atleast $42.00. Run the query and include the returned results.
SELECT *
FROM books
WHERE pubid in (1, 2)
OR retail >= 42;
--4) Based upon the contents of the ORDERS table, write a query which will display only those orders shipped to the zipcode zone that begins with 323. Run the query and include the returned results.
SELECT *
FROM ORDERS
WHERE zipcode like '323%';
--5) Based upon the CUSTOMERS table, write a query which will display the name of each customer in lower case characters. Run the query and include the returned results.
SELECT LOWER(name)
FROM customers;
--6) Based upon the contents of the BOOKS table, write a query that will return the number of different publishers represented in the table. Run the query and include the returned results.
SELECT COUNT(DISTINCT pubid)
FROM books;
--7) Based upon the BOOKS table, write a query which will display the retail price of the most expensive book provided by publisher 3. Run the query and include the returned results.
SELECT MAX(retail)
FROM books
WHERE pubid = 3;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.