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...

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 tableExample #1Select SUM(qty)from sales_orderExample #2Select c.customer, SUM(s.sale_order)from sales_order sinner join customer c on s.cust_id=c.custoer_nogroup by c.customerExample # 03Select...

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_or...

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_or...

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_...

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...

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...

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)...

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 Orde...

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 ...

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                          ...

read more

HAVING Clause

HavingHaving 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.SalesOrderDetailGROUP 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...

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,...

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...

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 employeewhere dept_id=10; Extract all those employees that have...

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 characterThe following example finds all telephone numbers that have area code 415 in the Contact table.SELECT FirstName, LastName, PhoneFROM Person.ContactWHERE phone LIKE '415%'The following example finds all telephone numbers that have area last digits 415 in the Contact table.SELECT FirstName, LastName, PhoneFROM Person.ContactWHERE phone LIKE '%415'The following example finds all telephone numbers that have area 415 in the Contact table.SELECT FirstName, LastName,...

read more

UNION/INTERSECT CLAUSES

1. Union ClauseThe 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 operationBy default, UNION eliminates duplicate rows from the combined result set.select count(*) as total applicationsfrom Employee_Applicationunion select count(*) as total_applciations from Loan_Applications2.Intersect Clause The following query returns any distinct values that are returned by both the query on the left and right...

read more

INSERT Statement Syntax

Adds a new row to a table or a view.Examples1. Use a simple INSERTInsert into table_name VALUES (1, 'Row #1')2.Insert data that is not in the same order as the columnsInsert into table_name (column_2, column_1) VALUES ('Row #1',1)3. Insert data with fewer values than columnsi. 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...

read more

SELECT - SQL Command

Select SyntaxPurpose: Retrieves data from one or more tablesThe following code examples show many ways of retrieving data with the SQL SELECT commandExample#1 Select FieldsSELECT emp_id, e_name FROM EmployeeExample#2 Using Aliasdisplays all records from a query in a specified column using the AS Column_Name clauseSELECT city_name AS 'City' FROM customerExample#3 Top 10 valus in SQL SERVERSELECT top 10 * FROM customerExample#4 Top 10 valus in MySQLSELECT * FROM customerlimit 10Example#4...

read more