CS320 Lab1
CS320 - Database Administration
Lab #1
Download Oracle 11g Express Edition either 64bit or 32bit version that the
Instructor has provided. Load and run the CS320.sql script and you then should be able to answer this lab. Please submit queries as download the results in a CSV file format and submit it to the course canvas. This covers SQL Part A (Chapters 2,8&
9 from the text Oracle 11g Sql by Joan Casteel
1. Create a list containing the publisher’s name, the person usually contacted, and the publisher’s telephone number. Rename the contact column “Contact Person” in the displayed results.
2. Determine which categories are represented in the current book inventory. List each category only once.
3. Create a list of authors that displays the last name followed by the first name for each author. The last names and first names should be separated by a comma and a blank space. 4. List all information for each order item, include an item total, which can be calculated by multiplying the Quantity and Paideach columns. Use a column alias for the calculated value to show the heading “item total” in the output.
5. Create a mailing list from the customers table. The mailing list should display the name, address, city, state, and zip code for each customer. Each customer’s name should be listed in order of last name followed by first name, separated with a comma, and have the column header “Name”. The city and state should be listed as one column of output, with the values separated by a comma and the column header “Location”.
6. To determine the percentage of profit for a particular item, subtract the item’s cost from the retail price to calculate the dollar amount of profit, and then divide the profit by the item’s cost. The solution is then multiplied by 100 to determine the profit percentage for each book. For the column displaying the percentage markup, use “Profit %” as the column heading.
7. Which customers live in New Jersey? List each customer’s last name, first name,
Lab #1
Download Oracle 11g Express Edition either 64bit or 32bit version that the
Instructor has provided. Load and run the CS320.sql script and you then should be able to answer this lab. Please submit queries as download the results in a CSV file format and submit it to the course canvas. This covers SQL Part A (Chapters 2,8&
9 from the text Oracle 11g Sql by Joan Casteel
1. Create a list containing the publisher’s name, the person usually contacted, and the publisher’s telephone number. Rename the contact column “Contact Person” in the displayed results.
2. Determine which categories are represented in the current book inventory. List each category only once.
3. Create a list of authors that displays the last name followed by the first name for each author. The last names and first names should be separated by a comma and a blank space. 4. List all information for each order item, include an item total, which can be calculated by multiplying the Quantity and Paideach columns. Use a column alias for the calculated value to show the heading “item total” in the output.
5. Create a mailing list from the customers table. The mailing list should display the name, address, city, state, and zip code for each customer. Each customer’s name should be listed in order of last name followed by first name, separated with a comma, and have the column header “Name”. The city and state should be listed as one column of output, with the values separated by a comma and the column header “Location”.
6. To determine the percentage of profit for a particular item, subtract the item’s cost from the retail price to calculate the dollar amount of profit, and then divide the profit by the item’s cost. The solution is then multiplied by 100 to determine the profit percentage for each book. For the column displaying the percentage markup, use “Profit %” as the column heading.
7. Which customers live in New Jersey? List each customer’s last name, first name,