Overview of SQL
SQL has been used on many different platforms because of its portability. SQL statement written on one platform works on another with little altertion. There are some simple to understand but useful fundamentals I bet some of you don't know. Like: Declarative Programming Language, Relational Database, Entity, Primary key, Record, Data Value, DBMS, RDBMS, ODBC etc.
SQL
SQL stands for Structured Query Language. This is a declarative computer language that allows the user to Create, Read, Update and Delete data from Database. Which we abbreviated CRUD.
Declarative and Imperative programming Language
Imperative: When programmer writes detailed programming statements to instruct the computer what specific steps to perform to solve a specific problem we call it imperative programming language. Those steps are combinedly called algorithm.
Declarative: In a declarative programming language like SQL, programmers write the statement to just retrieve the result. They do not need to write the algorithm to process the result prior to retrieve. Example below:
SELECT first_name FROM customers;Imagine we have a customer table where there is a first_name column. Then this SQL statement will show the "first_name" for all customers from customer's table. Here We are not writing algorithm to tell computer/server how to retrieve first_name for all customers. We are just seeking for result.
Database
Database is an organized collection of related data.
By Organized we mean: The data has to be stored in a well-designed and structured manner so it can be retrived and manipulate efficiently.
Related means: The collection of data should do/maintain one overall task/business. Like: Running a grocery shop, Staff management of a call center...
Entity
A database should keep track of different entities. In other words, a whole database system for any business keeps track of different type of things. A simple example will make it more clear:
Entity People (Employees, Suppliers, Customers)
Entity Items (Inventory, Vehicles, Buildings)
Entity Events (Sales, Purchases, Meetings)
Each entity type has its own set of properties to track. Means entity "people" needs to track the details of Customers (First Name, Last Name, Email address, Mobile phone number etc). Let's see a flat file where we have stored the properties of a video rental company:
In the above image you can see we have duplicate data for Moe and Larry. Since they rented different movie(s) on different day so we need to repeat their information on several occations. This is called redundancy. This is not an efficient way to store data. To avoid redundancy we have to introduce Relational Database.
Row/Record, Column/Field, Data Value
Before knowing about relational database let's touch some fundamentals of a database table.
Record/Row: In the below customer table each row represents a single instance of a customer. In programming terminology, this is known as a record. However, in SQL and most database software, the preferred term is simply row.
Column/Field: Each column represents a specific property (or attribute) we want to track for each record in the table (Customer number, name). This is known as field. However, in SQL and most database software, the preferred term is simply column.
Data Value: The individual piece of data contained at the intersection of a row and column is called a data value (Moe, C1002 etc)
Primary Key/Unique Identifier: Not all but in most tables there is one crucial column that holds a unique value for each row, known as primary key or unique identifier. We can avoid unambiguity by using primary key to identify each different record/row. Usually it is the primary key that is used to link related tables together. By default the data is ordered by primary key.
Relational Database
What if we divide the above flat table into 3 different tables and join them whenever it's needed. To do so we have to identify each of the customer and movie by a Unique Identifier so when we join them it should be clear who we join with whom. We need Unique Identifier to distinguish one customer form another, one movie from another. This approach is known as relational Database. The below image will make it more clear:
In the picture the CNo and MNo is unique identifier for Customers and Movies respectively. Now if we want to show the rental history of customer "Larry" we just need to link Larry's CNo (C1002) from customer's table with the Movie's MNo from Movie table (M9001, M9002 and M9005). So instead of repeating all information of customer Larry for renting 3 movies we are just repeating her CNo. On the other hand customer Moe (CNo M9001) rented same movie twice. So we just link Moe's CNo (M9001) with the same MNo (M9001) in two different row where the "DateRent" is different.
DBMS and RDBMS
Database Management System (DBMS) is a software package that manages the overall database. The relational database technique is used by majority which is known as Relational Database Management System (RDBMS). SQL is not itself an DBMS. SQL is the database programming language underlying most modern DBMS software.
Open Database Connectivity (ODBC)
ODBC is a standard API to access a database. It is database system and OS independent. ODBC requires ODBC driver to translate the query to make it understandable for different database. If an application can use ODBC then it's known as ODBC-compliant.