Ryan Turner - SQL: 2 books in 1 - The Ultimate Beginners & Intermediate Guide to Learn SQL Programming step by step

Never write when you can talk. Never talk when you can nod. And never put anything in an e-mail. Eliot Spitzer

On a hard disk, data can be stored in different file formats. It can be stored in the form of text files, word files, mp4 files, etc. However, a uniform interface that can provide access to different types of data under one umbrella in a robust and efficient manner is required. Here, the role of databases emerges.

The definition of a database is a collection of information stored in computer in a way that it can easily be accessed, managed and manipulated.

Databases store data in the form of a collection of tables where each table stores data about a particular entity. The information that we want to store about students will be represented in the columns of the table. Each row of the table will contain the record of a particular student. Each record will be distinguished by a particular column, which will contain a unique value for each row.

Suppose you want to store the ID, name, age, gender, and department of a student. The table in the database that will contain data for this student will look like this:

Student Table

Here, the letter S has been prefixed with the name of each column. This is just one of the conventions used to denote column names. You can give any name to the columns. (We will look at how to create tables and columns within it in the coming chapters.) It is much easier to access, manipulate, and manage data stored in this form. SQL queries can be executed on the data stored in the form of tables that have relationships with other tables.

A database doesnt contain a single table. Rather, it contains multiple related tables. Relationships maintain database integrity and prevent data redundancy. For instance, if the school decides to rename the Computer department from Computer to Comp & Soft, you will have to update the records of all students in the Computer department. You will have to update the 1st, 4th, and 5th records of the student table.

It is easy to update three records; however, in real life scenarios, there are thousands of students and it is an uphill task to update the records of all of them. In such scenarios, relationships between data tables become important. For instance, to solve the aforementioned redundancy problem, we can create another table named Department and store the records of all the departments in that table. The table will look like this:

Department Table

Now, in the student table, instead of storing the department name, the department ID will be stored. The student table will be updated like this:

Table Student

You can see that the department name column has been replaced by the department id column, represented by DID. The 1st, 4th, and 5th rows that were previously assigned the Computer department now contain the id of the department, which is 102. Now, if the name of the department is changed from Computer to Comp & Soft, this change has to be made only in one record of the department table and all the associated students will be automatically referred to the updated department name.

1. Advantages of Databases

The following are some of the major advantages of databases:

• Databases maintain data integrity. This means that data changes are carried out at a single place and all the entities accessing the data get the latest version of the data.
• Through complex queries, databases can be efficiently accessed, modified, and manipulated. SQL is designed for this purpose.
• Databases avoid data redundancy. Through tables and relationships, databases avoid data redundancy and data belonging to particular entities resides in a single place in a database.
• Databases offer better and more controlled security. For example, usernames and passwords can be stored in tables with excessive security levels.

1. Types of SQL Queries

On the basis of functionality, SQL queries can be broadly classified into a couple of major categories as follows:

• Data Definition Language (DDL)

Data Definition Language (DDL) queries are used to create and define schemas of databases. The following are some of the queries that fall in this category:

1. CREATE to create tables and other objects in a database
2. ALTER to alter database structures, mainly tables.
3. DROP - delete objects, mainly tables from the database

• Data Manipulation Language

Data Manipulation Language (DML) queries are used to manipulate data within databases. The following are some examples of DML queries.