SQL Create DB

The CREATE DATABASE statement is used to Create a Database.

SQL CREATE DATABASE Syntax

CREATE DATABASE  DataBasename

CREATE DATABASE Example

We use the following CREATE DATABASE statement:

CREATE DATABASE Inventory

Database tables can be added with the CREATE TABLE statement.

Posted by Siva 0 comments

Read More

SQL Order By

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.

The "custmast" table:

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Srivilliputtur	60	30
5	Kodee	Srivilliputtur	60	30

Now we want to select all the CustMast from the table above, however, we want to sort the persons by their custname.

We use the following SELECT statement:

SELECT * FROM CustMast
ORDER BY CustName 

               (OR)

SELECT * FROM CustMast
ORDER BY CustName ASC

custcode	custname	city	qty	rate
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
5	Kodee	Srivilliputtur	60	30
5	Kodee	Srivilliputtur	60	30
1	Siva	Srivilliputtur	50	70
4	Vijay	Chennai	30	30

ORDER BY DESC Example

Now we want to select all the persons from the table above, however, we want to sort the persons descending by their custname

We use the following SELECT statement:

SELECT * FROM CustMast
ORDER BY CustName DESC

custcode	custname	city	qty	rate
4	Vijay	Chennai	30	30
1	Siva	Srivilliputtur	50	70
5	Kodee	Srivilliputtur	60	30
5	Kodee	Srivilliputtur	60	30
3	Kanna	Madurai	100	200
2	Bala	Sivakasi	30	56

Posted by Siva 0 comments

Read More

SQL AND & OR

 The AND operator displays a record if both the first condition and the second condition is true.
 The OR operator displays a record if either the first condition or the second condition is true.


The syntax for a compound condition is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+


The "custmast" Table:

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Srivilliputtur	60	30
5	Kodee	Srivilliputtur	60	30

                                                                  AND

Ex:1

Select  *   from Custmast  WHERE custname='Siva' AND city='Srivilliputtur'

Output :

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70

Ex:2

Select * From Custmast WHERE custname='Siva' AND City='Sivakasi'

Output :

     No Records

                                                                     OR

The Above Example to OR condition

SELECT * FROM custmast WHERE custname='Siva' OR City='Sivakasi'

Output :

  

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56

                                                  Combining AND & OR

You can also combine AND and OR (use parenthesis to form complex expressions).
Now we want to select only the persons with the custname equal to "Siva" AND the City equal to "Srivilliputtur" OR to "Chennai":

We use the following SELECT statement

SELECT * FROM custmast WHERE

custname='Siva'

AND (City='Srivilliputtur' OR City='Chennai')

Output :

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70

Posted by Siva 0 comments

Read More

SQL Where

The WHERE clause is used to extract only those records that fulfill a specified criterion.

SQL WHERE Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

Table Name : CustMast

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Srivilliputtur	60	30
5	Kodee	Srivilliputtur	60	30

                                                         
                                                         For TEXT  values:

Ex: 1

Select CustName,City from CustMast where Custname='Siva'

OutPut :

custname	city
Siva	Srivilliputtur

Ex: 2

Wrong Below Coding

Select CustName,City from CustMast where Custname=Siva

Error : Invalid column name 'siva'.

Note: ( SQL uses single quotes around text values (most database systems will also accept double quotes).

Although, numeric values should not be enclosed in quotes.)

                                                   For NUMERIC  values:

This is correct:

SELECT * FROM Persons WHERE Year=1965

This is wrong:

SELECT * FROM Persons WHERE Year='1965'

Operators Allowed in the WHERE Clause

With the WHERE clause, the following operators can be used:

Operator	Description
=	Equal
<>	Not equal
>	Greater then
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

Note: In some versions of SQL the <> operator may be written as !=

Posted by Siva 0 comments

Read More

SQL Distinct

The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily mean that there will be redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCTafter SELECT. The syntax is as follows:

SELECT DISTINCT "column_name"
FROM "table_name"

For example, 

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Srivilliputtur	60	30
5	Kodee	Srivilliputtur	60	30

Ex:1

Select Distinct city from custmast

Output:

  

city

Srivilliputtur

Sivakasi

Madurai

Chennai

Ex:2

Select Distinct * from custmast

OutPut :

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Srivilliputtur	60	30

Posted by Siva 0 comments

Read More