SQL Commands Flashcards
What is SQL?
It is a declarative language used for creating, querying and updating tables in a relational database.
What is the SELECT keyword used for?
Used to extract a collection of fields from a given table.
Show the syntax of the SELECT command:
SELECT ,
FROM ,
WHERE
ORDER BY
The default for ORDER BY is ascending.
Example:
SELECT CDTitle, RecordCompany
FROM CD
WHERE DatePublished = 01/01/2015
ORDER BY CDTitle
What are the most common ‘standard conditions’ that can be used with SQL?
=, <=, >=, !=, AND, OR etc.
What does the IN condition mean?
Equal to a value within a set of values.
What does the LIKE condition mean?
Similar to.
What does the BETWEEN…AND condition mean?
Within a range, including the two defining values.
What does the IS NULL condition mean?
Field doesn’t contain a value. eg CD IS NULL
What does NOT do?
Invert truth.
What is the ORDER BY keyword used for?
Gives control over how the results are displayed.
How would you further sort data using the ORDER BY keyword?
Give the command another parameter to sort by. eg SORT BY Name, Date.
How would you use the SELECT command to extract and combine data from multiple tables?
Use the SELECT command as usual but prefix the field name with the table name: tablename.fieldname
eg SELECT Song.SongTitle, Artist.ArtistName
What does the JOIN command do?
JOIN combines rows from tables based on common fields between them.
What is the CREATE TABLE keyword used for?
Used to create a new SQL table.
Show the syntax of the CREATE TABLE command:
CREATE TABLE
(
)
Define the CHAR(n) Data Type.
Character string with fixed length, n.
Define the VARCHAR(n) Data Type.
Variable length character string, max length n.
Define the BOOLEAN Data Type.
TRUE or FALSE.
INTEGER, INT
Integer
FLOAT(a,b)
Float with max digits, a, and max digits after decimal, b.
DATE
Stored Day, Month, Year values.
CURRENCEY
Formats numbers based on a currency.
What is the ALTER TABLE keyword used for?
Used to add, delete or modify fields in an existing table.
Show how to ADD a field using the ALTER TABLE command:
ALTER TABLE
ADD
Show how to DELETE a field using the ALTER TABLE command:
ALTER TABLE
DROP COLUMN
Show how to Change a data type of a field using the ALTER TABLE command:
ALTER TABLE
MODIFY COLUMN
What is the INSERT keyword used for?
Inserting a new record.
Show the syntax of the INSERT command:
INSERT INTO (column #1, n) VALUES (value 1, n)
Where column is the field for the data to be inserted and value is the new value.
What is the UPDATE keyword used for?
Updates a record in a database.
Show the syntax of the UPDATE command:
UPDATE
SET = , =
WHERE columnX = value
What is the DELETE keyword used for?
Deleting a record.
Show the syntax of the DELETE command:
DELETE FROM
WHERE columnX = value
What is the PRIMARY KEY keyword used for?
Assigning a primary key, or a composite key.
Show the syntax of the PRIMARY KEY command:
PRIMARY KEY (,) if n is populated a composite key is created.
What is the FOREIGN KEY keyword used for?
Used to set up a Foreign Key.
Show the syntax of the FOREIGN KEY command:
FOREIGN KEY REFERENCES ()
