SQL

Question 1

Show record/s in a table

Answer 1

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

FROM [table name];

Question 2

show only distinct values in a column

Answer 2

SELECT DISTINCT [field name]

FROM [table name];

Question 3

Show records based on condition

Answer 3

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

Question 4

Specify multiple values in WHERE clause

Answer 4

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

Question 5

Select values within a range

Answer 5

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

Between is inclusive clause

Question 6

Search for specified pattern in a column

Answer 6

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

Example pattern : ‘%an%’

Question 7

sort records

Answer 7

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

By default orders in ascending
To force use ASC/DESC

Question 8

Group the result by one or more column

Answer 8

Used in conjunction with aggregation function

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

Question 9

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

Answer 9

Aggregate functions

Question 10

can not use clauses with aggregate functions

Answer 10

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

Eg expression
SUM(mark) > 80

Question 11

find empty fields in record

Answer 11

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

Question 12

query results are stored as ……………

Answer 12

Temporary result tables

Question 13

Limit number of records returned

Answer 13

SELECT [ ]
FROM [ ]
LIMIT [value]

Question 14

….. matches any characters

Answer 14

%

Question 15

Represents individual character

Answer 15

_

Question 16

SQL sorts ……. letter first

Answer 16

Capital

Question 17

query is used to ……. data from a table

Answer 17

Extract

Question 18

Will give us whole records

Answer 18

*

Question 19

Give the last record

Answer 19

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

Question 20

Returns value based on input

Answer 20

Scalar functions

Question 21

Aggregate function that give number of records in a given field

Answer 21

COUNT

Question 22

Works with integer and return the largest and smallest

Answer 22

SELECT MAX/MIN(age) FROM people;

Question 23

Aggregate functions

Answer 23

COUNT()
MAX()
MIN()
SUM()
AVG()

Question 24

Scalar functions

Answer 24

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

Question 25

constraint that requires a value for a field but that value can not be used elsewhere in the column

Answer 25

UNIQUE

Question 26

Constraint that review the value that is being added to the column and checks whether it meets the condition

Answer 26

CHECK

ALTER TABLE playlist(
ADD CONSTRAINT length
CHECK (length>=3));

Question 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

Answer 27

DEFAULT

ALTER TABLE playlist(
ADD played boolean
DEFAULT false);

Question 28

Delete a column

Answer 28

DROP

ALTER TABLE playlist
DROP length;

Question 29

Primary key

Answer 29

Can be used in one field per table

ALTER TABLE playlist
ADD PRIMARY KEY(id);

Question 30

Tables in same database relate to one another through

Answer 30

Keys

Question 31

Foreign key

Answer 31

ALTER TABLE playlist
ADD FOREIGN KEY(album_id)
REFERENCES albums(id);

can have multiple foreign keys
can be null

Question 32

Alter column

Answer 32

ALTER TABLE albums
ALTER COLUMN artist SET NOT NULL;

ALTER TABLE book
ALTER COLUMN id SET PRIMARY KEY;

Question 33

CONSTRAINT

Answer 33

A condition a value must meet in order to be inserted into the table

Question 34

what is sql

Answer 34

computer language for storing, manipulating and retrieving data stored in relational database