Database SELECT DML statement usage example and explanation

SELECT is a DML statement for retrieving data. The SELECT DML statement is usually used when searching data in a database TABLE.

SELECT DML statement structure description

SELECT DML statement example code.

-- syntax
SELECT [ TOP | COLUMN1, COLUMN2 | ALL | DISTINCT]  
FROM table name  
[ WHERE condition ]  
[ GROUP BY Attribute name ]  
[ HAVING condition( ]  
[ ORDER BY Attribute name [ ASC | DESC ];

SELECT DML statement example description.

[ ] : SQL reserved words in square brackets can be used optionally when necessary. | : You can use one of the available grammars.

SELECT DML statement example

COLUMN, ALL, DISTINCT example of SELECT.

**syntax code.**  
SELECT [DISTINCT] {*, column [Alias]}  
FROM table name;

USE sampleDB;
DROP TABLE IF EXISTS UserInfoForSelect;
CREATE TABLE UserInfoForSelect (
        StudentId       int,
        StudentName     varchar(255),
        KorScore        int,
        MathScore       int,
        EngScore        int,
        ScienceScore    int
);
 
insert into UserInfoForSelect(StudentId, StudentName, KorScore, MathScore, EngScore, ScienceScore)
 values(202401,'john',90,97,93,100)
      ,(202402,'Obama',90,70,100,100)
      ,(202403,'Trumb',20,15,5,5)
      ,(202404,'bush',50,15,35,25)
      ,(202405,'Biden',60,70,75,50)
      ,(202406,'Biden',80,90,75,50);
  
-- 1. COLUMN,ALL,DISTINCT example
-- Print all columns
   SELECT *
    FROM UserInfoForSelect;

  -- Print only necessary COLUMN
   SELECT StudentId, StudentName, KorScore
    FROM UserInfoForSelect;

  -- Print after removing duplicates
   SELECT  DISTINCT StudentName 
    FROM UserInfoForSelect;

SELECT DML statement WHERE condition example.

For the conditions, print only students with a KorScore score of 50 or higher.

-- 2. WHERE condition example 
SELECT  * 
  FROM UserInfoForSelect
  WHERE KorScore > 50;

SELECT DML statement GROUP BY example.

Please print duplicate students in the StudentName column only once.

-- 3. GROUP BY example
SELECT  StudentName 
  FROM UserInfoForSelect
GROUP BY  StudentName

HAVING clause example

• StudentName를 이용하여 학생이름이 같은 사람을 알려주세요.

-- 4. HAVING clause example
SELECT  StudentName  
  FROM UserInfoForSelect
GROUP BY  StudentName 
HAVING count(StudentName) > 1;

ORDER BY clause example.

• Print out students with good Korean scores first.

-- 5. ORDER BY clause example
SELECT  *  
  FROM UserInfoForSelect
ORDER BY  KORSCORE  DESC;

MSSQL SELECT TOP function

Purpose of using MSSQL TOP

Other databases use limit, but mssql only uses top, so be careful. The execution result returns N.

• Question: Please tell me three students with low Korean scores.

-- MSSQL TOP  
SELECT  TOP 3 *  
FROM UserInfoForSelect
ORDER BY  KORSCORE  ASC;