SQL Tutorials - 1             

1.1 Relational Database Management Systems
1.2 SQL
1.3 Data Definition Language(DDL)
1.4 Data Manipulation Language(DML)
1.5 Data Control Language(DCL)
2.1 Creating Database 
2.2 Creating Table in SQL
Topics covered here are - 
2.2.1 Schema
2.2.2 Data Types
2.2.3 Keys & Constraints 
2.2.4 Create a table
2.2.5 View table structure


1.1 Relational Database Management Systems

Now suppose other people in the company are interested in reading the reviews and adding users. Therefore, you decide to share the spreadsheet. Over time, the amount of information starts to increase. You begin to encounter issues with duplicate data, typos, perhaps even formatting issues if multiple people are working on one file. The simple spreadsheet now becomes unwieldy and finding/collecting information requires a lot of scrolling and searching. At this point, you would probably benefit from moving to a relational database management system.


A relational database is a database organized according to the relational model of data. In simple terms, the relational model defines a set of relations (which we can think of as analogous to tables) and describes the relationships, or connections, between them in order to determine how the data stored in them can interact.Using a relational database helps us to cut down on duplicated data and provides a much more useful data structure for us to interact with


A relational database management system, or RDBMS, is essentially a software application, or system, for managing relational databases. An RDBMS allows a user, or another application, to interact with a database by issuing commands using syntax that conforms to a certain set of conventions or standards.

There are many relational database management systems such as SQLite, MS SQL, PostgreSQL and MySQL. Some are lightweight, easy to install and use, while others are robust, scalable, but are complex to install. These various RDBMSes may vary in certain ways, and some of the commands they use may have slight syntactical differences. One thing they have in common however is the underlying language they all use: SQL.


For example - A program like MongoDB, , uses a document-oriented data storage model. This systems is non-relational data storage and retrieval models, are often loosely grouped together under the term 'NoSQL'. However, we are only interested in the relational model and how SQL can be used to interact with relational databases.

1.2 SQL

SQL, which stands for Structured Query Language, is the programming language used to communicate with a relational database.

SQL is a powerful language that uses simple English sentences that, with a few lines, allows you to work with large amount of data.

SQL is a little different to other programming languages you may have encountered. SQL is a declarative language; when you write an SQL statement you describe what needs to be done, but not exactly how to do it -- the exact details of how the query is executed are handled internally by the RDBMS you are using.


1.3 Data Definition Language(DDL):

The Data Definition Language is made up of SQL commands that can be used to design the database structure.
It is used to construct and modify the structure of database objects in the database. DDL refers to a set of SQL instructions for creating, modifying, and deleting database structures, but not data. 
Eg - create, drop ,alter, truncate

1.4 Data Manipulation Language:

The SQL commands that deal with manipulating data in a database are classified as DML (Data Manipulation Language), which covers the majority of SQL statements.
Eg - Insert , update, delete

1.5 Data Control Language(DCL):

DCL (Data Controlling Language) is a query language that allows users (give permission) to retrieve and edit data held in databases. The types of Data Controlling Language commands include Grant and Revoke.

2.1 Creating database

There are three SQL sub-languages: Data Definition Language, Data Manipulation Language, and Data Control Language. We will be using Data Definition Language, or DDL, to create our database, since this deals with setting up the structure, or schema, of a database.
As the name implies, Data Definition Language is focused on defining the characteristics of the database and its tables and columns. This includes creating databases and tables, or altering and changing finer details about your database, such as table names and column data types. But it does not deal with the data within a database.

Create DATABASE employee;

2.2 Creating Table

Now that we have created our employee database, we have the 'outer shell' of our building. Now we need to add some rooms to our building , or in SQL terms we need to add tables.

Before Creating a table let us understand about Schema, datatype and constraints. 

2.2.1 Schema

Schema is concerned with the structure of a database. This structure is defined by things such as name of table, columns in the table, data type of those columns & constraints.

Schema helps us interact with data associated with rows and columns of a database table. which means when you will use select statement referring to any particular column or field the sql database will know what value to return.

2.2.2 Data Types

A data type classifies particular values that are allowed for that column. This prevent storing invalid type of data being entered & stored in database.

The below lists some common data types.

1) Serial - This data type is used to create identifier columns for a PostgreSQL database. These identifiers are integers, auto-incrementing, and cannot contain a null value.

2) Char(N) - This data type specifies that information stored in a column can contain strings of up to N characters in length. If a string less than length N is stored, then the remaining string length is filled with space characters.

3) Varchar(N) - This data type specifies that information stored in a column can contain strings of up to N characters in length. If a string less than length N is stored, then the remaining string length isn't used.

4) Boolean - This is a data type that can only contain two values "true" or "false". In PostgreSQL, boolean values are often displayed in a shorthand format, t or f

5) Integer or INT -  An integer is simply a "whole number." An example might be 1 or 50, -50, or 792197 depending on what storage type is used.

6) Decimal(precision, scale) - The decimal type takes two arguments, one being the total number of digits in the entire number on both sides of the decimal point (the precision), the second is the number of the digits in the fractional part of the number to the right of the decimal point (the scale).

7) Timestamp - The timestamp type contains both a simple date and time in YYYY-MM-DD HH:MM:SS format.

8) Date - The date type contains a date but no time.

2.2.3 Keys & Constraints

While data types are a mandatory part of a column definition, constraints are optional.

One of the key functions of a database is to maintain the integrity and quality of the data that it is storing. Keys( will see when designing multiple tales) and Constraints are rules that define what data values are allowed in certain columns. They are an important database concept and are part of a database's schema definition. Defining Keys and Constraints is part of the database design process and ensures that the data within a database is reliable and maintains its integrity. Constraints can apply to a specific column, an entire table, more than one table, or an entire schema.

  • UNIQUE: Prevents any duplicate values from being entered into that column.

  • NOT NULL: Essentially means that when adding data to the table a value MUST be specified for this column; it cannot be left empty.

  • DEFAULT: If no value is set in this field when a record is created then a value pre-defined is set in that field.

We talk more about UNIQUE, NOT NULL and DEFAULT when we look at adding data into this table later.


2.2.4 Create a table

CREATE TABLE users (
id serial UNIQUE NOT NULL,
username char(25),
City varchar(50),
enabled boolean DEFAULT TRUE

);

Let's break this down a little to understand exactly what's going on here

  1. CREATE TABLE: Firstly, CREATE TABLE users is the primary command.
  2. users: The name of the table that will be created.
  3. (): The information in the parentheses is related to the columns in the table.
  4. id, username, enabled: These are the three columns of the table.
  5. serial, char(25), boolean: These are the data types of the columns. We will look at data types shortly.
  6. UNIQUE, NOT NULL: These are constraints. We'll talk about these later in this chapter.
  7. DEFAULT TRUE: Specifies a default value for the column. We'll revisit this later.
  8. Notice that each column definition is comma separated; this is the standard in any SQL database management system.


2.2.5 View table structure

DESCRIBE users;

Output -