Prashanth Jayaram

What is SQL in SQL Server?

December 5, 2018 by
What is SQL, in the context of SQL Server, is a common question asked by beginners. This article is meant to answer that question, while providing some history, context and an overview of the fundamentals of the language

Meaning and definition

SQL stands for Structured Query Language, a language for manipulating and talking about data in databases. It first came into use in 1970 and became a standard in 1986 by IBM in conjunction with several projects for the US government and for many years it remained a government-only project. It’s a programing language which is used to access data stored in a database.

The word “SQL” is an acronym, which is associated, today, with “Structured Query Language”. It was originally called SEQUEL with a slightly different meaning. Some people still enunciate the acronym as SEQUEL and some people enunciate each individual letter, S-Q-L. They mean the same thing.

SQL, as a data retrieval language, is an industry standard; All relational database products, SQL is the mechanism, language and syntax used to retrieve data from ar database.

According to Wikipedia

  • “SQL is designed based on relational algebra and tuple relational calculus, SQL consists of many types of statements, which may be informally classed as sublanguages, commonly: a data query language (DQL),[a] a data definition language (DDL),[b] a data control language (DCL), and a data manipulation language (DML).The scope of SQL includes data query, data manipulation (insert, update and delete), data definition (schema creation and modification), and data access control. Although SQL is often described as, and to a great extent is, a declarative language (4GL), it also includes procedural elements.
  • SQL was one of the first commercial languages for Edgar F. Codd’s relational model, as described in his influential 1970 paper, “A Relational Model of Data for Large Shared Data Banks”.[11] Despite not entirely adhering to the relational model as described by Codd, it became the most widely used database language “

Technical overview

SQL is a special-purpose programming language. That differentiates it from other languages like C, C++, JavaScript, or Java, which are all general-purpose programming languages. This means that SQL has a very particular purpose, which is to manipulate data sets. We manipulate the data sets using relational calculus.

Typically, we can use SQL on any kind of databases or data sources and even if we can’t use SQL directly against some of the data sources, most query languages today have some relationship to SQL. In general, once you’ve learned SQL, it’s pretty easy to pick up other query languages.

SQL has a number of standards. It’s both ANSI and ISO standard compliant. These standards are vital because each relational database vendor has to implement the standard, at least as a lowest common denominator, so that you know that if you learn the SQL standard, you can apply that knowledge to other databases products.

Interesting facts:

  1. The first product available to the public that utilized the SQL language was in 1979 with Oracle, version 2, and Oracle remains one of the premier database systems today.

  2. Most databases have some additional features that aren’t part of the standard. When you want to learn a particular database product, you’ll have to pick up on those. But the basic standards-based SQL will always be the same.

  3. SQL is generally whitespace independent, meaning that if you want to add some space between clauses or expressions to make your statement easier to read, you can do so

  4. The majority of SQL commands focused on four basic verbs i.e. SELECT INSERT, UPDATE and DELETE.

SQL is standardized in such a way by asking a specific questions of a database object, in the form of a structured query that a database knows how to respond to. SQL uses a command interpreter to parse the SQL. Because it’s such a powerful way of thinking about the data, SQL has been adopted into many database products. Some products support the SQL standard and add other features on top of it, and some support part, though not all, of the SQL standard.

DML statements

Before we start building SQL statements in SQL Server, we need to understand what the basic parts of a statement are. Overall, a “statement” is something you write in SQL to get an answer from a database or to make a change to it.

DML stands for Data Manipulation Language statements. These are statements that read, add, edit or remove data

    • The first statement we’ll describe is SELECT. This is used to retrieve data from the database.

      An example statement would be:

    • Then we have UPDATE. UPDATE will modify one or more existing rows in the database.

      A detailed explanation of SQL Update can be found here: Overview of the SQL update statement

    • INSERT creates one or more new rows in the database. A detailed explanation of SQL Insert can be found here: Overview of the SQL Insert statement

    • DELETE will remove one or more existing rows from the database. INSERT and DELETE always manipulate an entire row. We can only delete an entire row. We cannot delete part of a row. The only statement that manipulates part of a row is the UPDATE statement. It can update only one column or multiple columns or all of the columns. A detailed explanation of SQL Delete can be found here: Overview of the SQL Delete statement

DDL statements

DDL stands for Data Definition Language statements. These are statements that related to objects, such as tables, in SQL Server vs data. For example, to create a table, you would use a CREATE TABLE statement, specifying the name and other attributes of your table. Later you could use DML statements to add data to it, update or remove that data, and read the data

Note: A detailed explanation of SQL Create can be found here: Overview of the SQL create statement

Create, Read (Select), Update (Update) and Delete (Delete) statements are referred to, collectively, by the acronym CRUD. A detailed explanation of SQL CRUD operations can be found here: Creating and using CRUD stored procedures

Clauses

Clauses are the basic components of SQL statements, the smaller building blocks that make up the whole. These clauses are constructed out of keywords, which tell the database to take a specific action. There are also field names, which tell the database where to look and what to look for. There are also predicates, which let us specify what information we’re working with. Predicates include a value or condition called an expression. A clause can be a statement if you’re writing a really basic one. There are also operators, as we’ll see later on, which let us compare equality or ranges of data or treat information in other ways.

  • Note: Keywords and operators are customarily written as upper case, though usually, they don’t have to be. But, it helps to distinguish the SQL from your expressions and field names at a glance

Summary

Using SQL you can choose to see some of the data; select data from specific tables only; add filtering to the data; manipulate the data. You can do all of that using the SQL language. So, think of SQL as the API, or Application Programming Interface, the primary language which you use to interact with your Database.

Secondly, you can modify data; insert new data; update or delete existing data.

Third, the SQL language allows you to modify objects in the database. That is creating new tables. Or, modify the structure of existing tables, such as adding columns to tables or deleting columns from tables

Strengths

Some of the strengths of SQL include filtering particularly when running a SELECT statement. We have easy and powerful tools to filter the results that are returned to us. Sorting any results returned from a SQL query can easily is sorted ascending or descending on any column.

  1. A detailed explanation of the SQL Like operator can be found here: Overview of the SQL LIKE Operator

  2. A detailed explanation of SQL Coalesce function a commonly used data manipulation function can be found here: Using the SQL Coalesce function in SQL Server

  3. A detailed explanation of the SQL Order by clause can be found here: Overview of the SQL Order by clause
 

In SQL, the level of complexity of working with two or more tables is similar to the level of complexity of working with one table.

  1. A detailed explanation of SQL join can be found here: SQL Join overview and tutorial

Weaknesses

The biggest drawback of SQL is the structured control flow. The basic SQL standard really doesn’t have any decision structures like IF-ELSE statements nor does it have much looping structures such as For, While, and Do-While, where we want to iterate over some condition clause to execute some SQL over and over.

Therefore, some vendors have come up with some specific solutions. These language-additions include all of the standard SQL commands to manipulate data. Microsoft implements T-SQL. The T stands for ”Transact”. Similarly, Oracle supports a programming extension, PLSQL aka Procedural Language SQL

That’s all for now…

Hope you like this article. Feel free to comment below.

Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

DDL, DML, T-SQL

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

247 Views