cookbook-1

console_1.sql

#1.1 - You have a table and want to see all of the data in it.
SELECT * FROM EMP;

#1.2 - You have a table and want to see only rows that satisfy a specific condition.
SELECT * FROM EMP WHERE DEPTNO = 10;

#1.3 - You want to return rows that satisfy multiple conditions.
SELECT * FROM EMP WHERE DEPTNO = 10 OR COMM IS NOT NULL OR SAL <= 2000 AND DEPTNO = 20;
SELECT * FROM EMP WHERE (DEPTNO = 10 OR COMM IS NOT NULL OR SAL <= 2000) AND DEPTNO = 20;

#1.4 - You have a table and want to see values for specific columns rather than for all the columns.
SELECT ENAME,DEPTNO,SAL FROM EMP;

#1.5 - You would like to change the names of the columns that are returned by your query.
SELECT ENAME, SAL AS SALARY, COMM AS COMMISSION FROM EMP;

#1.6 - You would like to change the names of the columns that are returned by your query
# and would like to exclude some of the rows using the WHERE clause.
SELECT * FROM (SELECT SAL AS SALARY, COMM AS COMMISSION FROM EMP) AS X WHERE SALARY < 5000;
SELECT SALARY, COMMISSION FROM (SELECT SAL AS SALARY, COMM AS COMMISSION FROM EMP) AS X WHERE SALARY < 5000;
SELECT SAL AS SALARY, COMM AS COMMISSION FROM EMP WHERE SAL < 5000;

#1.7 - You want to return values in multiple columns as one column.
SELECT CONCAT(ENAME, ' WORKS AS A ' ,JOB) AS MSG FROM EMP;

#1.8 - You want to perform IF-ELSE operations on values in your SELECT statement.
SELECT ENAME,SAL,
       CASE WHEN SAL <= 2000 THEN 'UNDERPAID'
            WHEN SAL >= 4000 THEN 'OVERPAID'
            ELSE 'OK'
       END AS STATUS
FROM EMP;

SELECT ENAME AS NAME, SAL AS SALARY,
       CASE WHEN SAL <= 2000 THEN 'UNDERPAID'
            WHEN SAL >= 4000 THEN 'OVERPAID'
            ELSE 'OK'
       END AS STATUS
FROM EMP WHERE DEPTNO = 10;

#1.9 - You want to limit the number of rows returned in your query. You are not concerned with order; any n rows will do.
SELECT * FROM EMP LIMIT 5;

#1.10 - You want to return a specific number of random records from a table. You want to modify the following statement such that
# successive executions will produce a different set of five rows:
SELECT ENAME, JOB FROM EMP ORDER BY RAND() LIMIT 5;

#1.11 - You want to find all rows that are null for a particular column.
SELECT * FROM EMP WHERE COMM IS NULL;

#1.12 - You have rows that contain nulls and would like to return non-null values in place of those nulls.
SELECT COALESCE(COMM,0) AS COMMISSION FROM EMP;
SELECT ENAME, JOB, COALESCE(COMM,0) AS COMMISSION FROM EMP;

#1.13 - You want to return rows that match a particular substring or pattern.
SELECT ENAME, JOB FROM EMP WHERE DEPTNO IN (10,20);
SELECT ENAME, JOB FROM EMP WHERE DEPTNO IN (10,20) AND (ENAME LIKE '%I%' OR JOB LIKE '%ER');