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()
constraint that requires a value for a field but that value can not be used elsewhere in the column
UNIQUE
Constraint that review the value that is being added to the column and checks whether it meets the condition
CHECK
ALTER TABLE playlist(
ADD CONSTRAINT length
CHECK (length>=3));
When we do not have a value to add to a column we can add a constraint that can fill it in for us
DEFAULT
ALTER TABLE playlist(
ADD played boolean
DEFAULT false);
Delete a column
DROP
ALTER TABLE playlist
DROP length;
Primary key
Can be used in one field per table
ALTER TABLE playlist
ADD PRIMARY KEY(id);
Tables in same database relate to one another through
Keys
Foreign key
ALTER TABLE playlist
ADD FOREIGN KEY(album_id)
REFERENCES albums(id);
can have multiple foreign keys
can be null
Alter column
ALTER TABLE albums
ALTER COLUMN artist SET NOT NULL;
ALTER TABLE book
ALTER COLUMN id SET PRIMARY KEY;
CONSTRAINT
A condition a value must meet in order to be inserted into the table
what is sql
computer language for storing, manipulating and retrieving data stored in relational database
