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