04 - Basic SQL Statements Flashcards
Basic SELECT statement?
SELECT [field list]
FROM [tableName];
Basic WHERE clause?
SELECT [field list]
FROM [tableName]
WHERE [condition list];
Explain the following Boolean characters:
- <
- >
- <=
- >=
- <>
- < : smaller than
- > : largern than
- <= : smaller or equal
- >= : equal or larger
- <> : not equal to
Explain DISTINCT function?
SELECT DISTINCT(Division) From Personnel;
Shows different values of given columns.
ORDER BY statement?
SELECT [field list]
FROM [tableName]
ORDER BY [field] [ASC|DESC];
GROUP BY statement?
SELECT [field]
FROM [tableName]
GROUP BY [field];
Forensic functions?
SELECT Name, MD5(Name) FROM Personnel;
SELECT Name, SHA1(Name) FROM Personnel;
Regular Expression Matching - Find any name in Personnel beginning with “Jo”?
SELECT *
FROM [tableName]
WHERE Name REGEXP “^Jo”;
DELETE Command?
DELETE FROM [table]
[WHERE [condition]];
UPDATE Command?
UPDATE [table]
SET [field] = [value];
DESCRIBE Command?
DESCRIBE [tableName];
ALTER TABLE Command?
ALTER TABLE [tableName]
ADD INDEX [indexName] [indexfieldName]
| ADD PRIMARY KEY ([fieldName])
| ADD COLUMN ([fieldName])
| DROP PRIMARY KEY
| DROP INDEX
| DROP COLUMN [fieldName]
Check for NULL values?
SELECT [fields]
FROM [tableName]
WHERE [field] IS NULL;
Regular JOIN statement?
SELECT Personnel.Name, Cases.Type
FROM Personnel, Cases
WHERE Personnel.PID = Cases.OIC;
INNER JOIN statement?
SELECT Personnel.Name, Cases.Type
FROM Personnel
INNER JOIN Cases ON
Personnel.PID = Cases.OIC;
