1.3.2(d) – SQL

SELECT, FROM & WHERE

Used to get data from a database.

They follow the following format:

SELECT (something) FROM (table)

For example:

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)

For example:

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.

For example:

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

JOIN returns multiple records joined by a field which is the same in both.

For example:

SELECT name FROM Student JOIN Course ON Student.CourseID = Course.ID