MS SQL SERVER & MYSQL SELECT database with DISTINCT, WHERE & ORDER BY Clause

The SELECT statement in SQL syntax is used to retrieve data from the tables that exist in the database that we want to retrieve. Displays this result in tabular format.

Syntax:

SELECT column_name FROM table_name;

The semicolon is used at the end of the query just to show that our query is completed, for example, If you write two or more select queries than each query is separated by a semicolon. And FROM is used to connect to the respective table we want to deal with. The syntax of MS SQL SERVER and MYSQL SELECT database is the same.

Demo DataBase:

Here is a demo database tables to help you better understand.

Here are the Structured Query Language queries to create your own tables just like this table. Just copy it and paste it in your SQL server or MySQL workbench and start practising on MS SQL SERVER or MYSQL SELECT database with DISTINCT, WHERE & ORDER BY Clause.

1ST:

CREATE TABLE DPT ( DPTNO INT, DNAME VARCHAR(30), DptLoc VARCHAR(20));

INSERT INTO DPT VALUES (1, 'ACCOUNTING', 'NEW YORK' ),(2, 'RESEARCH', 'DALLAS'),(3, 'SALES', 'CHICAGO' ),(4, 'OPERATIONS', 'BOSTON' ),(5, 'DBM', 'CANADA' );

2ND:

CREATE TABLE SALGRADE (GRADE INT ,LOSAL INT ,HISAL INT );

INSERT INTO SALGRADE VALUES(1, 700, 1200), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999)

Cases of SELECT Query:

Retrieve all the data from a table:

To retrieve all the data from the above table name as DPT we use the following query:

SELECT * FROM DPT;

the same Syntax is used in both MS SQL Server & MYSQL as shown below:

Practical Example:

MYSQL WORKBENCH retrieve all data from a table

MS SQL SERVER retrieve all data from a table 

Retrieve One Column from a table:

Write a statement that displays DNAME from DPT.

SELECT DNAME FROM DPT;

Practical Example:

MYSQL retrieve data

SQL SERVER retrieves a single column

Retrieve multiple Columns from a table:

Write a statement that displays the columns DPTNO and DNAME from DPT.

SELECT DPTNO, DNAME FROM DPT;

Practical Example:

MYSQL WORKBENCH fetch multiple columns

SQL SERVER fetch multiple columns

Retrieve data from multiple tables:

Write a Query that displays the data from both tables mentioned above.

SELECT * FROM DPT, SALGRADE;

[NOTE]

Both the tables are separated by a comma. This simple Query is working in the form that if both tables contain 5 & 5 rows than the result contain 5*5=25 rows this case of the statement is also known as CROSS JOIN that we will learn later.

Practical Example:

MYSQL DATABASE  from multiple tables

MS SQL data from multiple tables.

CLAUSES in SQL:

With Distinct Clause

The distinct clause is used to eliminate duplicate rows from the result of a Statement. If Distinct is not used, all rows are returned including duplicates.

Write a query to display all distinct LOC from DPT.

SELECT DISTINCT LOC FROM DPT;

Practical Example:

MYSQL DATABASE distinct statement

SQL SERVER difference b/w Simple & Distinct Statement

With Where Clause

Where clause is used to retrieve data from a table conditionally. It can appear only after From clause.

Example:

Write a query that displays records of departments located in NEW YORK from 1st table.

SELECT * FROM DPT WHERE Loc = 'NEW YORK ' ;

Practical Example:

MYSQL WHERE clause

SQL SERVER WHERE clause

[NOTE]

Structured Query Language uses single quotes around text values. Most database systems also accept double-quotes. Numeric values should not be enclosed in quotes.

With Order BY Clause

The Order BY clause is used to sort the row. The process of arranging data or records in a sequence is called SORTING. A sort can be ascending or descending. Structured query language uses ASC keyword to specify ascending sort and DESC keyword for descending sort, and by default, data is arranged in ascending order. Order BY must be the last clause in the Select statement.

Example:

Display records of departments located in NEW YORK from DPT table in descending order by DPTNO.

SELECT * FROM DPT WHERE Loc = 'NEW YORK ' ORDER BY DPTNO DESC ;

Practical Example

MYSQL WORKBENCH ORDER BY

SQL SERVER ORDER BY

Conclusion:

In this session, we have learned the bases of SQL development that is how to fetch data from the tables given to us and we also learn how to apply any conditions to our query by using WHERE clause, that is the most important keyword of Structured Query Language, in most cases, we have to deal with this keyword and in the end, we learn how to use ORDER BY clause, when we want our output in a specific arranged order we use it.