WHAT IS SQL?
SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system
SQL stands for Structured Query Language. A query language is a kind of programming language that’s designed to facilitate retrieving specific information from databases, and that’s exactly what SQL does. To put it simply, SQL is the language of databases
What is SQL and why it is used?
Structured Query Language (SQL) is the standard and most widely used programming language for relational databases. It is used to manage and organize data in all sorts of systems in which various data relationships exist.
RDBMS (Relational Database Management System)
A database management system (DBMS) that incorporates the relational-data model, normally including a Structured Query Language (SQL) application programming interface. It is a DBMS in which the database is organized and accessed according to the relationships between data items. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence.
Features of relational database management systems
Elements of the relational database management system that overarch the basic relational database are so intrinsic to operations that it is hard to dissociate the two in practice.
The most basic RDBMS functions are related to create, read, update and delete operations — collectively known as CRUD They form the foundation of a well-organized system that promotes consistent treatment of data.
The RDBMS typically provides DATA DICTIONARIES and metadata collections that are useful in data handling. These programmatically support well-defined data structures and relationships. Data storage management is a common capability of the RDBMS, and this has come to be defined by data objects that range from binary large object — or blob– strings to stored procedures. Data objects like this extend the scope of basic relational database operations and can be handled in a variety of ways in different RDBMSes.
The most common means of data access for the RDBMS is SQL. Its main language components comprise data manipulation language and data definition language statements. Extensions are available for development efforts that pair SQL use with common programming languages, such as the Common Business-Oriented Language (COBOL), Java and .NET.
RDBMSes use complex algorithms that support multiple concurrent user access to the database while maintaining data integrity. Security management, which enforces policy-based access, is yet another overlay service that the RDBMS provides for the basic database as it is used in enterprise settings.
RDBMSes support the work of database administrators (DBAs) who must manage and monitor database activity. Utilities help automate data loading and database backup. RDBMSes manage log files that track system performance based on selected operational parameters. This enables measurement of database usage, capacity and performance, particularly query performance. RDBMSes provide graphical interfaces that help DBAs visualize database activity.
While not limited solely to the RDBMS, ACID compliance is an attribute of relational technology that has proved important in enterprise computing. These capabilities have particularly suited RDBMSes for handling business transactions.
As RDBMSes have matured, they have achieved increasingly higher levels of query optimization, and they have become key parts of reporting, analytics and data warehousing applications for businesses as well. RDBMSes are intrinsic to operations of a variety of enterprise applications and are at the center of most master data management systems.
SQL Functions
SQL has many built-in functions for performing calculations on data.
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
- AVG() – Returns the average value
- COUNT() – Returns the number of rows
- FIRST() – Returns the first value
- LAST() – Returns the last value
- MAX() – Returns the largest value
- MIN() – Returns the smallest value
- SUM() – Returns the sum
SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
- UCASE() – Converts a field to upper case
- LCASE() – Converts a field to lower case
- MID() – Extract characters from a text field
- LEN() – Returns the length of a text field
- ROUND() – Rounds a numeric field to the number of decimals specified
- NOW() – Returns the current system date and time
- FORMAT() – Formats how a field is to be displayed
SQL AVG() Function
The AVG() Function
The AVG() function returns the average value of a numeric column.
SQL AVG() Syntax
SELECT AVG(column_name) FROM table_name
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the “Products” table:
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
| 2 | Chang | 1 | 1 | 24 – 12 oz bottles | 19 |
| 3 | Aniseed Syrup | 1 | 2 | 12 – 550 ml bottles | 10 |
| 4 | Chef Anton’s Cajun Seasoning | 2 | 2 | 48 – 6 oz jars | 21.35 |
| 5 | Chef Anton’s Gumbo Mix | 2 | 2 | 36 boxes | 25 |
SQL AVG() Example
The following SQL statement gets the average value of the “Price” column from the “Products” table:
Example
SELECT AVG(Price) AS PriceAverage FROM Products;
The following SQL statement selects the “ProductName” and “Price” records that have an above average price:
Example
SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);
SQL COUNT() Function
The COUNT() function returns the number of rows that matches a specified criteria.
SQL COUNT(column_name) Syntax
The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:SELECT COUNT(column_name) FROM table_name;
SQL COUNT(*) Syntax
The COUNT(*) function returns the number of records in a table:SELECT COUNT(*) FROM table_name;
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:SELECT COUNT(DISTINCT column_name) FROM table_name;
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the “Orders” table:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10265 | 7 | 2 | 1996-07-25 | 1 |
| 10266 | 87 | 3 | 1996-07-26 | 3 |
| 10267 | 25 | 4 | 1996-07-29 | 1 |
SQL COUNT(column_name) Example
The following SQL statement counts the number of orders from “CustomerID”=7 from the “Orders” table:
Example
SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;
SQL COUNT(*) Example
The following SQL statement counts the total number of orders in the “Orders” table:
Example
SELECT COUNT(*) AS NumberOfOrders FROM Orders;
Try it Yourself »
SQL COUNT(DISTINCT column_name) Example
The following SQL statement counts the number of unique customers in the “Orders” table:
Example
SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;
Drop your comment
