Introduction To Sql

A Gentle Introduction to SQL Simple use of SELECT and WHERE These statements take the form : SELECT attribute-list FROM table-name WHERE condition • attribute-list This is usually a comma separated list of attributes (field names) Expressions involving these attributes may be used. The normal mathematical operators +, -, *, / may be used on numeric values. String values may be concatenated using || To select all attributes use * The attributes in this case are: name, region, area, population and gdp • table-name In these examples the table is always bbc. • condition This is a boolean expression which each row must satisfy. Operators which may be used include AND, OR, NOT, >, >=, =, <, <= The LIKE operator permits strings to be compared using 'wild cards'. The symbols _ and % are used to represent a single character or a sequence of characters. Note that MS Access SQL uses ? and * instead of _ and % . The IN operator allows an item to be tested against a list of values. There is a BETWEEN operator for checking ranges.

WHERE statements 1. SELECT population FROM bbc WHERE name = 'France‘ 2. SELECT name, population/area FROM bbc WHERE area > 5000000

WHERE and IN statements SELECT name FROM bbc WHERE name IN ('Sri Lanka', 'Ceylon', 'Persia', 'Iran')

WHERE and BETWEEN statements SELECT name, area FROM bbc WHERE area BETWEEN 207600 AND 244820

WHERE and AND statements SELECT name , region FROM bbc WHERE area < 2000 AND gdp > 5000000000

WHERE and LIKE statements SELECT name FROM bbc WHERE name LIKE 'D%'

SELECT statements within SELECT statements to perform more complex queries SELECT FROM SELECT SELECT name FROM bbc WHERE population> (SELECT population FROM bbc WHERE name='Russia')

SQL Functions Each page includes a compatability table showing which of SQL Server, Oracle, MySQL and PostgreSQL provide support. Where possible alternatives are given. ABS

CURRENT_TIMESTAMP

LENGTH

+ INTERVAL

SUBSTRING (ansi)

AVG

DATEPART

MAX

+ (string)

SUBSTRING

|| (strings)

DAY

MIN

POSITION

SUM

CASE

DIV

MINUTE

QUARTER

TAN

CAST

EXTRACT

MOD

RANK

TO_CHAR (dates)

CEIL

FLOOR

MONTH

REPLACE

TRIM

COALESCE

HOUR

NULLIF

RIGHT

YEAR

CONCAT

IFNULL

NVL

ROUND

COS

INSTR

PATINDEX

SECOND

COUNT

LEFT

% (modulo)

SIN

CURRENT_DATE

LEN

+ (dates)

SUBSTR

Functions 1. ABS returns the absolute value. The output is positive even if the input is negative ABS(x) = x if x>=0 ABS(x) = -x if x<0 2. AVG gives the average (the mean) of a whole column or a group of rows for a single column of values. AVG is an aggregate function it is normally used with GROUP BY. The mean of the values a, b, c is (a+b+c)/3, you divide by 3 because there are 3 numbers in this example. NULL values do not contribute to the calculation. SELECT region, AVG(population) FROM bbc GROUP BY region 3. || (strings) || allows you to stick two or more strings together. This operation is concatenation.

s1 || s2

Functions 4. CASE allows you to return different values under different conditions. If there no conditions match (and there is not ELSE) then NULL is returned. CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE def_value END

Example: SELECT name, population ,CASE WHEN population<1000000 THEN 'small' WHEN population<10000000 THEN 'medium' ELSE 'large' END FROM bbc

Result:

Functions 5. CAST allows you to convert from one type to another. Often a CAST is implicit - for example if you concatenate a string with a number the number will be automatically changed to a string. However sometimes you need to make the CAST explicit. CAST(expr TO type) In this example we get the population in millions by casting the floating point value to DECIMAL(8,1) - this ensures one decimal place of accuracy. SELECT CAST(population/1000000 AS DECIMAL(8,1)) AS a ,population/1000000 AS b FROM bbc

6. CEIL CEIL(f) is ceiling, it returns the integer that is equal to or just more than f CEIL(f) give the integer that is equal to, or just higher than f. CEIL always rounds up.

How SQL Works The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sublanguage. The purpose of SQL is to provide an interface to a relational database such as Oracle, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC. Among the features of SQL are the following: •It processes sets of data as groups rather than as individual units. •It provides automatic navigation to the data. •It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as "persistent stored modules" (PSM), and Oracle's PL/SQL extension to SQL is similar to PSM.

http://www.lorentzcenter.nl/awcourse/oracle/server.920/a96540/intro.htm#4216