Discuss the below in detail:
Q1: Employee table contains the following information: state, lastname, firstname, hiredate, department, salary.
Get all employees that satisfy the following conditions:
- from the state of Kansas
- hired during the month of Mar-99
- sorted by lastname
- salary greater than 20,000
Q2: Each week a food wholesaler company fulfills orders from various customers. Normally, each customer orders the same quantity of certain items each week. Occasionally, the quantity of an item that a customer orders is significantly less than the customer's usual quantity. The information about each order is stored in a table named Invoice shown below:
CREATE TABLE Invoice (
InvoiceID int NOT NULL,
InvoiceNumber char(10) NOT NULL,
CustomerName char(30) NOT NULL,
InvoiceAmount money NOT NULL DEFAULT (0),
CONSTRAINT PK_Invoice PRIMARY KEY (InvoiceID)
)
You want to identify any pattern to these unusual orders. To do this, you need to produce a list of the invoices for each customer that is for a lesser amount than the average invoice amount for that customer.