Notes:

Total mark is 100.
Use Oracle 11g, 12c DB Server or Zenit Server along with Oracle SQL Developer while answering questions. If you don't have installed on your home computer, I suggest that you install it first.
You can follow the documents that are on the Blackboard to install Oracle. It is located under Course Documents -> How to Documents.
Answer the questions using Oracle SQL Developer.
Your solutions should be saved as a word file or text file and sent to the Blackboard electronically. It is better if you have all the answers in one file with proper commenting including your student number, your name and question numbers. If you submit more than one file, you need to archive them in a ZIP or RAR file and submit only one file.
Any assignment that is submitted after due date will be punished with 10 points per day. It will be marked as zero after 2 days.
This is an individual assignment. You can discuss it with your friends and classmates but no more than one student should submit the same assignment. If it is done so, both students will be punished with zero.


DBS301 - Oracle 11g Notes
Image of book cover
Title	DBS301 - Oracle 11g Notes
Publisher	Oracle
Type	Print
Required
Description	Available at the Seneca bookstore.
Manual Entry Textbook
Oracle SQL By Example
Image of book cover
Title	Oracle SQL By Example
Author	Alice Rischert
ISBN	978-0137142835
Publisher	Prentice Hall
Publication Date	August 12, 2009
Type	Print
Recommended
Description	http://www.oraclesqlbyexample.com/
Manual Entry Textbook
Oracle 11G: SQL
Image of book cover
Title	Oracle 11G: SQL
Author	Joan Casteel
ISBN	978-1305268029
Publisher	Course Technology
Publication Date	June 25, 2015
Type	Print
Recommended
Description	Learn how to most effectively use PL/SQL programming language with one of the most popular and widely-used software programs in large companies today -- Oracle®11g.
Manual Entry Textbook
OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide
Image of book cover
Title	OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide
Author	Steve Ries
Publisher	Packt Publishing
Publication Date	December 5, 2011
Type	Print
Recommended
Description	Ace the 1Z0-051 SQL Fundamentals I exam and become a successful DBA by learning how SQL concepts work in the real world
Manual Entry Textbook
Oracle Database 11g SQL
Image of book cover
Title	Oracle Database 11g SQL
Author	Jason Price
ISBN	978-0071498500
Publisher	McGraw-Hill Education
Publication Date	November 26, 2007
Type	Print
Recommended
Description	Learn SQL to get results with the world’s top database platforms–Oracle for the enterprise and Microsoft Access for the desktop
Manual Entry Textbook
OCA/OCP Oracle Database 11g All-in-One Exam Guide: Exams 1Z0-051, 1Z0-052, 1Z0-053
Image of book cover
Title	OCA/OCP Oracle Database 11g All-in-One Exam Guide: Exams 1Z0-051, 1Z0-052, 1Z0-053
Author	Bob Bryla, John Watson, Roopesh Ramklass
ISBN	978-0-071-62921-8
Publisher	McGraw-Hill Education
Publication Date	September 5, 2009
Type	Print
Recommended
Manual Entry Textbook
Database Systems: Design, Implementation, & Management
Image of book cover
Title	Database Systems: Design, Implementation, & Management
Author	Carlos Coronel, Steven Morris
ISBN	978-1285196145
Publisher	Course Technology; 11 edition
Publication Date	February 4, 2014
Binding	http://www.amazon.com/Database-Systems-Design-Implementation-Management/dp/1285196147/ref=sr_1_1?s=books&ie=UTF8&qid=1453221520&sr=1-1&keywords=database+systems+design+implementation+%26+management+11th+edition
Type	Print
Required








Zenit User Name and Passwords
Attached Files:
File SQL Developer Connection (92.354 KB)
File User Names and Passwords (10.907 KB)
"User Names and Passwords" includes your user name with password for Zenit DB.

Students can login to Oracle Neptune DB from inside and outside of Seneca by following these steps:
- Run SQL Developer
- Create a new connection
- Use user name and  password for connection
- Change Connection Type to Advanced
- Type the following URL to Custom JDBC URL area
jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myoracle12c.senecacollege.ca)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = oracle12c)))
- Click on Save Password
- Click on Test. status should be Success
- Save
- Connect

If you want, you can change your password using ALTER USER. Please follow these steps to change your password.
- Connect to DB using either SQL Developer or SQL Plus
- Use your user name and initial password for connection
- After logging in, type the following statement
ALTER USER <your_user_name> IDENTIFIED BY <your_new_password>
Sample:
ALTER USER dbs301_131sa40 IDENTIFIED BY abc123
- Disconnect your connection
- Change your password on Properties
- Save and connect again. This time it will connect with your new password
- Keep your password in a safe place. You don't need to change it again





How to Create HR Schema
Attached Files:
File hr_schema.zip (12.638 KB)
Download attached HR_SCHEMA.ZIP file and extract it to your hard drive.

Execute the scripts in the following order using SQL Developer:

1 - hr_cre.sql

2 - hr_idx.sql

3 - hr_comnt.sql

4 - hr_popul.sql




Oracle 12c Connection
You can use this URL in order to connect Seneca Neptune database outside from the college. The followings need to be used in SQL Developer.

Custom JDBC URL


jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myoracle12c.senecacollege.ca)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = oracle12c)))

Item
Zenit Connection URL


https://zenit.senecac.on.ca/info/db-oracle11g.html

You can use this URL in order to connect Seneca Neptune database outside from the college. The followings need to be used in SQL Developer.

Custom JDBC URL


jdbc:oracle:thin:@(DESCRIPTION =  (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = zenit.senecac.on.ca)(PORT = 1521))  )(CONNECT_DATA =(SERVICE_NAME = neptune)(SERVER=DEDICATED)))


Temp user id: dbs301_171ma45

Temp password: 76481456

ALTER TABLE Statement
More links

http://www.tutorialspoint.com/sql/sql-alter-command.htm

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#SQLRF01001

Job_grade table
create table JOB_GRADES (
GRADE_LEVEL VARCHAR2(3),
LOWEST_SAL NUMBER,
HIGHEST_SAL NUMBER);

insert into JOB_GRADES (GRADE_LEVEL, LOWEST_SAL,HIGHEST_SAL)
values ('A',1000, 2999);
insert into JOB_GRADES (GRADE_LEVEL, LOWEST_SAL,HIGHEST_SAL)
values ('B',3000, 5999);
insert into JOB_GRADES (GRADE_LEVEL, LOWEST_SAL,HIGHEST_SAL)
values ('C',6000, 9999);
insert into JOB_GRADES (GRADE_LEVEL, LOWEST_SAL,HIGHEST_SAL)
values ('D',10000, 14999);
insert into JOB_GRADES (GRADE_LEVEL, LOWEST_SAL,HIGHEST_SAL)
values ('E',15000, 24999);
insert into JOB_GRADES (GRADE_LEVEL, LOWEST_SAL,HIGHEST_SAL)
values ('F',25000, 40000);
commit;