Write 3 paragraphs on the following:
- What was most compelling to you in this course?
- How did participating in the discussion board enhance your learning abilities?
- Is there anything you are uncertain about as it relates to physical security?
|
||
10% of your final grade (Covers chapters 7,8,10,11)
1. In Exercise 2.6, related to sales forecasting, the following business requirements were set. A-Oil & Chemical is chemical company that plans to create a database to forecast sales.
· A salesperson is responsible for a lead to sales. Each lead consists of the responsible salesperson, the customer targeted, date occur, projected date, projected sale amount and possibility of the sale to occur.
· Each Salesperson is specified by : First name, last name, telephone, date of hire
· Each customer is specified by title, address, telephone
· Leads that became sales are marked as “s” for success. Leads that fail are marked as “F” for fail. Leads that not have a final outcome yet are marked as “I” for idle.
The following diagram reflects the design for the database.
Guidelines
1. Attend and participate in assignment labs
2, . For each of the questions above create a clear screen shot that will include the database name, the SQL command and the produced results. Make sure that you have tested the results for correctness
3. show your work to your professor and get green light allowing you to submit assignment 1. Instructor will sign the evaluation rubric allowing you to submit.
7. Submit report on line on BB and print the report and hand it to your instructor. For late submissions 2 marks off for late submission penalty applies.
Exercises (chapter 7-10)
1. Create a view VE1 that will customer_id, cust_title and the total amount for each customer.
2. You want the same group results as in 1 but only for customers with total amount more than 25,000 (HAVING). Can you do it with a consecutive view VE2 from VE1, if not why? If cannot do it as consecutive do it as new view VE3.
3. Create a view VV4 to list customer_id, amount, possibility, cust_title. Create a consecutive view from VV4, named High_Possibility, for leads with possibility >80. Create a consecutive view from VV4 called TX_CUST_LIST to list the same three attributes for only the Texas customers. Why you cannot do It?
4. Using the ROUND function create an SQL query that will COUNT LEADS in possibilities in 10S. Show only 10s with that counted more than three possibilities. Shorted by 10s.
5. Using the FLOOR function show create an SQL query that will COUNT LEADS in AMOUNTS IN EVERY 5000 but with only for leads with amount more than the average amount. Shorted by 5000s.
6. Write an SQL query that will display the customers as customer title in capital, underscore, City with first letter in Capital and the rest in lower characters, dot, state in capital,dot, and zip code inside brackets [], dot , telephone the first three character in parenthesis followed by a dash . Example:
NCR_Houston.TX.[55120].(345)-99345625
7. Write an SQL query that will lead list all leads with expected day 2000 days before today.
8. Using the MONTH and YEAR functions list the count of leads for each month in 2015.
9. Using the MONTH function list the count of leads for all years but for only even months. Use the MOD function.
10. Using the script editor, create your user defined function (choose the name of the function) that will accept as parameter LEAD_ID multiply (amount * possibility) and return it as possible sale. Show the scripts and run the command in the SQL command.
11. Create your own function any for example concatenate first and last names of slpsr
12. Create the SQL command to show the data dictionary. In ORACLE APEX, choose one of items (for example, APEX_WORKSPACE_APEX_USERS) displayed it using the SELECT command. Show results with screenshots.
13. Create an index named T_INDEX on table CUSTOMER on attribute CUST_TITLE. What happens when you try to attempt to select from T_INDEX?
14. For the following GROUP BY SQL Statement
15. SELECT LEAD__ID, SLPRS_ID, SUM(AMOUNT) AS AMOUNT FROM LEAD GROUP BY (LEAD_ID, SLPRS_ID) ORDER BY LEAD_ID, SLPRS_ID;
Exercise a rollup and a cube group. Show the results and mark 2-D rollups and 3 dimensional cubes.
Self evaluation Rubric Submission Green Light
Instructor’s Signature
Student Name and ID:________________________________________________
SQL -exercises – 3.4 marks
| Item | Evaluation criteria | Student mark | Instructor mark |
| Query creation | For each of the 12 questions that is answered correctly add 0.3 marks |
Assignment meeting – 2 marks
| Item | Evaluation criteria | Student mark | Instructor mark |
| Participation | Add 2 points if you actively participate in the assignment lab throughout the session
Remove 1 point if you were silent and not actively participate and if you were disturbed by other things like texting on your phone Remove 1 point if you were absent(even excused) or leave before the session ends, or come without |
Report – 1.6 mark
| Item | Evaluation criteria | Student mark | Instructor mark |
| Present queries | Screen shot that includes the workspace name at top. If not take off 0.5 points for each item. | ||
| Present | Show changes in tables after the commands. If not take off 0.5 points for each item. | ||
| Evaluation rubric | Did you include the signed evaluation rubric in the report. If not put 0 on final grade. | ||
| Submit report | Did you submit report on BB and printed version. If not put 0 for final grade. | ||
| Late submission | For late submission take off 2 marks from final grade |
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but splendidwritings.com proved they are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 14***| Rating: ⭐⭐⭐⭐⭐
"The company has some nice prices and good content. I ordered a term paper here and got a very good one. I'll keep ordering from this website."
