Retrieve Data with Simple SELECT Statements

 

Some things that you may want to retrieve from a database can be very easy to obtain with an SQL statement. Other questions might be more involved, and correspondingly require a rather complex SQL statement. Much like English-language sentences, SQL statements can contain multiple clauses that serve to precisely filter out all the data you don’t want, leaving only the information you do want, in the form that you want to see it.

Let’s look at a very simple retrieval operation to answer a simple question, and proceed from there to add clauses to SQL SELECT statements to home in on more tightly targeted questions.

For anyone working for a business or even a non-profit entity, it is probably important that you know as much about your existing customers as possible, so that you can find new customers with similar needs. You can delve into your database’s tables to find out more about your customers and what they have bought in the past. Let’s look at how we could do this with SQL..

After launching your DBMS, connect to a database that contains the information of interest. Take a look at the list of tables included in the database. Suppose one of them is named ‘customer.’

Perhaps the first thing you want to know is the number of customers you have in your customers table. You can answer this question with a simple SELECT statement. Go to the blank window that your DBMS provides, into which you can type SQL statements.

In the window, type:

SELECT * FROM customers ;

The asterisk is a wildcard character that means “all columns.” This will cause the data in all the columns of the customers table to be returned. The semi-colon denotes the end of the statement.

To execute the statement, There should be an icon or button that you can click. Once you click it, your statement will be executed and the result of the operation will be displayed. Along with a list of all the fields in all the rows of your database, there will probably also be a message telling you how many rows have been returned.

Since all you wanted was the number of customers, not a display of all the data for all those customers, there is another way to answer your question, with the statement:

SELECT COUNT (*) FROM customers ;

That returns the number of rows in the customers table.

It’s nice to know how many customers you have, but more useful from a marketing perspective might be to know how many customers you have in a particular region that you could target with advertising. Suppose you want to know how many customers you have in California, for example. A small addition to your original SELECT statement will do the trick. Type:

SELECT * FROM customers

WHERE state = ‘CA’ ;

The WHERE clause returns only those rows where the value in the state column is CA. Text strings such as CA must be enclosed in quote marks for the database engine to understand that it is looking at a text string.

Earlier, we used COUNT to count the number of customers in the customers table COUNT is an example of a Set function. Other set functions are: MAX, MIN, SUM, and AVG. As you would expect, MAX will return the maximum value that exists in the specified column, MIN will return the minimum, SUM will add up all the values, and AVG will return the average value.

Suppose we want to know the total of all the sales recorded in the invoices table of the database. I bet you could figure out what the SQL for that would be. It would be:

SELECT SUM(Total) FROM invoices ;

Execute that statement and you receive the total amount of sales that have been made.

More likely, you are interested in the total sales during an interval of time, for example a month. We can obtain this information by adding a WHERE clause to our statement.

SELECT SUM(Total) FROM invoices

WHERE InvoiceDate > ‘2017-01-31’ AND InvoiceDate < ‘2017-03-01’ ;

This gives us the total sales for the month of February 2017. The AND keyword is a logical connective that enables us to express a compound condition. It only returns rows where both the predicate before the AND keyword and the predicate after the AND keyword are true. A predicate is a statement that may either be logically True or logically False. A date is either greater than February first 2017 or it is not.

Other logical connectives are OR and NOT, although NOT does not connect two predicates. A predicate preceded by a NOT keyword evaluates to True if the predicate itself evaluates to False. A clause with an OR connective is considered to be true if either of the two predicates in contains, evaluates to a true value.

 

 

5 Reasons Why You Need SQL


Some people may decide not to learn SQL because of something they may have heard, or just assumed, that is completely untrue. Myths such as these can stand in the way of people moving ahead in their careers. There are number of these myths that hold people back, but I would like to discuss just four of them.

1. SQL is a programming language and I am not a programmer. Although SQL is a language, it is not a programming language in the way you are probably thinking. Most computer languages are procedural languages. To use them, a programmer creates a procedure in which a series of instructions are written in a step-by-step manner to cause a computer to perform some action. The programmer must understand what is going on at a deep level in order to generate the correct sequence of instructions.

SQL is not like that. It is not a procedural language. It is called a non-procedural language because there is no need to write a procedure. With SQL all you need to do is write a statement that tells the computer what action you want it to perform. The DBMS figures out the details of how to do that, then goes ahead and does it.

2. SQL is only for people who have a programming job. Although it is true that information technology professionals have the most to do with databases, these days practically everyone in an organization has some exposure to them and may need information contained in them in order to do their jobs. You may not work with a database every day, but occasions will arise when you will need a fact contained in a database and there is no IT professional available to obtain it for you. Anyway, you should be able to perform basic database retrieval operations for yourself. It makes you a more valuable employee.

3. You need to be some kind of brainiac to understand SQL. There is a mystique surrounding computers in general and SQL in particular that they are beyond the comprehension of ordinary people. This is particularly untrue about SQL, which consists of simple statements that are very similar to ordinary English-language sentences. If you can compose and write down a sentence, you could just as easily write an SQL statement that would perform a query.

4. Knowing SQL won’t be of any value to me. This is the biggest myth of all. Our world today is totally dependent upon computers and the data stored within them. A lot of jobs will become obsolete and disappear within the next ten years, but jobs associated with information technology will not be among them. Learning SQL could be one of the most effective things that you could do to guarantee your future employability.

What SQL Is and What It Isn’t

SQL is not a procedural language.

Procedural languages such as C++ or Python operate on data items one item at a time. People who program in languages such as C++ and Python write procedures that perform a sequence of operations one after another until an entire task is completed.

SQL is considered a non-procedural language because when you use it, you do not code instructions to perform a sequence of operations. In other words, you do not specify a sequence of operations to perform a task. Instead, you merely tell the DBMS you are using what you want done, and it would proceed to do it without any further instructions from you. If what you want is to retrieve specific information from a set of database records, just specify exactly what you want, and the DBMS will decide how best to satisfy your request, then return the result to you.

Application programmers embed SQL statements in their programs to interact with databases, and use procedural code to construct user interfaces, screens, reports, and program logic. If all you want to do is retrieve some data from a database to answer a question, you don’t need to go to all the trouble of writing a program. You can just feed an SQL statement directly to the DBMS and read out the result when it is returned to you.

For people who are not programmers, and who do not intend to become one, a working knowledge of how to craft SQL queries can be very useful. Even if an application program with embedded SQL exists for a database, questions are bound to arise that were not anticipated when that application program was written. It can be quite valuable to be able to answer such questions quickly and easily with a simple SQL query.

If you think that a basic knowledge of SQL might be helpful to you, click here to find out about a short course that will bring you up to speed.

Lobster Newburg

When you were a kid, did you ever hear anything like this: “Eat everything on your plate! Millions of people are starving in China while you sit there wasting food!” That is something I heard often as a child. My mother grew up during the Depression, and had been taught not to waste anything. She came from good working-class Irish stock, and she learned how to cook from her no-nonsense, meat and potatoes, Irish mother.

I don’t know what experience you might have with Irish cooking, but I’ll tell you what mine was. My grandmother was a master at preparing dinners consisting of roast beef, string beans, and mashed potatoes and gravy.

This was a good thing, because my grandfather LOVED roast beef, string beans, and mashed potatoes and gravy. I figure this must be the official national dinner of Ireland. It seems like every time I visited my grandparents they had roast beef, string beans, and mashed potatoes and gravy for dinner.

So when my mother left home and married my Dad, she knew how to cook one meal: roast beef, string beans, and mashed potatoes and gravy. I have to give my Dad a lot of credit for surviving those early years before she learned how to make meatloaf to serve along with the string beans and mashed potatoes and gravy. At last he had some variety.

After spending my entire youth

eating Gerber’s baby food spinach and Gerber’s baby food squash out of those little jars,

I was really happy the first day I was finally allowed to eat big people’s food.

I thought the string beans and mashed potatoes and gravy were great. The roast beef, however, I chewed and chewed and chewed and chewed into a big wad and then I spit it out.

Mom finally got the idea that a little variety might be good. This was after about five years of nothing but roast beef, string beans, and mashed potatoes and gravy, with an occasional meatloaf thrown in. She talked to one of our Italian neighbors and picked up a new recipe.

She learned how to heat up canned Chef BOYARDEE ravioli. I really liked the ravioli, and it went pretty well with the string beans, and mashed potatoes and gravy. Then she learned how to make spaghetti and meatballs.

They were terrific. Even better, once you filled your plate with spaghetti and meatballs, there was no room left for the string beans and mashed potatoes and gravy.

Flushed with success, she decided to try out even more new meals on her growing family. By this time I had brothers. We all appreciated the spaghetti and meatballs,

the ravioli,

and even the roast beef, string beans and mashed potatoes and gravy,

since we now had it only six nights a week instead of seven like before.

The first new dinner she tried after her spaghetti success was liver and onions with a side of canned lima beans, and of course mashed potatoes and gravy.

This did not go over quite as well with the troops as spaghetti did. However, thanks to Mom and Dad’s Depression-era upbringing, we boys had our plates filled by a parent. Then we were commanded to eat everything on our plates.

It was about this time that my brother Tyson figured out how to upchuck on demand. He would eat a few spoonfuls of mashed potatoes and gravy, look at that liver and onions in front of him, think about how disgusting it was, then put a bite of it in his mouth. Yup. It really was disgusting. Before you knew it, a queasy look would come over his face and BLORRP! He would throw up onto his plate. This of course ruined the rest of his dinner and he would be sent from the table.

The “punishment” of being sent from the table was actually a victory for him. He didn’t have to eat the liver and onions. No such luck for me or for my other brothers. We had to stay at the table until we had eaten every last bite of that liver.

Somehow my mother got the idea that maybe liver and onions might not be the best alternative to roast beef, string beans, and mashed potatoes and gravy. She decided to try something else. Maybe seafood would be more popular. That was when she surprised us all with—Lobster Newburg.

I was surprised all right. The first surprise was the nauseating smell that started wafting from the kitchen about a half hour before dinnertime. The next surprise was the way it looked when I came to the table. I had never been a big fan of casseroles anyway, but this one reached a new low.

A curdled cream sauce, shot through with red speckles, covered lumps of slimy canned white lobster meat. The combination of smells coming from the lobster and the sauce was overpowering. I pinched my nose shut and started breathing through my mouth. Lobster Newburg was the most horrible thing I had ever seen or smelled in my life, let alone put into my mouth.

Not only did I have to see it and smell it—I had to eat it–eat every last bite on my plate. I took a bite and felt the slimy meat quiver in my mouth. I started feeling woooozy. The smell of it and the feel of it in my mouth made my skin crawl. I started to feel faint. At that moment, I knew what Hell must be like. Without even trying, I suddenly understood how Tyson could upchuck on demand. This time I beat him to it.

Mom never served us Lobster Newburg again. And I never complained again about dinners of roast beef, string beans, and mashed potatoes and gravy.

Creating a Database that Meets User Needs 3: The Requirements Phase

Computer Storage

After the scope of a database development project has been established during the Definition Phase, you need to find out what the stakeholders in the project need it to do for them. That is the job of the Requirements Phase. In the Definition Phase, you talk with the client. This is the person who has the authority to hire you or, if you are already an employee, assign you to this development task. This person is not, however, the only one with an interest in the project. In all probability, someone other than the client will use the system on a daily basis. Even more people may depend on the results generated by the system. It is important to find out what those people need and what they prefer because your primary client may not have a complete understanding of what would serve them best.

The amount of work you must do in the Requirements Phase depends on the client. If can be quick and easy if your are dealing with a client who has prior experience with similar database development projects. Such a client has a clear idea of what he wants and, equally important, what is feasible within the time and budget constraints that apply.

On the other hand, this phase can be difficult and drawn-out if the client has no experience with this kind of development, only a vague idea of what he wants, and an even vaguer idea of what can reasonably be done within the allotted time and budget.

Aside from your primary client, other stakeholders in the project, such as various users, managers, executives, and board members, also have ideas of what they need. These ideas often conflict with each other. Your job at that point is to come up with a set of requirements that everyone can agree upon. This will probably not meet everyone’s desires completely. It will be a compromise between conflicting desires, but will be the solution that gives the most important functions to the people who need them.

The Statement of Requirements

The Statement of Requirements is an explicit statement of the database application’s deliverables, including its display, update, and control mechanisms. It will answer such questions as:

  • What will the display look like? How will components be arranged? What will be the color scheme?
  • What items will need to be updated, and how will that be done?
  • How will users navigate between screens?
  • Will selections be made by key depressions, and if so, which  keys will do what?
  • Will operations be initiated by mouse clicks? If so, which operations?
  • What will the maximum acceptable response time to a query be?

The Statement of Requirements must be as detailed as possible because it is essentially a contract between you and your client. You are agreeing on exactly what will be delivered and when it will be delivered. To seal the arrangement, bot you and your client should sign the Statement of Requirements, signifying agreement on what you will be responsible for delivering.

Sumarizing, in the Requirements Phase you must:

  • Interview typical members of all classes of stakeholders in the project.
  • Provide leadership in getting stakeholders to agree on what is needed.
  • Create the Statement of Requirements, which describes in detail what the system will look like and what it will do.
  • Obtain client approval of the Statement of Requirements, indicated by a signature and date.

Creating a Database that Meets User Needs 2: The Definition Phase

Computer Storage

In the first installment of this series of posts I said that for a development effort to succeed in meeting its objectives, a series of steps should be taken, each one building upon the previous ones, without leaving any out. This series of steps or phases is called the System Development Life Cycle (SDLC). The phases are:

  • The Definition Phase
  • The Requirements Phase
  • The Evaluation Phase
  • The Design Phase
  • The Implementation Phase
  • The Final Documentation and Testing Phase
  • The Maintenance Phase

Rookie developers often, after receiving a development assignment, start building the database and its associated application. Unfortunately, that is what should be done in the Implementation Phase. They have just skipped the first four phases of the SDLC and consequently, their chance of success is minimal. It is really important to go through all the phases and to perform all the tasks within each phase.

Every phase is important, but the Definition Phase is crucial, because if you don’t define the task properly at the beginning, there is no chance that the final product will meet your client’s needs.

There are five major tasks that must be performed as part of the Definition Phase. They are:

  • Define the task to be performed. A database project begins when a person or group realizes that they have a problem and that a database is probably part of the solution of that problem. Depending on how experienced they are with such things, they may have a very clear idea of what the database should be and how it should perform, or they may have only a vague notion of what is needed. If you are called in to be the developer of this project, it is up to you to develop a detailed definition of exactly what the product of the development effort will do.
  • Determine the scope of the project. In addition to defining the task to be performed, at this point you also want to estimate how big the project is. What equipment will be needed? How much system analyst time will it take? How much programmer time? What is the required completion date?
  • Perform a feasibility analysis. The client has an idea of what she wants, when she wants it, and how much she is willing to pay for it. It is up to you to determine whether the project can be completed at all, given those constraints. If you determine that you cannot meet the requirements, you can negotiate for more time, more budget, or fewer features, or you can graciously decline to take the job.
  • Form a project team. You might be able to complete a simple project by yourself, but for a big project on a tight deadline, you should build a team, where each member works on the part of the overall task that they are best qualified to address. Network with fellow professionals so that when jobs arise, you have a pool of qualified people you can draw from for a development team.
  • Document everything. One important part of a development project that is often not given the attention it deserves is the documentation of what is learned and what is done.  Seemingly, documentation is a side issue, perhaps to be taken up after the “real” work of producing the database is done. Nothing could be further from the truth. Documenting every step along the way is vital. It is the only way to keep things on track, starting with the initial specification of what the project is supposed to do and ending with the post mortem after it is finally retired for good. On countless occasions, you will find that detailed documentation will keep you from going down the wrong path, and often it will save you from the database becoming unmaintainable when a key team member leaves.
  • Get the client to approve the Definition Phase document, in writing. It is important that you and your client be of one mind about exactly what the task is that you have been engaged to perform. Documenting exactly what you will provide (the deliverables) protects you both. It’s not uncommon for a client to tell you what you want, and then after you deliver it, say “Oh, but I thought we had agreed that you would also provide the “X” capability.” Fill in whatever you want for X. This all too common experience is called scope creep. The client wants you to do more, but does not want to pay you more for it. Your best defense is to get the client to sign the Definition Phase document at the conclusion of the Document Phase, before you start work in earnest on the project. If any additional work is desired beyond what is specified in the document, you are in a strong position to ask for additional payment.

Creating a Database that Meets User Needs

Computer Storage

 

A database is a repository for some person’s or organization’s data. For it to meet user needs, it must satisfy several criteria:

  • It must be easy to enter new data into it.
  • It must be easy and fast to retrieve just the information you want from it.
  • It must store the data in such a way that database operations do not corrupt it.

A database system consists of the hardware the database resides on, the operating system that controls that hardware, the database management system (DBMS) that performs operations on the database, the database application that translates the user’s commands into instructions the DBMS understands, and the database itself.

The database application is the part of the system that interfaces directly with the user, so it is the part that must make it easy to enter new data, and easy and fast to retrieve the desired information. This is the part of the system that database developers create. The DBMS comes from a vendor such as Microsoft or Oracle or from a consortium of professionals as is the case for open source DBMSs such as PostgreSQL or MySQL. The operating system (OS) comes from an OS vendor, such as Microsoft, or from an open source community, as is the case with Linux. Finally, the hardware comes from a hardware vendor such as IBM, Dell, or HP.

The hardware, operating systems, and DBMSs are all general purpose and have proven to be reliable in the marketplace. For these things, you can just buy the components that have enough capacity to meet your needs. The custom part is the database application. This is the software piece that solves the particular problem that you have. Since your needs are not exactly like anyone else’s you are either going to have to produce this part yourself, or hire someone to do it. Even if you decide to hire someone, it is good to understand what’s important so that you can make sure that what is finally produced actually does meet your needs.

There is a time-tested method for making sure that an application that results from a development effort actually meets the needs of those who will be using it. That method, which guides developers through the entire life of the development project is called the System Development Life Cycle (SDLC). The SDLC breaks down the development effort into a sequence of phases that, when carefully followed give a high probability of a successful project. The SDLC is not restricted to database development. It applies to development projects of any kind.

The phases are:

  • Definition
  • Requirements
  • Evaluation
  • Design
  • Implementation
  • Final Documentation and Testing
  • Maintenance

Each phase is important and skipping any one of them can cause the entire project to fail. I will describe each of these phases in detail in subsequent posts, including why it is important.