Level 18. Basic Principles of Working with SQL

Take a sheet of paper and write down all the categories by which the data about Greg's friends can be structured. There will be 9 categories in total.

The categories we define will be used to organize the data.

Before we get into the details of tables, rows, and columns, let's take a step back and try to see the big picture. The first SQL structure you need to know about is the container that holds all of your tables. It is called a database.

A database is a container that holds tables and other SQL structures for working with them. The dictionary definition of a database.

Every time you search the Internet, look something up in a reference, use a TiVo, order tickets, get a speeding ticket, or buy something in a store, the information needed is requested from a database.

A database consists of tables.

A table is a structure that holds data organized into columns and rows. Remember the categories from the previous example? Each category corresponds
to a column of the table.

For example, a column might contain one of these values: Single, Married, Divorced.

A table row holds all the information about one item in the table. In Greg's new table, a row holds the complete description of one person. For example, one row might store this data: John, Jackson, single, writer, jj@boards-r-us.com.

The information in a database is divided into tables.

Below you will find several cards and a table. Your task is to imagine yourself in the place of the partly filled table. Of course, you will want to fill in the empty spots and reach balance and inner fulfillment. Also give the columns of the table meaningful names. When you

finish the exercise, check whether you have managed to achieve spiritual unity with the table.

All the tables in a database must be related to each other in one way or another. For example, a database with information about doughnuts eaten might consist of the following tables:

Again, for efficiency. Choosing the optimal data type for each column of a table reduces its size and speeds up working with the data.

Okay, I get you. You don't joke around with large volumes of data. Every byte counts there. But is that all for today? Aren't there any other types?

There are, but these types are the most important. The exact set of supported data types also depends on the RDBMS, so consult the documentation for more information. We also recommend the book “SQL in a Nutshell” — it's an excellent reference that describes the main differences between the various RDBMSs.

Paradoxical but true: below are the main commands for managing tables — creating, deleting, and reading them. But, as the heading already suggests, testers often don't use these commands; they are more the domain of programmers. A tester's job comes down to being able to pull the necessary set of data out of the tables for tests. Management commands:

To display the description of a table's structure, use the DESC command.

The DROP TABLE command destroys a table along with all its contents. Be careful, and never use it just for the sake of it!

To save data in a table, use the INSERT command, which comes in several variants.

NULL is an undefined value that is not the same as zero or an empty string. For a column containing null, the IS NULL condition is true, but the column is not equal to NULL.

Columns whose value is not specified in an INSERT command are initialized to NULL by default.
To prevent a column from storing null, use the NOT NULL keywords when creating the table.

The DEFAULT clause defines a default value — if no value is given for a column when the table is filled in, the column is automatically filled with this value.

CREATE TABLE

The command creates a table, but to run it you need to know the NAMES and DATA TYPES of the columns. They are determined by analyzing the information that will be stored in the table.

DROP TABLE

The command deletes a table that was created with a mistake — but this should be done before running the INSERT commands that fill the table with data.

CREATE DATABASE

The command creates the database that stores all the tables with data.

USE DATABASE

The command opens the database for creating tables.

NULL and NOT NULL

When creating a database, you should know which columns must not accept the value NULL — this will simplify sorting and searching the data. The NOT NULL condition is set for columns when the table is created.

DEFAULT

Defines the default value for a column; it is used when the column's value is not specified when a row is inserted.

If a column often holds one particular value, you can assign it a default value with the DEFAULT keyword. The value that follows DEFAULT is automatically entered into the table each time a new record is added — unless a different value is given. The default value must match the column's data type

CREATE TABLE doughnut_list

(

doughnut_name VARCHAR(10) NOT NULL,

doughnut_type VARCHAR(6) NOT NULL,

doughnut_cost DEC(3,2) NOT NULL DEFAULT 1.00

);

DEFAULT

1) The column must ALWAYS contain a value. To ensure this, we not only declare it with the NOT NULL keywords, but also assign it a default value of 1.

2) This value is stored in the doughnut_cost column if the INSERT command does not specify another value.

DEC(3,2)

The value can contain up to 3 digits: one before and two after the decimal point

And here is how the my_contacts table will look if all columns are declared with the NOT NULL keywords:

When working with databases, selecting data is usually done more often than inserting data. In this chapter you will meet the mighty SELECT command
and learn how to get at the important information you have stored in your tables. You will also learn to use the WHERE, AND, and OR conditions to select data selectively and keep unneeded data from being returned.

Greg has finally moved all the data from his card file into the my_contacts table. Now he wants to relax. He got hold of two concert tickets and wants to invite one of his acquaintances — a girl named Anne from San Francisco. To find her email address, Greg browses the contents of the my_contacts table with the SELECT command from chapter 1.

SELECT * FROM my_contacts;

Searching for a contact

The search took far too long and was extremely tedious. There is also a very real danger that Greg missed a couple of suitable Annes, including the one he is looking for. Now that he has the email addresses, Greg sends out messages and gets replies...

The following SELECT command will help Greg find Anne's details much faster than painstakingly combing through the whole huge table. In this command we use the WHERE condition, which gives the RDBMS a more precise criterion for selecting records. The condition narrows the search results, and the command returns only the records for which the condition is met.

The = sign in the WHERE condition means that each value of the first_name column is checked for equality with the text 'Anne'. If the two values are equal, the whole record is included in the result. If not, the record is skipped.

This console window shows the query result — the subset of records whose first_name column contains the value 'Anne'.

The asterisk (*) tells the RDBMS to return the values of all the columns of the table.

Yes, you can. The asterisk selects all columns, but in a few pages you will learn how to limit the selection to some of the columns so the result is easier to work with

The asterisk (*) tells the RDBMS to return the values of all the columns of the table

You probably thought some boring assignments were coming, but nothing of the sort! Right now we're going to mix up fruit cocktails at the Galaxy QA Academy bar. Look through the table of the whole menu, and then the Master will give you an SQL query, and with its help you'll find out which cocktail you've got!

Which drink the query will return, tell me you must.

If you insert data into a table (INSERT) or run a query to search for data (SELECT) with a VARCHAR, CHAR, or BLOB value that contains an internal apostrophe, you must tell the RDBMS that this apostrophe does not end the text,
but is part of it and must be included in the string. To do this, you can put
a backslash before the apostrophe.

You must tell the RDBMS that the apostrophe does not mark the start or end of a string, but is part of the text

Escaping with a backslash

To solve this problem (and fix the INSERT command at the same time), put a backslash before the apostrophe in the text:

INSERT INTO my_contacts

VALUES

('Funyon', 'Steve', 'steve@onionflavoredring.com', 'M', '1970-01-04', 'Punk', 'Grover\'s Mill, NJ', 'Single', 'Rebellion', 'Like-minded people, guitarists');

When you put the prefix \ before an apostrophe to show that it is part of the text, it is called “escaping”

Escaping by doubling the apostrophe

You can also “escape” an apostrophe another way — by putting an extra apostrophe before it:

INSERT INTO my_contacts
VALUES

('Funyon', 'Steve', 'steve@onionflavoredring.com', 'M', '1970-01-04', 'Punk', 'Grover''s Mill, NJ', 'Single', 'Rebellion', 'Like-minded people', 'guitarists');

Apostrophes can also be “escaped” by doubling — that is, by replacing one apostrophe with two.

Rewrite the following command using two different ways of escaping the internal apostrophe:

SELECT * FROM my_contacts
WHERE
location = 'Grover's Mill, NJ';

So now you know how to write a SELECT command to retrieve any type of data — including data that contains apostrophes.

A SELECT command can include in the result only the columns you need.

To make the results convenient to work with, they need to be narrowed down a bit. In other words, the output of the table should contain fewer columns — only the columns of the table we are interested in.

Before typing the next SELECT query, work out what the result table will look like.

Just look at the neat, concise table our new query returns. That's a success!

By specifying which columns the query should return, we pick out the information we are interested in from the full results.

Just as the WHERE condition limits the number of records returned, the column selection clause limits the number of columns returned. In essence, you hand the job of picking out information over to SQL.

Selecting columns is useful and convenient, but it has other benefits too.

As the amount of data in a table grows, selecting columns speeds up getting results. The speedup also shows when SQL code is used from other programming languages, such as PHP.

Remember our easy_drinks table? The following SELECT command returns the “Kiss” cocktail:

SELECT drink_name FROM easy_drinks
WHERE
main = 'cherry juice';

Remember our easy_drinks table? The following SELECT command returns the “Kiss” cocktail:

SELECT drink_name FROM easy_drinks
WHERE
main = 'cherry juice';

The SELECT command is the one a tester uses most. Let's practice as much as we can — complete four SELECT commands so that they also return the “Kiss.” And, to lock it in, write three more SELECT commands that return the “Frog” cocktail.

Cool, we're sure you really enjoyed writing queries. Let's select something else with SQL. Using the my_contacts table, write a few queries for Greg. Include in the result only the columns needed to get the answer. Pay special attention to apostrophes.

Two search conditions — the “glazed” type and a rating of “10” — can be combined into one query with the AND keyword. The results of such a query satisfy both conditions.

The result of an AND query. Even if the query returns several records, we know that every one of these places has glazed doughnuts rated 10, so we can go to any of them. Or to all of them, one after another.

However, using two queries instead of one is inefficient; besides, you risk missing drinks that contain 1.75 or 3 ounces of soda. It is better to use the “greater than” comparison operator:

So far, our WHERE conditions have used only the = operator. You have just seen an example of the > operator, which compares one value with another. Below is the full summary of comparison operators.

This level's assignment is outrageously simple. May 1,000 droids take me apart if you don't finish it in one go, after all the assignments we've rewired in this level! You'll be working with a real MySQL-type DB. In the practical assignment you'll need to write 10 queries. When you're ready, send them to us through the form.