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 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.
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;
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...
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:
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.
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:
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.
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.