N5 SQL Essential Commands
SELECT statements are your go-to tool for finding specific information in a database. You'll need four key components: what fields you want (SELECT), which table to look in (FROM), your search conditions (WHERE), and how to sort results (ORDER BY).
Here's the magic formula: SELECT name, age FROM pupil WHERE age <18 ORDER BY name; This grabs names and ages of pupils under 18, sorted alphabetically. Remember to use speech marks around text values, and you can combine conditions using AND, OR, and NOT.
INSERT statements let you add fresh data to tables. The simplest approach adds values for every field: INSERT INTO Instructor VALUES (5, 'D Thomas', '1985/11/30',5); Just remember that field order matters massively here!
Sometimes you only need to fill certain fields. In that case, specify exactly which ones: INSERT INTO Instructor (InstructorID, Name) VALUES (5, 'D Thomas'); SQL dates follow the yyyy/mm/dd format, so '1985/11/30' means 30th November 1985.
Top Tip: Always double-check your field order when inserting data - getting this wrong is one of the most common SQL mistakes!
DELETE and UPDATE statements handle removing and changing existing data. DELETE works like: DELETE FROM Course WHERE CourseID = 'BMX05'; Be careful though - forget the WHERE clause and you'll delete everything! UPDATE follows this pattern: UPDATE Course SET Date = '2017/12/11' WHERE DATE = '2017/12/10';
EQUI joins connect related tables using matching key fields. This powerful technique lets you pull information from multiple tables simultaneously: SELECT Instructor.Name, Course.Title FROM Instructor, Course WHERE Course.InstructorID = Instructor.InstructorID;