glasses in front of computers with random code on the screen

January 24, 2019 - By Parwinder Singh

Select Statements:

In this blog, I would like to show some useful SELECT queries which are really helpful for SQL beginners to query data and retrieve the desired results.

–SIMPLE SELECT STATEMENT TO RETRIEVE ALL RECORDS FROM SPECIFIED TABLE:

SELECT * FROM HCMWORKER;

–SELECT STATEMENTS TO RETRIEVE RECORDS WITH CERTAIN CRITERIA FROM SPECIFIED TABLE:

SELECT DISTINCT CREATEDBY FROM HCMWORKER where CREATEDBY = ‘admin’;

SELECT * FROM HCMWORKER WHERE PERSON BETWEEN 22565422589 AND 22565422591;

SELECT * FROM HCMWORKER WHERE PERSONNELNUMBER = ‘000001’;

SELECT * FROM HCMWORKER WHERE PERSONNELNUMBER >000005 AND PERSONNELNUMBER <=000010;

SELECT * FROM HCMWORKER WHERE PERSONNELNUMBER = 000005 OR PERSONNELNUMBER=000010;

SELECT * FROM HCMWORKER WHERE MODIFIEDBY = ‘MIA’

AND (PERSONNELNUMBER=000003 OR PERSONNELNUMBER=000005);

SELECT * FROM HCMWORKER WHERE MODIFIEDBY=‘MIA’

OR PERSONNELNUMBER=000005;

–IDENTIFY THE OUTPUT OF QUERY WITH LINE NO ASSOCIATED TO THE TABLE WHEN RUNNING MANY SELECT STATEMENTS TOGETHER:

SELECT ‘1’ AS [LINENO], * FROM BATCHJOB

SELECT ‘2’ AS [LINENO], * FROM BATCHGROUP

–“IN” AND “NOT IN”:

SELECT * FROM HCMWORKER WHERE MODIFIEDBY IN (‘MIA’,‘JODI’,‘LUKE’)

ORDER BY MODIFIEDBY;

–“IN” AND “NOT IN”:

SELECT * FROM HCMWORKER WHERE MODIFIEDBY NOT IN (‘MIA’, ‘LUKE’);

–CONCATING TWO OR MORE COLUMNS:

SELECT CONCAT(MODIFIEDBY, ‘,’,CREATEDBY) FROM HCMWORKER;

–CONCATING TWO OR MORE COLUMNS:

SELECT CONCAT(CREATEDBY,‘,’,MODIFIEDBY) AS CONCATNATION FROM HCMWORKER;

–ADDNING 500 TO DATA VALUES:

SELECT ID, FIRSTNAME, LASTNAME, SALARY+500 AS SALARY1 FROM SALARY;

–UPPER AND LOWER CASE CONVERSION:

SELECT UPPER(FIRSTNAME) AS “FIRST NAME”, LOWER(LASTNAME) AS “LAST NAME” FROM SALARY;

–SQUARE ROOT FUNCTION:

SELECT SALARY, SQRT(SALARY) FROM SALARY;

–AVG FUNCTION:

SELECT AVG(SALARY) FROM SALARY;

–SUM FUNCTION:

SELECT SUM(SALARY) AS ‘TOTAL SALARY’ FROM SALARY;

–CONDITIONAL OPERATOR:

SELECT FIRSTNAME, SALARY FROM SALARY

WHERE SALARY < 3100

ORDER BY SALARY ASC;

SELECT FIRSTNAME, SALARY FROM SALARY

WHERE SALARY > (SELECT AVG(SALARY) FROM SALARY)

ORDER BY SALARY DESC

–LIKE OPERATOR:

SELECT * FROM SALARY WHERE FIRSTNAME LIKE ‘A%’;

SELECT * FROM SALARY WHERE LASTNAME LIKE ‘SM%’;

MIN FUNCTION:

SELECT MIN(SALARY) AS SALARY FROM SALARY;

–JOINING TWO TABLES:

SELECT Customers.ID, Customers.Name, Orders.Name, Orders.Amount

FROM Customers, Orders

WHERE Customers.ID=ORDERS.Customer_ID

ORDER BY CustomerS.ID;

–INNER JOIN AND JOIN ARE SAME:

SELECT HCMWORKER.PERSON, HCMWORKER.PERSONNELNUMBER, DIRPERSONNAME.FIRSTNAME, DIRPERSONNAME.LASTNAME

FROM HCMWORKER

INNER JOIN DIRPERSONNAME

ON HCMWORKER.PERSON=DIRPERSONNAME.PERSON;

–LEFT JOIN RETURNS ALL ROWS FROM THE LEFT TABLE, EVEN IF THERE ARE NO MATCHES IN THE RIGHT TABLE:

SELECT HCMWORKER.PERSONNELNUMBER, DIRPERSONNAME.RECID

FROM HCMWORKER LEFT OUTER JOIN DIRPERSONNAME

ON HCMWORKER.PERSONNELNUMBER=DIRPERSONNAME.PERSON;

–THE RIGHT JOIN RETURNS ALL ROWS FROM THE RIGHT TABLE, EVEN IF THERE ARE NO MATCHES IN THE LEFT TABLE:

SELECT table1.column1, table2.column2

FROM table1 RIGHT OUTER JOIN table2

ON table1.column_name = table2.column_name

–UNION REMOVES DUPLICATES BUT UNIONALL DOES NOT. UNIONALL IS FASTER THAN UNION:

SELECT Name, ID FROM Customers

UNION ALL

SELECT  NAME, ID FROM Orders

In next SQL blog, I will show you some useful queries to manipulate data in SQL database.

Share this story: