Where, Like, OrderBy, GroupBy, Having, and Top Clauses – Distinct Keyword – Sql Indexes in Database

By | February 22, 2016

Type of Classes in Database

rdbms-concepts-sql-keys (1)

WHERE Clause:

Where clause is used to specify condition while retrieving data from the table. Where clause is used mostly with Select, Update and Delete query. If the condition specified by where clause is true then only the result from the table is returned.
Syntax for WHERE clause

SELECT column-name1,
column-name2,
column-name3,
column-nameN
from table-name WHERE [condition];
Example using WHERE clause
Consider a Student table,

s_id
s_Name
age
address
101
Adam
15
Noida
102
Alex
18
Delhi
103
Abhi
17
Rohtak
104
Ankit
22
Panipat

Now we will use a SELECT statement to display data of the table, based on a condition, which we will add to the SELECT query using WHERE clause.

SELECT s_id,
s_name,
age,
address
from Student WHERE s_id=101;

s_id
s_Name
age
address
101
Adam
15
Noida

Like clause

Like clause is used as condition in SQL query. Like clause compares data with an expression using wildcard operators. It is used to find similar data from the table.
Wildcard operatorsThere are two wildcard operators that are used in like clause.

  • Percent sign % : represents zero, one or more than one character.
  • Underscore sign _ : represents only one character.

Example of LIKE clause
Consider the following Student table.

s_id
s_Name
age
101
Adam
15
102
Alex
18
103
Abhi
17

SELECT * from Student where s_name like ‘A%’;

The above query will return all records where s_name starts with character ‘A’.

s_id
s_Name
age
101
Adam
15
102
Alex
18
103
Abhi
17

ExampleSELECT * from Student where s_name like ‘_d%’;

The above query will return all records from Student table where s_name contain ‘d’ as second character.

s_id
s_Name
age
101
Adam
15

ExampleSELECT * from Student where s_name like ‘%x’;

The above query will return all records from Student table where s_name contain ‘x’ as last character.

s_id
s_Name
age
102
Alex
18

Order By Clause

Order by clause is used with Select statement for arranging retrieved data in sorted order. The Order by clause by default sort data in ascending order. To sort data in descending order DESC keyword is used with Order by clause.
Syntax of Order By SELECT column-list|* from table-name order by asc|desc;
Example using Order by

Consider the following Emp table,

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
10000
405
Tiger
35
8000
SELECT * from Emp order by salary; 

The above query will return result in ascending order of the salary.
eid
name
age
salary
403
Rohan
34
6000
402
Shane
29
8000
405
Tiger
35
8000
401
Anu
22
9000
404
Scott
44
10000

Example of Order by DESC
Consider the Emp table described above,SELECT * from Emp order by salary DESC;

The above query will return result in descending order of the salary.

eid
name
age
salary
404
Scott
44
10000
401
Anu
22
9000
405
Tiger
35
8000
402
Shane
29
8000
403
Rohan
34
6000

Group By Clause

Group by clause is used to group the results of a SELECT query based on one or more columns. It is also used with SQL functions to group the result from one or more tables.
The syntax for using Group by in a statement.

SELECT column_name, function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name

Example of Group by in a Statement

Consider the following Emp table.

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
9000
405
Tiger
35
8000

Here we want to find name and age of employees grouped by their salaries
SQL query for the above requirement will be,SELECT name, age  from Emp group by salary
Result will be,

name
age
Rohan
34
shane
29
anu
22

Example of Group by in a Statement with WHERE clause

Consider the following Emp table

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
9000
405
Tiger
35
8000

SQL query will be,

select name, salary
from Emp
where age > 25
group by salaryResult will be.

name
salary
Rohan
6000
Shane
8000
Scott
9000

You must remember that Group By clause will always come at the end, just like the Order by clause.

HAVING Clause

having clause is used with SQL Queries to give more precise condition for a statement. It is used to mention condition in Group based SQL functions, just like WHERE clause.

Syntax for having will be,

select column_name, function(column_name)
FROM table_name
WHERE column_name condition
GROUP BY column_name
HAVING function(column_name) condition

Example of HAVING Statement

Consider the following Sale table.

oid
order_name
previous_balance
customer
11
ord1
2000
Alex
12
ord2
1000
Adam
13
ord3
2000
Abhi
14
ord4
1000
Adam
15
ord5
2000
Alex

Suppose we want to find the customer whose previous_balance sum is more than 3000.

We will use the below SQL query,
SELECT *
from sale group customer
having sum(previous_balance) > 3000Result will be,

oid
order_name
previous_balance
customer
11
ord1
2000
Alex

Distinct keyword

The distinct keyword is used with Select statement to retrieve unique values from the table. Distinctremoves all the duplicate records while retrieving from database.

Syntax for DISTINCT Keyword SELECT distinct column-name from table-name;
Example
Consider the following Emp table.

eid
name
age
salary
401
Anu
22
5000
402
Shane
29
8000
403
Rohan
34
10000
404
Scott
44
10000
405
Tiger
35
8000

select distinct salary from Emp;

The above query will return only the unique salary from Emp table

salary
5000
8000
10000

Top clause:-

The TOP clause is used to specify the number of records to return.The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
Note: Not all database systems support the TOP clause.
SQL Server Syntax

{SELECT TOP number|percent column_name(s) FROM table_name}

{SELECT TOP 2 * FROM table name;}

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example on SQL server, which would fetch top 3 records from CUSTOMERS table:

SELECT TOP 3 * FROM CUSTOMERS;

This would produce the following result:

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+
If you are using MySQL server, then here is an equivalent example:
SQL> SELECT * FROM CUSTOMERS
LIMIT 3;
This would produce the following result: 
+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

If you are using Oracle server, then here is an equivalent example:
SELECT * FROM CUSTOMERS
WHERE ROWNUM <= 3;

This would produce the following result:
+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

SQL SELECT INTO Statement

   The SELECT INTO statement selects data from one table and inserts it into a new table.
SQL SELECT INTO Syntax
We can copy all columns into the new table:
SELECT *
INTO newtable [IN externaldb] FROM table1;Or we can copy only the columns we want into the new table:
SELECT column_name(s)
INTO newtable [IN externaldb] FROM table1;

The new table will be created with the column-names and types as defined in the SELECT statement. You can apply new names using the AS clause.

SQL SELECT INTO Examples Create a backup copy of Customers:

SELECT *
INTO CustomersBackup2013
FROM Customers;

Use the IN clause to copy the table into another database:
SELECT *
INTO CustomersBackup2013 IN ‘Backup.mdb’
FROM Customers;

Copy only a few columns into the new table:
SELECT CustomerName, ContactName
INTO CustomersBackup2013
FROM Customers;

Copy only the German customers into the new table:
SELECT *
INTO CustomersBackup2013
FROM Customers
WHERE Country=’Germany’;

Copy data from more than one table into the new table:
SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2013
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;

Tip: The SELECT INTO statement can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

SELECT *
INTO newtable
FROM table1
WHERE 1=0;


Read

20 Cool funny Websites Kills Your Boredom-2016

18 Cool and Interesting Websites 2016 – Internet

The Art of Writing a Perfect Resume – Allinfi


SQL – Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers.

An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there’s an index.
The CREATE INDEX Command:

The basic syntax of CREATE INDEX is as follows:
CREATE INDEX index_name ON table_name;

Single-Column Indexes:
A single-column index is one that is created based on only one table column. The basic syntax is as follows:

  CREATE INDEX index_name ON table_name (column_name);

Unique Indexes:

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table.

 The basic syntax is as follows: CREATE UNIQUE INDEX index_name on table_name (column_name);

Composite Indexes:

A composite index is an index on two or more columns of a table.

The basic syntax is as follows: CREATE INDEX index_name on table_name (column1, column2);

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query’s WHERE clause as filter conditions.

Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice.

Implicit Indexes:

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

The DROP INDEX Command:

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved.
The basic syntax is as follows: DROP INDEX index_name;
You can check INDEX Constraint chapter to see actual examples on Indexes.

When should indexes be avoided?

Although indexes are intended to enhance a database’s performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered:

  • Indexes should not be used on small tables.
  • Tables that have frequent, large batch update or insert operations.
  • Indexes should not be used on columns that contain a high number of NULL values.
  • Columns that are frequently manipulated should not be indexed.

AUTO INCREMENT a Field

Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.
We would like to create an auto-increment field in a table.           Sometimes we want to have the database create a numerical primary key value as we continue to add rows of data. In MySQL, this is done by specifying AUTO_INCREMENT to the primary key field.

The syntax for AUTO_INCREMENT is as follows:
CREATE TABLE TABLE_NAME (PRIMARY_KEY_COLUMN INT NOT NULL AUTO_INCREMENT

PRIMARY KEY (PRIMARY_KEY_COLUMN));

For example, let’s say we want to create a table that consists of a primary key, last name, and first name. We use the following SQL:

CREATE TABLE USER_TABLE
(Userid int NOT NULL AUTO_INCREMENT,
Last_Name varchar(50),
First_Name varchar(50),
PRIMARY KEY (Userid));

Upon creation, there is no data in this table.

We insert the first value: INSERT INTO USER_TABLE VALUES (‘Perry’, ‘Jonathan’);

Now the table has the following values: Table USER_TABLE

Userid
Last_Name
First_Name
1
Perry
Jonathan

Now we insert the second value:
INSERT INTO USER_TABLE VALUES (‘Davis’, ‘Nancy’);

Now the table has the following values:
Table USER_TABLE

Userid
Last_Name
First_Name
1
Perry
Jonathan
2
Davis
Nancy

By default, AUTO_INCREMENT starts with 1 and increases by 1. To change the default starting value, you can use the ALTER TABLE command as follows:

ALTER TABLE TABLE_NAME AUTO_INCREMENT = [New Number];

If you Like the article please do like or comment it. I will be appreciated for the work i have done.
ThankYou…!!

Leave a Reply

Your email address will not be published. Required fields are marked *