IT5433 VM SQL Server and Oracle Information

SQL Server & ORACLE information
IF you have problems with any CCSE computers including VLab computers please report it at  studenthelpdesk@kennesaw.edu

You can use CCSE Vlab or you can download and install SQL Server and Oracle on your own systems.

SQL Server: Free Microsoft programs available for download through OnTheHub
http://ccse.kennesaw.edu/student-resources.php
You need MS SQL Enterprise 2019

Oracle: Oracle Enterprise 18c, you can download and install it from the Oracle site db_home.

Table of Contents

SQL Queries and MS Word
CCSE Vlab
Oracle and SQL Developer
MS SQL Server 2019
How To Properly Disconnect from the VM very important!!

SQL Queries and MS Word

Be aware that when you type queries in MS Word and then paste them into Oracle or MS SQL you may have errors due to:

1. Smart quotes. To avoid this: in MS Word - Tools - Auto Correct - Auto Format - uncheck the box that says replace straight quotes with smart quotes.
2. Tabs
3. Unbreakable spaces

CCSE Vlab

Quick step-by-step guide:

The VMs are persistent but you should always save your code in case you need to run it again.

You can write all your code on the host machine and then paste it in the VM or save your files on OneDrive.

You can also use an USB drive or google docs inside the VM or shared folders from your host machine.

Unfortunately, now you cannot copy from VM and paste to the host.

Oracle

 Start Oracle VM and Start SQL Developer

 

Start SQL Developer (Note: Ignore  errors)

Create new connection

 Create new Database Connection

connection name: test
username: system
password: oracle18cdb
sid: oracle18cvm

Test the connection and if status is success, then click save, then connect.

 

Double click the connection, it will open new worksheet and your screen should be similar to one below

select * from dba_tables;

 

To execute SQL statement click GREEN ARROW

To execute several statements or PL/SQL code click RUNS SCRIPT BUTTON

SET SERVEROUTPUT ON
begin
   dbms_output.put_line('Hello!');
end;

To see compilation errors in SQL Developer find your procedure/function in Connection tree, right-click it and choose compile.

 

To display line numbers: Tools - Preferences - Code Editor - Line Gutter - Show Line Numbers

To change font size: Tools - Preferences - Code Editor - Fonts.

To change password, disconnect, right click the connection and choose reset password.

 

To connect to a PDB using SQL*Plus add @PDBname after the user name.

 

If you use SQL developer to connect, use service name samplescdb.win.kennesaw.edu

 

Always choose Disconnect and log off when you finished. It will release resource to other students to use.

 
MS SQL Server 2019

 Start SQL Server Management Studio (Search - type SQL Server management).

     OR  

Note: your server name might be different (use default server name). Click connect

Check that both AdventureWorks is listed under Databases.
To create a new query: right-click database name and choose "New Query".

 

If you want to execute all statements in a query window as a batch, just click Execute icon, all results will be displayed in the results window. NOTE: SQL queries in MS SQL Server are not case sensitive.

 

If you would like to execute only selected queries, just select those queries and click the same button. Only the resulst of highlighted queries will be displayed in the results window.

 
Always pay attention what database name is displayed in the drop box. In this case
select * from xxxx is the same as select * from adventureworks2014.xxxxx

How To Properly Disconnect from the VM

Always choose Disconnect and log off when you finished. It will release resource to other students to use.