Monthly Count at Column Level

Below is the easiest way to Calculate monthly leaves and late comings by an individual employee for the year 2010 at column level

Select  emp_id , Date_format(e.registration_date,'%m-%y')
, (Select count(leave_id) from emp leave el     
where e.emp_id=el.em_id
and Date_format(e.registration_date,'%m-%y')=Date_format(el.leave_date,'%m-%y')TotalLeaves

, (Select count(leave_id)
from emp_attendence ea
where e.emp_id=ea.emp_id
and Date_format(e.registration_date,'%m-%y')=Date_format(el.leave_date,'%m-%y')TotalLatecomings

from employee e
where e.registration_date between '2010-04-01 00:00:00' and '2010-12-31 23:59:59'

read more

SQL SUM() Function

Sql SUM() funtionc is used to get sum of all the values of an expression, to get how many slaes orders have been sent ot each customer or to know that how many visitors have visited the website for each country below are the exampels  has been illustrated.

Syntax:

Select SUM(field_name)

from table

Example #1

Select SUM(qty)

from sales_order

Example #2

Select c.customer, SUM(s.sale_order)

from sales_order s

inner join customer c on s.cust_id=c.custoer_no

group by c.customer

Example # 03

Select c.customer, SUM(s.sale_order)

from sales_order s
inner join customer c on s.cust_id=c.custoer_no
group by c.customer

read more

SQL Min() Function

Sql Min() frunction is an aggregate function that returns the Lowest value of an expression, to get the minimum value in your statement use this function.


Example:1
Select MIN(sal)
from emp;
Example:2
Select MIN(sales_order_date)
from sales_order

read more

SQL Max() Function

Sql Max() frunction is an aggregate function that returns the highest value of an expression, to get the maximum value in your statement use this function.

Example:1
Select MAX(sal)
from emp; 
Example:2
Select Max(sales_order_date)
from sales_order

read more

SQL Last() Function

The function LAST returns the last value of items in a group. Without a GROUP BY clause it will return the last row of the SELECT. The value is nondeterministic if the values in a group are differently because there is no warranty of the row order.
Examples #1:
 Select Last(qty)
from sales_order
Examples #2 
Select  Last(qty)
from sales_order
group by order_id

read more

SQL Not Exists

NOT EXISTS works the opposite as EXISTS. The WHERE clause in NOT EXISTS is satisfied if no rows are returned by the subquery. The following example finds employees who are not in departments which have names that start with P.
Example #1
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE NOT EXISTS (SELECT NULL)
ORDER BY Name ASC



Example # 02
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE NOT EXISTS
(SELECT *
FROM HumanResources.Department AS d
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE e.EmployeeID = edh.EmployeeID
AND d.Name LIKE 'P%')
ORDER BY LastName, FirstName

read more

FIRST() Function

The FIRST() function returns the first value of the selected column.
In MYSQL
SQL FIRST() Syntax
SELECT FIRST(column_name)
FROM table_name
Example
SELECT OrderPrice
FROM Orders ORDER BY O_Id
LIMIT 1
 In SQL Server
Syntax
SELECT FIRST(column_name)
FROM table_name
In SQL SERVER
Example:
SELECT top 1 OrderPrice
FROM Orders ORDER BY O_Id

read more

COUNT() function

 The COUNT() function returns the number of rows that matches a specified criteria.
(NULL values will not be counted) of the specified column:
Count()
SQL Syntax
1. SQL COUNT(column_name) Syntax
SELECT COUNT(Customer) AS CustomerNilsen
FROM Orders
WHERE Customer='Nilsen'
Count(*)SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:
2. SELECT COUNT(*) FROM table_name
SELECT COUNT(*) AS NumberOfOrders
FROM Orders
Distinct Count
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:
SELECT COUNT(DISTINCT column_name) FROM table_name
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers
 FROM Orders

read more

The AVG() Function

The AVG() function returns the average value of a numeric column.
SQL AVG() Syntax
SELECT AVG(column_name) FROM table_name
Example:
In order to find the customers that have an OrderPrice value higher than the average OrderPrice value.
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice)
FROM Orders)

read more

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Following are SQL 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

read more

Group By Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

In order to find that how many orders have been given by individual customers in the month of april 2010.
SELECT Customer,SUM(OrderPrice) as "Total Orders"
FROM Orders
where order_date between '2010-04-01 00:00:00' and '2010-04-3023:59:59'
GROUP BY Customer



Result:
Customer                            Total Orders
Ali                                             60
David                                        50
Muhammad Junaid                     75 

read more

HAVING Clause

Having

Having Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
ORDER BY SalesOrderID ;

read more

SQL Joins

SQL Joins  Microsoft SQL Server 2008 Management and Administration

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements

1) SQL Inner Join  PostgreSQL: A Better Way to Learn PostgreSQL (Prima Development)


In SQL Inner Join  the query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate

SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

2. SQL Equi-join     Build Your Own Database Driven Website Using PHP and MySQL
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate
SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

SQL provides an optional shorthand notation for expressing equi-joins, by way of the USING construct
 SELECT *
FROM employee
INNER JOIN department
USING (DepartmentID


3) SQL Natural join   Beginning Database Design: From Novice to Professional
 A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns

SELECT *
FROM employee NATURAL JOIN department

4) SQL Cross join      Learn SQL

A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement. In other words, a cross join combines every row in B with every row in A. The number of rows in the result set will be the number of rows in A times the number of rows in B.

SELECT *
FROM employee CROSS JOIN department


5) SQL Left Outer Join     Sams Teach Yourself SQL in 24 Hours (4th Edition)

The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).

SELECT *
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

6) SQL Right Outer Join  Learning MySQL

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.

SELECT * 
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

7. SQL Full outer join    SQL: A Beginner's Guide

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

SELECT *FROM employee

FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

8. Self-join          MySQL: Your visual blueprint for creating open source databases

A self-join is joining a table to itself

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country 
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
                                                                         

read more

Full Text Search

Full-Text Search (Contains & Free Text )

SQL Server 2008 provides the functionality for applications and users to issue full-text queries against character-based data in SQL Server tables. Before full-text queries can be run on a given table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max)

FREE TEXT
 

The FREETEXT predicate is part of the WHERE clause, and supports searching for words and phrases in text columns. The FREETEXT predicate is better suited for finding documents containing combinations of the search terms spread throughout the column, in contrast to the CONTAINS predicate, which is better suited for finding "exact" matches. You can also perform exact-phrase matching with the FREETEXT predicate by placing the phrase in double quotation marks.

 

This query returns you all paths to files which contain the "Hello World" text.

SELECT Path

 FROM SCOPE()
WHERE FREETEXT(Contents, 'Hello World')

read more

CONTAINS Predicate

CONTAINS Statement

The CONTAINS predicate provides several text-matching options for building queries. Typically, you use this predicate to query the Contents property— the text contents of a file. You can also query other textual properties with content, such as DocTitle, DocSubject, or a user-defined property. This predicate is an optional part of the optional WHERE clause of the SELECT statement.

1 The below stated query finds all employees with a dept 10 and  that contain the e-name "jhones".

SELECT E-Name, Dept
FROM Employee
WHERE Dept=10
AND CONTAINS(E-Name, 'jhones');
 
2. Using CONTAINS and OR with
The following example returns all category descriptions containing strings with prefixes of either "chain" or "full".
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');

3. Using CONTAINS with
The following example returns all product names that have the word bike near the word performance.

SELECT Description

FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');

4. Using CONTAINS with

The following example searches for all products with words of the form ride: riding, ridden, and so on.

SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ');

5.Using CONTAINS with A Logical Operator (AND)
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');

read more

WHERE Clause

The WHERE clause specifies the search condition for selecting rows in the virtual table defined by the FROM clause. The matching rows constitute the resulting rowset. The WHERE clause is an optional part of the SELECT statement. However, if the WHERE clause is absent, the resulting rowset can be extremely large because all rows of the virtual table defined by the FROM clause are returned in the resulting rowset.

Select e_name

from employee

where dept_id=10;

Extract all those employees that have 'ali' as their names and are working in dept 10

Select *
from employee
where e_name='ali'
and dept=10;
Select all invoices for the entire month of April of 2010
Select *
from invoice
where invoid_date between'2010-04-01 00:00:00' and '2010-04-31 23:59:59'

read more

EXISTS

EXISTS simply tests whether the inner query returns any row. If it does, then the outer query proceeds. If not, the outer query does not execute, and the entire SQL statement returns nothing.
SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE region_name = 'West')

read more

Like Operator

1. LIKE with the % wildcard character
The following example finds all telephone numbers that have area code 415 in the Contact table.

SELECT FirstName, LastName, Phone
FROM Person.Contact
WHERE phone LIKE '415%'

The following example finds all telephone numbers that have area last digits 415 in the Contact table.

SELECT FirstName, LastName, Phone
FROM Person.Contact
WHERE phone LIKE '%415'

The following example finds all telephone numbers that have area 415 in the Contact table.

SELECT FirstName, LastName, Phone
FROM Person.Contact
WHERE phone LIKE '%415%'

3. Usng NOT LIKE with the % wildcard character

SELECT FirstName, LastName, Phone
FROM Person.Contact
WHERE Phone NOT LIKE '415%' AND FirstName = 'Gail'ORDER BY LastName;

4. Using the [ ] wildcard characters

The following example finds Contacts with the first name of Cheryl or Sheryl.

SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE FirstName LIKE '[CS]heryl';

The following example finds the rows for Contacts with last names of Zheng or Zhang.

SELECT LastName, FirstName, Phone
FROM Person.ContactWHERE LastName
LIKE 'Zh[ae]ng'ORDER BY LastName ASC, FirstName ASC;

read more

UNION/INTERSECT CLAUSES

1. Union Clause
The UNION clause combines the results from two or more SQL SELECT statements into a single result set containing rows from all the queries in the UNION operation

By default, UNION eliminates duplicate rows from the combined result set.

select count(*) as total applications
from Employee_Application

union

select count(*) as total_applciations
from Loan_Applications

2.Intersect Clause

The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

SELECT ProductID FROM Production.Product
INTERSECT
SELECT ProductID FROM Production.WorkOrder

3. Except Clause
SELECT ProductID FROM Production.Product
EXCEPT
SELECT ProductID FROM Production.WorkOrder

read more

INSERT Statement Syntax

Adds a new row to a table or a view.

Examples
1. Use a simple INSERT

Insert into table_name VALUES (1, 'Row #1')

2.Insert data that is not in the same order as the columns

Insert into table_name (column_2, column_1) VALUES ('Row #1',1)

3. Insert data with fewer values than columns

i. Insert into table_name (column_4) VALUES ('Explicit value')
ii. Insert into table_name (column_2,column_4) VALUES ('Explicit value', 'Explicit value')
iii.Insert into table_name (column_2,column_3,column_4) VALUES ('Explicit value',-44,'Explicit value')

4.Load data into a table with an identity column

SET IDENTITY_INSERT T1 ON INSERT INTO T1 (column_1,column_2) VALUES (-99,'Explicit identity value')

5 .Insert data using the TOP clause in a SELECT statement

INSERT INTO new_authors SELECT TOP 10 * FROM authors

6. Insert data using default values options

insert into table_name default values

read more

SELECT - SQL Command

Select Syntax

Purpose: Retrieves data from one or more tables

The following code examples show many ways of retrieving data with the SQL SELECT command

Example#1 Select Fields
SELECT emp_id, e_name FROM Employee

Example#2 Using Alias
displays all records from a query in a specified column using the AS Column_Name clause

SELECT city_name AS 'City' FROM customer

Example#3 Top 10 valus in SQL SERVER

SELECT top 10 * FROM customer


Example#4 Top 10 valus in MySQL

SELECT * FROM customer
limit 10


Example#4 Second Largest value in MySQL

SELECT * FROM customer
limit 2,2

read more