SQL Queries for Employee Table Manipulation and Analysis

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

3. Inserting into a Table

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/OracleLecture1/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?

10. More SQL: Delete • Let’s downsize the company! • Find all employees that have names that start with the letter ‘B’ • use like ‘<chars>%’ in the where clause • What is going to happen if we delete this employee?

11. More SQL: Delete • Who are BLAKE’s employees? • Who are the other managers? • Jones is about to move up… assign all of BLAKE’s employees to Jones. • Delete the employee BLAKE • COMMIT your changes

13. More SQL: Groups & Joins • Rank by department name, the best departments to work for in terms of average salary

14. SQL*PLUS Misc. • Find out what tables you have: • select * from user_catalog • Find out attributes for a specific table: • desc emp; • Save your sql*plus session to a file: • spool session • … • spool off • results of session will be in session.lst

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