Syllabus Detail

  • SQL insert, update and select queries 12 ATAR

Background

  • SQL stands for Structured Query Language
  • It's considered one of the most common languages used to communicate with databases
  • SQL is how we can input and retrieve information from a datbase, as well as update, edit and even remove
  • A general SQL query will follow the structure: SELECT [x] FROM [y] WHERE [z]
    • Where [x] is a database, [y] is a table and [z] is the condition
  • SQL is heavily English-based which makes it easy to understand and work with

 

SQL Queries and an Example

  • "SELECT" begins our SQL query
  • It tells the database what information we wish to view from the proceeding query
  • Examples of information to select are: "* (Wildcard; all"), "FirstName", "LastName", "Gender"

  • "FROM" tells the database where to look for this information
  • This is where we input the name of the table we want the query to search
  • For example, the "Students" table within our database

  • "WHERE" represents the conditions of the search results
  • This means that we can filter our results by providing a search term
  • For example, "WHERE `FirstName` = `Bobby`" will only show results that contain Bobby as the value for the FirstName field

  • "ORDER BY" is the only way to order data in SQL alone
  • It accepts options such as ASC (ascending) and DESC (descending)
  • To select all male students and order them by last name we'd do the following:
  • SELECT `FirstName`, `LastName` FROM `Students` WHERE `Gender` = `Male` ORDER BY `LastName` DESC

  • "UPDATE" allows us to update data that already exists within a field
  • For example, UPDATE `Students` SET `Gender` = `Male` WHERE `Gender` = `Female`
  • This would update our Students table and turn all students male

  • "DELETE" allows us to delete data from within our database
  • For example, DELETE * FROM Students
  • This would delete all records from the Students table

  • "INSERT INTO" allows us to insert new data in to our database
  • For example, INSERT INTO Students VALUES (FirstName, LastName, Gender)
  • This would allow us to insert a new student in to the Students table with appropriate values

 

Further Research

  • Read more about SQL and how it works at NTCHosting here
  • Read more about MySQL, databases and SQL from TheSiteWizard.com here

 

Worksheet and Practice (yet to be added)

Found an error or have an enhancement? Please let us know via this contact form