SQL Basic and fundamental concepts

SQL is a language for managing data in a database. SQL stands for Structured Query Language. SQL is used to create, modify, retrieve and delete data from a database. SQL works with relational databases. A relational database is a collection of data organized into tables. Each table has rows and columns of data. The rows are called records or tuples, and the columns are called attributes or fields. The tables in a relational database are related to each other by common attributes. The structure of the tables in a relational database is called the schema or model of the database. The schema defines how information is stored in the database, including what tables exist, how they relate to each other, what their attributes are and what value types they contain (e.g., numeric, character string).

SQL statements can be written interactively through an interactive tool called SQL*Plus that comes with Oracle Database software; or they can be embedded in an application program written in any high-level language, such as C, C++, Java, or PL/SQL. In this tutorial, we will be using SQL*Plus to write SQL statements.

Database: A database is a collection of related data stored in a computer system. Database management systems (DBMSs) are software packages that organize and control the storage and retrieval of information in a database. The DBMS provides the mechanism for accessing data in the database and for protecting the integrity of the data by enforcing access control and security restrictions.

Database management systems usually have a user interface for creating, modifying, and deleting records (called transactions). They also have facilities for defining access rules that control who may use the system to perform what types of operations on which data. Most DBMSs also provide facilities that allow users to create their own applications using programming languages provided by the DBMS vendor or through external interfaces such as JDBC or ODBC.

Schema: A schema is an organized structure of objects in a database. The schema contains all the objects that are considered part of the database, such as tables, views, indexes and other types of objects.

Table: A table is a two-dimensional area where data is stored in rows and columns. Each column represents a category of information (such as name, address or date), and each row represents an item (such as a person, place or event). The intersection of a column and row is called an "attribute" or "field". A table may also be referred to as an entity.

A table can contain data of varying types: numeric, character strings (text), binary large objects (BLOBs), or other complex data types. In addition to columns and rows, tables contain one or more indexes to speed up the retrieval of information from the table by using key values on one or more columns. An index is like an index in a book; it provides locations for specific items in the table based on values found.

What are different databases in RDBMS?

There are many databases in RDBMS, such as MySQL, Oracle, DB2, SQL Server, Sybase, Informix etc. In this tutorial, we will use MySQL database.

What is the most popular RDBMS database?

The most popular RDBMS database is Microsoft SQL Server.

What is the difference between an RDBMS and a NoSQL database?

The main difference between an RDBMS and a NoSQL database is in how they store data. An RDBMS stores it in tables, rows, and columns. A NoSQL database stores it in documents. This means that you can retrieve all of the data for a specific document without having to query multiple tables or rows/columns in a table. The data storage structure of an RDBMS also means that joins are required to retrieve information from multiple tables, whereas this is not required with a NoSQL database, which can retrieve information from multiple related documents at once using what are called "selectors." Another difference between the two types of databases is that an RDBMS stores its metadata about the data itself within its data (called "in-line metadata"), whereas a NoSQL database store this information separately from its data (called "out-of-line metadata").

What is the difference between a columnar database and an RDBMS?

A columnar database is similar to an RDBMS in that it is also a structured data storage system. However, a columnar database stores data in columns instead of tables, rows, and columns. This means that columns of data can be processed faster than an entire table of data. Also, unlike an RDBMS, a columnar database does not need to retrieve multiple pieces of information from different tables in order to process its data (called "joins"). Instead, it can process all of the information from one column at once.

What is the difference between SQL and NoSQL?

SQL and NoSQL are both structured query languages for querying databases. However, SQL uses tables to store its structured data whereas NoSQL stores its data in documents. Also, SQL uses joins to retrieve information from different tables whereas NoSQL retrieves all of the information from one table at once.

What is the difference between a row-oriented database and a column-oriented database?

A row-oriented database stores data in columns but retrieves it in rows. A column-oriented database stores data in rows but retrieves it in columns.