SQL

  INTRODUCTION TO SQL

SQL means structured query language which is used to store and manage the data in a database.

History Of SQL

Early Development (1970s): SQL's roots can be traced back to the research of IBM's Donald D. Chamberlin and Raymond F. Boyce. They worked on a language called SEQUEL (Structured English Query Language) in the early 1970s, which later had to be renamed to SQL.

IBM's System R and SQL/DS: IBM developed System R, a pioneering database system that implemented SQL as its query language. SQL/DS, released in 1981, was one of the first commercial database systems to support SQL.

ANSI and ISO Standardization: In the 1980s and 1990s, SQL became standardized by ANSI (American National Standards Institute) and later by ISO (International Organization for Standardization). This helped SQL gain widespread adoption and compatibility across different database management systems (DBMS).

RDBMS Dominance: Relational Database Management Systems (RDBMS) like Oracle, IBM DB2, Microsoft SQL Server, PostgreSQL, and MySQL adopted SQL as their standard language, further cementing its importance in database operations.

Evolution and Advancements: Over the years, SQL has evolved significantly. Various versions have been released with new features and improvements, enhancing its capabilities for data manipulation, retrieval, and management. Advanced SQL functionalities include triggers, stored procedures, views, and more.

NoSQL and New Challenges:   With the rise of NoSQL databases in the late 2000s, SQL faced challenges due to the different data models and storage methods they employed. However, SQL remained a dominant force, and many NoSQL databases started to offer SQL-like query languages to bridge the gap.

Current Trends: Today, SQL continues to be a fundamental skill for database management, data analysis, and software development. It's widely used in various industries for querying, updating, and managing structured data.

Why is SQL Used?

Relational Database Interaction: SQL is the primary language for interacting with relational databases, enabling data retrieval, manipulation, and management.

Data Retrieval and Analysis: It facilitates efficient querying, filtering, sorting, and aggregation of data from large datasets, aiding in analysis and reporting.

Database Management: SQL is essential for creating and modifying database structures, setting permissions, and maintaining database integrity.

Web Development Backbone: It serves as a crucial component in web development, allowing backend systems to manage and retrieve data for web applications.

Business Intelligence & Reporting: SQL is integral to BI tools, enabling extraction, transformation, analysis, and visualization of data for informed decision-making.

Data Warehousing and ETL: It's used in data warehousing environments for ETL processes, querying, and manipulating data stored in data warehouses.

Security and Access Control: SQL provides mechanisms for setting access controls, defining user privileges, and ensuring data security.

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL and for all other modern database systems such as MS SQL Server, IBM DB2, Oracle, and MySQL.

The data in RDBMS is stored in database objects called tables.

A table is a collection of related data entries and it consists of columns and rows.

DataTypes

It is an indication to the user what type of data to be stored in a column.

SQL mainly  consists of below following datatypes

1. Char

2. Number

3. Varchar

4. Date

 Char DataType

If a column is assigned with a char datatype it is going to store the values of 0-9, a-z, A-Z, and 3 special characters i ,e  $,*,#.

If a column is assigned with a char datatype if you do not insert any value in that it is going to be considered as space.

space always consumes memory.

Number DataType

If a column is assigned with a number datatype then it will only store numbers.

Varchar DataType

If a column is assigned with a char datatype it is going to store the values of all the combinations i,e Numbers, Alphabets, Special Characters.

If a column is assigned with a char datatype if you do not insert any value in that it is going to be considered as null.

Null doesn't store any  memory

Date DataType

If a column is assigned with a  Date datatype then it will only store date values.

We can restrict the size of  any datatype in SQL with the following syntax

Datatype Size;

Constraints

SQL constraints are a set of rules given to the column for validating the data in the table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. 

Constraints can be column level or table level. Column-level constraints apply to a column, and table-level constraints apply to the whole table.

The following  7  constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE- Ensures that all values in a column are different

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

FOREIGN KEY - Prevents actions that would destroy links between tables

CHECK- Ensures that the values in a column satisfy a specific condition

DEFAULT - Sets a default value for a column if no value is specified

CREATE INDEX- Used to create and retrieve data from the database very quickly

                                  Difference between Primary key and Foreign key

Statements

A SQL statement is the most fundamental building block of SQL. A statement is a command to be understood by the interpreter and executed by the SQL engine.

SQL statements may do many different types of work in a database: they can return data as the result of a query, modify data or the structure of the tables, delete data or even whole tables, as well as more advanced operations such as updating user permissions or altering the underlying way data are stored and returned from a table.

Broadly speaking, SQL statements fall into four main categories of sub-languages:

Data Query Language (DQL) Statements, which return data from tables in the database.

Data Definition Language (DDL) Statements, which alter or return information on the underlying structure and format of data in a SQL database.

Data Manipulation Language (DML) Statements, which add, modify, delete, and control access to data.

Data Control Language (DCL) Statements, which control access rights of users to data and operations within a database.

Create:-create is a statement used to create the table in SQL.

syntax:- create table tablename(column1 datatype, column2 datatype,column3 datatype);

Alter:- Alter is a statement used to modify the table with respect to the column.

syntax:- Alter table tablename ADD column_name datatype ;

Rename:- Rename is a statement used to rename the table and columns.

syntax:- Rename oldtablename to newtablename;

Truncate:- It is used to remove all the records from the table in one shot.

It will not remove the table from the database instead it will remove records from the table.

syntax:- Truncate table tablename;

Drop:- Drop is used to remove the table from the database.

If the Drop is applied to a table the table is removed from the database and stored in a bin.

syntax:- Drop table tablename;

Purge:- The purge is used to remove the table from the database and as well as from bin permanently.

syntax:- 

Before Drop :- Drop table tablename purge;

After Drop:- purge table tablename

sub queries:-

The query is written under another query to get output from the table.

The inner query will get executed first and give the result to the outer query as input, and then the outer query executes to get the final result or output.

Example;-