SQL Flashcards
Show record/s in a table
SELECT [field name],{field name} ,…
FROM [table name];
show only distinct values in a column
SELECT DISTINCT [field name]
FROM [table name];
Show records based on condition
SELECT [ ]
FROM [ ]
WHERE [condition] {AND/OR} [condition];
Specify multiple values in WHERE clause
WHERE [field name] IN [value, value,…];
Select values within a range
SELECT [ ]
FROM [ ]
WHERE [field name] BETWEEN [value] AND [value] ;
Between is inclusive clause
Search for specified pattern in a column
SELECT [ ]
FROM [ ]
WHERE [field name] LIKE [pattern];
Example pattern : ‘%an%’
sort records
SELECT [ ]
FROM [ ]
ORDER BY [field name]
By default orders in ascending
To force use ASC/DESC
Group the result by one or more column
Used in conjunction with aggregation function
SELECT [ ], [function(field name)]
FROM [ ]
GROUP BY [field name];
Gives us single value from a calculation performed on all values in a column
Aggregate functions
can not use clauses with aggregate functions
SELECT [ ] , [function(field name)]
FROM [ ]
HAVING [expression];
Eg expression
SUM(mark) > 80
find empty fields in record
SELECT[ ]
FROM [ ]
WHERE [field name] IS NULL;
query results are stored as ……………
Temporary result tables
Limit number of records returned
SELECT [ ]
FROM [ ]
LIMIT [value]
….. matches any characters
%
Represents individual character
_
SQL sorts ……. letter first
Capital
query is used to ……. data from a table
Extract
Will give us whole records
*
Give the last record
SELECT *
FROM [ ]
ORDER BY ID DESC
LIMIT 1;
Returns value based on input
Scalar functions
Aggregate function that give number of records in a given field
COUNT
Works with integer and return the largest and smallest
SELECT MAX/MIN(age) FROM people;
Aggregate functions
COUNT() MAX() MIN() SUM() AVG()
Scalar functions
LENGTH()
UCASE()
LCASE()
ROUND()