Đang chuẩn bị liên kết để tải về tài liệu:
Oracle SQL Plus The Definitive Guide- P15

Không đóng trình duyệt đến khi xuất hiện nút TẢI XUỐNG Tải xuống

Oracle SQL Plus The Definitive Guide- P15:Every day, computer professionals the world over wake up, travel to the office, sit down in front of a computer, and begin another day working with that database called Oracle. Programmers write queries and stored procedures. Database administrators monitor performance, make database changes, and perform other maintenance tasks. Operations people may need to back up or recover a database. | previous page page_117 next page Page 117 Taking Advantage of Unions A union is a SQL construct that allows you to knit together the results of several SQL queries and treat those results as if they had been returned by just one query. I find them invaluable when writing queries and one of the more creative uses I ve discovered involves using unions to produce reports that need to show data grouped by categories and that may need to show the same records in more than one of those categories. A typical example A good example of this type or report would be one that fulfills the following request Produce an employee turnover report that lists everyone employed at the beginning of the year everyone hired during the year everyone terminated during the year and everyone still employed at the end of the year. The report should be divided into four sections one for each of those categories. This is not an unusual type of request not for me at least. The interesting thing about this request though is that every employee will need to be listed in exactly two categories. That means you would need to write a query that returned each employee record twice in the correct categories. When you are faced with this type of query it can be helpful to simplify the problem by thinking in terms of separate queries one for each category. It s fairly easy to conceive of a query to bring back a list of employees that were on board at the beginning of the year. You just need to make sure the first of the year is between the hire and termination dates and account for the fact that the termination date might be null. Here s the query to return a list of people employed as of January 1 1998 SELECT employee_id employee_name employee_hire_date employee_termination_date FROM employee WHERE employee_hire_date TO_DATE 1-Jan-1988 dd-mon-yyyy AND employee_termination_date IS NULL OR employee_termination_date TO_DATE 1-Jan-1988 dd-mon-yyyy This gives you the first section of the reportthose employed