SQL for Dummies in 60 seconds

A database is a collection of related information. SQL is a tool to work with databases. It consists of 3 components.

• Data Manipulation Language (DML), (select update insert delete)
• Data Definition Language (DDL), (create table, drop table)
• Data Control Language (DCL). (set access rights)

SQL query analyser is the front end application to send queries to server.

SELECT statement :
SELECT column1,column2 FROM table
returns the corresponding columns from the table.

OPTIONAL CLAUSES.

• where
• group by(space included) --data into groups
• having --with group by to give condition 4 columns
• order by(space included) --condition to sort query results

select price from table1 where price>200
select col1,col2 from table1 order by col1 ASC (DESC for decending)

group by combines aggregate and non aggregate columns in same SELECT stmnt.
SELECT itemnumber,sum(price) FROM sales GROUP BY itemnumber
gives the sum of price of each occurence of the itemnumber in sales table.
> group by must contain all non aggregate columns used in the select clause
GROUPBY itemnumber WITH CUBE gives a super aggregate result.

HAVING is used as WHERE cannot contain aggregate fns. and it cannot give conditions for group by clause.

SELECT itemnumber, custid, SUM(price)
FROM sales
GROUP BY custid, itemnumber
HAVING SUM(price)>1000


Aggregate functions: AVG , COUNT , MAX , MIN , SUM

• used as-- function(column)
• agg. fns does not consider NULL value records.
• DISTINCT keyword can be used as SELECT COUNT(DISTINCT column)

COLUMN ALIASES : used to give headings aggregate columns.
used as-- SELECT avg(column) as columnalias

CONCATENATION OPERATOR '+'

• used as-- SELECT column1+column2 FROM table
• ROUND(expression, length) rounds to given length
• STR(expression, length , decimal) converts numeric to character.
• POWER(expression, pwr) same as exp(x,n);

DATE FUNCTIONS
DATEPART(datepart, date) returns the part of date-day/min/hour
DATEADD(datepart, number, date) adds interval number to the date
DATEDIFF(datepart,date1,date2) date2-date1 in terms of datepart
DATENAME(datepart, date) datepart as string
GETDATE() computers clock time and date.


STRING SEARCH
% is equivalent of *
LIKE '%price'
LIKE '_ing' '_' is considerd as one missing letter. returns results like ping sing

ding
LIKE ['BC']all returns results CALL and BALL if any.
LIKE ['a-e']ell letters from a to e
['^'a] omits results with the letter a in that position.

LOGICAL OPERATORS

• AND, OR ,
• IN ('value1', 'value2' , 'value3') --same as OR
• WHERE price BETWEEN value1 AND value2
• NOT

IS NULL is used to check for null values
WHERE column IS NULL
WHERE column IS NOT NULL


JOINS
used in queries which use 2 or more tables in a database. INNER JOIN returns all the rows tat r present in both the tables.
FROM customerinfo INNER JOIN sales ON customerinfo.custid=sales.custid

FROM item AS i
LEFT OUTER JOIN sales AS s ON i.itemnumber *= s.itemnumber
*= returns all on left and only the common ones in the right table.

DSN

• Data source name allow the data on server to be fetched by client constructed by ODBC data src adminstrator 4m ctrlpanel. after creating the bridge to access server. go to accessand File >acute;Get External Data>acute;Link Tables from the menu.
• design grid and design view window are used to view data in access.
• To generate a report, select the query, Insert>report >autoreport tabular.