1 / 15
Télécharger la présentation

SQL Queries for Employee Table Manipulation and Analysis

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. Inserting into a Table

  3. Selecting from a Table • Return a list of all employees and associated data • List all employees with salary less than 1000

  4. Executing .sql files • Goto the following URL: • http://www.stonehill.edu/compsci/cs325/OracleLecture1/sqlplus5.html

  5. 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?

  6. 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?

  7. 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?

  8. 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

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

  10. 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

  11. 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.

More Related