SQL Flashcards

1
Q

Show record/s in a table

A

SELECT [field name],{field name} ,…

FROM [table name];

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

show only distinct values in a column

A

SELECT DISTINCT [field name]

FROM [table name];

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Show records based on condition

A

SELECT [ ]
FROM [ ]
WHERE [condition] {AND/OR} [condition];

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Specify multiple values in WHERE clause

A

WHERE [field name] IN [value, value,…];

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Select values within a range

A

SELECT [ ]
FROM [ ]
WHERE [field name] BETWEEN [value] AND [value] ;

Between is inclusive clause

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Search for specified pattern in a column

A

SELECT [ ]
FROM [ ]
WHERE [field name] LIKE [pattern];

Example pattern : ‘%an%’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

sort records

A

SELECT [ ]
FROM [ ]
ORDER BY [field name]

By default orders in ascending
To force use ASC/DESC

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Group the result by one or more column

A

Used in conjunction with aggregation function

SELECT [ ], [function(field name)]
FROM [ ]
GROUP BY [field name];

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Gives us single value from a calculation performed on all values in a column

A

Aggregate functions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

can not use clauses with aggregate functions

A

SELECT [ ] , [function(field name)]
FROM [ ]
HAVING [expression];

Eg expression
SUM(mark) > 80

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

find empty fields in record

A

SELECT[ ]
FROM [ ]
WHERE [field name] IS NULL;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

query results are stored as ……………

A

Temporary result tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Limit number of records returned

A

SELECT [ ]
FROM [ ]
LIMIT [value]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

….. matches any characters

A

%

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Represents individual character

A

_

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

SQL sorts ……. letter first

17
Q

query is used to ……. data from a table

18
Q

Will give us whole records

19
Q

Give the last record

A

SELECT *
FROM [ ]
ORDER BY ID DESC
LIMIT 1;

20
Q

Returns value based on input

A

Scalar functions

21
Q

Aggregate function that give number of records in a given field

22
Q

Works with integer and return the largest and smallest

A

SELECT MAX/MIN(age) FROM people;

23
Q

Aggregate functions

A
COUNT()
MAX()
MIN()
SUM()
AVG()
24
Q

Scalar functions

A

LENGTH()
UCASE()
LCASE()
ROUND()

25
constraint that requires a value for a field but that value can not be used elsewhere in the column
UNIQUE
26
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));
27
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);
28
Delete a column
DROP ALTER TABLE playlist DROP length;
29
Primary key
Can be used in one field per table ALTER TABLE playlist ADD PRIMARY KEY(id);
30
Tables in same database relate to one another through
Keys
31
Foreign key
ALTER TABLE playlist ADD FOREIGN KEY(album_id) REFERENCES albums(id); can have multiple foreign keys can be null
32
Alter column
ALTER TABLE albums ALTER COLUMN artist SET NOT NULL; ALTER TABLE book ALTER COLUMN id SET PRIMARY KEY;
33
CONSTRAINT
A condition a value must meet in order to be inserted into the table
34
what is sql
computer language for storing, manipulating and retrieving data stored in relational database