Databases | SQL | Oracle | MS Access Answers

write a quesry to add the commison and gets it total

Salesmen

salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12

The following stored procedures are required :

a) Return store details showing purchases done on a given date and the current stock of a given product

b) The system should ensure that during the quality check, if the product has a defect, it should be recorded in the

defect table and an appropriate message should be displayed

SQL Statements to:

a) Find Total sales for a particular date

b) List available items having a given color

c) List available stock of clothes on a particular date with their accompanying including the store with that stock

d) List new stock received on a given date (order by branch)

The following stored procedures are required :

a) Return store details showing purchases done on a given date and the current stock of a given product

b) The system should ensure that during the quality check, if the product has a defect, it should be recorded in the

defect table and an appropriate message should be displayed

Exercise: Auto Repair

• You are designing a database for an automobile repair shop. When a customer brings in a vehicle, a service advisor will write up a repair order. This order will identify the customer and the vehicle, along with the date of service and the name of the advisor.
• A vehicle might need several different types of service in a single visit. These could include oil change, lubrication, rotate tires, and so on.
• Each type of service is billed at a pre-determined number of hours work, regardless of the actual time spent by the technician.
• Each type of service also has a flat book rate of dollars-per-hour that is charged.
• Describe each entity in English.
• Describe each association in English (both directions).
• Draw the entity diagram, including association entities if required.

Write a query to create a view that shows the average and total orders for each salesman after his or her name. (Assume all names are unique)

 

R1: Salesman

salesman_id | name | city | commission

-------------+------------+----------+------------

5001 | James Hoog | New York | 0.15

5002 | Nail Knite | Paris | 0.13

5005 | Pit Alex | London | 0.11

5006 | Mc Lyon | Paris | 0.14

5007 | Paul Adam | Rome | 0.13

5003 | Lauson Hen | San Jose | 0.12

Write a procedure and call it into main pl/sql program to find a factorial of a number.

Write a procedure to calculate factorial of a number entered by user.

Write a query to create a view that shows the average and total orders for each salesman after his or her name. (Assume all names are unique)

R1: Salesman

salesman_id | name | city | commission

-------------+------------+----------+------------

5001 | James Hoog | New York | 0.15

5002 | Nail Knite | Paris | 0.13

5005 | Pit Alex | London | 0.11

5006 | Mc Lyon | Paris | 0.14

5007 | Paul Adam | Rome | 0.13

5003 | Lauson Hen | San Jose | 0.12

R2: Orders

ord_no purch_amt ord_date customer_id salesman_id

---------- ---------- ---------- ----------- -----------

70001 150.5 2012-10-05 3005 5002

70009 270.65 2012-09-10 3001 5005

70002 65.26 2012-10-05 3002 5001

70004 110.5 2012-08-17 3009 5003

70007 948.5 2012-09-10 3005 5002

70005 2400.6 2012-07-27 3007 5001

70008 5760 2012-09-10 3002 5001

70010 1983.43 2012-10-10 3004 5006

70003 2480.4 2012-10-10 3009 5003

70012 250.45 2012-06-27 3008 5002

70011 75.29 2012-08-17 3003 5007

70013 3045.6 2012-04-25 3002 5001

Write a program in PL/SQL to show the uses of SQL%FOUND to determine if a DELETE statement affected any rows.

Write a block in PL/SQL to print the specific number of rows from a table.