1.3.2(d) – SQL
SELECT, FROM & WHERE
Used to get data from a database.
They follow the following format:
SELECT (something) FROM (table)
SELECT name FROM Students;
WHERE can be used to add a condition to the query, for example:
SELECT name FROM students WHERE name = “Jeff”
SELECT statements can also be nested.
SELECT name FROM book WHERE Author = (SELECT id FROM Author WHERE surname = “Steinbeck”)
LIKE & Wildcards
LIKE is used alongside WHERE to speficy a specific format, and uses wildcards. The two you need to know are ‘*’ and ‘%’.
‘*’ means ALL
‘%’ means 0 or more unspecified characters.
It is used like: SELECT (something) FROM (table) WHERE (field name) LIKE (format)
SELECT * FROM book WHERE bookName LIKE ‘a%” finds any book which starts with “a”.
AND & OR
AND and OR can be used alongside WHERE.
AND is used to only return the record if all of the conditions are true.
OR is used to only return the record if any of the conditions are true.
SELECT * FROM book WHERE bookName = “Great Expectations” AND ISBN = “9781548731854”
SELECT * FROM book WHERE bookName = “Great Expectations” OR ISBN = “9781548731854”
DELETE, INSERT & DROP
DELETE is used to delete records from a table.
For example: DELETE FROM book WHERE bookName = “Great Expectations”
INSERT is used to add new records to a table.
For example: INSERT INTO book (id, name, author) VALUES (1, “Great Expectations”, “Charles Dickens”)
DROP is used to delete tables or databases.
For example: DROP DATABASE bookInfo or DROP TABLE book
JOIN returns multiple records joined by a field which is the same in both.
SELECT name FROM Student JOIN Course ON Student.CourseID = Course.ID