SQL simple -> Data Manipulation syntax for desktop DBMS: MS-Access, Paradox, dBase

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


sqlexamples.info