ORACLE SQL PLSQL SELECT training institutes in Hyderabad

SQL SELECT Statement

The SELECT statement is the most commonly used command in Structured Query Language. It is used to access the records from one or more database tables and views. It also retrieves the selected data that follow the conditions we want.

By using this command, we can also access the particular record from the particular column of the table. The table which stores the record returned by the SELECT statement is called a result-set table.

Syntax of SELECT Statement in SQL

  1. SELECT Column_Name_1, Column_Name_2, ....., Column_Name_N FROM Table_Name;

In this SELECT syntax, Column_Name_1, Column_Name_2, ….., Column_Name_N are the name of those columns in the table whose data we want to read.

If you want to access all rows from all fields of the table, use the following SQL SELECT syntax with * asterisk sign:

  1. SELECT * FROM table_name;

Examples of SELECT Statement in SQL

Here, we took the following two different SQL examples which will help you to execute the SELECT statement for retrieving the records:

Example 1:

Firstly, we have to create the new table and then insert some dummy records into it.

Use the following query to create the Student_Records table in SQL:

  1. CREATE TABLE Student_Records
  2. (
  3. Student_Id Int PRIMARY KEY,
  4. First_Name VARCHAR (20),
  5. Address VARCHAR (20),
  6. Age Int NOT NULL,
  7. Percentage Int NOT NULL,
  8. Grade VARCHAR (10)
  9. ) ;

The following query inserts the record of intelligent students into the Student_Records table:

  1. INSERT INTO Student VALUES (201, Akash, Delhi, 18, 89, A2),
  2. (202, Bhavesh, Kanpur, 19, 93, A1),
  3. (203, Yash, Delhi, 20, 89, A2),
  4. (204, Bhavna, Delhi, 19, 78, B1),
  5. (05, Yatin, Lucknow, 20, 75, B1),
  6. (206, Ishika, Ghaziabad, 19, 51, C1),
  7. (207, Vivek, Goa, 20, 62, B2);

The following SQL query displays all the values of each column from the above Student_records table:

  1. SELECT * FROM Student_Records;

The output of the above query is:

Student_ID First_Name Address Age Percentage Grade
201 Akash Delhi 18 89 A2
202 Bhavesh Kanpur 19 93 A1
203 Yash Delhi 20 89 A2
204 Bhavna Delhi 19 78 B1
205 Yatin Lucknow 20 75 B1
206 Ishika Ghaziabad 19 91 C1
207 Vivek Goa 20 80 B2

Example 2:

The following query displays the values of particular column from the above Student_Record table:

  1. SELECT Student_Id, Age, Percentage, Grade FROM Employee;
Student_ID Age Percentage Grade
201 18 89 A2
202 19 93 A1
203 20 89 A2
204 19 78 B1
205 20 75 B1
206 19 91 C1
207 20 80 B2

SELECT Statement with WHERE clause

The WHERE clause is used with SELECT statement to return only those rows from the table, which satisfy the specified condition in the query.

In SQL, the WHERE clause is not only used with SELECT, but it is also used with other SQL statements such as UPDATE, ALTER, and DELETE statements.

Syntax of SELECT Statement with WHERE clause

  1. SELECT * FROM Name_of_Table WHERE [condition];

In the syntax, we specify the condition in the WHERE clause using SQL logical or comparison operators.

Example of SELECT Statement with WHERE clause

Firstly, we have to create the new table and then insert some dummy records into it.

Use the following query to create the Employee_Details table in SQL:

  1. CREATE TABLE Employee_Details
  2. (
  3. Employee_ID INT AUTO_INCREMENT PRIMARY KEY,
  4. Emp_Name VARCHAR (50),
  5. Emp_City VARCHAR (20),
  6. Emp_Salary INT NOT NULL,
  7. Emp_Panelty INT NOT NULL
  8. ) ;

The following INSERT query inserts the record of employees into the Employee_Details table:

  1. INSERT INTO Employee_Details (Employee_ID, Emp_Name, Emp_City, Emp_Salary, Emp_Panelty) VALUES (101, Anuj, Ghaziabad, 25000, 500),
  2. (102, Tushar, Lucknow, 29000, 1000),
  3. (103, Vivek, Kolkata, 35000, 500),
  4. (104, Shivam, Goa, 22000, 500);

The following SELECT query shows the data of the Employee_Details table:

  1. SELECT * FROM Employee_Details;
Employee_Id Emp_Name Emp_City Emp_Salary Emp_Panelty
101 Anuj Ghaziabad 25000 500
102 Tushar Lucknow 29000 1000
103 Vivek Kolkata 35000 500
104 Shivam Goa 22000 500

The following query shows the record of those employees from the above table whose Emp_Panelty is 500:

  1. SELECT * FROM Employee_Details WHERE Emp_Panelty = 500;

This SELECT query displays the following table in result:

Employee_Id Emp_Name Emp_City Emp_Salary Emp_Panelty
101 Anuj Ghaziabad 25000 500
103 Vivek Kolkata 35000 500
104 Shivam Goa 22000 500

SQL SELECT Statement with GROUP BY clause

The GROUP BY clause is used with the SELECT statement to show the common data of the column from the table:

Syntax of SELECT Statement with GROUP BY clause

  1. SELECT column_Name_1, column_Name_2, ....., column_Name_N aggregate_function_name(column_Name2) FROM table_name GROUP BY column_Name1;

Example of SELECT Statement with GROUP BY clause

Use the following query to create the Cars_Details table:

  1. CREATE TABLE Cars_Details
  2. (
  3. Car_Number INT PRIMARY KEY,
  4. Car_Name VARCHAR (50),
  5. Car_Price INT NOT NULL,
  6. Car_AmountINT NOT NULL
  7. ) ;

The following INSERT query inserts the record of cars into the Cars_Details table:

  1. INSERT INTO Cars_Details (Car_Number, Car_Name, Car_Amount, Car_Price)
  2. VALUES (2578, Creta, 3, 1500000),
  3. (9258, Audi, 2, 3000000),
  4. (8233, Venue, 6, 900000),
  5. (6214, Nexon, 7, 1000000);

The following SELECT query displays the values in the output:

  1. SELECT * FROM Cars_Details;

 

Car_Number Car_Name Car_Amount Car_Price
2578 Creta 3 1000000
9258 Audi 2 900000
8233 Venue 6 900000
6214 Nexon 7 1000000

The following SELECT with GROUP BY query lists the number of cars of the same price:

  1. SELECT COUNT (Car_Name), Car_Price FROM Cars_Details GROUP BY Car_Price;

The output of above GROUP BY query is shown below:

Output:

Count (Car_Name) Car_Price
2 1000000
2 900000

SQL SELECT Statement with HAVING clause

The HAVING clause in the SELECT statement creates a selection in those groups which are defined by the GROUP BY clause.

Syntax of SELECT Statement with HAVING clause

  1. SELECT column_Name_1, column_Name_2, ....., column_Name_N aggregate_function_name(column_Name_2) FROM table_name GROUP BY column_Name1 HAVING ;

Example of SELECT Statement with HAVING clause

Let's create the Employee_Having table in SQL using the below CREATE command:

  1. CREATE TABLE Employee_Having
  2. (
  3. Employee_Id INT PRIMARY KEY,
  4. Employee_Name VARCHAR (50),
  5. Employee_Salary INT NOT NULL,
  6. Employee_City VARCHAR (50)
  7. ) ;

The following INSERT query inserts the record of employees into the Employee_Having table:

  1. INSERT INTO Employee_Having (Employee_Id, Employee_Name, Employee_Salary, Employee_City)
  2. VALUES (201, Jone, 20000, Goa),
  3. (202, Basant, 40000, Delhi),
  4. (203, Rashet, 80000,Jaipur),
  5. (204, Aunj, 20000, Goa),
  6. (205, Sumit, 50000, Delhi);

The following SELECT query shows the values of Employee_Having table in the output:

  1. SELECT * FROM Employee_Having;

 

Employee_Id Employee_Name Employee_Salary Employee_City
201 Jone 20000 Goa
202 Basant 40000 Delhi
203 Rashet 80000 Jaipur
204 Anuj 20000 Goa
205 Sumit 50000 Delhi

The following query shows the total salary of those employees having more than 5000 from the above Employee_Having table:

  1. SELECT SUM (Employee_Salary), Employee_City FROM Employee_Having GROUP BY Employee_City HAVING SUM(Employee_Salary)>5000;

This HAVING query with SELECT statement shows the following table:

Output:

SUM (Employee_Salary) Employee_City
90000 Delhi
80000 Jaipur

SELECT Statement with ORDER BY clause

The ORDER BY clause with the SQL SELECT statement shows the records or rows in a sorted manner.

The ORDER BY clause arranges the values in both ascending and descending order. Few database systems arrange the values of column in ascending order by default.

Syntax of SELECT Statement with ORDER BY clause

  1. SELECT Column_Name_1, Column_Name_2, ....., column_Name_N FROM table_name WHERE [Condition] ORDER BY[column_Name_1, column_Name_2, ....., column_Name_N asc | desc ];

Example of SELECT Statement with ORDER BY clause in SQL

  1. CREATE TABLE Employee_Order
  2. (
  3. Id INT NOT NULL,
  4. FirstName VARCHAR (50),
  5. Salary INT,
  6. City VARCHAR (50)
  7. ) ;

The following INSERT query inserts the record of employees into the Employee_Having table:

  1. INSERT INTO Employee_Order (Id, FirstName, Salary, City)
  2. VALUES (201, Jone, 20000, Goa),
  3. (202, Basant, 15000, Delhi),
  4. (203, Rashet, 80000,Jaipur),
  5. (204, Aunj, 90000, Goa),
  6. (205, Sumit, 50000, Delhi);

The following SELECT query shows the values of the table in the output:

  1. SELECT * FROM Employee_Order;

 

Id FirstName Salary City
201 Jone 20000 Goa
202 Basant 15000 Delhi
203 Rashet 80000 Jaipur
204 Anuj 90000 Goa
205 Sumit 50000 Delhi

The following query sorts the salary of employees in descending order from the above Employee_Order table:

  1. SELECT * FROM Employee_Order ORDER BY Emp_Salary DESC;

This SQL query shows the following table in result:

Output:

Emp_Id Emp_Name Emp_Salary Emp_City
204 Anuj 90000 Goa
203 Rashet 80000 Jaipur
205 Sumit 50000 Delhi
201 Jone 20000 Goa
202 Basant 15000 Delhi

 

SQL SELECT UNIQUE

Actually, there is no difference between DISTINCT and UNIQUE.

SELECT UNIQUE is an old syntax which was used in oracle description but later ANSI standard defines DISTINCT as the official keyword.

After that oracle also added DISTINCT but did not withdraw the service of UNIQUE keyword for the sake of backward compatibility.

In simple words, we can say that SELECT UNIQUE statement is used to retrieve a unique or distinct element from the table.

Let's see the syntax of select unique statement.

  1. SELECT UNIQUE column_name
  2. FROM table_name;

SQL SELECT DISTINCT statement can also be used for the same cause.

 

SQL SELECT DISTINCT

The SQL DISTINCT command is used with SELECT key word to retrieve only distinct or unique data.

In a table, there may be a chance to exist a duplicate value and sometimes we want to retrieve only unique values. In such scenarios, SQL SELECT DISTINCT statement is used.

Note: SQL SELECT UNIQUE and SQL SELECT DISTINCT statements are same.

Let's see the syntax of select distinct statement.

  1. SELECT DISTINCT column_name ,column_name
  2. FROM  table_name;

Let's try to understand it by the table given below:

Student_Name Gender Mobile_Number HOME_TOWN
Rahul Ojha Male 7503896532 Lucknow
Disha Rai Female 9270568893 Varanasi
Sonoo Jaiswal Male 9990449935 Lucknow

Here is a table of students from where we want to retrieve distinct information For example: distinct home-town.

  1. SELECT DISTINCT home_town
  2. FROM students

Now, it will return two rows.

HOME_TOWN
Lucknow
Varanasi

 

SQL SELECT COUNT

The SQL COUNT() is a function that returns the number of records of the table in the output.

This function is used with the SQL SELECT statement.

Let's take a simple example: If you have a record of the voters in the selected area and want to count the number of voters, then it is very difficult to do it manually, but you can do it easily by using SQL SELECT COUNT query.

Syntax of Select Count Function in SQL

  1. SELECT COUNT(column_name) FROM table_name;

In the syntax, we have to specify the column's name after the COUNT keyword and the name of the table on which the Count function is to be executed

Examples of Select Count Function in SQL

In this article, we have taken the following two SQL examples that will help you to run the Count function in the query:

Example 1: In this example, we have a table called Bike with three columns:

Bike_Name Bike_Color Bike_Cost
Pulsar Black 185,000
Apache Black NULL
KTM RC Red 90,0000
Royal Enfield White NULL
Livo Black 80,000
KTM DUKE Red 195,000
  • Suppose, you want to count the total number of bike colors from Bike Table. For this operation, you have to write the following SQL statement:
  1. SELECT COUNT (Bike_Color) AS TotalBikeColor FROM Bikes ;

This query will show the following output on the screen:

TotalBikeColor
6

The output of this query is six because the Bike_Color column does not contain any NULL value.

  • Suppose, you want to count the total values of the Bike_Cost column from the above Bike Table. For this operation, you have to write the following statement in SQL:
  1. SELECT COUNT (Bike_Cost) AS TotalBikeCost FROM Bikes ;

This query will show the following output on the screen:

TotalBikeCost
4

The output of this query is four because two values of the Bike_Cost column are NULL and, these two NULL values are excluded from the count function. That's why this query shows four instead of 6 in the output.

Example 2: In this example, we have an Employee_details table with four columns:

Emp_Id Emp_Name Emp_Salary Emp_City
2001 Saurabh 25000 NULL
2002 Ram 29000 Delhi
2003 Sumit 30000 NULL
2004 Ankit 45000 Goa
2005 Bheem 40000 NULL
  • Suppose, you want to count the total values of the Emp_City column of the above Employee_details table. For this query, you have to write the following statement in Structured Query Language:
  1. SELECT COUNT (Emp_City) AS TotalCity FROM Employee_details ;

This query will show the following output on the screen:

TotalCity
2

The output of this query is two because the three values of the Emp_City column are NULL. And, these three NULL values are excluded from the count function. That's why this query shows two instead of 5 in the output.

Select Count(*) Function in SQL

The count(*) function in SQL shows all the Null and Non-Null records present in the table.

Syntax of Count (*) Function in SQL

  1. SELECT COUNT(*) FROM table_name;

Example of Count (*) Function in SQL

In this example, we have the following Bike table with three columns:

Bike_Name Bike_Color Bike_Cost
Livo Black 185,000
Apache Red NULL
Pulsar Red 90,0000
Royal Enfield Black NULL
KTM DUKE Black 80,000
KTM RC White 195,000
  • Suppose, you want to count the total number of records from the Bike Table. For this condition, you have to write the following statement in Structured Query Language:
  1. SELECT COUNT (*)  FROM Bikes ;

This query will show the following output on the screen:

Count(*)
6

SQL Count() Function With WHERE Clause

We can also use the Count() function with the WHERE clause. The Count Function with WHERE clause in the SELECT statement shows those records that matched the specified criteria.

Syntax of Count() Function With WHERE clause in SQL

  1. SELECT COUNT(column_name) FROM table_name WHERE [condition];

Examples of Count Function With WHERE clause in SQL

The following two examples will help you to run the Count function with the WHERE clause in the SQL query:

Example 1: In this example, we have the following Bike table with three columns:

Bike_Name Bike_Color Bike_Cost
Apache Black 90,0000
Livo Black NULL
KTM RC Red 185,000
KTM DUKE White NULL
Royal Enfield Red 80,000
Pulsar Black 195,000
  • Suppose, you want to count the total number of bikes whose color is black. For this, you have to type the following statement in SQL:
  1. SELECT COUNT (Bike_Name) AS TotalBikeBlackColor FROM Bikes WHERE Bike_Color = 'Black';

This query will show the following output on the screen:

TotalBikeBlackColor
3

Example 2: In this example, we have an Employee_details table with four columns:

Emp_Id Emp_Name Emp_Salary Emp_City
2001 Bheem 30000 Jaipur
2002 Ankit 45000 Delhi
2003 Sumit 40000 Delhi
2004 Ram 29000 Goa
2005 Abhay 25000 Delhi
  • Suppose, you want to count the total number of those employees who belong to Delhi city. For this, you have to write the following SQL statement:
  1. SELECT COUNT (Emp_Name) AS TotalEmpCity FROM Employee_details WHERE Emp_City = 'Delhi';

This query will show the following output on the screen:

TotalEmpCity
3

 

SQL SELECT TOP

The SELECT TOP statement in SQL shows the limited number of records or rows from the database table. The TOP clause in the statement specifies how many rows are returned.

It shows the top N number of rows from the tables in the output. This clause is used when there are thousands of records stored in the database tables.

Let's take a simple example: If a Student table has a large amount of data about students, the select TOP statement determines how much student data will be retrieved from the given table.

Note: All the database systems do not support the TOP keyword for selecting the limited number of records. Oracle supports the ROWNUM keyword, and MySQL supports the LIMIT keyword.

Syntax of TOP Clause in SQL

  1. SELECT TOP number | percent column_Name1, column_Name2, ....., column_NameN  FROM table_name WHERE [Condition] ;

In the syntax, the number denotes the number of rows shown from the top in the output. column_Name denotes the column whose record we want to show in the output. We can also specify the condition using the WHERE clause.

 

 

 

Advertisement
Advertisement

Examples of TOP Clause in SQL

The following four SQL examples will help you how to use the Number and Percent in SQL TOP clause in the query:

Example 1: In this example, we have a table called Cars with three columns:

Car Name Car Color Car Cost
Hyundai Creta White 10,85,000
Hyundai Venue White 9,50,000
Hyundai i20 Red 9,00,000
Kia Sonet White 10,00,000
Kia Seltos Black 8,00,000
Swift Dezire Red 7,95,000
  • Suppose, you want to show the first three Names and Color of Car from the above table. To do this, you have to type the following query in SQL:
  1. SELECT TOP 3 Car_Name, Car_Color FROM Cars;

This query shows the following table on the screen:

Car_Name Car_Color
Hyundai Creta White
Hyundai Venue White
Hyundai i20 Red

Example 2: In this example, we have a table called Student with three columns:

Stu_ID Stu_Name Stu_Marks
1001 Abhay 85
1002 Ankit 75
1003 Bheem 60
1004 Ram 79
1005 Sumit 80
  • Suppose, you want to show the details of the first four students in the result from the above table. To do this, you have to type the following query in SQL:
  1. SELECT TOP 4 * FROM Student;

This query shows the following table on the screen in the SQL output:

Stu_ID Stu_Name Stu_Marks
1001 Abhay 85
1002 Ankit 75
1003 Bheem 60
1004 Ram 79

Example 3: In this example, we have a table called Employee with four columns:

Emp_Id Emp_Name Emp_Salary Emp_City
201 Abhay 25000 Goa
202 Ankit 45000 Delhi
203 Bheem 30000 Goa
204 Ram 29000 Goa
205 Sumit 40000 Delhi
  • Suppose, you want to show the details of those first four employees whose city is Goa from the above table. To do this, you have to type the following query in SQL:
  1. SELECT TOP 4 * FROM Employee WHERE Emp_City = Goa ;

This query shows the following table on the screen in the SQL output:

Emp_Id Emp_Name Emp_Salary Emp_City
201 Abhay 25000 Goa
203 Bheem 30000 Goa
204 Ram 29000 Goa

Example 4: In this example, we have a table called Bikes with three columns:

Bike_Name Bike_Color Bike_Cost
KTM DUKE Black 185,000
Royal Enfield Black NULL
Pulsar Red 90,0000
Apache White NULL
Livo Black 80,000
KTM RC Red 195,000
  • Suppose, you want to show the 50 percent of data from the above table. To do this, you have to type the following query in SQL:
  1. SELECT TOP 50 PERCENT * FROM Bikes;

This query shows the following table on the screen:

Bike_Name Bike_Color Bike_Cost
KTM DUKE Black 185,000
Royal Enfield Black NULL
Pulsar Red 90,0000

Syntax of LIMIT Clause in MySQL

  1. SELECT column_Name1,column_Name2, ....., column_NameN FROM table_name LIMIT value;

In the syntax, we have to specify the value after the LIMIT keyword. The value denotes the number of rows to be shown from the top in the output.

Example of LIMIT Clause in MySQL

The following SQL example will help you how to use the LIMIT clause in the query. In this example, we have a table called Cars with three columns:

Car Name Car Color Car Cost
Hyundai Creta White 10,85,000
Hyundai Venue White 9,50,000
Hyundai i20 Red 9,00,000
Kia Sonet White 10,00,000
Kia Seltos Black 8,00,000
Swift Dezire Red 7,95,000
  • Suppose, you want to show the first three records of Car using a LIMIT clause in MySQL. To do this, you have to type the following query in MySQL:
  1. SELECT * FROM Cars LIMIT 3;

This query shows the following table on the screen:

Car Name Car Color Car Cost
Hyundai Creta White 10,85,000
Hyundai Venue White 9,50,000
Hyundai i20 Red 9,00,000

Syntax of ROWNUM keyword in WHERE Clause in Oracle database

  1. SELECT column_Name1,column_Name2, ....., column_NameN FROM table_name WHERE ROWNUM <= value;

In the syntax, we have to assign the value to ROWNUM in the WHERE clause. The value denotes the number of rows to be shown from the top in the output.Example of ROWNUM keyword in WHERE Clause in Oracle

The following SQL example will help you how to use the ROWNUM keyword in the query. In this example, we have a table called Cars with three columns:

Car Name Car Color Car Cost
Hyundai Creta White 10,85,000
Hyundai Venue White 9,50,000
Hyundai i20 Red 9,00,000
Kia Sonet White 10,00,000
Kia Seltos Black 8,00,000
Swift Dezire Red 7,95,000
  • Suppose, you want to show the first three records of Car using the ROWNUM keyword in Oracle. To do this, you have to type the following query in the Oracle database:
  1. SELECT * FROM Cars WHERE ROWNUM <= 3;

This query shows the following table on the screen:

Car Name Car Color Car Cost
Hyundai Creta White 10,85,000
Hyundai Venue White 9,50,000
Hyundai i20 Red 9,00,000

 

SQL SELECT FIRST

The SQL first() function is used to return the first value of the selected column.

Let's see the syntax of sql select first() function:

  1. SELECT FIRST(column_name) FROM table_name;

Here a point is notable that first function is only supported by MS Access.

If you want to retrieve the first value of the "customer_name" column from the "customers" table, you need to write following query

  1. SELECT FIRST(customer_name) AS first_customer FROM customers;

Let us take the example of CUSTOMERS to examine SQL SELECT FIRST command:

Table CUSTOMERS

CUSTOMER_NAME AGE ADDRESS EXPENDITURE
KAMAL SHARMA 26 GHAZIABAD 6000
ROBERT PETT 23 NEWYORK 26000
SHIKHA SRIVASTAV 22 DELHI 9000

If you want to retrieve the first value of the "customer_name" column from the "customers" table, you need to write following query:

Let's see the syntax of sql select first() function:

  1. SELECT FIRST (CUSTOMER_NAME) AS first_customer FROM CUSTOMERS;
  2. After that query, you will find the result:
  3. KAMAL SHARMA

Note: The SELECT FIRST statement is only supported by MS Access. This statement doesn't work with other databases like Oracle, MySQL etc.

SQL SELECT LAST

The LAST() function in Structured Query Language shows the last value from the specified column of the table.

Note: This SQL function is only supported in Microsoft Access database. Oracle supports ORDER BY and ROWNUM keywords, and MySQL supports the LIMIT keyword for selecting the last record.

Syntax of LAST() Function

  1. SELECT LAST (Field_Name) FROM Table_Name ;

In the above syntax, the LAST keyword denotes the last row to be shown from the table in the output, and the Field_Name denotes the column whose value we want to show.

Example of the LAST function in SQL

Example 1:

Firstly, we have to create a table and insert the data into the table in SQL.

The following SQL statement creates the Student_Details table with Student_ID as the primary key:

  1. CREATE TABLE Student_Details
  2. (
  3. Student_ID INT NOT NULL,
  4. Student_Name varchar(100),
  5. Student_Course varchar(50),
  6. Student_Age INT,
  7. Student_Marks INT
  8. );

The following SQL queries insert the record of students into the above table using INSERT INTO statement:

  1. INSERT INTO Student_Details VALUES (101, Anuj, B.tech, 20, 88);
  2. INSERT INTO Student_Details VALUES (102, Raman, MCA, 24, 98);
  3. INSERT INTO Student_Details VALUES (104, Shyam, BBA, 19, 92);
  4. INSERT INTO Student_Details VALUES (107, Vikash, B.tech, 20, 78);
  5. INSERT INTO Student_Details VALUES (111, Monu, MBA, 21, 65);
  6. INSERT INTO Student_Details VALUES (114, Jones, B.tech, 18, 93);
  7. INSERT INTO Student_Details VALUES (121, Parul, BCA, 20, 97);
  8. INSERT INTO Student_Details VALUES (123, Divya, B.tech, 21, 89);
  9. INSERT INTO Student_Details VALUES (128, Hemant, MBA, 23, 90);
  10. INSERT INTO Student_Details VALUES (130, Nidhi, BBA, 20, 88);
  11. INSERT INTO Student_Details VALUES (132, Priya, MBA, 22, 99);
  12. INSERT INTO Student_Details VALUES (138, Mohit, MCA, 21, 92);

Let's see the record of the above table using the following SELECT statement:

  1. SELECT * FROM Student_Details;

 

Student_ID Student_Name Student_Course Student_Age Student_Marks
101 Anuj B.tech 20 88
102 Raman MCA 24 98
104 Shyam BBA 19 92
107 Vikash B.tech 20 78
111 Monu MBA 21 65
114 Jones B.tech 18 93
121 Parul BCA 20 97
123 Divya B.tech 21 89
128 Hemant MBA 23 90
130 Nidhi BBA 20 88
132 Priya MBA 22 99
138 Mohit MCA 21 92

The following query shows the last Student_Name from the above table in the output:

  1. SELECT LAST (Student_Name) AS Last_Student FROM Student_Details;

Output:

SQL SELECT LAST

Syntax of LIMIT Clause in MySQL

  1. SELECT column_Name FROM Table_Name ORDER BY Column_Name DESC LIMIT 1;

In this MySQL syntax, we have to specify the value 1 just after the LIMIT keyword for indicating the single row/record.

Example of LIMIT Clause in MySQL

Let's take the following Employee table to explain how to use the LIMIT clause in MySQL for accessing the last record:

Employee_Id Emp_Name Emp_City Emp_Salary Emp_Bonus
101 Anuj Ghaziabad 35000 2000
102 Tushar Lucknow 29000 3000
103 Vivek Kolkata 35000 2500
104 Shivam Goa 22000 3000

The following MySQL query shows the last value of the Emp_City column from the above Employee table:

  1. SELECT Emp_City FROM Employee ORDER BY Emp_City DESC LIMIT 1;

Output:

Goa

ROWNUM keyword in Oracle

The syntax for accessing the last record from the Oracle database is given below:

  1. SELECT Column_Name FROM Table_Name ORDER BY Column_Name DESC WHERE ROWNUM <=1;

In this Oracle syntax, we have to specify the ROWNUM keyword, which is less than and equal to 1. In Oracle, the ROWNUM keyword is used in the WHERE clause for retrieving the last record from the table.

Example of ROWNUM Clause in Oracle

Let's take the following Cars table to explain how to use the ROWNUM keyword in MySQL:

Car_Number Car_Name Car_Amount Car_Price
2578 Creta 3 900000
9258 Audi 2 1100000
8233 Venue 6 900000
6214 Nexon 7 1000000

The following MySQL query shows the last name of the car from the Car_Name column of the Cars table:

  1. SELECT Car_Name FROM Cars ORDER BY Car_Name DESC WHERE ROWNUM <=1;

Output:

Nexon

SQL SELECT RANDOM

The SQL SELECT RANDOM() function returns the random row. It can be used in online exam to display the random questions.

There are a lot of ways to select a random record or row from a database table. Each database server needs different SQL syntax.

If you want to select a random row with MY SQL:

  1. SELECT column FROM table
  2. ORDER BY RAND ( )
  3. LIMIT 1

If you want to select a random row with Microsoft SQL server:

  1. SELECT TOP 1 column FROM table
  2. ORDER BY NEW ID()

If you want to select a random record with ORACLE:

  1. SELECT column FROM
  2. (SELECT column FROM table
  3. ORDER BY dbms_random.value)
  4. WHERE rownum =1

If you want to select a random row with PostgreSQL:

  1. SELECT column FROM table
  2. ORDER BY RAND()
  3. LIMIT 1

 

SQL SELECT IN

SQL IN is an operator used in a SQL query to help reduce the need to use multiple SQL "OR" conditions.

It is used in SELECT, INSERT, UPDATE or DELETE statement.

Advantage of SQL SELECT IN

It minimizes the use of SQL OR operator.

Let's see the syntax for SQL IV

  1. Expression IN (value 1, value 2 ... value n);

Take an example with character values.

  1. SELECT *
  2. FROM students
  3. WHERE students_name IN ( Amit , Raghav, Rajeev)

Let's take another example with numeric values.

  1. SELECT *
  2. FROM marks
  3. WHERE roll_no IN (001, 023, 024);

SQL SELECT from Multiple Tables

This statement is used to retrieve fields from multiple tables. To do so, we need to use join query to get data from multiple tables.

Let's see the example for the select from multiple tables:

  1. SELECT orders.order_id, suppliers.name
  2. FROM suppliers
  3. INNER JOIN orders
  4. ON suppliers.supplier_id = orders.supplier_id
  5. ORDER BY order_id;

Let us take three tables, two tables of customers named customer1 and customer2 and the third table is product table.

Customer1 table

Cus_id Name1
1 Jack
2 Jill

Customer2 table

Cus_id Name2
1 Sandy
2 Venus

Product table

P_id Cus_id P_name
1 1 Laptop
2 2 Phone
3 P1 Pen
4 P2 Notebook

Example syntax to select from multiple tables:

  1. SELECT p. p_id, p.cus_id, p.p_name, c1.name1, c2.name2
  2. FROM product AS p
  3. LEFT JOIN customer1 AS c1
  4. ON p.cus_id=c1.cus_id
  5. LEFT JOIN customer2 AS c2
  6. ON p.cus_id = c2.cus_id

 

P_id Cus_id P_name P_name P_name
1 1 Laptop Jack NULL
2 2 Phone Jill NULL
3 P1 Pen NULL Sandy
4 P2 Notebook NULL Venus

 

SQL SELECT DATE

SQL SELECT DATE is used to retrieve a date from a database. If you want to find a particular date from a database, you can use this statement.

For example: let's see the query to get all the records after '2013-12-12'.

  1. SELECT * FROM
  2. table-name WHERE your date-column >= '2013-12-12'

Let's see the another query to get all the records after '2013-12-12' and before '2013-12-13' date.

  1. SELECT* FROM
  2. table-name where your date-column < '2013-12-13' and your date-column >= '2013-12-12'

If you want to compare the dates within the query, you should use BETWEEN operator to compare the dates.

  1. SELECT * FROM
  2. table_name WHERE yourdate BETWEEN '2012-12-12' and '2013-12-12'

Or if you are looking for one date in particular you can use. You should change the date parameter into the acceptable form.

  1. SELECT* FROM
  2. table_name WHERE cast (datediff (day, 0, yourdate) as datetime) = '2012-12-12'

 

SQL SELECT SUM

It is also known as SQL SUM() function. It is used in a SQL query to return summed value of an expression.

Let's see the Syntax for the select sum function:

  1. SELECT SUM (expression)
  2. FROM tables
  3. WHERE conditions;

expression may be numeric field or formula.

This would produce the following result.

ID EMPLOYEE_NAME SALARY
1 JACK REACHER 32000
2 PADMA MAHESHWARI 22000
3 JOE PETRA 41000
4 AMBUJ AGRAWAL 21000

After using this SQL SELECT SUM example, it will produce the result containing the sum of the salary greater than 20000.

Total salary: 116,000

SQL SUM EXAMPLE with single field:

If you want to know how the combined total salary of all employee whose salary is above 20000 per month.

  1. SELECT SUM (salary) AS "Total Salary"
  2. FROM employees
  3. WHERE salary > 20000;

In this example, you will find the expression as "Total Salary" when the result set is returned.

SQL SUM EXAMPLE with SQL DISTINCT:

You can also use SQL DISTINCT clause with SQL SUM function.

  1. SELECT SUM (DISTINCT salary) AS "Total Salary"
  2. FROM employees
  3. WHERE salary > 20000;

SQL SUM EXAMPLE with SQL GROUP BY:

Sometimes there is a need to use the SQL GROUP BY statement with the SQL SUM function.

For example, we could also use the SQL SUM function to return the name of department and the total sales related to department.

  1. SELECT department, SUM (sales) AS "Total Sales"
  2. FROM order_details
  3. GROUP BY department;

Let us take a table named order_details

ID DEPARTMENT DATE DAILY SALES
1 Mechanical 2012-08-13 360
2 Electrical 2012-08-13 100
2 Electrical 2012-08-14 110
3 Electronics 2012-08-13 150
3 Electronics 2012-08-14 170

After using the SQL GROUP BY statement with SUM, you will find the following result.

DEPARTMENT SUM(DAILY SALES)
Mechanical 360
Electrical 210
electronics 320

 

SQL SELECT NULL

First of all we should know that what null value is? Null values are used to represent missing unknown data.

There can be two conditions:

  1. Where SQL is NULL
  2. Where SQL is NOT NULL

If in a table, a column is optional, it is very easy to insert data in column or update an existing record without adding a value in this column. This means that field has null value.

Note: we should not compare null value with 0. They are not equivalent.

Where SQL is NULL:

How to select records with null values only? (in the marks column)

There is an example of student table:

SIR_NAME NAME MARKS
TYAGI SEEMA
SINGH RAMAN 5.5
SHARMA AMAR
JAISWAL VICKY 6.2

Let's see the query to get all the records where marks is NULL:

  1. SELECT SIR_NAME, NAME, MARKS FROM STUDENTS
  2. WHERE MARKS IS NULL

It will return the following records:

SIR_NAME NAME MARKS
SHARMA AMAR
TYAGI SEEMA

Where SQL is NOT NULL:

How to select records with no null values(in marks column)? Let's see the query to get all the records where marks is NOT NULL

  1. SELECT SIR_NAME, FIRSTNAME, MARKS FROM STUDENTS
  2. WHERE MARKS IS NOT NULL
SIR_NAME NAME MARKS
SINGH RAMAN 5.5
JAISWAL VICKY 6.2