Aqua Data Studio Access Aqua Data Studio Aqua

  • Slides: 22
Download presentation
Aqua Data Studio

Aqua Data Studio

Access Aqua Data Studio • Aqua Data Studio is available on i. Mac in

Access Aqua Data Studio • Aqua Data Studio is available on i. Mac in ILS Mac Lab (LI 002 A) • Download the latest version from official website (14 days free trial) (http: //www. aquafold. com/aquadatastudio. html) • For MS Windows user, you can download a free version (V 4. 7) from IU Ware. (http: //iuware. iu. edu/Windows/Title/585)

Enabling Semi-colon (; ) as Statement Separator

Enabling Semi-colon (; ) as Statement Separator

 • Aqua Data Studio has a default setting which prefers / and GO

• Aqua Data Studio has a default setting which prefers / and GO as statement separators for SQL statements. When working on your own with ADS, you have the option to either conform with this syntax requirement, or change the options in ADS to accept a semi-colon ( ; ) as a statement separator. • For this class, we are using prewritten SQL files that use a semi-colon as a statement separator. Different statement separators have different pros and cons; the semi-colon is the standard statement separator for command line and GUI interfaces with many SQL languages, so we have opted for the semi-colon. • In order for ADS to understand semi-colon separators , we must change some options inside the program.

File → Options • Under General subcategory, check ‘; ’ Statement separator

File → Options • Under General subcategory, check ‘; ’ Statement separator

Register Database Server (Postgre. SQL, My. SQL, Oracle)

Register Database Server (Postgre. SQL, My. SQL, Oracle)

Postgre. SQL Steps to register Postgre. SQL server 5 2 3 4 1 1.

Postgre. SQL Steps to register Postgre. SQL server 5 2 3 4 1 1. Select “Postgre. SQL” 2. Name your database 3. Login name: your IU username Password: your Postgre. SQL* 4. Host: db. ils. Indiana. edu Port: 5433 Database: your IU username 5. (Not yet to press OK) Switch to “Driver” tab *See the instruction about getting Postgre. SQL password

Steps to register Postgre. SQL server 6. Enter “? ssl=true” in Parameters box 7.

Steps to register Postgre. SQL server 6. Enter “? ssl=true” in Parameters box 7. Then press OK 6 7

Oracle • Get your username and password • Login Canvas → Go to course

Oracle • Get your username and password • Login Canvas → Go to course • Scrolling down in the left panel to find “Web. DB” section • Click “Web. DB” to find your username and password

Oracle Steps to register Oracle server 1. Select “Oracle 9 i/10 g/11 g” 2.

Oracle Steps to register Oracle server 1. Select “Oracle 9 i/10 g/11 g” 2. Name your database 3. Login name: your username Password: your password Uncheck “Save password” 4. Host: esdbd 110. uits. indiana. edu Port: 1521 (default) SID: oed 1 prd 2 3 4 1

My. SQL Steps to register My. SQL server 2 3 1. Select “My. SQL”

My. SQL Steps to register My. SQL server 2 3 1. Select “My. SQL” 2. Name your database 3. Login name: Your IU username Password: Your Postgre. SQL password* 4. Host: db. ils. Indiana. edu Port: 3306 (default) Database: Your IU username *The password is the same as your password for Postgre. SQL server 1 4

Start writing queries

Start writing queries

Queries 1. Click on database you are interested in (in this example My. SQL)

Queries 1. Click on database you are interested in (in this example My. SQL) 4. Click on icon Execute 2. Click on icon Query Analyzer 3. Write statements in window

Import Data in Tables Tools > Import

Import Data in Tables Tools > Import

Import Data in Tables 1. Choose Server 2. Click OK

Import Data in Tables 1. Choose Server 2. Click OK

Import Data in Tables 3. Click on browse to find your Comma Separated File

Import Data in Tables 3. Click on browse to find your Comma Separated File (. csv) – if the file extension doesn’t show up, make sure you add it at the end. 4. If your file has column names in the first line, make sure to click this option. 5. Click Next

Import Data in Tables 6. Choose the option “Import into Database” 7. Click Next

Import Data in Tables 6. Choose the option “Import into Database” 7. Click Next

Import Data in Tables 8. Click Next If your import is successful, you should

Import Data in Tables 8. Click Next If your import is successful, you should get this message.

Tips for Importing Data into Tables • Always back up your database before making

Tips for Importing Data into Tables • Always back up your database before making any changes. • It is a good idea to create the table in your database first and then import the data. • Make sure the. cvs file with the data you want to import has exactly the same structure as your table (same columns)

Export Data from Aqua Data Studio 1. Right click on the database you want

Export Data from Aqua Data Studio 1. Right click on the database you want to export data 2. Select “Tools → Export Data”

Export Data from Aqua Data Studio 1. Choose the schema to export 2. Select

Export Data from Aqua Data Studio 1. Choose the schema to export 2. Select object to export 3. Select tables to export 4. Click Next

Export Data from Aqua Data Studio 1. Select a folder to save data 3.

Export Data from Aqua Data Studio 1. Select a folder to save data 3. Select “; ” as statement separator 2. Select the data format to export. “INSERT Statements” will export SQL query files for each table 4. Click Next to export data