Creating a Table

1
Creating a Table

• Create a table, emp, containing
• empno a 4 digit employee number
• ename up to 10 character string
• job up to 9 character string
• mgr a 4 digit employee number
• hiredate a date
• salary a 7 digit number with two decimal places
  after the decimal point
• comm a 7 digit number with two decimal places
  after the decimal point
• deptno a 2 digit number

2
(No Transcript)
3
Inserting into a Table
empno ename job mgr hiredate salary comm deptno
7369 'SMITH' 'CLERK' 7902 '17-DEC-80' 800 NULL 20
7499 'ALLEN' SALESMAN' 7698 '20-FEB-81' 1600 300 30
7521 WARD' 'CLERK' 7698 22-FEB-81' 1250 500 30
4
(No Transcript)
5
Selecting from a Table

• Return a list of all employees and associated
  data
• List all employees with salary less than 1000

6
Executing .sql files

• Goto the following URL
• http//www.stonehill.edu/compsci/cs325/OracleLectu
  re1/sqlplus5.html

7
More SQL

• Get the list of all employees again how many are
  there now?
• What is the largest monthly salary?
• What is the name of the employee who makes that
  salary?
• How many times greater is the largest salary
  compared to the smallest salary?

8
More SQL Update

• What job type makes the lowest salary?
• Get the ename, job, and salary for all employees
  with this job type.
• Give all employees with this job type a 10
  raise.
• Get the ename, job, and salary for all employees
  with this job type again to observe your raise.
• Type rollback
• Get the ename, job, and salary for all employees
  with this job type again what happened?

9
(No Transcript)
10
More SQL Delete

• Lets downsize the company!
• Find all employees that have names that start
  with the letter B
• use like ltcharsgt in the where clause
• What is going to happen if we delete this
  employee?

11
More SQL Delete

• Who are BLAKEs employees?
• Who are the other managers?
• Jones is about to move up assign all of BLAKEs
  employees to Jones.
• Delete the employee BLAKE
• COMMIT your changes

12
(No Transcript)
13
More SQL Groups Joins

• Rank by department name, the best departments to
  work for in terms of average salary

14
SQLPLUS Misc.

• Find out what tables you have
• select from user_catalog
• Find out attributes for a specific table
• desc emp
• Save your sqlplus session to a file
• spool session
• spool off
• results of session will be in session.lst

15

1. List products in order of popularity.
2. List in order of popularity, and include a
   description of the product.
3. What is the most popular product (include its
   description), and how much of the product has
   been sold?
4. List products in order by revenue generated.
5. List products in order by revenue and include a
   description of the product.
6. What product (including description) generated
   the most revenue and what was that revenue?
7. What is the total number of orders and average
   revenue of an order for each customer ranked by
   average revenue of an order?
8. What is the name of the customer who buys the
   most product?
9. What is the name of the customer who buys the
   lest product?
10. Rank sales people by revenue generated.