What Are The Queries In Database And How To Use Them In SQL Server : 

In this topic, you will learn how to use the SELECT statement to perform retrievals. We will discuss every clause in this statement and gives numerous examples using the sample database to demonstrate the practical use of each clause. After that, we will look into aggregate functions and the set operators, as well as computed columns and temporary tables.

Simple SELECT:

The Transact-SQL language has one basic statement for retrieving information from a database: the SELECT statement. With this statement, it is possible to query information from one or more tables of a database (or even from multiple databases).

The result of a SELECT statement is another table, also known as a result set.

The simplest form of the SELECT statement contains a SELECT list with the FROM clause. (All other clauses are optional.) This form of the SELECT statement has the following syntax:

SELECT [ ALL |DISTINCT] column_list FROM {table1 [tab_alias1] } ,...

table1 is the name of the table from which information is retrieved. tab_alias1 provides an alias for the name of the corresponding table. An alias is another name for the corresponding table and can be used as a shorthand way to refer to the table or as a way to refer to two logical instances of the same physical table.

column_list contains one or more of the following specifications:

·         The asterisk symbol (*), which specifies all columns of the named tables in the FROM clause (or from a single table when qualified, as in table2.*)

·         The explicit specification of column names to be retrieved

·         The specification column_name [AS] column_heading, which is a way to replace the name of a column or to assign a new name to an expression

·         An expression

·         A system or an aggregate function

FULL SELECT SYNTAX:

The following is the syntax of a SELECT statement that references a table, with (almost) all possible clauses included:

SELECT select_list[INTO new_table_] FROM table

[WHERE search_condition]

[GROUP BY group_by_expression]

[HAVING search_condition]

[ORDER BY order_expression [ASC | DESC] ];

WHERE CLAUSE:

Often, it is necessary to define one or more conditions that limit the selected rows. The WHERE clause specifies a Boolean expression (an expression that returns a value of TRUE or FALSE) that is tested for each row to be returned (potentially). If the expression is true, then the row is returned; if it is false, it is discarded.

Example 1, Get the names and numbers of all departments located in Dallas:

USE sample;

SELECT dept_name, dept_no FROM department WHERE location = 'Dallas';

Example 2, Get the last and first names of all employees with employee numbers greater than or

equal to 15000:

USE sample;

SELECT emp_lname, emp_fname FROM employee WHERE emp_no >= 15000;

Example 3, Get the project names for all projects with a budget > 60000 £. The current rate of

exchange is 0.51 £ per $1.

USE sample;

SELECT project_name FROM project WHERE budget*0.51 > 60000;

 

Comparisons of strings (that is, values of data types CHAR, VARCHAR, NCHAR, or NVARCHAR) are executed in accordance with the collating sequence in effect (the “sort order” specified when the Database Engine was installed). If two strings are compared using ASCII code (or any other code), each of the corresponding (first, second, third, and so on) characters will be compared. One character is lower in priority than the other if it appears in the code table before the other one. Two strings of different lengths are compared after the shorter one is padded at the right with blanks, so that the length of both strings is equal. Numbers compare algebraically. Values of temporal data types (such as DATE, TIME, and DATETIME) compare in chronological order.

Boolean Operators (AND, OR, NOT)

Example 4, Get the employee numbers for all employees who work for either project p1 or project p2 (or both):

USE sample;

SELECT project_no, emp_no FROM works_on WHERE project_no = 'p1' OR project_no = 'p2';

 

How will you remove the duplicate values from result set in the emp_no column?

Use of DISTINCT, it should be used before all the colulmn in a column list.

 

Example 5 (use of NOT), Get the employee numbers and first names of all employees who do not belong to the department d2:

USE sample

SELECT emp_no, emp_lname FROM employee WHERE NOT dept_no = 'd2';

 

IN and BETWEEN Operators:

An IN operator allows the specification of two or more expressions to be used for a query search. The result of the condition returns TRUE if the value of the corresponding column equals one of the expressions specified by the IN predicate.

Example 6 (IN operator), Get all the columns for every employee whose employee number equals 29346, 28559, or 25348:

USE sample;

SELECT emp_no, emp_fname, emp_lname FROM employee WHERE emp_no IN (29346, 28559, 25348);

Example 7, Get all columns for every employee whose employee number is neither 10102 nor 9031:

USE sample;

SELECT emp_no, emp_fname, emp_lname, dept_no FROM employee WHERE emp_no NOT IN (10102, 9031);

Example 8 (BETWEEN operator), Get the names and budgets for all projects with a budget between $95,000 and $120,000, inclusive:

USE sample;

SELECT project_name, budget FROM project WHERE budget BETWEEN 95000 AND 120000;

Example 9, Get the names of all projects with a budget less than $100,000 and greater than $150,000:

USE sample;

SELECT project_name FROM project WHERE budget NOT BETWEEN 100000 AND 150000;

Queries Involving NULL Values

A NULL in the CREATE TABLE statement specifies that a special value called NULL (which usually represents unknown or not applicable values) is allowed in the column. These values differ from all other values in a database. The WHERE clause of a SELECT statement generally returns rows for which the comparison evaluates to TRUE. The concern, then, regarding queries is, how will comparisons involving NULL values be evaluated in the WHERE clause?

All comparisons with NULL values will return FALSE (even when preceded by NOT). To retrieve the rows with NULL values in the column, Transact-SQL includes the operator feature IS NULL. This specification in a WHERE clause of a SELECT statement has the following general form:

column IS [NOT] NULL

Example 10 (use of IS NULL), Get employee numbers and corresponding project numbers for employees with unknown jobs who work on project p2:

USE sample;

SELECT emp_no, project_no FROM works_on WHERE project_no = 'p2' AND job IS NULL;

Because all comparisons with NULL values return FALSE, Example 11 shows syntactically correct, but logically incorrect, usage of NULL.

Example 11

USE sample;

SELECT project_no, job FROM works_on WHERE job <> NULL;

 

The condition “column IS NOT NULL” is equivalent to the condition “NOT (column IS NULL).” The system function ISNULL allows a display of the specified value as substitution for NULL (see Example 12)

Example 12,

USE sample;

SELECT emp_no, ISNULL(job, 'Job unknown') AS task FROM works_on WHERE project_no = 'p1';

LIKE Operator

Example 13, Get the first and last names and numbers of all employees whose first name contains the letter a as the second character:

USE sample;

SELECT emp_fname, emp_lname, emp_no FROM employee WHERE emp_fname LIKE '_a%';

Example 14, Get full details of all departments whose location begins with a character in the range C through F:

USE sample;

SELECT dept_nt, dept_name, location FROM department WHERE location LIKE '[C-F]%';

Example 15, Get the numbers and first and last names of all employees whose last name does not begin with the letter J, K, L, M, N, or O and whose first name does not begin with the letter E or Z:

USE sample;

SELECT emp_no, emp_fname, emp_lname FROM employee WHERE emp_lname LIKE '[^J-O]%' AND emp_fname LIKE '[^EZ]%';

Example 16, Get full details of all employees whose first name does not end with the character n:

USE sample;

SELECT emp_no, emp_fname, emp_lname FROM employee WHERE emp_fname NOT LIKE '%n';

GROUP BY Clause:

The GROUP BY clause defines one or more columns as a group such that all rows within any group have the same values for those columns. Example 6.22 shows the simple use of the GROUP BY clause.

Example 17, Get all jobs of the employees:

USE sample;

SELECT job FROM works_on GROUP BY job;

Example 18, Group all employees using their project numbers and jobs:

USE sample;

SELECT project_no, job FROM works_on GROUP BY project_no, job;

Here are some the task which you  have tp perform for the practice:

NOTE:

             USE SQL SERVER:

1.       Execute the following two queries and elaborate the reason why the results are different?

USE sample;

SELECT emp_no, emp_fname, emp_lname FROM employee WHERE emp_no = 25348 AND emp_lname = 'Smith' OR emp_fname = 'Matthew' AND dept_no = 'd1';

 

SELECT emp_no, emp_fname, emp_lname FROM employee WHERE ((emp_no = 25348 AND emp_lname = 'Smith') OR emp_fname ='Matthew') AND dept_no = 'd1';

2.       Write a query that shows the same result as in Example 8, but do not use the BETWEEN operator?

                                      

Execute the following two queries and elaborate the reason why the results are different?

 

SELECT emp_no,emp_fname,emp_lname FROM Employee WHERE emp_no = 25348 AND emp_lname = 'Smith' OR emp_fname = 'Matthew' AND dept_no ='d1';

SELECT emp_no,emp_fname,emp_lname FROM Employee WHERE ((emp_no = 25348 AND emp_lname = 'Smith') OR emp_fname = 'Matthew') AND dept_no ='d1';

Reason:
In first query, OR operator is performed between (emp_no = 25348 AND emp_lname = 'Smith') and (emp_fname = 'Matthew' AND dept_no ='d1'). As evident from the database, left side of OR returns 1 and right side returns 0. So, 1 OR 0 gives 1 and therefore the result is displayed.
In second query, AND operation is performed between (emp_no = 25348 AND emp_lname = 'Smith') OR emp_fname = 'Matthew') and (dept_no ='d1'). As evident from the database, , left side of AND returns 1 and right side returns 0. So, 1 AND 0 gives 0 and therefore the nothing is  displayed.

3. Write a query that shows the same result as in Example 8, but do not use the BETWEEN operator?

 

USE Mem_Sample

SELECT project_name,budget  FROM Project WHERE budget >=95000 AND budget <=120000