Write the following SQL statements in Microsoft Access by using the Books databa
ID: 3773051 • Letter: W
Question
Write the following SQL statements in Microsoft Access by using the Books database from Week 2 Assignment 2. Once complete, copy and paste the SQL statements in a Microsoft Word document:
Write SQL statements:
To update the publisher name from READ WITH US to READ FOR US
To verify the updated name field for the publisher with ID 6
To make the following updates to the Publisher table (be careful with WHERE):
Make Contact="John Travolta" for publisher with ID number 4
Make Phone=888-999-7777 for TEXT AND MORE
To list the customer numbers and names of all individuals who have purchased books in the information systems category
To identify the book written by an author with the last name Cain. Perform the search by using the author name
To produce a list of all the customers who live in the state of Indiana and have ordered books on information systems
To display a list of all the books in the Books table. If a customer has ordered a book, list the corresponding order number and the state in which the customer resides
To identify the authors of the books ordered by Sheila Smith. Perform the search by using the customer name
To list the customer numbers and names of all the individuals who have purchased books in the children category
To add the following items to the Books table:
To delete the last record added
To update the name Susan Smythe to Susan Smythe-Brown
ISBN Title Author PubDate Cost Retail Category PubID 1234567890 A Good Book John Jeffries 10/11/2005 13.5 15.00 Non-Fiction 7 2314569874 Trains for Everyone Cory Berkey 12/15/2011 10.25 25.25 Children 8 1598745682 Eating In Susan Smythe 09/09/2012 20.00 30.50 Cooking 6 99954123654 Another Vampire Story Ann Oats 09/05/2010 11.23 13.52 Fiction 4 1025874159 Databases are Fun! Tony Stevens 01/01/2013 4.12 5.23 Information Systems 9Explanation / Answer
1) select distinct (c.customerID) ,c.firstname,c.lastname from customers c ,books b,orders o where o.customerid = c.customerid and o.ISBN = b.ISBN and b.category = 'Information System';
2) select author from books where author like '%Cain%';
3) select c.firstname,c.lastname from customers c ,books b,orders o where o.customerid = c.customerid and o.ISBN = b.ISBN and c.state = 'IN' and b.category = 'Information System';
4) select b.title,b.ISBN,o.orderid,c.state from customers c ,books b,orders o where o.customerid = c.customerid and o.ISBN = b.ISBN;
5) select b.author from customers c ,books b,orders o where o.customerid = c.customerid and o.ISBN = b.ISBN and c.lastname = 'Smith' and c.firstname = 'Sheila';
6) select c.customerid, c.firstname,c.lastname from customers c ,books b,orders o where o.customerid = c.customerid and o.ISBN = b.ISBN and b.category = 'Children';
7) delete from books where ISBN = '1' order by pubdate DESC LIMIT 1;
8) update customers set author = 'Susan Smythe-Brown' where author = 'Susan Smythe';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.