SQL - The Shortest Route For Beginners (2015)

Chapter 1. Essential Database Concepts

Introduction

Like many people, you might be a self-learner who likes some practical stuff to explore a technology. Although it’s a little painful route, you can get through it and achieve your milestone if you possess strong determination and are fortunate enough to get some real good material on the subject. In this publication I’ve tried to save your precious time by providing such hands-on stuff on SQL. I won't take you in the past to tell something that is of no interest to you today (at least as a beginner). But, felling the necessity, I'll provide a little background of SQL and will cover some preliminary concepts that you must know. This chapter will really pay off if you grasp the basic concepts about database and SQL.

First of all, let's come to the word SQL. Many people say that this acronym stands for "Structured Query Language", and some are of opinion that it should be read individually as "ess-que-el" or as "sequel". Leaving this debate aside, let's see why and when this useful language was initiated. In June 1970, Dr. E. F. Codd published the principles of the relational model, wherein he proposed the relational model for database systems. Along with definition of the relational model, he also suggested a language called DSL/Alpha for data manipulation in database tables. After the release of Codd's paper, IBM formed a team to build a prototype of DSL/Alpha based on Codd's proposal. The team got success in this endeavor and developed the first version named SQUARE, which finally got the name SQL.

While SQL has become a very stable language today, you must know that all SQL implementations are not created equal. Almost all database vendors support the latest SQL standard (published by American National Standards Institute – ANSI), but most vendors have added extensions to make their implementations more powerful or easier to use. These extensions are fairly similar across different platforms, and once you have become proficient with the SQL language on one DBMS platform, you can switch to other platforms without any difficulty. For your convenience, I've explicitly covered a free Oracle platform in this book besides the following database management systems. I have provided specific instructions for these platforms, where needed.

§ Oracle

§ MySQL

§ Microsoft SQL Server

§ Microsoft Access

§ IBM DB2

§ MariaDB

§ PostgreSQL

§ SQLite

This book is meant to help you learn SQL quickly by yourself, therefore, you'll be provided instructions to set up free Oracle environment on your own PC to execute hands-on exercises for learning purposes.

Who Is this Book For?

· It is for you if you are new to SQL.

· It is for you if you are looking for a fast track to explore SQL.

· It is for you if you want to learn SQL without someone's help and in your own environment.

Before we roll up our sleeves and get to work, it is necessary to have some basic knowledge of database and related concepts first.

Database and Database Management System

We interact with many databases in our daily lives to get some information. For example, a phone book is a database of names and phone numbers, and an email list is a database of customer names and email addresses. A database can simply be defined as a collection of individual named objects (such as tables) to organize data. File cabinets used in an organization that carry folders and name tags are examples of paper database. From technology viewpoint, this kind of organized information handling is performed by special computer software, called database management system (DBMS). And just as file cabinets come in many different colors and sizes, each DBMS available today has its own characteristics. A good understanding of these characteristics will help you make better use of your DBMS.

Database, Schemas, and tables

è A database is a container that holds various schemas (sales, hr, finance, etc). A schema (e.g. Sales) is a collection of individual named objects, such as tables, indexes, views, triggers and so on.

è Related data is organized and stored in tables such as Products, Orders, Customers etc. A table is similar to a spreadsheet, containing rows and columns.

è Data is stored under relevant columns in a table. For example, all order numbers are stored in the Orders table under the Order_ID column.

Database Tables and Relationship

A relational database organizes data in tables under individual schemas. Each table comprises columns and rows. Columns report different categories (headings) of data, and rows contain the actual vales for each column. Relationship among database tables is formed with the help of Primary, Composite, and Foreign keys. The following figure illustrates an example of a related database containing two tables.

A related database with two tables

Primary Key

A primary key is a column or a set of columns in a database table that uniquely identifies each record in that table. In order to keep data integrity, every table must have a primary key. A primary key cannot be NULL and must not allow duplicates. In the above figure, the PRODUCT_ID column in the Products table is a primary key because it holds a unique value for each product. Besides unique identification of records, values in the primary key are used to create relationship with other database tables.

Foreign Key

You create relationship among database tables using matching columns. The above figure displays how PRODUCT_ID 1 and PRODUCT_ID 2 in the Products table relate to ORDER_ID 1000 in the Orders table. A primary key column in another table which creates a relationship between the two tables is called a foreign key. PRODUCT_ID is a foreign key in the Orders table. The foreign key value must exist in the table where it is a primary key. For instance, if you try to add a new order for PRODUCT_ID 4, the insert process will fail because there is no primary record for PRODUCT_ID 4 in the Products table.

Composite Key

It is a set of columns in a table combined together to form a unique primary key. As you can see in the above figure, the first two records in the Orders table carry 1000 for both records, so the ORDER_ID value is not unique for these records. However, combining ORDER_ID and PRODUCT_ID columns will create a unique primary key for the Orders table, which is called a composite key.

Data Type

Each column in a table has an associated data type which specifies what type of data the column can contain. For example, if the column were to contain a number (quantity of items in an order), the data type would be a numeric data type. If the column were to contain dates or text the appropriate data type would be used to store data accordingly. You select relevant data types to also restrict the type of data that can be stored in a column (for instance, to prevent recording of alphabetical or special characters into a numeric column). See Chapter 7 – Creating Tables, for more information on data types.

Data Integrity

By implementing the following four integrity constraint types, you ensure that your database is in a correct and consistent state.

· Entity: This constraint type defines a primary key which should not be NULL and must contain a unique value.

· Referential: It relates to foreign keys, which must match a primary key in another table, or be NULL.

· Column: Values in the column must adhere to the defined data type. For example, a numeric column must not contain any alphabet.

· User-defined: This includes compliance of data with the defined business rules. For example, customers’ credit limit should be less than or equal to 5000. It is implemented using the CHECK constraint.

See “What are Constraints?” on Page 105 for further details.

What is SQL?

SQL is a command language that you use to interact with databases. It provides you with a simple and efficient way to read and write data from and to a database. It is used in two different ways: embedded or interactively. In the former case, you embed SQL commands in a program created in a different programming platform (such as Java). In the later scenario, you enter SQL commands using your keyboard on a SQL command prompt or in a GUI software (such as SQL Developer), to get your desired information on your screen. This book is intended to deal with the later scenario where you'll be taught how to communicate with databases interactively.

Normally, the SQL language is divided into the following four command categories:

· Data Definition Language (DDL)

· Data Manipulation Language (DML)

· Data Control Language (DCL)

· Data Query Language (DQL)

Data Definition Language (DDL)

The SQL data definition commands allow you to create, modify, and delete objects of a database. Typical database objects include tables, views, procedures, users, triggers and so on. Almost all SQL data definition commands start with one of the following three keywords:

CREATE: To add new database objects such as tables, users etc.

ALTER: To modify the structure of an existing database object

DROP: To delete a database object

Data Manipulation Language (DML)

This category of SQL commands allows you to change the contents of your database. For this purpose, SQL offers three basic data manipulation commands:

INSERT: To add new rows (records) into a table

UPDATE: To modify column values of existing rows

DELETE: To delete rows from a table

Data Control Language (DCL)

Data control commands are used to control access to different database objects (tables, views and so on). Data control commands include GRANT and REVOKE.

Data Query Language (DQL)

This category has just one command, but is the most significant one: SELECT. It is the sole command in SQL which is used to retrieve (query) data from a database.

Well, that's it for now. You have been briefed about some of the most important concepts in the world of database. You will revisit these concepts in upcoming chapters, because the information provided in these minute sections need a permanent place to sit in your minds.

Test Your Skill

1. What is a database?

a. It stores data.

b. It carries tables and other objects.

c. It is a container that contains schemas, tables, and other objects to store data.

2. What is a schema?

a. It is a database object.

b. It is a logical area in a database created for each user to store tables and other database objects.

c. It is a box in a database which holds objects.

3. What is a table?

a. It contains data.

b. It is a database spreadsheet.

c. It is a database object which stores data in rows and columns for a particular category.

4. What is a table column?

a. It represents data category in a table.

b. It identifies a row.

c. It stores data.

5. What is a row?

a. It contains actual values for each column.

b. It represents columns in a table.

c. It is a table component.

6. Relationships among database tables are established using:

a. Primary Keys

b. Foreign Keys

c. Both

7. The basic purpose of creating a Primary Key is to:

a. Make a table unique.

b. Make a column unique.

c. Make a row unique.

8. A Foreign Key is created in a table to:

a. Create relationship with table rows.

b. Create relationship with parent table(s).

c. Create relationship among databases.

9. Data Types are defined to:

a. Specify what type of data a column can contain.

b. Form table structure.

c. Create a new table.

10. New objects in a database are created by using:

a. DML

b. DDL

c. DCL

11. The SELECT SQL command is used to:

a. Insert new data.

b. Update existing data.

c. Query data.

12. Identify command category (DML, DDL etc.):

a. Revoke d. Alter g. Insert

b. Delete e. Update h. Create

c. Drop f. Grant

13. Identify Primary Key and Foreign Key columns for the following tables: