SQL (Structured query language) is a programming language used by Relational Database Management System (RDBMS) for storing, accessing, adding, updating and deleting data. SQL is a declarative language where it tells what to do not how to do.
In the 1970s, IBM scientists Donald Chamberlin and Raymond Boyce developed and introduced SQL. It originated from the concept of relational models and was initially called structured English query language (SEQUEL) before being shortened to SQL. It has become the global standard for relational database management systems.
SQL was standardized by the American National Standards Institute (ANSI) in 1986 and the International Organization for Standardization (ISO) in 1987.
SQL Objects are the structure and components within the database. The following are the objects:
Table is the fundamental element of SQL. It’s a table that has columns and rows. Tables are relational meaning multiple tables can link to each other.
Views are virtual tables formed based on the result of a SQL query.
Indexes provide indexing of data that provides quick provide retrieval of data from the database.
Schemas provide the structoring of the objects.
Stored procedures are stored set of statements for increased efficiency and productivity.
Functions are built-in or user-defined programs that performs routine operations in database. There are two types of functions – aggregate function and scalar function.
Aggregate Function performs action on multiple values from different rows and gives the output.
- COUNT() – Returns the number of rows in a set.
- SUM() – Returns the total sum of a numerical column.
- AVG() – Returns the average value of a numerical column.
- MIN() – Returns the smallest value within the selected column.
- MAX() – Returns the largest value within the selected column.
Scalar Function performs actions on a single input value and returns a single output.
String Functions
- UPPER() – Converts a string to upper case.
- LOWER() – Converts a string to lower case.
- SUBSTRING() – Extracts some characters from a string
- CONCAT() – Adds two or more strings together
- LENGTH() – Gives the length of a string
Numeric Functions
- ROUND() – Rounds a number to a specified number of decimals
- SUM() – Calculates the sum of a set of values
- ABS() – Returns the absolute value of a number
- AVG() – Returns the average value of an expression
Date/Time Function
- DAY() – Returns the day of the month for a specified date
- GETDATE – Returns the current database system date and time
- SYSDATETIME() – Returns the date and time of the SQL Server
SQL Commands also known as SQL queries are valid instructions given to the relational database management system. The SQL commands are classified as DDL, DQL, DML, DCL and TCL which are explained below:
DDL (Data Definition Language) commands are used to define, modify, and manage the structure of database objects such as tables, indexes etc.
- Create – Creates new objects
- Drop – Deletes objects
- Alter – Modifies objects
- Truncate – Removes all records from a table
DQL (Data Query Language) commands are used to query data from the database.
- Insert – Adds new rows of data into a table.
- Delete – Removes rows of data from a table.
- Update – Upate rows in a table
DML (Data Manipulation Language) commands are used to manipulate the data within the database objects.
- Select – Retrieves data from one or more tables based on specified criteria.
- Where – Filter records based on specified criteria.
DCL (Data Control Language) commands are used to give control who can access the data.
- Grant – Provides specific privileges to users.
- Revoke – Removes previously granted privileges from users.
TCL (Transaction Control Language) commands are used make transactionall changes to the data.
- Commit – Saves all changes made during a transaction permanently to the database.
- Rollback – Undoes all changes made during a transaction, restoring the database to its state before the transaction began.
- Savepoint – Sets a point within a transaction to which you can later roll back.