ITSE 2309 LAB 1 Database and SQL Queries
Submit: Query Statements Query Output
Chapters 2,3,4,5 11g SQL book
Objectives: Be able to access ORACLE/SQL Plus Be able to do simple SQL operations
– Log onto the sample Oracle 11g database created.
– See Chapter 7 in “Database Systems” for information on coding, saving, and executing your queries.
– Save the output of the queries by using the SPOOL option ( see chpt 14 in 11g SQL in lab resources tab)
– Save both the queries code and the output of the queries as “Lab1_LastName.txt and submit via Blackboard—using the – Attach file – Upload function
Do the following queries:
1. List all columns and rows in the stock table.
2. List the last name, first name, and company of all customers (List the columns in that order). Place the list in alphabetical order by company name.
3. List the company names for all customers from Sunnyvale, Redwood City, or San Francisco.
4. List all orders that were placed between the dates 12/31/1999 and 01/03/2000. List order number, order date, customer number, ship date, and paid date. (Hint: Specify year in single quotes ‘DD-MMM-YYYY’)
5. List the order number, order date, and shipping charges for all orders that are not on backlog and for which the shipping charge is over $15.00.
6. List all stock items which are baseball items which have a unit price greater than $200.00 and a manufacturer code which starts with ‘H’. (Hint: use LIKE)
7. List the company name for all customers who have orders. Don not list a company more than once.
8. List the customer number and the description (from the stock table) of all items ordered by customers with customer numbers 104-108. Order the output by customer number and description. (There should be no duplicate rows in your output).
9. List the number of (distinct) customers having an order. Label the column “Total_Customers_with_Orders”.
10. For each customer having an order, list the customer number, the number of orders that customer has, the total quantity of items on those orders, and the total price for the items. Order the output by customer number. (Hint: You must use a GROUP BY clause in this query).
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more