Welcome to week two

In week one of the course, you learnt about what a database is and explored an SQLite database of computer sales data using a DBMS (database management system).

This week will look at using SQL (Structured Query Language) to search and update the data in the computer sales database.

What is SQL?

Structured Query Language is a programming language designed to communicate with a DBMS. If you want to search, edit, or add data to a DBMS, SQL is the language to use. It was initially called ‘Structured English Query Language’ (SEQUEL) and pronounced ‘sequel’; it later had its name shortened to ‘Structured Query Language’ (SQL) for trademark reasons.

Applications of SQL

In the 1970s, IBM created new database software called ‘System R’. To enable the data within System R to be managed efficiently, Structured English Query Language (SEQUEL) was created. You can pronounce this as ‘S-Q-L’ or ‘sequel’; either is acceptable.

SQL has become a database query language standard; it is the basis of a variety of well-established database applications. With advances in database technology, SQL-based databases have become increasingly accessible for the regular user. This is due to the introduction of various open-source SQL DBMSs such as MySQL, PostgreSQL, SQLite, Firebird, and many more with no upfront cost.

Although most relational database systems use SQL, most of them also have their own additional proprietary extensions, which are usually only used on their own systems. However, the standard SQL commands such as SELECT, INSERT, UPDATE, DELETE, and CREATE can be used to accomplish almost everything you need to do with a database.

Why do you need to learn about SQL?

Put yourself in the shoes of an employee or a manager of a company who works with large amounts of data. For example, you could be working in Business Intelligence (BI), data science, database administration, or web development. All of these jobs involve managing huge amounts of data. Therefore, before carrying out any analysis with data, you will have to learn how to interact with it. Learning SQL will help you to extract information from a database efficiently and complete your tasks efficiently.

SQL language elements

The SQL language is based on several elements:

• Queries: a query will retrieve data based on a given criteria. The example below selects all the data in the name field from the model table:

SELECT name From model;

• Clauses: a clause is used to filter records and return data that fulfil a specified condition.
• Predicates: predicates specify conditions, which are used to filter the return of the SQL statements. In the example below, the WHERE clause is used to return the name of the models with four cores. '= '4' is the predicate.

SELECT name FROM model
WHERE cores = '4'

• Expressions: a combination of one or more values, functions, and operators to return a value when it is executed (run). The example below uses the count() function to return the number of records with four cores from the model table:

SELECT count(*) FROM model
WHERE cores = '4'

Activity

Below is an SQL statement that uses a SELECT, *, and FROM.

SELECT * from model;

In the comments section, describe what you think this SQL statement will return when it is executed.