SQL: HAVING CLAUSE

SQL HAVING clause with examples.

DESCRIPTION

The SQL HAVING Clause is mostly combination with GROUP BY Clause to restrict the result set that get from  groups of returned rows to only those whose the condition will be TRUE.

SYNTAX

Syntax for SQL HAVING Clause is:

SELECT expression1, expression2, ... expression_n,  aggregate_function (expression) FROM tables WHERE conditions GROUP BY expression1, expression2, ... expression_n HAVING condition;

·         aggregate_function is a function such as SQL SUM function, SQL MIN function, or SQL MAX function.

·         expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.

·         condition is the condition that is used to restrict the groups of returned rows.

EXAMPLE - USING SUM FUNCTION

SQL HAVING clause example that uses the SQL SUM function.

SELECT department, SUM(salary) AS "Total salary " FROM Employee GROUP BY department HAVING SUM(salary) > 1000;

SQL SUM functions to return the name of the department and the total salary (in the associated department). The SQL HAVING clause will filter the results so that only departments with sales greater than 1000 will be returned.

EXAMPLE - USING COUNT FUNCTION

The HAVING clause with the SQL COUNT function.

SELECT department, COUNT(*) AS "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department HAVING COUNT(*) > 10;

SQL COUNT functions to return the name of the department and the number of employees (in the associated department) that make over 25,000.

EXAMPLE - USING MIN FUNCTION

We could use the HAVING clause with the SQL MIN function.

SQL MIN functions to return the name of each department and the minimum salary in the department.

SELECT department, MIN(salary) AS "Minimum salary" FROM employees GROUP BY department HAVING MIN(salary) > 35000;

SQL: GROUP BY CLAUSE

SQL GROUP BY clause with examples.

DESCRIPTION

SQL GROUP BY clause  used in a SELECT statement to collect data having multiple records and group them into results by one or more columns.

SYNTAX

·         SELECT expression1, expression2, ... expression_n,   aggregate_function (expression) FROM tables WHERE conditions GROUP BY expression1, expression2, ... expression_n;

·         expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.

·         aggregate_function  function as SUM function, COUNT function, MIN function, or MAX function.

·         tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

·         conditions are conditions that must be met for the records to be selected.

EXAMPLE - USING SUM FUNCTION

SQL GROUP BY query example that uses the SQL SUM function.

SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department;

This GROUP BY example uses the SUM function to return the name of the department and the total sales (for the department).

EXAMPLE - USING COUNT FUNCTION

GROUP BY clause with the SQL COUNT function.

This GROUP BY example with COUNT function returns the name of the department and the number of employees (in that department where salary is 20000;

SELECT department, COUNT(*) AS "No.  employee" FROM employees WHERE salary > 20000 GROUP BY department;

EXAMPLE - USING MIN FUNCTION

GROUP BY clause with the SQL MIN function.

GROUP BY example uses with MIN function to return the name of each department and minimum salary in the department.

SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department; 

SQL: EXISTS CONDITION

Use of SQL EXISTS condition with  examples.

DESCRIPTION

SQL EXISTS condition is used for combination with  sub query and it is considered to be met the condition, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

Syntax for SQL EXISTS condition is:

WHERE EXISTS ( subquery );

subquery is a SELECT statement.

EXAMPLE - WITH SELECT STATEMENT

Start with simple example.

SQL SELECT statement that uses the SQL EXISTS condition:

SELECT Amount FROM Salary WHERE  Employee_Id EXISTS (SELECT  employee_Id   FROM employee  WHERE designation=’Manager’);

SQL Exist will return the employee_id and matching the employee_id relation with the salary will return the amount.

EXAMPLE - WITH SELECT STATEMENT USING NOT EXISTS

EXISTS condition can also be combined with the NOT operator.

For example,

SELECT * FROM suppliers WHERE NOT EXISTS (SELECT *  FROM orders WHERE suppliers.supplier_id = orders.supplier_id);

SQL EXISTS example will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

SQL: DROP TABLE STATEMENT

The SQL DROP TABLE statement with examples.

DESCRIPTION: SQL DROP TABLE statement allows to remove or delete a table from the SQL database.

SYNTAX

DROP TABLE table_name;

table_name is the name of the table to remove from the database.

EXAMPLE

An example shows how to drop a table using the SQL DROP TABLE statement.

For example:

DROP TABLE employee;

DROP TABLE statement example would drop the table called employee.

SQL: DISTINCT CLAUSE

SQL DISTINCT clause with examples.

DESCRIPTION

SQL DISTINCT clause is used to remove duplicates from the result set of a SELECT statement.

SYNTAX

The syntax for the SQL DISTINCT clause is:

SELECT DISTINCT expressions FROM tables WHERE conditions;

expressions are the columns.

tables are the tables that you wish to retrieve records from..

conditions are conditions that must be met for the records to be selected.

EXAMPLE - WITH SINGLE FIELD

the simplest SQL DISTINCT query example. the SQL DISTINCT clause to return a single field that removes the duplicates from the result set.

For example:

SELECT DISTINCT designation FROM employee;

This SQL DISTINCT example would return all unique designation values from the employee table.

EXAMPLE - WITH MULTIPLE FIELDS

How you might use the SQL DISTINCT clause to remove duplicates from more than one field in your SQL SELECT statement.

For example:

SELECT DISTINCT designation, location FROM employee;

This SQL DISTINCT clause example would return each unique designation and location combination. In this case, the DISTINCT applies to each field listed after the DISTINCT keyword.

SQL: DATA TYPES

Datatype using in SQL syntax;

Type	Syntax	description
integer	integer	contaning the number values
bit	bit(x)	x is  number of bits to store.
bit varying	bit varying(x)	x is  number of bits to store. The length can vary based on x.
character	char(x)	x is  number of characters to store.
character varying	varchar2(x)	x is the number of characters to store. This data type does NOT space pad.
date	date	Stores year, month, and day values.
day-time interval		Contains a day value, an hour value, a minute value, and/or a second value.
decimal	decimal(p,s)	p is a precision value; s is a scale value.
double precision	double precision	Double-precision floating point number
float	float(p)	p is a precision value.
numeric	numeric(p,s)	p is a precision value; s is a scale value. For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
real	real	Single-precision floating point number
smallint	smallint	small interger values
time	time	Stores the hour, minute, and second values.
time with time zone	time with time zone	Exactly the same as time, but also stores an offset from UTC of the time specified.
timestamp	timestamp	Stores year, month, day, hour, minute, and second values.
timestamp with time zone	timestamp with time zone	Exactly the same as timestamp, but also stores an offset from UTC of the time specified.
year-month interval		Contains a year value, a month value, or both.

SQL: COUNT FUNCTION

SQL COUNT function with syntax, examples, and practice exercises.

DESCRIPTION

 SQL COUNT function is generally used to count the number of rows returned in a SELECT statement.

SYNTAX

Syntax for SQL COUNT function is:

SELECT COUNT(expression) FROM tables WHERE conditions;

expression can be a numeric field or formula.

ONLY INCLUDES NOT NULL VALUES

SQL COUNT function example that demonstrates how NULL values are evaluated by the COUNT function.
For example, if you have the following table called employee:

employee_id	employee_name	age
1	bob	22
2	julie
3	sweety

if you ran the following SQL SELECT statement that uses the SQL COUNT function:

SELECT COUNT(employee_id) FROM employee;

it return 3 since all employee_id values in the query's result set are NOT NULL.
SQL SELECT statement that uses the SQL COUNT function:

SELECT COUNT(age) FROM employee;

It will only return 1, since only one age value in the query's result set is NOT NULL.

EXAMPLE - SINGLE EXPRESSION

The simplest way to use the SQL COUNT function would be to return a single field that returns the COUNT of something.

For example, return the list of employee  count who is getting more than 20,000

SELECT COUNT(*) AS "Number of employees" FROM employees WHERE salary > 20000;

As a result, "Number of employees" will display as the field name when the result set is returned.

EXAMPLE - GROUP BY CLAUSE

The SQL GROUP BY clause with the SQL COUNT function.

For example, you could use the SQL COUNT function to return the name of the department and the number of employees (in the associated department) that make over 20,000 / year.

SELECT designation, COUNT(*) AS "Number of employees" FROM employees WHERE salary > 20000 GROUP BY designation;

SQL: BETWEEN CONDITION

SQL BETWEEN condition with examples and syntax.

DESCRIPTION

SQL BETWEEN Condition is using to retrieve values within the given range in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

syntax for BETWEEN Condition using in SQL:

expression BETWEEN value1 AND value2;

PARAMETERS OR ARGUMENTS

expression is a column or calculation.

value1 and value2 create an inclusive range that expression is compared to.

-          The SQL BETWEEN Condition returns the records where expression is within the range from value1 to value2 (inclusive).

EXAMPLE - WITH NUMERIC

Below numeric example uses the BETWEEN condition to retrieve values within a numeric range.

For example:

SELECT * FROM employee WHERE age BETWEEN 22 AND 30;

This SQL BETWEEN example would return all rows where the age is between 22 and 30.

It is same as the following SQL SELECT statement:

SELECT * FROM employee WHERE age  >= 22 AND age  <= 30;

EXAMPLE - WITH DATE

You can use  BETWEEN condition with Dates also. The below  example uses the SQL BETWEEN condition to retrieve values within a date range.

For example:

SELECT *  FROM employee WHERE  Joining_date BETWEEN TO_DATE ('2010/01/01', 'yyyy/mm/dd') AND TO_DATE ('2012/12/31', 'yyyy/mm/dd');

EXAMPLE - USING NOT OPERATOR

The SQL BETWEEN condition can also be combined with the SQL NOT operator.

For example:

SELECT * FROM  Employee WHERE age NOT BETWEEN 25 AND 30;

SQL BETWEEN condition example would return all rows where the age is NOT between 25 and 30.

It is same as  following SQL SELECT statement:

SELECT * FROM Employee WHERE age  < 25 OR age  > 30;

SQL: AVG FUNCTION

SQL AVG functions with syntax and examples.

DESCRIPTION

SQL AVG function used to get the average of an expression of SELECT statement.

SYNTAX : for the SQL AVG function is:

SELECT AVG(expression) FROM tables WHERE conditions;

expression will be a numeric fields or formula.

EXAMPLE - WITH SINGLE  VALUE EXPRESSION

For example, you might wish to know how the average salary of all employee that are in the assosiate designation.

SELECT AVG(salary) AS "Average Salary" FROM employee WHERE designation = ‘assosiate’;

SQL Example alias AVG(salary) expression as "Average salary ". As a result, "Average salary" will display as the field name when the result set is returned.

EXAMPLE - USING FORMULA

SQL AVG function is not needed to be a single field. You can use it is a formula. For example, you might want the average profit for a product. Average profit is calculated as In Hand salary less cost.

SELECT AVG(salary - tax) AS "In Hand Salary" FROM employee;

You can also want to perform a mathematical operation in  AVG function. For example, you can calculate the 12 month average salary

SELECT AVG(salary * 12) AS “Yearly” FROM employee;

EXAMPLE – USING SQL GROUP BY

Many time its required to use the SQL GROUP BY clause with the AVG function.

e.g. , AVG function to return the name of the department and the average sales (in the associated department).

SELECT department, AVG(sales) AS "Average Sales" FROM order_details GROUP BY department;

SQL: AND & OR Conditions

SQL tutorial explains how to use AND and OR condition together in a query with syntax and examples.

Description

The SQL AND and OR can be combined in SELECT, INSERT, UPDATE, or DELETE statement.

When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition. (Like we are doing in Maths)

Syntax

The syntax for the SQL AND Condition is:

WHERE condition1 AND condition2

... OR condition_n;

Parameters or Arguments

condition1, condition2, condition_n are the conditions that evaluated to determine if the records will be selected.

SQL Example – On SELECT Statement

combines the AND condition and OR condition in a SELECT query.

For example:

SELECT *

FROM employee

WHERE (city = 'delhi' AND CName = 'IBM')

OR (ranking >= 10);

This SQL SELECT example would return all employee  that reside in delhi whose company name is IBM  and ranking is greater than or equal to 10. The brackets determine the order that the AND and OR conditions are evaluated. The next example takes a look at a more complex statement.

For example:

SELECT employee_id

FROM employee

WHERE (CName = 'IBM')

OR (CName = ‘Infosys' AND city = 'Bombay')

OR (name = 'Wipro' AND status = 'Active' AND city = 'Chennai');

This SQL SELECT statement would return all supplier_id values where the supplier's name is IBM or the name is Hewlett Packard and the city is Atlantic City or the name is Gateway, the status is Active, and the city is Burma.

SQL Example - INSERT Statement

example demonstrates how the SQL AND condition and SQL OR condition can be combined in the 

INSERT statement.

For example:

INSERT INTO Employee

(Employee_id, Employee_name)

SELECT account_no, customer_name

FROM customers

WHERE (customer_name = 'Infosys' OR customer_name = 'Wipro')

AND employees > 15;

SQL Example - UPDATE Statement

This example shows how the AND and OR conditions can be used in the UPDATE statement.

For example:

UPDATE Employee

SET Cname = 'HP'

WHERE Cname = 'IBM'

AND state = 'California';

This SQL AND & OR condition example would update all Cname values in the employee table to HP where the Cname was IBM and resides in the state of California.

SQL Example - DELETE Statement

Finally, this last AND & OR condition example demonstrates how the AND and OR condition can be used in the DELETE statement.

For example:

DELETE FROM employee

WHERE city = 'New York'

AND (designation = 'manager' OR Cname = 'Dell');

This SQL AND and OR condition example would delete all employee from the employee table whose city was New York and either the designation is manager or the company name is Dell.

SQL: ALIASES

Learn how to use SQL ALIASES (temporary names for columns or tables) .

Description

ALIASES are used to create a temporary name for columns or tables.

• COLUMN ALIASES used to make column headings for result se, so it become easy to read.
• TABLE ALIASES used to shorten SQL Query and it make easier to read query or when you are performing  joins.

Syntax

1.      Syntax for ALIAS A COLUMN in SQL is:

column_name AS alias_name

For example:

Aliases used to make the column headings in your result set easier to read. For example, when using the COUNT function,

SELECT designation, COUNT(*) AS TOTAL

FROM employees

GROUP BY designation;

2.      Syntax for ALIAS A TABLE in SQL is:

table_name alias_name

When creating table aliases, it is not necessary to create aliases for all of the tables listed in the FROM clause. You can choose to create aliases on any or all of the tables.

Let's look at an example of how to alias a table name.

For example:

SELECT e.employee_id, e. employee_name, order_details.order_date
FROM employee e
INNER JOIN orders
ON e.employee_id = order.employee_id

WHERE e.employee_id > 5000;

Parameters or Arguments

column_name is the original name of the column that you wish to alias.

table_name is the original name of the table that you wish to alias.

alias_name is the temporary name to assign.

Note

• It is acceptable to use spaces when you are aliasing a column name. However, it is not generally good practice to use spaces when you are aliasing a table name.
• If the alias_name contains spaces, you must enclose the alias_name in quotes.
• The alias_name is only valid within the scope of the SQL statement.

how to find the ip address of a computer c#

There are different ways to get the IP address of client computers;

Exploring the two way to get the IP address;

First is to find the LAN IP of computer address; which is in part of the LAN.

//Getting the lan IP of client

private string GetClientComputerLANIP()

{

    string strHostName = System.Net.Dns.GetHostName();

    string IPADDRESS = string.Empty;

    IPHostEntry ipEntry = System.Net.Dns.GetHostEntry(strHostName);

    

    foreach (IPAddress ipAddress in ipEntry.AddressList)

    {

        if (ipAddress.AddressFamily.ToString() == "InterNetwork")

        {

            IPADDRESS =  ipAddress.ToString();

        }

    }

    return IPADDRESS;

}

Get Internet IP address;

There is no any particular inbuilt method in .Net to get the IP address ; Thats why need to call dyndns.org

//Getting the omputer IP address

private string GetSystemInternetIP()

{

    WebRequest request = WebRequest.Create("http://checkip.dyndns.org");  // check IP using DynDNS's service

    WebResponse response = request.GetResponse();

    StreamReader stream = new StreamReader(response.GetResponseStream());

   

    request.Proxy = null; //  set Proxy to null, to drastically INCREASE the speed of request

    // read complete response

    string ipAddress = stream.ReadToEnd();

    // replace everything and keep only IP

    return ipAddress.

        Replace("<html><head><title>Current IP Check</title></head><body>Current IP Address: ", string.Empty).

        Replace("</body></html>", string.Empty);

}