A query is a command that is given to produce certain specified information from the tables.

SELECT command let you make queries on the database. It is the only SQL verb to cause an output to be displayed on monitor.

Syntax :

select column_name ,[column_name,.......]
from table_name;
                

Selecting Columns

---

To select desired column and view them.

select Name, Class
from Student;
    

copy

Note : The order of selection determines the order of display.

Selecting All Columns

---

To view complete list of columns as follows

select *
from Student;
    

copy

Eliminating redundant data by 'distinct' keyword

---

It eliminate duplicate rows from the result of a select statement.

select distinct SUPW
from Student;
    

copy

Note :

• Distinct keyword is applied to the entire row.


• It can be specified only once in select command.


• If clause select multiple rows then it will eliminate all rows which are same.


• Rows in which some values are different and some same will be retained.

Selecting from all rows using (ALL keyword)

---

It will give values of mentioned column from every row no matter whether there are duplicate values.

select all SUPW
from Student;
    

copy

Using Column Aliasing

---

The column that you select in a query can be given different name that is column alias name for output purposes

If you want to extract Age from ClubMembers table and want the name of Age column to be "Age of Members" then we should write :

select Age as "Age of Members"
from ClubMembers;
    

copy

Selecting specific row using WHERE clause

---

SQL enables you to define criteria to determine which row are selected for output.

The 'WHERE' clause in select statement specifies the criteria for selection of rows to be returned.

Syntax :

SELECT column1, column2, ...
FROM table_name
WHERE condition;
            

select Coach_name, Coach_id
from ClubMembers
where age > 35;
    

copy

Note : When 'where' clause is present then the database program goes through the entire table one by one at a time and examine where the given condition is true and that row is displayed in the output.

Select using LIMIT clause

---

It closes used to specify the number of records to return. It is useful on large table with a thousands of record, running a large amount of record can impact performance.

Syntax :

SELECT column_name1, column_name2,......
FROM table_name
WHERE condition
LIMIT number;
            

select Coach_name, Coach_id
from ClubMembers
where age > 35
limit 2;
    

copy

Aggregate Functions

---

1. MIN() : The MIN() function returns the smallest value of the selected column.

Syntax :

SELECT MIN(column_name)
FROM table_name
WHERE condition;
                

SELECT MIN(Age)
from ClubMembers;
                        

copy

2. MAX() : The MAX() function returns the greatest value of the selected column.

Syntax :

SELECT MAX(column_name)
FROM table_name
WHERE condition;
            

SELECT MAX(Age)
from ClubMembers;
                    

copy

3. SUM() : The SUM() function returns the total sum of a numeric column.

Syntax :
    
SELECT SUM(column_name)
FROM table_name
WHERE condition;
                

select sum(Age)
from ClubMembers;
                        

copy

4. AVG() : The AVG() function returns the average value of a numeric column.

Syntax :
    
SELECT AVG(column_name)
FROM table_name
WHERE condition;
                

select avg(Age)
from ClubMembers;
                        

copy

5. COUNT() : The COUNT() function returns the number of rows that matches a specified criteria.

Syntax :
    
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
                

select count(Coach_name)
from ClubMembers;
                        

copy

BETWEEN and NOT BETWEEN Operator

---

The between operator is used to retrieve value based on given range of values.The range includes both lower and upper value

Lets us understand by an example

Write an SQL statment to list the coaches whose age falls between 30 and 40.

    select * 
    from clubMembers
    where AGE BETWEEN 30 AND 40;
    
                        

copy

The operator NOT BETWEEN is jsut the reverse of the BETWEEN operator,that is the row which is not under the specified range of values will be retrieved

Lets us understand by an example

Write an SQL statment to list the coaches whose age does not fall between 30 and 40 from clubMembers Table.

(Click on this link for insertion and creation of ClubMembers table)

    select * 
    from clubMembers
    where AGE NOT BETWEEN 30 AND 40;
    
                        

copy

IN Operator in Select

---

Here In operator is used to select the values that matches with the given list of values

Lets us understand by an example

Write an SQL statment to display all the details of students which are in class 7th,8th and 9th from student table.

(Click on this link for insertion and creation of Student table)

    select * 
    from Student
    where Class IN (7,8,9);
    
                        

copy

Pattern Matching

---

In SQL,for pattern matching we use LIKE operator,this is string matching operator which is used with WHERE clause to search for a specified pattern in a columns

Here patterns are described using two special wildcard characters:

1. Percent(%)-This represent zero,one,or multiple characters
2. Underscore(_)-this represent one,single characters

SYNTAX

    SELECT col1,col2,....
    FROM table_name
    WHERE column_name LIKE pattern;
                

Lets us understand by an example

Write Query to display all the names of students whose names starts from "S".Use student table

(Click on this link for insertion and creation of Student table)

    select *
    from student
    where Name LIKE "S%";
    
                        

copy

Order By Clause

---

Here ORDER BY clause is used to sort the results or a query in a specified order.It allows sorting of query results by one or more columns

Sorting can be done either in ascending order and descending order,the default order is ascending.The data in the table is not sorted,only the result that will appear will be sorted

SYNTAX

    SELECT col1,col2,....
    FROM table_name
    [WHERE predicate]
    [ORDER BY col_name];
                

Lets us understand by an example

Write Query to display the list of coaches in the alphabetical order of their names.Use clubmembers table

(Click on this link for insertion and creation of Clubmembers table)

    SELECT * 
    FROM CLUB
    ORDER BY COACHNAME;
    
                        

copy