Oracle SQL Sample Commands Worksheet
The following provides some pre-work to help you get started on the Oracle
assignments. The following items are included in this document:
•
•
•
Information about the Oracle demonstration database included in Oracle
Application Express
A set of problems that require you to create and query tables in Oracle
Express 11g
Solutions to the problem set provided
Take the time to execute at least some of the SQL commands presented below.
This will give you a chance to get comfortable with working in the Oracle
command execution environment.
You may complete the work for this class with either the Application Express
Editor or the Oracle Command Line editor. The Oracle Command Line Editor is
not a full screen editor, you cannot copy and paste, and you cannot correct
mistakes after you execute a command. The Application Express Editor is a full
screen editor which most students prefer to use. If you have not used it before,
there is a very short learning curve on Application Express. Most students find it
worth the time.
Oracle Express 11g comes with two demonstration databases already built. We
will be working with the simpler of the two located in Application Express. In order
to access the demonstration database you must have created a new user
workspace in Application Express. (See Part 5: Application Express Editor usage
information at the end of this document)
BEFORE CONTINUING: Enter the Application Express environment and
create your workspace as described in Part 5. Application Express Editor
Usage information included at the end of this document.
© 2015 Laureate Education, Inc.
Page 1 of 9
Part 1. You may want to start by creating a UML Class Diagram of the database.
This is optional, but is a good exercise and provides a nice visual representation
of the Primary Key and Foreign Key relationships in the database. This diagram
is very simple.
The “describe” command is used to list attributes and data types for a specified
table.
describe emp
Note the following tables are included in Oracle Application Express as part of
the demonstration database. You do not need to create the tables, they already
exist.
DEPT
DEPTNO
DNAME
LOC
EMP
EMPNO
ENAME
JOB
MGR
HIRE_DATE
SAL
COMM
DEPTNO
© 2015 Laureate Education, Inc.
NOT NULL
NUMBER(2)
VARCHAR2(14)
VARCHAR2(13)
NOT NULL
NUMBER(6)
VARCHAR2(10)
VARCHAR2(9)
NUMBER($)
DATE
NUMBER(7,2)
NUMBER (7,2)
NUMBER(2)
Page 2 of 9
Part 2. Use the Application Express Editor to execute the commands you create
below. The Application Express Editor is a full screen editor. You can enter
commands and change any and all lines as you move along. Enter the
commands in the upper screen; the results are displayed in the lower screen.
Blank lines on the input screen may create problems. For instructions on how to
set up and use Application Express in Oracle 11g, see Part 5 of this document
(“Application Express Editor Usage”).
Execute each command in Oracle.
Show the SQL commands to do the following:
1. Create a new table named JOBS that includes the following attributes and
constraints:
JOB_ID
NOT NULL
VARCHAR2(10)
JOB_TITLE
NOT NULL
VARCHAR2(35)
MIN_SALARY
NUMBER(8,2)
MAX_SALARY
NUMBER(8,2)\
Additional Constraint: JOB_ID must start with the character “J”.
2. Create a new table named JOB_HISTORY that includes the following
attributes and constraints:
EMPNO
START_DATE
END_DAT
JOB_ID
DEPTNO
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NUMBER(6)
DATE
DATE
VARCHAR2(10)
NUMBER(2)
The primary key for the JOB_HISTORY table is EMPNO +
START_DATE.
EMPNO and JOB_ID are all foreign keys linking JOB_HISTORY to
the appropriate tables.
3. Create a new table named SALGRADE:
GRADE
LOSAL
HISAL
NUMBER(6)
NUMBER(6)
NUMBER(6)
4. Oops! We forgot to include the foreign key DEPTNO in the
JOB_HISTORY table linking it to DEPT. Use the ALTER TABLE
command to add this foreign key to JOB_HISTORY.
5. List all employees working in department 20.
6. List the name and job description of all employees hired after January 1,
1982.
© 2015 Laureate Education, Inc.
Page 3 of 9
7. List the name and employee id for all employees whose name starts with
“A”, sort by last name.
8. List all employees with salaries between 6600 and 3000, sort the output
by descending salary value.
9. Show the minimum and average salary for all employees
10. Show the deptno, minimum and average salary, and number of
employees in each department grouped by deptno.
11. Show the same information as in #10 above, but only include employees
with a salary above $1600.
12. Show the same information as in #10 above, but only include departments
where the average salary is below $2,000
13. List the dname, ename, and hiredate for all employees sorted by dname
within that by ename (must use join)
14. List the dname, ename and location for all employees in the accounting,
sales, and operations departments (use the IN operator). Sort the output
by ename. (must use join)
15. List the dname, ename, salary, and grade for employees ‘JONES’,
‘ALLEN’ and ‘JAMES’. (must use join)
© 2015 Laureate Education, Inc.
Page 4 of 9
Part 3. Solutions to problems:
1. CREATE TABLE jobs (
job_id
VARCHAR2(10) CONSTRAINT check_job_id CHECK (job_id LIKE 'J%’),
job_title
VARCHAR2(35)
NOT NULL,
min_salary
NUMBER(8,2)
NOT NULL,
max_salary
NUMBER(8,2)
NOT NULL,
PRIMARY KEY (job_id)
);
2. CREATE TABLE job_history (
Empno
Number(6)
NOT NULL,
Start_date
DATE
NOT NULL,
End_date
DATE
NOT NULL,
job_id
VARCHAR2(10),
Deptno
NUMBER(2),
PRIMARY KEY (Empno, start_date),
FOREIGN KEY (Empno) REFERENCES emp,
FOREIGN KEY (job_id) REFERENCES jobs,
FOREIGN KEY (Deptno) REFERENCES depart
);
3. CREATE TABLE sal_grade (
Grade
Losal
Hisal
Number(6),
Number(6),
Number(6));
4. ALTER TABLE job_history
ADD FOREIGN KEY (deptno) REFERENCES dept;
5. Select *
from emp
where deptno = 20;
6. Select ename, job
from emp
where hire_date > '01-JAN-82';
7. Select ename, empno
from emp
where ename like 'A%'
order by ename;
8. select *
from emp
where salary between 6600 and 3000
order by salary desc;
9. select min(salary), avg(salary)
from emp;
10. select deptno, min(salary), avg(salary), count (*)
from emp
group by deptno;
© 2015 Laureate Education, Inc.
Page 5 of 9
11. select deptno, min(salary), avg(salary), count (*)
from emp
where salary > 1600
group by deptno;
12. select deptno, min(salary), avg(salary), count (*)
from emp
group by deptno
having avg(salary) < 2000;
Join commands below include both the old-style and new-style join solutions.
13. select dname, ename, hiredate
from emp, dept
where dept.deptno = emp.deptno
order by dname, ename;
select dname, ename, hiredate
from emp natural join dept
order by dname, ename;
14. select dname, employeet_name, location
from emp, dept
where dept.deptno = emp.deptno
and dname IN ('ACCOUNTING', 'SALES', 'OPERATIONS')
order by ename;
select dname, ename, email
from emp natural join dept
where dname in ('ACCOUNTING', 'SALES', 'OPERATIONS')
order by ename;
15. select dname, ename, salary, grade
from emp, dept, salgrade
where dept.deptno = emp.deptno
and salary between losal and hisal
and ename in (‘JONES’, ‘ALLEN’, ‘JAMES’);
select dname, ename, salary, grade
from emp natural join dept
join salgrade on salary between losal and hisal
where ename in (‘JONES’, ‘ALLEN’, ‘JAMES’);
© 2015 Laureate Education, Inc.
Page 6 of 9
Part 4. Optional work using the command line editor and the HR database.
If you would like to also have practice working with the Oracle command line
editor, you can access the HR database, the following command will log you in
as user HR in the command line editor, allow you to create a password for HR,
and provide a listing of tables in that database.
alter user HR account unlock identified by yournewHRpassword;
Select table_name from all_tables where tablespace_name = ‘USERS’
Next you must connect to Oracle as the user HR. At the SQL prompt(s) enter:
Connect
HR
yournewHRpassword
Enter the following command to list the HR tables. You may use the describe
command to get a listing of the attributes for each table.
Select table_name from user_tables;
Describe Regions;
etc,
You may now access the HR tables as listed above.
© 2015 Laureate Education, Inc.
Page 7 of 9
Part 5. Application Express Editor Usage once Oracle Express 11g Installed
Note: If you are using a newer or different version of Oracle, these instructions
may need to be modified accordingly. Contact your Instructor for more
information.
•
From Windows Start Menu:
Oracle Express 11g
Getting Started
•
Within browser window that opens :
Select “Application Express” from the menu.
•
Login:
Username: System
Password: YourPasswordCreatedForSystem
•
Create a new user workspace:
Database User Name: george (Just use your first name here)
Application Express name: george
Password: MyFavoritePassword
Click on: Create Workspace
•
Click on: Already have an account? Login Here
Enter credentials as requested:
Password: MyFavoritePassword
•
Enter the SQL Workshop
Select SQL Commands
•
In the top box, you may now enter SQL commands using a full-screen
editor.
The bottom box shows the results.
•
Enter a SQL command – Click “Run” to execute
• Repeat until assignment completed
• To view a History of commands so you can copy and paste, click
the "History" tab above the lower box.
To return to Application Express for another session in your newly created
workspace:
Repeat steps 1–3 above
In the “Getting Started” box on the right select "Already have an account?
Login Here".
Enter your newly created Application Express credentials
Click on SQL Workshop
Click on SQL Commands
Cut and paste all commands to Microsoft Word!
© 2015 Laureate Education, Inc.
Page 8 of 9
For additional information on the use of SQL Workshop click the "Help" tab while
in the SQL Workshop application.
© 2015 Laureate Education, Inc.
Page 9 of 9
Expense
Payroll Department
Login
UserName:Char
Password: Varchar
Expenseld: Int (PK)
Vendorld: Int
VendorName: String
ExpenseDate: Datetime
ExpenseAmount: Float
Payrollld: Int(PK)
Employeeld: Int(FK)
EmployeeName: String
EmployeeWorkedHours: Float
PayrollDate: Datetime
1..1 EmployeegrossPay: Float
EmployeePayRate: Float
Employee GrossPaid: Float
+access (in Employeeld: string, in password : String) String
+verification ( in Employeeld: string, in password: String): String
1..1
1..1
+Create expense.
+Changevendor(int Vendorrld),
+EditExpenseAmount(Float ExpenseAmount),
+Delete Expense
10.*
Customer
1. *
1..1
+CreatePayrollo
+AdjustHours Worked(Loat Hours Worked),
+AdjustEmployeePayRate),
+CreateDeduction(Float GrossPay),
|+DeletePayrollo
Invoice
.1
1 *
1..1
Customerld: Int(pk)
CustomerName: String
CustomerAddress: String
CustomerEmail: String
CustomerPhoneNo: Int
CustomerCreditCardinf: String
Customer Shippinginf: String
0.
1.*
Employee
Report decription
Payroll Rate
Invoiceld: Int (PK)
Customerld: Int (FK)
CustomerName: String
InvoiceAmount: Float
Expense Date: Datetime
Employeeld: Int (PK)
EmployeeName: String
Employee Address: Varchar
EmployeeEmail: String
EmployeeDOB: String
EmployeePhoneNo: int
EmployeePosition: Int
+Registero
+Logino
+Update
Reportid: Int (PK)
Employeeld: Int (FK)
Date: Datetime
ReportType: String
Employeeld: Int
PayRateld: Int
EmployeePayRate: Float
Effective Date: Datetime
+CreateInvoice:
+EditinvoiceAmounto: String
+Deletelnvoice(String): Boolean
1..1
1..
1..*
+GenerateReporto
+PrintReporto
+getEmployeeldo
+getEmployeePayRate(): Float
Order
+UpdateEmployee Details
+AddEmployee Details
+Delete Employee Details
+SearchEmployee
1..1
Payment
0..*
Product
Tax
1 *
Orderld: Int (PK)
OrderCustomerld: (FK)
OrderDate: String
OrderAddress: String
OrderTotal: String
OrderStatus: String
OrderPrice: Double
OrderCommision: String
PaymentMethod: String
PaymentReceived: Currency
PaymentDate: Date
0.*
Productid: Int(PK)
ProductName: String
ProductDescription: String
ProductPrice: Currency
+ addPaymento: Boolean
+editPaymento: Boolean
+removePaymento: Boolean
TaxID: Int (PK)
Employeeld: int (FK)
TaxName: String
TaxType: String
TaxRate: String
Fita Rate: Int
FicaRate: Int
Salary
+Create Ordero
+EditOrder(IntOrderld)
|+ Delete Ordero
+addProducto
+modifyProcto
|+showProducto
+ deleteProducto
+CalcTax 0
+getTaxRate 0:string
0.*
1..*
0..
Empld: Int (PK)
EmpName: Varchar
month: String
date: Datetime
SalaryRate: Flow
Deductions: Int
PayrollRate: Flow
0..
Vendor
0.*
Supplier
+ getSalinfo (in Empld: int): String
Sale
Vendorld: Int(PK)
VendorName: String
VendorAddress: String
VendorCity: String
Vendor State: String
VendorphN: String
Productld: Int
SaleDate: Datetime
Sale Transaction: Int
Supplierld: Int(PK)
SupplierName: String
0.* SupplierPhNo: String
SupplierAddress: String
1..*
Yatavia Summers
&
Maria Orellana
+ReceivePaymento
+ Supply Stock
+TrackOrders
+getVendorldo
+getVendorName): String
+getVendorphNo: String
+getProductidt(int)
+getSalesDate : Datetime
+getSalesTransaction(Int)
Purchase answer to see full
attachment