SQL basics

 

 

Simple Queries

Each table in a relational database contains rows (records) and columns (fields). In computer science terminology, rows are sometimes called "tuples," columns may be referred to as "attributes," and the tables themselves may be called "relations." A table can be visualized as a matrix of rows and columns, where each intersection of a row and column contains a specific value. It is "relational" since all records share the same fields.

person  table in the sample database SQLTW_Sample_DB

person table in the sample database SQLTW_Sample_DB

phone_contract  table in the sample database SQLTW_Sample_DB

phone_contract table in the sample database SQLTW_Sample_DB

We want to see what is in the table person:

SELECT * FROM person;

The * returns ALL of the columns that are contained in the person table.

This is sometimes too much information.  We can specify only the columns that we want returned by specifying the name of each column we want to see as shown below.  The statement returns only the name and residence columns from the person database.  Column names are separated by a comma:

SELECT name, residence FROM person; 

We can get the number of lines of results by using the COUNT command. To get the number of entries in the person table we use:

SELECT count(*) FROM person;

Results might contain duplicates (they form a multi-set). We can use the DISTINCT keyword to get only the distinct results.  All duplicate results are omitted with the use of this keyword.

SELECT DISTINCT age FROM person; 

Note that you should always enter only a single SQL-statement at once. Now you have learned enough start writing SQL queries and to answer the first question

1.) Return a list of residences without duplicates.

2.) How many different cities of residence do the people in the persons table have?

Constraints

Quite often we are not interested in all of the entries of the database, especially because it might contain a huge number of lines. To get only the lines fulfilling some constraints, the WHERE clause is used, e.g:

SELECT name, residence FROM person WHERE age > 30;

For different types of data, different constraints make sense. For all types of data, we can check for equal and not equal != . For numbers, one can also compare for larger >larger or equal >=smaller <smaller or equal <= .

The following example queries the person table for an exact match using the equal = operator. 

SELECT name, residence FROM person WHERE name = 'Melvin'

We surround the name Melvin with single quotes because it is a character or text field.  This query gives only the results that exactly match the name Melvin, if it exists in our table.

In contrast to the equal = operator, the LIKE keyword returns results that are not an exact match.  We use the LIKE operator to find the characters that may exist in a sentence or phrase.  The syntax looks like this ' %character to look for%' .  a single quote followed by a percent symbol (referred to as a wildcard symbol in SQL).  When written in an SQL query the wildcard returns an exact match of only the characters that are between them.  It returns any characters that may exist before and after them.  This may be a bit confusing the first time, so call a coach over for more clarification if you need to.  See the following example below.

SELECT * from  person where name LIKE '%an%' 

The query returns all names that have the characters 'an' in it.  

3.) Get all people whose names contains 'oh'.

In SQLite the ><operators for dates, do a lexicographical comparison, not a numeric one. This means that if there is an inconsistent representation of dates, the comparison might fail even though the two values represent the same dates. E.g. if the format "2014-10-23"and the format "2014/10/16" is used. Note that for dates double quotes " are used instead of the single quotes '. used for text. (No quotes used for numbers)

There is a table named flight containing flight data. The column with the dates is called date.

4.) Get all flights before October 20.

The operator in checks if the field on the left side of the operator is a member of the set right to the operator. 

SELECT * FROM person WHERE name in ('Philip', 'Carlos') 

The query above will return results where persons are named either Philip OR Carlos.

SELECT * FROM person WHERE name = 'Philipp' or name = 'Carlos'

The query above produces the exact result as the previous example that uses the in operator.

When creating a query that is searching for more than one criteria (for instance the name Phillipp or Carlos) we make use of the operator to include multiple the multiple search items.  For example, the following query returns people whose names are either Philip OR Carlos.  Observe that the WHERE clause is only used once and the field that contains the value you are looking for (name = ) is repeated.

SELECT name, residence FROM person WHERE name = 'Phillip' OR name = 'Carlos'

5.)Formulate two suitable queries to demonstrate that.

Constraints can be combined by the logical operator and and the operator or.

6.) Find all people whose first name is Philipp and who are older than 50 years.

There is another table which is called phone_contract. The column containing the status (active, non-active) is called status.

7.) Find out, how many contracts are there. With a second query find out how many of them are active.

Results from more than one table

Usually, it is necessary to get information which is spread over more than one table. For example, you may want to know the names of the people who have an active phone contract.

When you know what you are looking for you are already able to formulate a concrete condition in the where clause. With SQL you can combine information from various tables, e.g.

select person.name, phone_contract.phone_number
from person, phone_contract;

Note that we could also write phone_number instead of phone_contract.phone_number as the table person does not contain a column called phone_number and, therefore, no ambiguity could arise.

8.) What does this query return?

To output the matching names and phone numbers, we can use a where clause again:

select p.name, c.phone_number from person p, phone_contract c
where c.name = p.name;

As we did not want to write the complete table name, we have defined and abbreviation in the from clause (person p) and (phone_contract c).

9.) Find all names and ages of persons who have an active phone contract.

10.) Find all names and phone numbers of people who are older than 30.

Sub-queries

The methods described above allow to get information from an arbitrary number of tables. But if you want to formulate a condition which depends on values of certain data entries, you need sub-queries.

11.)What does the following query do?

select person.name, person.age, residence, phone_number from person, phone_contract
where person.name = phone_contract.name and
age in (select age from person where residence = 'Paris');

The part in the last line in parentheses is called a sub-query. Such sub-queries can be a little bit confusing at first sight so you might want to pause for a moment.

Now let's look at the statement above a little bit closer. First, only perform the sub-query

select age from person where residence = "Paris";

and look at the results. It returns a the list of all ages of persons with residence in Paris. As a next step, try the query

select person.name, person.age, residence from person where age in (21,36);

Now you can replace the where clause with the sub-query above without altering the result. Try this! The last step to get to the complete statement is to add the matching phone_number from the table phone_contract.

This technique of decomposing a complex SQL-statement into its simpler parts and then reassembling them is often extremely helpful when dealing with new or unfamiliar constructs.

If you have no more questions concerning basics SQL queries, you can proceed to the next chapter and solve the theft of the Mona Lisa.

Solutions

Back to start