Oracle Databases Create database

Part 1: Install Oracle Database

Install Oracle Database 11gR2 or later. You will be installing a docket container with an Oracle Database install on this environment for your assignment.

Once you install Oracle Database, use the following procedure must be followed to access the Oracle database server:

//$                               is the system prompt

$ sqlplus                      use sqlplus to issue sql statements

Part 2: Create Relations and Populate Data

Using the dataset provided, you are required to populate your database with the given data and test data with the queries in Part3. Use the excel data files for this. The excel file has tabs in the bottom for each data section.

1. Create a “createdb.sql” file that includes required CREATE TABLE statements to create required tables, primary keys, define foreign keys and referential integrity constraints, table constraints, etc
   • Indicate Primary Keys and table constraints where required
   • Use reference for foreign keys and specify what action should be taken in case of update and/or deletion of the referenced tuple/row (i.e., cascade, reject, or set default/null).
2. Include INSERT statements in your “createdb.sql” file to populate all the provided data based on the skeleton data provided using the excel data files. SQL Syntax:

INSERT INTO table_name (column1, column2, column3, …)

VALUES (value1, value2, value3, …);

• Execute “created.sql” script in Oracle to create all the tables and populate the data. To do so, login to Oracle database, as shown in the important notes above:

$ sqlplus

SQL> @createdb.sql

To execute a script file in sqlplus, type @ and then the file name. The above command assumes that the file is in the current directory. (ie: the current directory is usually the directory that you were located in before you launched SQLPlus.) This command would run a script file called created.sql.

• Create a “dropdb.sql” file to drop all the tables that are created by createdb.sql. SQL Syntax:

DROP TABLE <table_name>

Part 3: Queries

Write the following queries in Oracle SQL and run them on your database developed as mentioned in Part 2 of this assignment. Depend on the data, your query might not return any data, but it does not mean your query is wrong.

1. Count the number of businesses having business category name as “National Parks” situated in Arizona
2. Find the users who were born in CA but never visited CA
3. Find every business in California that has the word “Coffee” (case-sensitive) in its name but is not classified as a coffee place (i.e. has no word “Coffee” in any of its categories). List the business id, bid, and name in ascending bid order.
4. List the users who are Male and complimented at least one female friend
5. Find all the businesses whose reviewers have at least 1 friend in their friend list. Order by number of reviews (decreasing), break ties by business ID (increasing).
6. Get the review counts for businesses in each business category. Results should be sorted by the Review counts in descending order.

Submission Guidelines

1. Your submission should include one createdb.sql file, one dropdb.sql file, 6 .sql files for queries described in part 3 (named q1.sql to q6.sql), and one readme.txt file.
2. createdb.sql file should create required tables and populate all the provided data based on the skeleton data provided. This file is needed in order to check queries.
3. The dropdb.sql file should drop all tables that are created by createdb.sql. This file is needed to drop all database tables.
4. q1.sql ~ q6.sql query files should contain SQL statements for queries Q1 to Q6 as described in part 3. If you need to write two or more SQLs for ONE step, then they should be written after each other in ONE file.
5. Make sure to properly test created.sql, dropdb.sql and the query files (q1.sql…q6.sql) before submission.
6. The readme.txt file must have the list of tables that your createdb.sql file generates and execution result of query files (q1.sql…q6.sql).
7. You must make a .zip file to include all of your files in one file. Your zip file should contain createdb.sql dropdb.sql readme.txt q1.sql q2.sql q3.sql q4.sql q5.sql and q6.sql files.

Additional Information

Please provide step on setup of Oracle DB with Docker on Mac using Netbeans. Especially on how to have the ability to pass .sql files with @ command and not obtaining the following error:

Reference:
Refer to Oracle SQL reference manual for information on how to create tables, indexes, insert data, etc. (http://docs.oracle.com/cd/E11882_01/server.112/e41084.pdf).