What is SQL? 

SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.  

Relational Database  

A type of database (organized collection of data), with items or data points that have pre-defined relationships with each other, in the form of tables consisting of rows and columns. One row typically has data of one entity with the columns identifying the type of data and the field storing the actual value of the data. Each table generally has a unique identifier or primary key that is then used for accessing the rows and their values.  

Practical Example 

Think of a library full of books arranged on shelves. Now, if you want to retrieve a book, say about SQL, wouldn’t it be a lot easier if there were index cards for the books mentioning the aisle and shelf the book could be found at, instead of having to go through the countless books individually to find the one you were looking for? Here again, it would be a lot less time-consuming to create an index card for the books if the shelves themselves were numbered or arranged by topic or even alphabetically by author.  

In terms of SQL, the Library is the equivalent of a Relational Database (you would only find things to read in a library), a Shelf is equivalent to a Table (with rows of books in columns of topics), and the Index Card is the equivalent to a Query written in SQL enabling the retrieval or storage of the book in/from its proper place in the Library. 

Example: 

The Provider tables 

The Hospital table 

Take the example above. There are 2 tables, one called Provider which contains details of health care providers in the US and another called Hospital which contains data of hospitals. This sample data allows us to spot links between the 2 tables and draw conclusions as well. For example, we can infer that John Smith is a doctor from New York and practices at the Faith Memorial Hospital.  

Since the size of the sample is small, these inferences are easily made. But when the data becomes huge as expected for the above type of tables, there comes a need to frame questions that can give the desired results from the data. This is what SQL does. It provides a medium of communication to enable the creation, manipulation and retrieval of data in a relational database. 

Description 

SQL, just like any other language, has its syntax and vocabulary. However, the similarity to English is what makes it very easy to learn. You would need to have a basic understanding of the database to work with it though. Knowledge of what the database contains and the columns it has is a prerequisite to running a query on it via SQL. 

All SQL statements start with a Keyword and end with a semicolon. Some of the common keywords used in SQL are SELECT, CREATE, INSERT, UPDATE and DELETE. Even though SQL is not case sensitive, using capitals for the statements makes it easier to understand and hence eliminates errors while creating queries.  

Making use of the above tables, a simple SELECT statement will look like this –  

SELECT * FROM Provider; (In SQL, * means “all the columns”.) 

This command will cause all data from the table Provider to be displayed. Here, SELECT is the statement and FROM is the clause that gives direction to the instructions. More than one clause can be used in a statement depending on the requirement. WHERE is another clause that can be used to put a condition in any SQL statement. 

SELECT First_Name, Last_Name FROM Provider WHERE Designation = ‘Doctor’; 

This statement will retrieve just the first and last names of all doctors from the table Provider.  

INSERT, UPDATE & DELETE are three other simple commands that are used often in SQL. Here are some statements showing their usage concerning the above tables. 

INSERT INTO Hospital (ID, Name, City, Zip) VALUES (4, Holy Family Hospital, Philadelphia, 19111);  

This statement will insert a row into the table Hospital with values for all columns except Address. A row can also be inserted without specifying the columns, with the condition that all data should be in the exact order of the existing columns.  

UPDATE Hospital SET Address = ’33 Hedge Row Fox Chase’ WHERE ID = ‘4’; 

This will update the Address column in row 4 of table Hospital which was missing in the previous statement. 

DELETE FROM Provider WHERE Last_Name = ‘Rich’; 

After processing the above set of commands, the modified tables will look like this – 

The Hospital table 

The Provider tables 

There are more commands in SQL apart from the ones described above which are used in various situations. Some of the frequently used ones are JOIN (used to combine rows from two separate tables using a related column between them), ALTER (used to change or modify columns or tables), CREATE (used to create tables or views or database, etc.), DROP (used to delete anything from a database or table or data within), CONSTRAINT (used to add or delete a constraint), among others.  

The ALTER TABLE command adds, deletes, or modifies columns in a table. 

ALTER TABLE Customers 
ADD Email varchar(255); 

The CREATE TABLE command creates a new table in the database. 

CREATE TABLE Persons ( 
    PersonIDint, 
    LastNamevarchar(255), 
    FirstName varchar(255), 
    Address varchar(255), 
    City varchar(255) 
); 

The DROP TABLE command deletes a table in the database. 

DROP TABLE Shippers; 

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself. 

TRUNCATE TABLE Categories; 

Common Mistakes 

The common mistakes made while using SQL are – 

• Selecting an incorrect database. This can be avoided by checking available databases before querying. 
• Incorrectly spelling the commands. This is the most common mistake in SQL and the use of capitalized commands and proper syntax will minimize these errors. 
• Use of commas and semicolons and quotation marks. All of these characters play a very important role in the structure of a query and care should be taken during their use to prevent mistakes. 
• It is also very important to keep practicing SQL queries to improve your grasp of the language. 

Context and Applications 

This topic is significant in the professional exams for both undergraduate and graduate courses                                                                                                     

• Computer Sciences 
• Data Sciences 
• Computer Engineering 

Related Concepts 

• Big Data 
• Database Development 
• Querying and managing data 
• Programming