SQL expression examples

The first six examples in this section all refer to a grid that contains three columns: EmpId, DeptId, and Salary.

Example 1

The expression >50000 in the Criteria row in the Salary column in the grid retrieves information for employees whose salaries are less than $50,000.

The sample shows the grid from the bottom of the Quick Select dialog box. At left are four labels for the rows of the grid. They are column, sort, criteria, or. Three column names display: Emp I D, Dept I D, and Salary. Displayed in the cell for Salary Criteria is the expression less than 50000.

The SELECT statement that Sybase WorkSpace creates is:

SELECT emp_id, dept_id, salary
FROM employee
WHERE salary < 50000

Example 2

The expression 100 in the Criteria row in the DeptId column in the grid retrieves information for employees who belong to department 100.

The sample shows the grid from the bottom of the Quick Select dialog box. At left are four labels for the rows of the grid. They are column, sort, criteria, or. Three column names display: Emp I D, Dept I D, and Salary. Displayed in the cell for Department I D Criteria is the number one hundred.

The SELECT statement that Sybase WorkSpace creates is:

SELECT emp_id, dept_id, salary
FROM employee
WHERE dept_id = 100

Example 3

The expression >300 in the Criteria row in the EmpId column and the expression <50000 in the Criteria row in the Salary column in the grid retrieve information for any employee whose employee ID is greater than 300 and whose salary is less than $50,000.

The sample shows the grid from the bottom of the Quick Select dialog box. At left are four labels for the rows of the grid. They are column, sort, criteria, or. Three column names display: Emp I D, Dept I D, and Salary. Displayed in the cell for Employee I D criteria is the expression greater than 300. Displayed in Salary Criteria is the expression less than 50000.

The SELECT statement that Sybase WorkSpace creates is:

SELECT emp_id, dept_id, salary
FROM employee
WHERE emp_id >300 AND salary <50000

Example 4

The expressions 100 in the Criteria row and >300 in the Or row for the DeptId column, together with the expression <50000 in the Criteria row in the Salary column, retrieve information for employees who belong to:

The sample shows the grid from the bottom of the Quick Select dialog box. At left are four labels for the rows of the grid. They are column, sort, criteria, or. Three column names display: Emp I D, Dept I D, and Salary. For Department I D the Criteria row shows 100 and the Or row has the expression greater than 300. The criteria row for salary shows the expression less than 50000.

The SELECT statement that Sybase WorkSpace creates is:

SELECT emp_id, dept_id, salary
FROM employee
WHERE (dept_id = 100 AND salary < 50000)
OR dept_id > 300

Example 5

The expression IN(100, 200, 500) in the Criteria row in the DeptId column in the grid retrieves information for employees who are in department 100 or 200 or 500.

The sample shows the grid from the bottom of the Quick Select dialog box. At left are four labels for the rows of the grid. They are column, sort, criteria, or. Three column names display: Emp I D, Dept I D, and Salary. has the expression greater than 300. The criteria row for the department I D column displays the expression IN ( 100, 200, 500 ).

The SELECT statement that Sybase WorkSpace creates is:

SELECT emp_id, dept_id, salary
FROM employee
WHERE dept_id IN (100, 200, 500)

Example 6

This example shows the use of the word AND in the Or criteria row. In the Criteria row, >=500 is in the EmpId column and >=30000 is in the Salary column. In the Or row, AND <=1000 is in the EmpId column and AND <=50000 is in the Salary column. These criteria retrieve information for employees who have an employee ID from 500 to 1000 and a salary from $30,000 to $50,000.

The sample shows the grid from the bottom of the Quick Select dialog box. At left are four labels for the rows of the grid. They are column, sort, criteria, or. Three column names display: Emp I D, Dept I D, and Salary. In the criteria row, the employee ID column shows the expression greater than or equal to 500, and Salary shows the expression greater than or equal to 30000. In the Or row, the Employee I D column shows the expression AND less than or equal to 1000, and the Salary column displays the expression AND less than or equal to 50000.

The SELECT statement that Sybase WorkSpace creates is:

SELECT emp_id, dept_id, salary
FROM employee
WHERE (emp_id >= 500 AND emp_id <= 1000)
AND (salary >= 30000 AND salary <= 50000)

Example 7

In a grid with three columns: Emp Last Name, Emp First Name, and Salary, the expressions LIKE C% in the Criteria row and LIKE G% in the Or row in the Emp Last Name column retrieve information for employees who have last names that begin with C or G.

The sample shows the grid from the bottom of the Quick Select dialog box. At left are four labels for the rows of the grid. They are column, sort, criteria, or. Three column names display: Emp Last Name, Emp First Name, and Salary. For the Emp Last Name column, the criteria row shows the expression LIKE C %, and the Or row shows the expression LIKE G %.

The SELECT statement that Sybase WorkSpace creates is:

SELECT emp_last_name, emp_first_name, salary
FROM employee
WHERE emp_last_name LIKE 'C%'
OR emp_last_name LIKE 'G%'



Copyright © 2007. Sybase Inc. All rights reserved. Using SQL Select