Understanding SQL and Database Basics

SQL and Database Basics for novicesIn this lesson, you’ll learn about databases and SQL, prerequisites to learning about Oracle and PL/SQL.

The fact that you are reading this book indicates that you, somehow, need to interact with databases. And so before diving into Oracle and its implementation of the SQL language (PL/SQL), it is important that you understand some basic concepts about databases and database technologies.


Table of contents[Show]


Whether you are aware of it or not, you use databases all the time. Each time you select a name from your email or smartphone address book, you use a database. When you conduct a search on an Internet search site, you use a database. When you log in to your network at work, you validate your name and password against a database. Even when you use your ATM card at a cash machine, you use databases for PIN verification and balance checking.

But even though we all use databases all the time, much confusion remains about what exactly a database is. This is especially true because different people use the same database terms to mean different things. Therefore, a good place to start our study is with a list and explanation of the most important database terms.

Tip: Reviewing Basic Concepts

 

What Is a Database?

The term database is used in many different ways, but for our purposes a database is a collection of data stored in some organized fashion. The simplest way to think of it is to imagine a database as a filing cabinet. The filing cabinet is simply a physical location to store data, regardless of what that data is or how it is organized.

Database

Caution: Misuse Causes Confusion

 

Tables

When you store information in your filing cabinet, you don’t just toss it in a drawer. Rather, you create files within the filing cabinet, and then you file related data in specific files.

In the database world, that file is called a table. A table is a structured file that can store data of a specific type. A table might contain a list of customers, a product catalog, or any other list of information.

 

Table

The key here is that the data stored in the table is one type of data or one list. You would never store a list of customers and a list of orders in the same database table. Doing so would make subsequent retrieval and access difficult. Rather, you would create two tables, one for each list.

Every table in a database has a name that identifies it. That name is always unique—meaning no other table in that database can have the same name.

 

Note: Table Names

Tables have characteristics and properties that define how data is stored in them. These include information about what data may be stored, how it is broken up, how individual pieces of information are named, and much more. This set of information that describes a table is known as a schema, and schema describe specific tables within a database, as well as entire databases (and the relationship between tables in them, if any).

 

Schema

 

Note: Schema or Database?

 

Columns and Datatypes

Tables are made up of columns. A column contains a particular piece of information in a table.

 

Column

The best way to understand a column is to envision database tables as grids, somewhat like spreadsheets. Each column in the grid contains a particular piece of information. In a customer table, for example, one column contains the customer number, another contains the customer name, and the address, city, state, and Zip Code are all stored in their own columns.

 

Tip: Breaking Up Data

Each column in a database has an associated datatype. A datatype defines what type of data the column can contain. For example, if the column is to contain a number (perhaps the number of items in an order), the datatype would be a numeric datatype. If the column were to contain dates, text, notes, currency amounts, and so on, the appropriate datatype would be used to specify this.

 

Datatype

Datatypes restrict the type of data that a column can store (for example, preventing the entry of alphabetical characters into a numeric field). Datatypes also help sort data correctly, and play an important role in optimizing disk usage. As such, you must give special attention to picking the right datatype when creating tables.

Rows

Data in a table is stored in rows; each record saved is stored in its own row. Again, envisioning a table as a spreadsheet style grid, the vertical columns in the grid are the table columns, and the horizontal rows are the table rows.

For example, a customer’s table might store one customer per row. The number of rows in the table is the number of records in it.

 

Row

 

Note: Records or Rows?

Primary Keys

Every row in a table should have some column (or set of columns) that uniquely identifies it. A table containing customers might use a customer number column for this purpose, whereas a table containing orders might use the order ID. An employee list table might use an employee ID or the employee Social Security Number column.

 

Primary Key

This column (or set of columns) that uniquely identifies each row in a table is called a primary key. You use the primary key to refer to a specific row. Without a primary key, updating or deleting specific rows in a table becomes extremely difficult because no guaranteed safe way exists to refer to just the rows to be affected.

 

Tip: Always Define Primary Keys

You can establish any column in a table as the primary key, as long as it meets the following conditions:

  • No two rows can have the same primary key value.
  • Every row must have a primary key value (primary key columns may not allow NULL values).

 

Tip: Primary Key Rules

You usually define primary keys on a single column in a table. But this is not required, and you may use multiple columns together as a primary key. When you use multiple columns, the rules previously listed must apply to all columns that make up the primary key, and the values of all columns together must be unique (individual columns need not have unique values).

Tip: Primary Key Best Practices

 

 

What Is SQL?

SQL (pronounced as the letters S-Q-L or as sequel) is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases.

Unlike other languages (spoken languages such as English, or programming languages such as C, Java, or Python), SQL is made up of very few words. This is deliberate. SQL is designed to do one thing and do it well—provide you with a simple and efficient way to read and write data from a database.

 

What are the advantages of SQL?

  • SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language enables you to interact with just about every database you’ll run into.
  • SQL is easy to learn. The statements are all made up of descriptive English words, and there aren’t that many of them.
  • Despite its apparent simplicity, SQL is a very powerful language, and by cleverly using its language elements, you can perform complex and sophisticated database operations.

Note: DBMS-Specific SQL

 

 

Try It Yourself

All the lessons in this book use working examples, showing you the SQL syntax, what it does, and explaining why it does it. I strongly suggest that you try each and every example for yourself to learn Oracle firsthand.

Note: You Need Oracle

Obviously, you’ll need access to an Oracle DBMS to follow along.

 

Summary

In this first lesson, you learned what SQL is and why it is useful. Because SQL is used to interact with databases, you also reviewed some basic database terminology.

 

Вас заинтересует / Intresting for you:

SQL - Structured Query Languag...
SQL - Structured Query Languag... 1000 views Aida Wed, 25 Apr 2018, 11:12:33
NoSQL case studies
NoSQL case studies 1109 views Aida Mon, 18 Jun 2018, 10:58:54
What is NoSQL?
What is NoSQL? 541 views Aaltonen Fri, 15 Jun 2018, 12:41:03
Types of NoSQL Databases
Types of NoSQL Databases 591 views Валерий Павлюков Wed, 13 Jun 2018, 19:12:01