I need some example code for these 12 in mysql: 1-Retrieve subset of columns. 2-
ID: 3853946 • Letter: I
Question
I need some example code for these 12 in mysql:
1-Retrieve subset of columns.
2-Uses a simple condition.
3-Uses a compound condition.
4-Uses the LIKE, IN, or BETWEEN operator.
5-Uses an aggregate function.
6-Uses the GROUP BY clause.
7-Uses joins to retrieve data from more than one table.
8-Use the IN or EXISTS operator.
10-Use a subquery.
11-Performs an inner or outer join.
12-Uses ALL or ANY operators.
I need some example code for these 12 in mysql:
1-Retrieve subset of columns.
2-Uses a simple condition.
3-Uses a compound condition.
4-Uses the LIKE, IN, or BETWEEN operator.
5-Uses an aggregate function.
6-Uses the GROUP BY clause.
7-Uses joins to retrieve data from more than one table.
8-Use the IN or EXISTS operator.
10-Use a subquery.
11-Performs an inner or outer join.
12-Uses ALL or ANY operators.
Explanation / Answer
DROP TABLE IF EXISTS CUSTOMER
DROP TABLE IF EXIST ORDERS
CREATE TABLE CUSTOMER
(
CUSTID INT ,
NAME VARCHAR(20),
COUNTRY VARCHAR(15),
PRIMARY KEY(CUSTID)
);
CREATE TABLE ORDERS
(
ORDERID INT primary key,
CUSTID INT,
AMOUNT NUMERIC(5,2),
ORDERDATE DATE,
FOREIGN KEY (CUSTID) REFERENCES CUSTOME(CUSTID)
);
INSERT INTO CUSTOMER VALUES(1, "JOHN", "GERMANY");
INSERT INTO CUSTOMER VALUES(2, "JOSEPH", "MEXICO");
INSERT INTO CUSTOMER VALUES(3, "PETER", "GERMANY");
INSERT INTO CUSTOMER VALUES(4, "BOB", "SWEDEN");
INSERT INTO CUSTOMER VALUES(5, "ALICE", "MEXICO");
INSERT INTO CUSTOMER VALUES(6, "HENRY", "SWEDEN");
INSERT INTO ORDERS VALUES(1, 1, 100, "2017-07-01");
INSERT INTO ORDERS VALUES(2, 1, 200, "2017-07-02");
INSERT INTO ORDERS VALUES(3, 2, 300, "2017-07-03");
INSERT INTO ORDERS VALUES(4, 3, 350, "2017-07-04");
QUERIES
1-Retrieve subset of columns.
============================
SELECT CUSTID, NAME FROM CUSTOMER;
The above query only retrieves the CUSTID and NAME columns from the CUSTOMER TABLE
2-Uses a simple condition.
==========================
SELECT * FROM CUSTOMER WHERE COUNTRY = "GERMANY";
The above query retrives customers living in Germany
3-Uses a compound condition.
===========================
SELECT * FROM ORDERS
WHERE CUSTID=1 AND AMOUNT>150;
The above query uses compound condition with AND, to find orders with more than $150 for AMOUNT by customer with CUSTID =1
4-Uses the LIKE, IN, or BETWEEN operator.
========================================
SELECT * FROM CUSTOMER WHERE NAME LIKE "JO%";
The above query returns customers whose name begins with "JO".
SELECT * FROM CUSTOMER WHERE COUNTRY IN ("GERMANY", "SWEDEN");
The above query returns customers living in MEXICO or SWEDEN
5-Uses an aggregate function.
============================
SELECT CUSTID, SUM(AMOUNT) FROM ORDERS WHERE CUSTID = 1;
The above query uses aggregate function sum() to get total of all amounts from customer with CUSTID=1
6-Uses the GROUP BY clause.
===========================
SELECT CUSTID, SUM(AMOUNT)
FROM ORDERS
GROUP BY CUSTID;
The above query retrieves total amount for each of the customer, by grouping on CUSTID
7-Uses joins to retrieve data from more than one table
=====================================================
SELECT O.CUSTID, C.NAME, O.ORDERDATE, O.AMOUNT
FROM ORDERS O
INNER JOIN CUSTOMER C ON (O.CUSTID = C.CUSTID);
The above query joins the customer and orders table on common column custid to match the customer names and display their order details
8-Use the IN or EXISTS operator and 10-Use a subquery.
=======================================================
SELECT * FROM CUSTOMER C
WHERE EXISTS (SELECT AMOUNT FROM ORDERS O WHERE O.CUSTID = C.CUSTID);
The above query uses a subquery and also EXIST to retrieve only those customers who have placed some orders i.e. have records in orders.
11-Performs an inner or outer join.
=================================
Shown in query 7
12-Uses ALL or ANY operators.
=============================
SELECT * FROM CUSTOMER WHERE CUSTID = ANY(SELECT CUSTID FROM ORDERS WHERE AMOUNT >= 200);
Retrieves customers who have placed orders more than 200