CUSTOMER_EX2 table contains following information: CustID CustName Address City
ID: 3810336 • Letter: C
Question
CUSTOMER_EX2 table contains following information:
CustID
CustName
Address
City
State
Zip
INVOICE_EX2 Table contains following information:
Inv_ID
Inv_Date
Order_Date
Cust_ID
Emp_ID
Day_sales
Note:
Inv_ID is the invoice_ID
EmployeeID is the ID of employee who made the sale
Order_date is the date product was ordered
Inv_date is the date customer was billed
Day_ sales is the amount of sales($) made by that employee
EMPLOYEE_EX2 Table contains following information
Emp_ID
Emp_Name
HireDate
EmpComm (in %)
Please help with the following SQL queries using the information provided above. (SQLPlus)
1.Increase the commission of all employees by 12%
2.Give the names of customers who ordered between 9/12/2015 and 9/05/2016 (this needs a subquery, I believe)
3. Give the names of customers that have 2 A’s in their name
4. Give the names of customers that have not yet ordered.
5. How many orders are made by each customer.
6. Give the names of customers of employee Alex, K(Hint: use a subquery or a join)
7. Give the count of days when the difference between invoice and order is more than 100 days. Hint: difference between dates gives number of days
8. Give the total salary (commission * day_sales) for each salesperson
9. Give the id of employee making more than 2 sales.
Explanation / Answer
1.Increase the commission of all employees by 12%
Update table Employee_Ex2 SET EmpComm = (EmpComm + (EmpComm * .12))
2.Give the names of customers who ordered between 9/12/2015 and 9/05/2016 (this needs a subquery, I believe)
Select CustName AS CustName
From CUSTOMER_EX2 C
Inner Join INVOICE_EX2 I ON C.CustID = I.Cust_ID
Where I.Order_Date >= '9/12/2015' AND I.Order_Date <= '9/05/2016 '
3. Give the names of customers that have 2 A’s in their name
Select C.CustName From CUSTOMER_EX2 C
Where C.CustName like '*A*'
4. Give the names of customers that have not yet ordered.
Select CustName AS CustName
From CUSTOMER_EX2
Where CustID NOT IN (Select distinct Cust_ID From INVOICE_EX2)
5. How many orders are made by each customer.
Select Cust_ID, Count(Inv_ID) From INVOICE_EX2 Group By Cust_ID Order By Cust_ID DESC
6. Give the names of customers of employee Alex, K(Hint: use a subquery or a join)
Select CustName AS CustName From CUSTOMER_EX2 C
INNER JOIN INVOICE_EX2 I ON I.Cust_ID = C.CustID
INNER JOIN EMPLOYEE_EX2 E ON I.Emp_ID = E.Emp_ID
Where E.Emp_Name = 'Alex'
7. Give the count of days when the difference between invoice and order is more than 100 days. Hint: difference between dates gives number of days
Select Inv_ID, DATEDIFF(day,Inv_Date,Order_Date) AS DaysCount
From INVOICE_EX2 Where DATEDIFF(day,Inv_Date,Order_Date) > 100
8. Give the total salary (commission * day_sales) for each salesperson
Select E.Emp_Name, (E.EmpComm * I.Day_sales) AS Total Sales
From INVOICE_EX2 I
INNER JOIN EMPLOYEE_EX2 E ON I.Emp_ID = E.Emp_ID
9. Give the id of employee making more than 2 sales.
Select E.Emp_ID From EMPLOYEE_EX2 E
Where (Select COUNT(Inv_ID) From INVOICE_EX2 I Where I.Emp_ID = E.Emp_ID ) >= 2