SQL Flashcards

All 13 cards in this deck

In SQL, what does SELECT * do?

Retrieves all fields from the records that match the query.

In SQL, what does the FROM command specify?

Which table the data is retrieved from.

In SQL, what does the WHERE command do?

e.g. WHERE Year = 11

Filters the records so only those meeting the condition are returned — only records whose Year is 11.

In SQL, how do you select more than one field?

e.g. the Name and Year fields

List the field names after SELECT, separated by commas: SELECT Name, Year (not AND).

In an SQL WHERE clause, what is the difference between AND and OR?

AND means all the conditions must be true; OR means at least one condition must be true.

In SQL, what does ORDER BY do?

e.g. ORDER BY Surname ASC

Sorts the results by the named field. ASC gives ascending order (A to Z, smallest first); DESC gives descending order (Z to A, largest first).

In an SQL query that uses two tables, how are the tables linked in the WHERE clause?

e.g. tables Member and Award, both with a MemberID field

With a condition that matches the foreign key to the primary key, naming each table: WHERE Member.MemberID = Award.MemberID.

In SQL, how must text values be written in a condition?

e.g. finding the city Leeds

Inside quotation marks: WHERE City = 'Leeds'. Numbers are written without quotes, e.g. WHERE Age = 14.

Which SQL command adds a new record to a table?

INSERT INTO, followed by the table name, the field names in brackets, then VALUES with the data in brackets in the same order:
INSERT INTO Pet (PetID, Name) VALUES (7, 'Rex')

Which SQL command changes data in records that already exist?

UPDATE, with SET giving the new values and WHERE choosing the records:
UPDATE Pet SET Weight = 4.2 WHERE PetID = 7

Which SQL command removes records from a table?

DELETE FROM, with WHERE choosing the records to remove:
DELETE FROM Pet WHERE PetID = 7

What happens if an SQL UPDATE or DELETE command has no WHERE clause?

It acts on every record in the table: every record is changed, or every record is deleted.

In SQL UPDATE and DELETE commands, why is the primary key often used in the WHERE clause?

Its value is unique, so the condition picks out exactly one record. A condition on a non-unique field, such as a name, could change or delete other records too.