untung99.homes: Introduction to SQL Basics SQL Tutorial for Beginners

SELECT

It is the most basic SQL query one can use for manipulating a database. The select command is used to select the data from the database and display it to the user.

Syntax:

Select column 1, column 2…..column N
From Table;

Example:

Select name From Student;

The above example will display all the names from the student table. If we want to display all the fields in the table then we have to use *(Star) operator. This will display the entire table.

Example: 

Select * from Student;

If we want to display certain field without any duplicates then we use the DISTINCT keyword along with the select command. 

Example:

Select DISTINCT FName From Student;

WHERE

If we need only certain records from the table then we use the where clause. Where clause acts as a Filtering mechanism. Under the Where section we need to specify certain conditions, only if those conditions are met the records will be extracted.

Syntax:

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

Example: 

SELECT FName FROM Students
WHERE City='Delhi';

AND, OR, NOT

If we need to add two or more conditions in the where clause then we can use the above-mentioned operators. These keywords will add more complexity to the query. 

• AND Operator: This operator displays a record if all the conditions separated by AND are TRUE.

          Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

          Example:

SELECT * FROM Student
WHERE FName='John' AND Lname='Doe';

•  OR Operator: This operator displays a record if any of the conditions separated by OR is TRUE.

           Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

           Example:

SELECT * FROM Student
WHERE FName='John' OR Lname='Doe';

•  NOT Operator: This operator displays a record if the condition/conditions are NOT TRUE.

           Syntax:

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

           Example:

SELECT * FROM Student
WHERE NOT Lname='Doe';

INSERT INTO

If we want to insert any new record or data into a table then we can use the INSERT query. We can use the Insert into in two ways:

• Here we specify the column names for which we need to insert the record.

           Syntax:

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2, value3, ...);

           Example:

Insert into Student(studentID, FName, LName, Address, City, Marks)
Values (101, ‘JHON’,’DOE’,’#21, MG ROAD’,  ‘Bengaluru’, 550);

• In this, we don’t have to specify the columns of the table. But make sure the order of the values is in the same order as the columns in the table.

           Syntax:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

          Example:

INSERT INTO Student VALUES (102, ‘Alex’,’Cook’,’#63, Brigade ROAD, NEAR HAL’, ‘Bengaluru’, 490);

If we want to insert into specific columns then we need to follow the below method.

        Example:

INSERT INTO Student(studentID, FName) VALUES (103, ‘Mike’);

AGGREGATE FUNCTIONS

An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria and a single value is returned. We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement. We will be discussing GROUP BY, ORDER BY and HAVING later in this section. Some of the Aggregate functions are COUNT, SUM, AVG, MIN, MAX.

Let’s discuss each one by one. 

•  COUNT(): This function returns the number of rows that match specified criteria.

             Syntax:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

             Example:

SELECT COUNT (studentID)
FROM Student;

• AVG(): This function returns the average value of a numeric column.

            Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

            Example:

SELECT AVG(Marks)
FROM Student;

• SUM(): This function returns the total sum of a numeric column.

            Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

             Example:

SELECT SUM(Marks)
FROM Student;

• MIN(): This function returns the smallest value of the selected column.

           Syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;

           Example:

SELECT MIN(Marks) AS LeastMarks
FROM Student;

• MAX(): This function returns the largest value of the selected column.

            Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;

            Example:

SELECT MAX(Marks) AS HighestMarks
FROM Student;

Note: We have used aliasing here (AS new_name), Which we will be discussing in a short while.

GROUP BY, HAVING, ORDER BY

These keywords (GROUP BY, HAVING, ORDER BY) are used in a query to increase the functionality. Each one of them has a specific role to play.

• GROUP BY: This functionality is used to arrange a similar type of data into a group. For instance, if the column in a table consists of similar data or value in different rows then we can use GROUP BY function to group the data.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s);

Example:

SELECT COUNT(StudentID), Fname
FROM Student
GROUP BY Fname;

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition;

SELECT Fname, SUM(Marks)
FROM Student
GROUP BY Fname
HAVING SUM(Marks)>500;

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

SELECT COUNT(StudentID), City
FROM Student
GROUP BY City
ORDER BY COUNT(StudentID) DESC;

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

Select Fname, Lname
From Student
Where Marks IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Select Fname, Lname
From Student
Where Marks IS NOT NULL;

UPDATE table_name
SET column1 = value1, column2 = value2,...
WHERE condition;

UPDATE Student
SET Fname = 'Robert', Lname= 'Wills'
WHERE StudentID = 101;

DELETE FROM table_name
WHERE condition;

DELETE FROM Student
WHERE FName='Robert';

Delete From Student;

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT StudentID, Fname, Lname
FROM Student
WHERE City IN ('Delhi', 'Goa', 'Pune','Bengaluru');

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT StudentID, Fname, Lname FROM Student
WHERE Marks BETWEEN 400 AND 500;

SELECT column_name AS alias_name
FROM table_name;

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

SELECT column_name(s) FROM table_name AS alias_name;

SELECT S.Fname, S.LName
FROM Student as S