Notes

SQL-very important language for querying and updating databases
-is supported by virtually every DBMS
-Acess, MS SQL, Oracle, MySQL

Guidelines
1. You must design queries appropriately, identifying the required fields, tables, criteria, sorting, grouping, and operations to use.
Select the fields


Determine the sort order
Determine grouping
Determine any update operations to be performed

Commands
Select-extracts data
Update-
Delete
Insert into
Create database
Alter database
Create table
Alter table
Create index
Drop index

General form
SELECT field_1
FROM table_1
WHERE criterion_1;

Example
SELECT*FROM Customers *wildcard character

Other SELECT options: SELECT DISTINCT and Count

= equal
<> not equal
< greater than
> less than
>= g or equal
<= l or equal
BETWEEN range
LIKE search for a pattern
IN specify multiple possible values
=======================================================
Logical operators (and, or, not)
AND syntax
SELECT*FROM Customers
WHERE condition1 AND condition2 AND condition3;

SELECT*FROM Customers
WHERE City='Berlin' AND City='Munchen';

In the AND table------>the conditions must all be true to make the result true
=======================================================
OR
SELECT*FROM Customers
WHERE City='Berlin' OR City='Munchen';

In the OR table------>one of the the conditions must be true to make the result true
======================================================
In the NOT table------>reserve true or false

NOT
SELECT*FROM Customers
WHERE NOT City='Berlin';

SELECT*FROM tblEmployees
WHERE deptname = 'MIS';

Select and group students by subjects
SELECT*FROM tblStudents, tblSubjects
GROUP BY subjectcode;

Select and group employees by department
Select and group students by subjects
SELECT*FROM tblEmployee, tblDepartment
GROUP BY deptcode;

Display all students whose grade is above 90
SELECT*FROM tblStudents WHERE grade>90;

=========================================================General Form
SELECT field_1
FROM table_1
WHERE criterion_1;


=========================================================
SELECT command

UPDATE command-used to update existing records in a talbe
SET column1=value1, column2=value2,...
WHERE some_column=some_value;

Sample:
UPDATE Customers
SET ContactName='Name',
City='Hamburg'
WHERE CustomerName='Name';

Like--only used for comparison



=========================================================
DELETE STATEMENT-used to delete records in a table records in a table

DELETE FROM Customers
WHERE CustomerName='Alfreds Futterskiste'
AND ContactName='Maria Anders';

It is possible to delete all rows ina table without deleting the table.
DELETE FROM table_name;
============================================================
GROUP BY statement

SElECT column_name, FROM table_name
WHERE column_name operator value
GROUP BY column name


SELECT firstname, lastname, age
FROM tblStudents
WHERE Age>18
GROUP BY firstname, lastname, age;


==============================================================
INSERT INTO--used to insert new records in a table.

doesn't specify the column names where the data will be inserted
INSERT INTO table_name
VALUES (value1, value2, value3,...);

specifies both the colum names and the values to be inserted
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...);


Assume we wish to insert new row in the customer table
INSERT INTO Customers (CustomerName, Contact Name, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B., 'Skagen 21', 'Stavanger', '4006', 'Norway');

INSERT INTO tblEmployees2
VALUES ('001', 'Desiree', 'Shock', '1101 Hibbard Avenue', 'Dumaguete', 'Neg. Or', '6200', '03/01/2012';

============================================================
ORDER BY

selects all customers from the "Customers" table, sorted by the "Country"
SELECT*FROM Customers
ORDER BY Country DESC;

selects all customers from the "Customers" table, sorted by the "Country" and "CustomerName"

SELECT*FROM Customers
ORDER BY Country, CustomerName;
===========================================================

Aggregate Functions
SUM
The following SQL statement finds the sum of all the "Quantity" fields for the "OrderDetails" table:
SELECT SUM (Quantity) AS
TotalItemsOrdered FROM OrderDetails;

----------------------------------------------------
AVG
returns the average value of a numeric column

SELECT AVG (Price) AS PriceAverage FROM Products;

Selects the "ProductsName" and "Price" records that have an above average price:
SELECT ProductName, Price FROM Products
WHERE Price> (SELECT AVG(Price) FROM Products);

-------------------------------------------------
COUNT

SELECT COUNT(column_name) FROM table_name;

SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders WHERE CustomerID='7';


----------------------------------------------------
MAX Function

SELECT MAX (Price) AS HighestPrice FROM Products;
----------------------------------------------------
MIN Function
SELECT MIN (Price) AS LowestPRice FROM Products;

-------------------------------------------------
UCASE Function
SELECT UCASE (CustomerName) AS Customer, City FROM Customers;

------------------------------------------------
LCASE Function
SELECT LCASE (CustomerName) AS Customer, City FROM Customers;