Class 6 - Knowlege before writing Queries

KNOWLEDGE BEFORE WRITING QUERIES

WILDCARD EXAMPLES :
% - V% - VENKAT, VIJAY, VAMSI
- - V-NKAT - VENKAT
[] - [CS]HERYL - CHERYL, SHERYL
[^] - [^C] - SHERYL

FUNCTIONS :
Arithmetic operators
Comparison operators
Logical operators
Assignment operators
String concatenation operators
Unary operators

Arithmetic Operators
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulo (returns the integer remainder of a division)

Comparison Operators
= Equal to
> Greater than
<>= Greater than or equal to
<= Less than or equal to <> Not equal to
! = Not equal to (SQL-89 standard)
! <> Not greater than (SQL-89 standard)

Logical Operators
AND True if both expressions evaluate to true
BETWEEN True if the value is within a specified range
IN True if the result is equal to one in a list
LIKE True if the result matches a pattern
NOT Reverses the value of any other logical operator (such as NOT IN)
OR True if either logical expression evaluates to true
EXISTS True if a subquery (introduced later in this session) returns any Records

---ALL True if all of a set of compared values evaluates to true
---ANY True if any one of a set of compared values evaluates to true
---SOME True if some of a set of compared values evaluates to true

The assignment operator
Transact-SQL only has one assignment operator, and you’ve probably guessed it
already—it’s the equals sign (=). You use it when assigning values to variables or
specifying column headings.

The string concatenation operator
String concatenation is an operation you’ll find yourself performing over and over
again. Luckily, it is very intuitive—T-SQL uses the plus sign (+) to concatenate
strings. You can use it in SELECT statements like the following:
SELECT au_fname + ‘,’ + au_lname FROM authors

Unary Operators
Operator Description
+ The number is positive
- The number is negative

Working with Aggregate Functions
DISTINCT tells the query to ignore duplicate values, and ALL is a default
SUM returns the total of all the values in a numeric field
AVG returns the average of all the values in the numeric column:
COUNT returns the number of records in the group:
COUNT_BIG for big size purpose
MAX returns the highest value in the column:
MIN returns the lowest value in the column:
STDEV finding standar deviation
STDEVP finding standar deviation

CLAUSES :
AS
SET
ORDER BY
GROUP BY
UNION
UNIONALL
HAVING


CASE FUNCTION :
SELECT case when id = 4 then 'VENKAT' ELSE 'VIJAY' END NAME FROM SYSOBJECTS
WHILE : WHILE (SELECT AVG(price) FROM titles) < $30BEGIN UPDATE titles SET price = price * 2 SELECT MAX(price) FROM titles IF (SELECT MAX(price) FROM titles) > $50 BREAK ELSE CONTINUEENDPRINT 'Too much for the market to bear'
IFDECLARE @msg varchar(255)IF (SELECT COUNT(price) FROM titles WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20) > 0 BEGIN SET NOCOUNT ON SET @msg = 'There are several books that are a good value between $10 and $20. These books are: ' PRINT @msg SELECT title FROM titles WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20 ENDELSE BEGIN SET NOCOUNT ON SET @msg = 'There are no books between $10 and $20. You might consider the following books that are under $10.' PRINT @msg SELECT title FROM titles WHERE title_id LIKE 'TC%' AND price <>Inbuilt functions :
GOTO
WAITFOR
RTRIM (select Right of given characters)
LTRIM (select Right of given characters)
Getdate()
Cast
Convert
Isnull
Coleasce
Datediff
Identity


Queries :
SELECT employeeID, firstname, lastname FROM names WHERE dept = 'prod' or city = 'Orlando' or division = 'food'

UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE dept = 'prod' UNION ALL SELECT employeeID, firstname, lastname FROM names WHERE city = 'Orlando' UNION ALL SELECT employeeID, firstname, lastname FROM names WHERE division = 'food'


GROUP BY
USE Northwind SELECT OrderID FROM [Order Details] WHERE UnitPrice > 10 GROUP BY OrderID
Using a NOT EXISTS
SELECT a.hdr_key FROM hdr_tbl a WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)
Using a NOT IN
SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)

QUERY :
SELECT companyid, plantid, formulaid FROM batchrecords WHERE companyid = '0001' and plantid = '0202' and formulaid = '39988773' OR companyid = '0001' and plantid = '0202'


QUERY WITH VARIABLE :
DECLARE @age int SET @age = "30" DECLARE @service_years int SET @service_years = "10" SELECT employee_id FROM employees WHERE age = @age and service_years = @service_years



................. I will post more................ wait and see.................