Introduction to SQL

  • SQL is a powerful language used to interrogate and manipulate relational databases.
  • People working in library- and information-related roles have skills that allow them to use SQL to organize and access data.

Selecting and sorting data

  • SQL is ideal for querying databases
  • SQL queries have a basic query structure starting with SELECT field FROM table with additional keywords and criteria that can be used.

Filtering

  • Use WHERE to filter and retrieve data based on specific conditions.
  • Use AND, OR, and NOT to add additional conditions.
  • Use the comparison keyword LIKE and wildcard characters such as % to match patterns.

Ordering and commenting

  • Queries often have the structure: SELECT data FROM table WHERE certain criteria are present.
  • Comments can make our queries easier to read and understand.

Aggregating and calculating values

  • SQL can be used for reporting purposes.
  • Queries can do arithmetic operations on field values.

Joins and aliases

  • Joining two tables in SQL is an good way to analyse datasets, especially when both datasets provide partial answers to questions you want to ask.
  • Creating aliases allows us to spend less time typing, and more time querying!

Saving queries

  • Saving queries as ‘Views’ allows you to save time and avoid repeating the same operation more than once.

Database design

  • Database design is helpful for creating more efficient databases.

Creating tables and modifying data

  • Use CREATE and DROP to create and delete tables.
  • Use INSERT to add data.
  • Use UPDATE to modify existing data.
  • Use DELETE to remove data.
  • It is simpler and safer to modify data when every record has a unique primary key.
  • Do not create dangling references by deleting records that other records refer to.

Other database tools

  • There are additional database tools beyond DB Browser and SqliteOnline.

Extra challenges (optional)

  • It takes time and practice to learn how to translate plain English queries into SQL queries.

Good Style

  • There are many ways to write an SQL queries, but some look better than others.