|
|
Retrieve all records from table MyEmp:
Examples:
SELECT * FROM MyEmp; /* MS Access */
SELECT * FROM MyEmp.db; /* Paradox */
SELECT * FROM MyEmp.dbf; /* dBase */
|
MS Access syntax:
/* copy Departments table into new table. */
INSERT INTO NewDepts
SELECT * FROM Departments;
/* retrieve list of employees and bulding number of their department
using join between two tables. insert result set into new table. */
SELECT a.FirstName, b.LastName, b.BuildingNum
INTO NewTable
FROM Employees AS a INNER JOIN Departments AS b
ON a.DeptNo = b.DeptNo ;
/* add BuildingNum field to Employees table */
ALTER TABLE Employees ADD COLUMN BuildingNum INT;
/* update BuildingNum field in Employees table using Join with Departments */
UPDATE Employees AS a INNER JOIN Departments AS b
ON a.DeptNo = b.DeptNo ;
SET a.BuildingNum = b.BuildingNum;
/* boss want to find the most populated departments in the company.
query uses GROUP BY to do count and HAVING condition to filter the results */
SELECT DeptNo, count(*) AS dept_count
FROM Employees
GROUP BY DeptNo
HAVING count(*) > 10;
|
Paradox and dBase DML:
SELECT INTO statement is not supported.
Table name must be the name of the file, like: Employees.db
Examples:
SELECT a.FirstName, b.LastName, b.BuildingNum
FROM Employees.db AS a INNER JOIN Departments.db AS b
ON a.DeptNo = b.DeptNo;
INSERT INTO NewDepts.dbf
SELECT * FROM Departments.dbf;
|
In sql scripts /* remarks */ are allowed:
SELECT * FROM Settlements.dbf
/* here remarks can be places */
WHERE DistrictID > 2;
|
Build-In FUNCTIONS:
[*] ANSI-standard SQL string manipulation functions:
UPPER(), to force a string to uppercase
LOWER(), to force a string to lowercase
TRIM(), to remove repetitions of a specified character from
the left, right, or both sides of a string
SUBSTRING() to create a substring from a string
|
SELECT SUBSTRING(firstname FROM 1 FOR 4 ) FROM Employees.db;
|
[*] Date functions:
EXTRACT() function for isolating a single numeric field from
a date/time field on retrieval using the following syntax:
EXTRACT (extract_field FROM field_name)
|
SELECT EXTRACT(YEAR FROM HIRE_DATE) FROM Employees.db;
|
You can also extract MONTH, DAY, HOUR, MINUTE, and SECOND using this function.
[*] The following aggregates are supported:
SUM, AVG, MIN, MAX, COUNT
The following operators are supported:
+, -, *, /, =, < >, IS NULL, IS NOTNULL, >=, =<, AND, OR, NOT, ||, LIKE
|