Please Answer using MYSQL (FOLLOW INFO GIVEN HERE this LINK) Include (SCREENSHOT
ID: 3719392 • Letter: P
Question
Please Answer using MYSQL (FOLLOW INFO GIVEN HERE this LINK) Include (SCREENSHOT INPUT & OUTPUT)
(QUESTION 4.D & 4.E)
https://www.chegg.com/homework-help/questions-and-answers/orders-fest-hane-lam-nae-130-uome-mplayee-id-order-line-1m-hem-id-1-10-item-garments-cest--q28912473
Screen shots of SQL queries you used to retrieve data from the tables along with the query results. For each query, explain in a complete, coherent sentence what it is intended to do, what data is expected as a result and how they are different from previous queries. Clearly identify each screen shot you paste into this section. To create queries, use the following structures 4. a. A query that is based on two tables and includes both an arithmetic and a special operator in the conditional expression. A query that is based on more than two tables and includes both an arithmetic and a special operator in the conditional expression. Both the arithmetic and the special operator must be different from the ones used in part a. A query that includes a sub-query A query that contains the GROUP BY clause, one of the aggregate functions, the WHERE statement, and the HAVING operator. Two additional complex queries designed by you that are different from the above. A complex query is based on two or more tables and includes either several conditions or a subquery (or both) b. c. d. e.Explanation / Answer
Hi Student,
Find the queries below:
a. SELECT CompanyName, ContactName, Phone FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.CustomerID >101 AND CompanyName = 'ABC';
b. SELECT EmployeeId, FirstName, LastName FROM Employee LEFT JOIN Orders ON Employee.EmployeeID = Orders.EmployeeId WHERE Employee.firstName <> 'JOHN' and .EmployeeId IN (101,201);
c. SELECT orderid,quantity from orders where customerid = (select customerid from customer where firstname = 'Mary');
d. Select EMPLOYEEID,CUSTOMERID FROM ORDERS WHERE IN_DATE < SYSDATE GROUP BY CUSTOMERID HAVING QUANTITY >200;
e.
SELECT FIRSTNAME,LASTNAME FROM EMPLOYEE INNER JOIN ORDERS ON
EMPLOYEE.EMPLOYEE_ID = ORDERS.EMPLOYEE_ID WHERE CUSTOMER_ID IN ( SELECT CUSTOMER_ID FROM CUSTOMER WHERE FIRST_NAME LIKE 'A%')
AND ORDER_ID IN ( SELECT DISTINCT ORDERID FROM ORDER_LINE INNER JOIN ITEM ON ORDER_LINE.ITEM_ID = ITEM.ITEM_ID
WHERE ITEM.COST >1000);
Happy Learning :)