SQL Basics

In today’s world data is everything. But to manage it, one has to master the art of data management. With that comes the language i.e, SQL which is the basis to all. SQL is the core of relational type databases which is used among most of the companies.

Introduction to SQL

SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This was initially called SEQUEL(Structured English QUEry Language). The main objective of SQL is to update, store, manipulate and retrieve data stored in a relational database. Over the years SQL has undergone a lot of changes. A lot of functionality such as support for XML, Triggers, Stored Procedures, Regular Expression Matching, Recursive Queries, Standardised Sequences and much more are added.

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables.

Difference between SQL and MySQL

SQL is a standard language which is used to operate on database in the form of queries. But MySQL is Open Source Database Management System or simply a Database Software. MySQL will organize and then store the data in its database.

Advantages:

• SQL has well-defined standards
• SQL is interactive in nature
• With the help of SQL, one can create multiple views
• Portability of code in SQL is a prominent feature.

Database

A database is an organized collection of data, which is generally stored and accessed electronically from a computer system. In simple words, we can say a database in a place where the data is stored. The best analogy is the library. The library contains a huge collection of books of different genres, here library is database and books are the data.

The database can be classified broadly into the following groups:

• Centralized database
• Distributed database
• NoSQL database
• Operational database
• Relational database
• Cloud database
• Object-oriented database
• Graph database

Now we will be focusing more on the relational database which uses SQL for its operations.

How to Create a database?

We use CREATE DATABASE statement to create a new database.

Syntax:

1CREATE DATABASE databasename;

Example:

1CREATE DATABASE School;

So the database of name School will be created. If you want to delete this database, you have to use the following syntax.

How to Drop a database?

Syntax:

1DROP DATABASE databasename;

Example:

1DROP DATABASE School;

The database with name School will be deleted.

Table

A table in a database is nothing but a collection of data in a tabular way. It consists of columns and rows. The table contains data elements also known as values using a model of vertical columns and horizontal rows. The point of intersection of a row and a column is called a CELL. A table can have any number of rows but should have a specified number of columns.

Create a Table

So to create a table in database we use the following SQL query.

Syntax

1CREATE TABLE table_name (

2column1 datatype,

3column2 datatype,

4column3 datatype, ....);

Here the keyword Create Table is used to say to a database that we are going to create a new table. Then we need to mention the table name. This name has to be unique. SQL is case insensitive, but the data stored inside the table will be case sensitive. We add the columns inside the open and close brackets. We specify each column with a certain data type.

Example:

1CREATE TABLE Student (

2studentID int,

3FName varchar(25),

4LName varchar(25),

5Address varchar(50),

6City varchar(15),

7Marks int);

We have created a table with the name Student and added a few parameters into the table. This is how we can create a table using SQL.

Drop a Table

If we want to delete the entire table with all its data then we have to use the DROP command.

Syntax:

1DROP TABLE table_name;

Example:

1DROP TABLE Student;

So the student table will be deleted.

SQL BASIC QUERIES

Now, let’s focus on some SQL basic commands that one should know when they start learning about SQL. There are many queries that seem to be basic, but I have covered few that are really essential for a beginner. For explaining all the query I have considered the Student table, which I will be using.

SELECT

It is the most basic SQL query one can use for manipulating a database. The select command is used to select the data from the database and display it to the user.

Syntax:

1Select column 1, column 2…..column N

2From Table;

Example:

1Select name From Student;

The above example will display all the names from the student table. If we want to display all the fields in the table then we have to use *(Star) operator. This will display the entire table.

Example:

1Select * from Student;

If we want to display certain field without any duplicates then we use the DISTINCT keyword along with the select command.

Example:

1Select DISTINCT FName From Student;

WHERE

If we need only certain records from the table then we use the where clause. Where clause acts as a Filtering mechanism. Under the Where section we need to specify certain conditions, only if those conditions are met the records will be extracted.

Syntax:

1SELECT column1, column2, ...column N

2FROM table_name

3WHERE condition;

Example:

1SELECT FName FROM Students

2WHERE City='Delhi';

AND, OR, NOT

If we need to add two or more conditions in the where clause then we can use the above-mentioned operators. These keywords will add more complexity to the query.

• AND Operator: This operator displays a record if all the conditions separated by AND are TRUE.

Syntax:

1SELECT column1, column2, ...

2FROM table_name

3WHERE condition1 AND condition2 AND condition3 ...;

Example:

1SELECT * FROM Student

2WHERE FName='John' AND Lname='Doe';

• OR Operator: This operator displays a record if any of the conditions separated by OR is TRUE.

Syntax:

1SELECT column1, column2, ...

2FROM table_name

3WHERE condition1 OR condition2 OR condition3 ...;

Example:

1SELECT * FROM Student

2WHERE FName='John' OR Lname='Doe';

• NOT Operator: This operator displays a record if the condition/conditions are NOT TRUE.

Syntax:

1SELECT column1, column2, ...

2FROM table_name

3WHERE NOT condition;

Example:

1SELECT * FROM Student

2WHERE NOT Lname='Doe';

This are some of the SQL Basics article. I hope you understood the concepts of SQL basics.