SQL GROUP BY Clause

The SQL GROUP BY statement is used together with the SQL aggregate functions to group the retrieved data by one or more columns. The GROUP BY concept is one of the most complicated concepts for people new to the SQL language and the easiest way to understand it, is by example.

Remember we used the SUM keyword to calculate the total sales for all stores? What if we want to calculate the total sales for each store? Well, we need to do two things: First, we need to make sure we select the store name as well as total sales. Second, we need to make sure that all the sales figures are grouped by stores. The corresponding SQL syntax is,

SELECT "column_name1", SUM("column_name2")

FROM "table_name"

GROUP BY "column_name1";

Let's illustrate using the following table,

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
Los Angeles	300	Jan-08-1999
Boston	700	Jan-08-1999

We want to find total sales for each store. To do so, we would key in,

SELECT Store_Name, SUM(Sales)

FROM Store_Information

GROUP BY Store_Name;

Result:

Store_Name	SUM(Sales)
Los Angeles	1800
San Diego	250
Boston	700

Read More

SQL Aggregate Functions

SQL Aggregate Functions

SQL Aggregate functions return a single value, using values in a table column. Useful aggregate functions:

• AVG( ) - Returns the average value
• COUNT( ) - Returns the number of rows
• FIRST( ) - Returns the first value
• LAST( ) - Returns the last value
• MAX( ) - Returns the largest value
• MIN( ) - Returns the smallest value
• SUM( ) - Returns the sum

In this chapter we are going to introduce a new table called Sales, which will have the following columns and data:

OrderID	OrderDate	OrderPrice	OrderQuantity	CustomerName
1	12/22/2005	160	2	Smith
2	08/10/2005	190	2	Johnson
3	07/13/2005	500	5	Baldwin
4	07/15/2005	420	2	Smith
5	12/22/2005	1000	4	Wood
6	10/2/2005	820	4	Smith
7	11/03/2005	2000	2	Baldwin

The SQL COUNT function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. If we want to count how many orders has made a customer with CustomerName of Smith, we will use the following SQL COUNT expression:

SELECT COUNT (*) FROM Sales WHERE CustomerName = 'Smith'

Let's examine the SQL statement above. The COUNT keyword is followed by brackets surrounding the * character. You can replace the * with any of the table's columns, and your statement will return the same result as long as the WHERE condition is the same.

The result of the above SQL statement will be the number 3, because the customer Smith has made 3 orders in total.

If you don't specify a WHERE clause when using COUNT, your statement will simply return the total number of rows in the table, which in our case is 7:

SELECT COUNT(*) FROM Sales

How can we get the number of unique customers that have ordered from our store? We need to use the DISTINCT keyword along with the COUNT function to accomplish that:

SELECT COUNT (DISTINCT CustomerName) FROM Sales

The SQL SUM function is used to select the sum of values from numeric column. Using the Sales table, we can get the sum of all orders with the following SQL SUM statement:

SELECT SUM(OrderPrice) FROM Sales

As with the COUNT function we put the table column that we want to sum, within brackets after the SUM keyword. The result of the above SQL statement is the number 4990.

If we want to know how many items have we sold in total (the sum of OrderQuantity), we need to use this SQL statement:

SELECT SUM(OrderQuantity) FROM Sales

The SQL AVG function retrieves the average value for a numeric column. If we need the average number of items per order, we can retrieve it like this:

SELECT AVG(OrderQuantity) FROM Sales

Of course you can use AVG function with the WHERE clause, thus restricting the data you operate on:

SELECT AVG(OrderQuantity) FROM Sales WHERE OrderPrice > 200

The above SQL expression will return the average OrderQuantity for all orders with OrderPrice greater than 200, which is 17/5.

Best LED TV to Buy Under Rs 15000 in India
Best LED TV to Buy Under Rs 80000 in India

The SQL MIN function selects the smallest number from a numeric column. In order to find out what was the minimum price paid for any of the orders in the Sales table, we use the following SQL expression:

SELECT MIN(OrderPrice) FROM Sales

The SQL MAX function retrieves the maximum numeric value from a numeric column. The MAX SQL statement below returns the highest OrderPrice from the Sales table:

SELECT MAX(OrderPrice) FROM Sales

Read More

SQL Operators

SQL Operators

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s).There are two type of Operators, namely

• Comparison Operators 
• Logical Operators.

There are Operators That are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

• Arithmetic operators
• Comparison operators
• Logical operators
• Operators used to negate conditions

SQL Arithmetic Operators:

Assume variable a holds 10 and variable b holds 20, then:

Operator	Description	Example
+	Addition - Adds values on either side of the operator	a + b will give 30
-	Subtraction - Subtracts right hand operand from left hand operand	a - b will give -10
*	Multiplication - Multiplies values on either side of the operator	a * b will give 200
/	Division - Divides left hand operand by right hand operand	b / a will give 2
%	Modulus - Divides left hand operand by right hand operand and returns remainder	b % a will give 0

Comparison Operators:

Comparison operators are used to compare the column data with specific values in a condition.

Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions.

Assume variable a holds 10 and variable b holds 20, then:

Operator	Description	Example
=	Checks if the values of two operands are equal or not, if yes then condition becomes true.	(a = b) is not true.
!=	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(a != b) is true.
<>	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(a <> b) is true.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.	(a > b) is not true.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.	(a < b) is true.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.	(a >= b) is not true.
<=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.	(a <= b) is true.
!<	Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.	(a !< b) is false.
!>	Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.	(a !> b) is true.

SQL Logical Operators:

There are three Main Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.

Best LED TV to Buy Under Rs 20000 in India
Best 42 – 43 inch LED TV to Buy in India

Here is a list of all the logical operators available in SQL.

Operator	Description
ALL	The ALL operator is used to compare a value to all values in another value set.
AND	The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
ANY	The ANY operator is used to compare a value to any applicable value in the list according to the condition.
BETWEEN	The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS	The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
IN	The IN operator is used to compare a value to a list of literal values that have been specified.
LIKE	The LIKE operator is used to compare a value to similar values using wildcard operators.
NOT	The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
OR	The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
IS NULL	The NULL operator is used to compare a value with a NULL value.
UNIQUE	The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

Read More

ALL SQL COMANDS COLLECTION

Here is the collection of all sql commands. You can use this for recap and Quick study of SQL Commands.

This post consist of all SQL Commands with syntax and Functions for quick understanding. 

This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax:

All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).

Important point to be noted is that SQL is case insensitive which means SELECT and select have same meaning in SQL statements but MySQL make difference in table names. So if you are working with MySQL then you need to give table names as they exist in the database.

SQL SELECT Statement:

SELECT column1, column2....columnN

FROM   table_name;

SQL DISTINCT Clause:

SELECT DISTINCT column1, column2....columnN

FROM   table_name;

SQL WHERE Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION;

SQL AND/OR Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION-1 {AND|OR} CONDITION-2;

SQL IN Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  column_name IN (val-1, val-2,...val-N);

SQL BETWEEN Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  column_name BETWEEN val-1 AND val-2;

SQL Like Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  column_name LIKE { PATTERN };

SQL ORDER BY Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION

ORDER BY column_name {ASC|DESC};

SQL GROUP BY Clause:

SELECT SUM(column_name)

FROM   table_name

WHERE  CONDITION

GROUP BY column_name;

SQL COUNT Clause:

SELECT COUNT(column_name)

FROM   table_name

WHERE  CONDITION;

SQL HAVING Clause:

SELECT SUM(column_name)

FROM   table_name

WHERE  CONDITION

GROUP BY column_name

HAVING (arithematic function condition);

SQL CREATE TABLE Statement:

CREATE TABLE table_name(

column1 datatype,

column2 datatype,

column3 datatype,

.....

columnN datatype,

PRIMARY KEY( one or more columns )

);

Best 40 inch LED TV to Buy in India
Best 50 inch LED TV to Buy in India

SQL DROP TABLE Statement:

DROP TABLE table_name;

SQL CREATE INDEX Statement :

CREATE UNIQUE INDEX index_name

ON table_name ( column1, column2,...columnN);

SQL DROP INDEX Statement :

ALTER TABLE table_name

DROP INDEX index_name;

SQL DESC Statement :

DESC table_name;

SQL TRUNCATE TABLE Statement:

TRUNCATE TABLE table_name;

SQL ALTER TABLE Statement:

ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};

SQL ALTER TABLE Statement (Rename) :

ALTER TABLE table_name RENAME TO new_table_name;

SQL INSERT INTO Statement:

INSERT INTO table_name( column1, column2....columnN)

VALUES ( value1, value2....valueN);

SQL UPDATE Statement:

UPDATE table_name

SET column1 = value1, column2 = value2....columnN=valueN

[ WHERE  CONDITION ];

SQL DELETE Statement:

DELETE FROM table_name

WHERE  {CONDITION};

SQL CREATE DATABASE Statement:

CREATE DATABASE database_name;

SQL DROP DATABASE Statement:

DROP DATABASE database_name;

SQL USE Statement:

USE DATABASE database_name;

SQL COMMIT Statement:

COMMIT;

SQL ROLLBACK Statement:

ROLLBACK;

Read More

SQL Constraint

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.

If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

SQL CREATE TABLE + CONSTRAINT Syntax
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

Common types of constraints include the following:

• NOT NULL Constraint: Ensures that the column does not accept NULL values.
• DEFAULT Constraint: Provides a default value for a column when none is specified.
• UNIQUE Constraint: Ensures that all values in a column are different.
• CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
• Primary Key Constraint: Used to uniquely identify a row in the table.
• Foreign Key Constraint: Used to ensure referential integrity of the data.

SQL NOT NULL CONSTRAINT:

create table employee(eno int NOT NULL,ename varchar(25) NOT NULL,eaddress varchar(25));

+----------+---------------+------+------+---------+-------+

 | Field     | Type             | Null  | Key | Default | Extra |

+----------+---------------+------+------+---------+-------+

| eno         | int(11)         | NO   |          | NULL  |          |

| ename    | varchar(25)  | NO   |         | NULL  |          |

| eaddress | varchar(25) | YES  |         | NULL  |          |

+----------+---------------+------+------+---------+-------+

SQL UNIQUE CONSTRAINT:

create table employee1(eno int NOT NULL,ename varchar(24) NOT NULL,eaddress varchar(24),UNIQUE(eno));

+----------+---------------+------+-----+---------+-------+

| Field      | Type             | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| eno         | int(11)         | NO   | PRI | NULL    |       |

| ename    | varchar(24)  | NO   |        | NULL    |       |

| eaddress | varchar(24) | YES  |        | NULL    |       |

+----------+-------------+--------+-----+---------+-------+      

SQL UNIQUE CONSTRAINT ON ALTER TABLE:

alter table employee1 add UNIQUE(ename);

+----------+-------------+- -  - --+ -----+------- --+-------+

| Field      | Type           | Null    | Key | Default | Extra |

+----------+-------------+----   --+-- --- +- ---- ----+-------+

| eno         | int(11)         | NO    | PRI | NULL    |         |

| ename    | varchar(24) | NO    | UNI | NULL    |         |

| eaddress | varchar(24) | YES  |         | NULL    |         |

+----------+-- ------ -----+----- -+- ----+--- ------+-------+

SQL PRIMARY KEY CONSTRAINT:

create table employee2(eno int NOT NULL,ename varchar(25) NOT NULL,eaddress varchar(25),PRIMARY KEY(eno));

+----------+---------- ---+------+----- +---------+-------+

| Field      | Type           | Null | Key  | Default | Extra |

+----------+----------- --+------+----- +---------+-------+

| eno         | int(11)         | NO  | PRI | NULL   |         |

| ename    | varchar(25) | NO   |        | NULL   |         |

| eaddress | varchar(25) | YES |        | NULL   |         |

+----------+-------------+------+-----+---------+-------+

SQL FOREIGN KEY CONSTRAINT:

create table employee2(eno int NOT NULL,ename varchar(25) NOT NULL,eaddress varchar(25),PRIMARY KEY(eno),FOREIGN KEY(ename)REFERENCES employee2(ename));

+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| eno      | int(11)     | NO   | PRI | NULL    |       |

| ename    | varchar(25) | NO   | MUL | NULL    |       |

| eaddress | varchar(25) | YES  |     | NULL    |       |

+----------+-------------+------+-----+---------+-------+

SQL CHECK CONSTRAINT:

create table employee5(eno int NOT NULL,ename VARCHAR(20) not null,eaddress varchar(20),CHECK(eno));

+----------+---- ------ ---+------+-----+---------+-------+

| Field      | Type            | Null | Key | Default | Extra |

+----------+----- ---- ----+------+-----+---------+-------+

| eno         | int(11)         | NO   |       | NULL    |         |

| ENAME | varchar(20) | NO   |       | NULL    |        |

| eaddress | varchar(20)  | YES |       | NULL   |         |

+----------+----- --- -- ---+------+-----+---------+-------+

SQL DEFAULT CONSTRAINT:

mysql> create table employee6(eno int NOT NULL,ename varchar(25) NOT NULL,eaddress varchar(25) DEFAULT 'chennai');

+----------+----------- --+------+-----+---------+-------+

| Field      | Type           | Null | Key | Default | Extra |

+----------+------------ -+------+-----+---------+-------+

| eno         | int(11)        | NO   |       | NULL    |       |

| ename    | varchar(25) | NO   |      | NULL    |       |

| eaddress | varchar(25) | YES |       | chennai |       |

+----------+-------------+------+-----+---------+-------+

Read More

SQL ORDER BY

Another important clause used along with SQL SELECT is the ORDER BY clause. ORDER BY defines in what order do we get the data we have requested. Here is an example of using SQL ORDER BY:

 SELECT * FROM Cars ORDER BY Year;

The SQL statement above will select all columns from the Cars table, and will order them by the Year column, returning a result like this:

CarMake	Model	Year	Color
Honda	Accord EX	2002	Black
Toyota	Camry XLE	2005	Gray
BMW	3 Series Coupe	2008	Red
Lexus	ES 350	2008	Grayr

You can order by more than one column, by simply typing a comma-separated list of column names after the ORDER By clause. For example if you want to order by both Year and Color, you can use the following:

 SELECT * FROM Cars ORDER BY Year, Color; 

The result will be this:

CarMake	Model	Year	Color
Honda	Accord EX	2002	Black
Toyota	Camry XLE	2005	Gray
Lexus	ES 350	2008	Gray
BMW	3 Series Coupe	2008	Red

The Gray Lexus goes on top of the red BMW even though they are the same year, because the Color column is ordered alphabetically and Gray goes first.

So far all ORDER BY statements above ordered the data alphabetically, but what if we want the data ordered backwards? To answer this question we must introduce ASC and DESC keywords. The ASC and DESC keywords determine the order in which the results are displayed. If you don't specify ASC or DESC after the ORDER BY clause, the SQL interpreter defaults the order to ascending, which means that the following 2 SQL statements yield the same result:

 SELECT * FROM Cars ORDER BY CarMake; 

 SELECT * FROM Cars ORDER BY CarMake ASC; 

And here is the result:

CarMake	Model	Year	Color
BMW	3 Series Coupe	2008	Red
Honda	Accord EX	2002	Black
Lexus	ES 350	2008	Gray
Toyota	Camry XLE	2005	Gray

If you specify DESC instead of ASC in the SQL expression above, you will get this result:

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Gray
Lexus	ES 350	2008	Gray
Honda	Accord EX	2002	Black
BMW	3 Series Coupe	2008	Red

You can specify ASC or DESC, after each column following ORDER BY. For example:

 SELECT * FROM Cars ORDER BY Year ASC, Color DESC; 

The result will be:

CarMake	Model	Year	Color
Honda	Accord EX	2002	Black
Toyota	Camry XLE	2005	Gray
BMW	3 Series Coupe	2008	Red
Lexus	ES 350	2008	Gray

First the result set is ordered by Year ascending, and then for the rows having one and the same year as the BMW and Lexus ones, the Color column is ordered descending, that's why the Red BMW comes on top of the Gray Lexus.

Read More

SQL TRUNCATE TABLE

The SQL TRUNCATE TABLE clause deletes all rows from a database table. Here is the SQL TRUNCATE TABLE syntax:

 TRUNCATE TABLE Weather 

The SQL TRUNCATE TABLE clause does the same as a SQL DELETE clause which doesn't have a SQL WHERE clause. The following two SQL statements are equivalent:

 TRUNCATE TABLE Weather 


 DELETE FROM Weather 

Use SQL TRUNCATE TABLE only when you want to delete all rows in a table.
Best 60 – 65 inch Smart LED TV to Buy in India
Best 55 inch LED TV to Buy in India 

Read More

SQL DISTINCT Command

SQL DISTINCT COMMAND

The SQL DISTINCT clause works in conjunction with the SQL SELECT clause and selects only distinct (unique) data from a database table(s). Here is an example of SQL DISTINCT clause:

 SELECT DISTINCT Column1 FROM Table1

As you can see the DISTINCT keyword goes immediately after the SELECT clause and is then followed by a list of one or more column names. I'll give you an example why you might need to use the DISTINCT SQL clause. I'll use the Weather table from the SQL WHERE tutorial to demonstrate the SQL DISTINCT application:

City	AverageTemperature	Date
New York	22 C	10/10/2005
Seattle	21 C	10/10/2005
Washington	20 C	10/10/2005
New York	18 C	10/09/2005
Seattle	20 C	10/09/2005
Washington	17 C	10/09/2005

Consider the following SQL statement utilizing SQL DISTINCT:

 SELECT DISTINCT City  FROM Weather 

This SQL DISTINCT expression will return a list with all cities found in the City column of the Weather table, but it will remove the duplicates and leave only a single entry for each city:

City

New York

Seattle

Washington

You can use the SQL DISTINCT with any table column for example with the AverageTemperature:

 SELECT DISTINCT AverageTemperature  FROM Weather 

The result of this SQL DISTINCT will be:

AverageTemperature

22 C

21 C

20 C

18 C

17 C

You can use the SQL DISTINCT with more than one column and if you do that, the result will have all distinct combinations of values for all columns. For example if our Weather table has the following entries:

City	AverageTemperature	Date
New York	22 C	10/10/2005
New York	22 C	10/09/2005
New York	20 C	10/08/2005
New York	20 C	10/07/2005
New York	18 C	10/06/2005

And we run the following SQL DISTINCT statement:

 SELECT DISTINCT City, AverageTemperature  FROM Weather 

The result will be:

City	AverageTemperature
New York	22 C
New York	20 C
New York	18 C

Read More

SQL DELETE Command

The SQL DELETE clause is used to delete data from a database table. The simplest SQL DELETE syntax looks like this:

DELETE FROM Table1 

The SQL DELETE statement above will delete all data from the Table1 table.

Most of the time we will want to delete only table rows satisfying certain search criteria defined in the SQL WHERE clause. We will use the Weather table again to illustrate how to use SQL DELETE to delete a limited number of rows from a table:

City	AverageTemperature	Date
New York	22 C	10/10/2005
Seattle	21 C	10/10/2005
Washington	20 C	10/10/2005
New York	18 C	10/09/2005
Seattle	20 C	10/09/2005
Washington	17 C	10/09/2005

If we wanted to delete all rows containing Weather data for New York, we would use the following SQL DELETE statement:

 DELETE FROM Weather WHERE City = 'New York' 

Be extremely careful when using SQL DELETE, as you cannot restore data once you delete it from the table. You might want to make a backup of important data before performing delete on it.

Read More

SQL UPDATE Command

So far we only looked at retrieving data from SQL database, but we never talked about modifying/updating data. The SQL UPDATE command is used to modify data stored in database tables.

If you want to update the email of one of the users in our Users table, you'll use a SQL statement like the one below:

UPDATE Users SET Email = 'new_email_goes_here@yahoo.com' WHERE Email = 'sgrant@softsekar.com'

Let's examine the statement above. The first row has the keyword UPDATE followed by the name of the table we are updating. The second row is the row that defines the changes made to the database fields using the keyword SET followed by the column name, equal sign and the new value for this column. You can have more than one assignment of new value after the SET keyword, for example if you want to update both the email and the city you will use the SQL statement below:

UPDATE Users SET Email = 'new_email_goes_here@yahoo.com', City = 'San Francisco' WHERE Email = 'sgrant@softsekar.com'

The third line is our WHERE clause, which specifies which record(s) to update. In our case it says to update the Email filed of the row having email sgrant@softsekar.com.

What happens if you remove the WHERE clause and your SQL query looks like this:

UPDATE Users SET Email = 'new_email_goes_here@yahoo.com'

The answer is that all Email entries in the Users table will be changed to new_email_goes_here@yahoo.com. Most likely you will not want to do something like this, but you might have a case when you need to update several table rows at once. For example if one of the company's offices has been moved from San Francisco to Los Angeles you might want to update all users with City San Francisco to Los Angeles (we assume that the employees have moved too). To do that, use the following SQL statement:

UPDATE Users SET City = 'Los Angeles' WHERE City = 'San Francisco'

In both UPDATE example having WHERE clause above, I've changed a table field to new value, using the same field in the WHERE clause criteria. This was purely coincidental and you can update different field(s) than the one used in your WHERE criteria, for example:

UPDATE Users SET Email = 'new_email_goes_here@yahoo.com' WHERE FirstName = 'Stephen' AND LastName = 'Grant'

When updating make sure that the WHERE clause criteria you have specified updates only the rows you want. Using the example above if you didn't have FirstName = 'Stephen' in your WHERE criteria, you would have updated 2 records (Susan Grant and Stephen Grant), because both these users have one and the same last name.

Read More

SQL WHERE Command

The SQL WHERE keyword is used to select data conditionally, by adding it to already existing SQL SELECT query. The WHERE keyword can be used to insert, update and delete data from table(s), but for now we'll stick with conditionally retrieving data, as we already know how to use the SELECT keyword.

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

Operator   Description

=                 Equal

<>                 Not equal. Note: In some versions of SQL this operator may be written as !=

>                 Greater than

<                 Less than

>=                 Greater than or equal

<=                 Less than or equal

BETWEEN Between an inclusive range

LIKE         Search for a pattern

IN                 To specify multiple possible values for a column

In order to illustrate better the WHERE keyword applications, we are going to add 2 columns to the Users table we used in the previous chapters and we'll also add a few more rows with actual data entries:

FirstName	LastName	DateOfBirth	Email	City
John	Smith	12/12/1969	john.smith@softsekar.com	New York
David	Stonewall	01/03/1954	david@softsekar.com	San Francisco
Susan	Grant	03/03/1970	susan.grant@softsekar.com	Los Angeles
Paul	O'Neil	09/17/1982	paul.oneil@softsekar.com	New York
Stephen	Grant	03/03/1974	sgrant@softsekar.com	Los Angeles

Consider the following SQL query:

SELECT FirstName, LastName, City FROM Users WHERE City = 'Los Angeles'

The result of the SQL expression above will be the following:

FirstName	LastName	City
Susan	Grant	Los Angeles
Stephen	Grant	Los Angeles

Our SQL query used the "=" (Equal) operator in our WHERE criteria:

City = 'Los Angeles'

As you can see we have selected only the users which entries have the value 'Los Angeles' in the City column. You may also have noticed that we put the Los Angeles string value into single quotes. Whenever you use string (character) values in your SQL queries, you have to put them between single quotes. For example the SQL query below will fail because it uses double quotes instead of single quotes for the string value:

SELECT FirstName, LastName FROM Users WHERE City = "Los Angeles"

But what to do if we want to retrieve all users having LastName O'Neil? The SQL statement below will fail:

SELECT FirstName, LastName FROM Users WHERE LastName = 'O'Neil'

The reason for the failure is the single quote which is part of the string we used in our WHERE criteria. The SQL engine will try to interpret our SQL statement and will consider the single quote inside the string as the end of that string. The remaining part of the SQL statement will be Neil', which cannot be interpreted correctly, thus we'll get an error. So how do we deal with strings having single quotes then?

The answer is simple - by replacing all single quotes in our string with two single quotes. When we have two single quotes together, they are interpreted by SQL as one single quote. Here is our improved SQL statement which will work correctly:

SELECT FirstName, LastName FROM Users WHERE LastName = 'O''Neil'

We used the = (Equal) operator in the examples above, but you can use any of the following comparison operators in conjunction with the SQL WHERE keyword:

<> (Not Equal)

SELECT FirstName, LastName FROM Users WHERE FirstName <> 'Jon'

> (Greater than)

SELECT FirstName, LastName FROM Users WHERE DateOfBirth > '02/03/1970'

>= (Greater or Equal)

SELECT FirstName, LastName FROM Users WHERE DateOfBirth >= '02/03/1970'

< (Less than)

SELECT FirstName, LastName FROM Users WHERE DateOfBirth < '02/03/1970'

<= (Less or Equal)

SELECT FirstName, LastName FROM Users WHERE DateOfBirth <= '02/03/1970'

In addition to the comparison operators you can use WHERE along with logical operators. SQL logical operators are used to combine two or more criterions in the WHERE clause of an SQL statement.

If we want to select all users from our Users table, which live in New York and are born after 10/10/1975 we will use the following SQL query:

SELECT FirstName, LastName, DateOfBirth, Email, City FROM Users WHERE City = 'New York' AND DateOfBirth > '10/10/1975'

Here is the result of the above SELECT:

FirstName	LastName	DateOfBirth	Email	City
Paul	O'Neil	09/17/1982	paul.oneil@softsekar.com	New York

As you can see we now have to criteria concatenated with the AND logical operator, which means that both conditions have to be true.

If we want to select all users from our Users table, which live in New York or are born after 10/10/1975 we will use the following SQL query:

SELECT FirstName, LastName, DateOfBirth, Email, City FROM Users WHERE City = 'New York' OR DateOfBirth > '10/10/1975'

The result is:

FirstName	LastName	DateOfBirth	Email	City
John	Smith	12/12/1969	john.smith@softsekar.com	New York
Paul	O'Neil	09/17/1982	paul.oneil@softsekar.com	New York
Stephen	Grant	03/03/1974	sgrant@softsekar.com	Los Angeles

This time the two criteria are joined with OR, which means that all rows satisfying at least one of them will be returned.

You can use the NOT logical operator in your SQL statements too. Consider the following example:

SELECT FirstName, LastName, DateOfBirth, Email, City FROM Users WHERE City NOT LIKE '%York%'

This statement will select all users whose city name doesn't contain the string York. (I've explained the LIKE statement below).

LIKE (similar to)

SELECT FirstName, LastName FROM Users WHERE FirstName LIKE 'S%'

We'll talk about the LIKE keyword later, but for now it's enough to know that the SQL statement above returns all users with first name starting with the letter S. When you use the % character inside a LIKE expression, the % is considered to be a wildcard (note that the syntax I've used is for SQL Server, and different SQL implementations may have different syntax for wildcard character %).

You can use the WHERE keyword along with the BETWEEN keyword which defines a range:

SELECT FirstName, LastName FROM Users WHERE DateOfBirth BETWEEN '02/03/1970' AND '10/10/1972'

You can use the WHERE keyword along with the IN keyword which defines a criteria list:

SELECT FirstName, LastName FROM Users WHERE City IN ('Los Angeles', 'New York')

The SQL statement above will return all users from Los Angeles and New York.

Read More

SQL command INSERT INTO

The SQL INSERT INTO clause facilitates the process of inserting data into a SQL table. Here is how you can insert a new row into the Weather table, using SQL INSERT INTO:

 INSERT INTO Weather (City, AverageTemperature, Date) VALUES ('Los Angeles', 20, '10/10/2005')

The result of the execution of the SQL INSERT INTO above will look like this:

City	AverageTemperature	Date
New York	22 C	10/10/2005
Seattle	21 C	10/10/2005
Washington	20 C	10/10/2005
Los Angeles	20 C	10/10/2005

You can produce the same result, with a slightly modified SQL INSERT INTO syntax:

 INSERT INTO Weather VALUES ('Los Angeles', 20, '10/10/2005')

You are allowed to omit the list of column names in the SQL INSERT INTO clause, if you enter values for each of the table columns.

When using SQL INSERT INTO you might not want to enter values for all columns and in this case you have to specify the list of columns you are entering values for. If you do not enter values for all columns, then the columns you have omitted must allow NULL values or at least have a default value defined. The following SQL INSERT example enters only 2 of the 3 columns in the Weather table:

 INSERT INTO Weather (City, Date) VALUES ('Boston', '10/10/2005')

The result of this SQL INSERT will be as follows:

City	AverageTemperature	Date
New York	22 C	10/10/2005
Seattle	21 C	10/10/2005
Washington	20 C	10/10/2005
Los Angeles	20 C	10/10/2005
Boston	NULL	10/10/2005

We've inserted a new row for Boston, but we haven't received the temperature value for 10/10/2005 that's why we didn't enter it.

Read More

SQL SELECT

SQL SELECT Command

SQL Select Command Video tutorial

What do we use SQL commands for? A common use is to select data from the tables located in a database. Immediately, we see two keywords: we need to SELECT information FROM a table. (Note that a table is a container that resides in the database where the data is stored. For more information about how to manipulate tables, go to the Table Manipulation Section). Hence we have the most basic SQL query structure:

SELECT "column_name" FROM "table_name";

There are three ways we can retrieve data from a table:

• Retrieve one column 
• Retrieve multiple columns 
• Retrieve all columns 

Let's use the following table to illustrate all three cases:

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
Los Angeles	300	Jan-08-1999
Boston	700	Jan-08-1999

Select One Column

To select a single column, we specify the column name between SELECT and FROM as follows:

SELECT Store_Name FROM Store_Information;

Result:

Store_Name

Los Angeles

San Diego

Los Angeles

Boston

Select Multiple Columns

We can use the SELECT statement to retrieve more than one column. To select Store_Name and Sales columns from Store_Information, we use the following SQL:

SELECT Store_Name, Sales FROM Store_Information;

Result:

Store_Name	Sales
Los Angeles	1500
San Diego	250
Los Angeles	300
Boston	700

Select All Columns

There are two ways to select all columns from a table. The first is to list the column name of each column. The second, and the easier, way is to use the symbol *. For example, to select all columns from Store_Information, we issue the following SQL:

SELECT * FROM Store_Information;

Result:

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
Los Angeles	300	Jan-08-1999
Boston	700	Jan-08-1999

Exercises

For these exercises, assume we have a table called Users with the following columns:

Table Users

Column Name

First_Name

Last_Name

Birth_Date

Gender

Date_Joined

1. Which of the following SQL statement is incorrect? (There can be more than one answer) 

a) SELECT * FROM Users; 

b) SELECT First_Name, Gender, Last_Name FROM Users; 

c) SELECT First_Name, Last_Name Users; 

d) SELECT All FROM Users;

2. (True Or False) In SQL, the order of the columns in a SELECT statement must be the same as the order of the columns in the underlying table. For example, in the table Users, you must select First_Name before Last_Name.

3. (True Or False) The following two SQL statements are equivalent: 

a) Select * From Users; 

b) SELECT * FROM Users;

Answer

1. c), d).

2. False. The order of columns in a table has no relationship to the order of columns in a SELECT statement.

3. False. SQL keywords such as SELECT and FROM are not case-sensitive. Table names and column names, on the other hand, can be configured to be case-sensitive or case-insensitive depending on the database being used.

Read More

RELATIONAL DATABASE MANAGEMENT SYSTEMS AND DATABASE TABLES

Short for Relational DataBase Management System and pronounced as separate letters, a type of database management system (DBMS) that stores data in the form of related tables. Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways.

RDBMS data is structured in database tables, fields and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.  Many modern DBMS do not conform to the Codd’s definition of a RDBMS, but nonetheless they are still considered to be RDBMS.
Almost all full-scale database systems are RDBMS's. Small database systems, however, use other designs that provide less flexibility in posing queries.

What is table ?

• The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.
• A table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows.

Remember, a table is the most common and simplest form of data storage in a relational database.

What is field?

• A table consists of several records(row), each record can be broken into several smaller entities known as Fields.
•  A space allocated for a particular item of information. A tax form, for example, contains a number of fields: one for your name, one for your Social Security number, one for your income, and so on. In database systems, fields are the smallest units of information you can access. In spreadsheets, fields are called cells.
• Most fields have certain attributes associated with them. For example, some fields are numeric whereas others are textual, some are long, while others are short. In addition, every field has a name, called the field name.

A field is a column in a table that is designed to maintain specific information about every record in the table.


What is record or row?

• A collection of fields is called a record.
• It is also called a row of data, is each individual entry that exists in a table. 
• A record is a horizontal entity in a table.

What is column?
A column is a vertical entity in a table that contains all information associated with a specific field in a table.
In Relational table, a column is a set of value of a particular type. The term Attribute is also used to represent a column. For example, in Employee table, Name is a column that represent names of employee.

Name
Adam
Alex
Stuart
Ross

Read More

SQL

What is SQL?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.

SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.

Also, they are using different dialects, such as:

• MS SQL Server using T-SQL,
• Oracle using PL/SQL,
• MS Access version of SQL is called JET SQL (native format), etc

Why SQL?

• Allows users to access data in relational database management systems.
• Allows users to describe the data.
• Allows users to define the data in database and manipulate that data.
• Allows to embed within other languages using SQL modules, libraries & pre-compilers.
• Allows users to create and drop databases and tables.
• Allows users to create view, stored procedure, functions in a database.
• Allows users to set permissions on tables, procedures, and views

SQL Commands:

SQL commands are instructions, coded into SQL statements, which are used to communicate with the database to perform specific tasks, work, functions and queries with data.

SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

• Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
• Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. 
• These Data Manipulation Language commands are: SELECT, INSERT, UPDATE, and DELETE.
• Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
• Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

RDBMS data is structured in database tables, fields and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.  Many modern DBMS do not conform to the Codd’s definition of a RDBMS, but nonetheless they are still considered to be RDBMS.

What is table ?
The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

What is field?
A table consists of several records(row), each record can be broken into several smaller entities known as Fields.

What is record or row?

• A collection of fields is called a record.
• It is also called a row of data, is each individual entry that exists in a table. 
• A record is a horizontal entity in a table.

What is column?
A column is a vertical entity in a table that contains all information associated with a specific field in a table.
In Relational table, a column is a set of value of a particular type. The term Attribute is also used to represent a column. For example, in Employee table, Name is a column that represent names of employee.

Name
Adam
Alex
Stuart
Ross

Read More