IMPORTANT & USEFUL SQL COMMANDS

On this page, you will find SQL syntax for some of the most important SQL commands. These below SQL Syntax's will be suitable for quick reference.

SELECT [hint][DISTINCT] select_list FROM table_list

[WHERE conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]

[ORDER BY order_list [ASC DESC] ]

[FOR UPDATE for_update_options]

SQL Select Statement

SELECT "column name" FROM "table name"

Example:

Select salary from emp;

SQL Where

SELECT "column name"
FROM "table name"
WHERE "condition"

Example:

Select salary from emp

Where salary > 2000

SQL Distinct

SELECT DISTINCT "column name"
FROM "table name"

Example:

Select DISTINCT name from emp;

SQL And/Or

SELECT "column name"
FROM "table name"
WHERE "condition"
{[ANDOR] "condition"}+

Example:

SELECT salary

FROM emp

WHERE Salary > 1000

OR (Salary <> 275)

SQL Between

SELECT "column name"
FROM "table name"
WHERE "column name" BETWEEN 'value1' AND 'value2'

Example:

SELECT *

FROM emp

WHERE Date BETWEEN 'Jan-01-1999' AND 'Jan-15-1999'

SQL In

SELECT "column name"
FROM "table name"
WHERE "column name" IN ('value1', ‘value2’ ...)

Example:

SELECT *

FROM emp

WHERE last_name IN ('sharma', 'dhall')

SQL Like

SELECT "column name"
FROM "table name"
WHERE "column name" LIKE {PATTERN}

Example:

SELECT *

FROM emp

WHERE last_name LIKE '%EN%'

SQL Order By

SELECT "column name"
FROM "table name"
[WHERE "condition"]
ORDER BY "column name" [ASC, DESC]

Example:

SELECT name, Salary

FROM emp

ORDER BY name DESC

SQL Count

SELECT COUNT ("column name")
FROM "table name"

Example: 

SELECT COUNT (salary)

FROM emp

SELECT * FROM Table;

According to wikipedia Commonly available SQL keywords related to SELECT include: 

FROM is used to indicate from which tables the data is to be taken, as well as how the tables JOIN to each other. 

WHERE is used to identify which rows to be retrieved, or applied to GROUP BY. WHERE is evaluated before the GROUP BY. 

GROUP BY is used to combine rows with related values into elements of a smaller set of rows. 

HAVING is used to identify which of the "combined rows" (combined rows are produced when the query has a GROUP BY keyword or when the SELECT part contains aggregates), are to be retrieved. HAVING acts much like a WHERE, but it operates on the results of the GROUP BY and hence can use aggregate functions. 

ORDER BY is used to identify which columns are used to sort the resulting data.

Returning A Single Column From A Table

The syntax is as follows:

SELECT Column FROM Table;

Oracle uses the ANSI standard concatenation operator, . Because this operator is reserved for string concatenation, the operands don't need to be cast to a string type—they'll be converted automatically:

SELECT FirstName ' ' LastName AS Name FROM EMP_Database;

Sorting With The ORDER BY Clause

The ORDER BY clause is always applied after all other clauses are applied, such as the WHERE and GROUP BY clauses. Without an ORDER BY clause in an SQL statement, rows will often be retrieved in the physical order in which they were added to the table. The default behavior is to sort rows in ascending order.

SELECT ColumnA, ColumnB FROM Table
ORDER BY ColumnA ASC;

Although this is the default behavior so you don't need to explicitly specify the ASC keyword, it's a better idea to include it to ensure that your queries are as easy to read as possible.

Also, you may want to sort your rows in descending order, in which case you use the DESC keyword:

SELECT ColumnA, ColumnB FROM Table
ORDER BY ColumnA DESC;

Filtering Data

DISTINCT always retrieves the first value from a repeating group. If there are multiple repeating groups DISTINCT will retrieve the first row from each group.

Hence, DISTINCT will always require a sort. DISTINCT can operate on a single or multiple columns.

SELECT DISTINCT ColumnA FROM Table;

In order to filter out duplicate rows, you use the DISTINCT keyword.

If you want to retrieve multiple columns, you can guarantee that every row you obtain contains unique data for the specified set of columns.

For example, the following query will return only unique combinations of customer names and debit card details:

SELECT DISTINCT CustomerName, DebitCard FROM Customers;

This doesn't mean that you won't have duplicates in either column, only that each combination is unique. You could, for example, have several customers with the same name but with different debit card numbers.

Using WHERE Clause

We can use the WHERE clause to restrict the rows returned by a query.

SELECT ColumnA, ColumnB, ColumnC FROM Table
WHERE Condition;

Condition is very flexible, allowing you to test for equalities and inequalities in column data, ranges of values to look for etc. You achieve all this using a simple syntax that includes various operators and keywords that, when combined, allow you to search for pretty much anything.

We can use following comparison operators with the WHERE clause:

Operator	Meaning
=	Equal
<>, !=, ^=	Not Equal
>	Greater Than
<	Less Than
>=	Greater Than Or Equal
<=	Less Than Or Equal

And many more for example BETWEEN ...AND..., LIKE, IS NULL etc.

Some examples of where

1)SELECT name FROM bedroom WHERE bedcolor = 'BLACK' OR ceilingcolor = 'GREEN' OR wallcolor = 'YELLOW'

2) SELECT z FROM t WHERE x = 6 AND y > 7 ;

3)Find the average price of Bud. Sells (bar, beer, price)

SELECT AVG (price) FROM Sells WHERE beer = 'Bud';

Courtesy : http://potools.blogspot.in/

Seventh Pay Commission May Recommend Permanent Pay Panel

The four-member Seventh Central Pay Commission
team headed by its Chairman Justice A K Mathur (second from right siting).

New Delhi: The Seventh Pay Commission is likely to recommend the government to form a permanent pay panel to give recommendations to the government from time to time on issues pertaining to pay structure of central government employees.

The permanent pay panel would recommend regular salary hikes in keeping with the rate of inflation.

The formation of the permanent pay panel would help raise the salaries and allowances of central government officials and employees, an official of the pay panel said.

He added the permanent pay panel would recommend salary and allowance hikes in keeping with the rising inflation rate, which will be implemented by the government. “Then it will not be necessary to form a new commission during the next several years for central government employees.”

However, the Seventh Pay Commission got one month extension to submit its recommendations.

Accordingly it is expected to submit its report by the end of September. The time allotted for the commission ends this month.

The government appointed the Seventh Pay Commission on 28 February 2014 under chairman, Justice Ashok Kumar Mathur, with a time frame of 18 months to make its recommendations

“There are some data points that are missing, which we hope to get by this month end. We are trying to submit the report by 20 September,” the official of the pay panel also said.

The government’s salary bill will rise by 9.56% to Rs 1,00,619 crore with the implementation of the recommendations of the Seventh Pay Commission, according to a statement tabled in Parliament by Finance Minister Arun Jaitley on August 12.

The recommendations of the Seventh Pay Commission, is likely to be implemented in April, next year.

Source : http://www.tkbsen.in/

PFRDA to launch online facility to open NPS accounts

MUMBAI: Pension fund regulator PFRDA is set to launch an online facility for opening of accounts under the National Pension System (NPS) to net in prospective customers. 

"We are set to launch the online on-boarding facility for the prospective customers of pension schemes under the NPS," PFRDA chairman Hemant Contractor told reporters on the sidelines of an insurance summit organised by the industry lobby CII here this evening. 

"We have also urged the government to offer tax-breaks to pension schemes falling on the lines of other financial products like mutual funds and insurance, to popularise pension products in the unorganised sector," he said. 

The Pension Funds Regulatory and Development Authority (PFRDA has sought clarification from the government if the pension products being run by various fund houses and life insurers can be regulated by it

"We have asked the government to clarify whether we can regulate the pension business being run by mutual fund houses and life insurers because the PFRDA Act empowers us to regulate all kinds of pension business," Contractor said. 

On the pension business being managed by the Employees Provident Fund Organisation ( EPFO), he said it is up to the government to decide that who will be regulating it. 

Commenting on the progress of NPS, he said the NPS corpus has already crossed Rs 94,000 crore so far, which includes Rs 7,000 crore from the Atal Pension Yojana. 

He further said, "We've already opened 6,80,000 accounts under the Atal Pension scheme during the past couple of months and we are looking at taking it to 2 crore by December. Out of these 6,80,000 accounts, 65 per cent subscribers come from urban areas and the rest from the rural areas." 

The three state-owned fund managers, who are managing the Atal Pension scheme funds are UTI, SBI and LIC Pension Fund. 

The regulator is getting ready to appoint new fund managers for the NPS, which has seven active fund managers now and also reviewing fund management charges. 

Contractor also said the regulator is planning to increase the tenure of NPS fund managers to five years from the present three. 

"We don't mind paying a higher commission to incentivise fund managers and distributors so as to expand the NPS reach. We are reviewing a proposal in this regard and we will be issuing a request for proposal to appoint new fund managers within a month," he said.

Source : http://economictimes.indiatimes.com/

Issue of Pensioners Identity Card to pensioners.

CLICK HERE TO VIEW O.M No. 41/21/2000-P&PW(D), DATED 20.08.2015 ISSUED BY DEPARTMENT OF PENSION & PENSIONERS WELFARE.

UPSC Advt No 11/2015 for Various Vacancies

UPSC Advt No 11/2015 for Various Vacancies: Union Public Service Commission (UPSC) has issued notification for Various Vacancies.


Name of the Posts:

• Economic Officer
• Medical Officer/ Research Officer (Homeopathy)
• Company Prosecutor
• Additional Director General
• Joint Director General
• Director
• Anthropologist (Physical Anthropology Division)
• Assistant Anthropologist(Cultural Anthropology Division)
• Assistant Anthropologist (Physical Anthropology Division)
• Assistant Keeper
• Assistant Professor (English)
• Assistant Professor Chinese
• Senior Scientific Officer Grade-II (Electronics & Communication Engineering)
• Assistant Professor (Survey and Engineering)
• Assistant Professor of Dentistry (Oral Surgery)
• Dental Surgeons
• Factory Manager
• Deputy Director (Medical)
• Training Officer (Architectural Assistant ship)
• Training Officer (Electronics)
• Assistant Government Advocate
• Assistant Legislative Counsel (Oriya)
• Superintending Mining Geologist
• Deputy Ore Dressing Officer
• Divisional Medical Officer (Anesthesia)
• Divisional Medical Officer (ENT)
• Divisional Medical Officer (Dermatology)
• Divisional Medical Officer (Ophthalmology)
• Divisional Medical Officer (Orthopaedics)
• Divisional Medical Officer (Pathology)
• Divisional Medical Officer (Radio-diagnosis)
• Divisional Medical Officer (Surgery)
• Assistant Divisional Dental Surgeons
• Scientist B (Mechanical Engineering)
• Education Officer/Assistant Director of Education
• Assistant Professor (Non-Technical) (English & Communication Skills)
• Assistant Professor (Non-Technical) (Applied Chemistry)
• Assistant Professor (Non-Technical) (Applied Physics)
• Professor of Hospital Administration-cum-Medical Superintendent
• Assistant Public Prosecutor

Important Dates:
Closing Date for Submission of Online Application: 10-09-2015 up to 23:59 hrs
Last Date for Printing of Completely Submitted Online Application: 11-09-2015 up to 23:59 hrs

Advertisement : Click Here

Apply Online : Click Here

 

Promotions & Postings in HSG- I (CO/RO) Cadre

Circle office, Hyderabad has ordered promotions and postings in HSG- I (CO/RO) cadre vide memo no ST/47-5/HSG I/2010 dated 24.08.2015.

Copy of CO memo referred to above is reproduced below.