SQL Tutorial for Beginners

Many of you must have come across the term “SQL” while learning about computers and its programming. But what is it and why does it hold such an important stature in data management and processing ?

 

Let’s learn about SQL and important components that are used in its programming.

 

Understanding SQL

 

Donald D. Chamberlin and Raymond F. Boyce created SQL at IBM in the early 1970s. SEQUEL was the original name for this (Structured English QUEry Language). SQL's primary goal is to update, save, manipulate, and retrieve data from relational databases. SQL has evolved significantly throughout the years. 

 

Many new features have been introduced, including XML support, triggers, stored procedures, regular expression matching, recursive queries, standardised sequences, and much more.

 

SQL stands for Structured Query Language (pronounced "ess-que-el"). SQL is a language for interacting with databases. It is the standard language for relational database management systems, according to ANSI (American National Standards Institute). Learn more about basics of SQL-Applications and Uses from the link.

 

Oracle, Sybase, Microsoft SQL Server, Access, Ingres, and other relational database management systems that employ SQL are just a few examples. Although most database systems utilise SQL, they often include their own proprietary extensions that are exclusively used on their own platform.

 

Standard SQL commands such as "Select," "Insert," "Update," "Delete," "Create," and "Drop" may be used to perform practically anything with a database. This lesson will teach you the fundamentals of each of these commands and provide you the opportunity to practise them using the SQL Interpreter. You can discover SQL project ideas/topics to make your learning more streamlined.

 

What is a Database?

 

In SQL Server, a database is made up of a group of tables that each hold a specific set of structured data. A table is made up of rows, which are also known as records or tuples, and columns, which are also known as attributes. Each column in the table is intended to hold a certain sort of data, such as dates, names, dollar amounts, and numbers.

 

By storing data in tables, a database simulates real-world entities such as academics and institutions. Data from a single entity type is contained in each table. By storing entities just once, eliminates duplication. For example, a single row of data describing the details of a certain firm is all that is required. Lastly, a database can be used to model the relationship between entities.

 

SQL allows us to store, retrieve, and change data in a relational database management system (RDBMS) using simple code snippets called queries (relational database management system).

 

(Also read: SQL vs NoSQL)

 

The data is stored in an organised manner in the RDBMS, with relationships between the various entities and variables. The database schema, which determines the relationship between distinct entities as well as the arrangement of data for the entities, defines these relationships.

 

1. Creating a database

 

We use the CREATE DATABASE statement to create a new database. 

 

Syntax:

CREATE DATABASE databasename;

Example:
 

CREATE DATABASE School;

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

 

2. Dropping a database

 

Syntax:

DROP DATABASE databasename;

Example:

DROP DATABASE School;

The database with the name School will be deleted.

 

(Must Catch: SQL-Easy or Tough)

 

3. Tables

 

Tables are one or more objects in a relational database system. These tables are where the database's data or information is kept. Tables are made up of columns and rows and are recognised by their titles. 

 

The column name, data type, and any other properties for the column are all stored in columns. The records or data for the columns are stored in rows. Here's a "weather" table as an example.

 

The columns are city, state, high, and low. The data for this table is included in the rows:

 

Weather city state high New Delhi Delhi 44 Mumbai Maharashtra 43 Chennai Tamil nadu 45 Kashmir Kashmir 22 Jaipur Rajasthan 46

 

Creating a Table

 

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

 

Syntax-

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype, ....);

(Related Blog: Top Sites to Learn SQL)

 

 

Different Queries in SQL

---

Different Queries in SQL

---

1. SELECT- The SQL SELECT command is used to get data from a database table, which is returned as a result table. Result-sets are the names for these result tables.

 

Syntax-

SELECT column1, column2, …

FROM table_name;

 

2. INSERT- The SQL INSERT INTO Statement is used to insert new data rows into a database table.

Syntax-

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  

VALUES (value1, value2, value3,...valueN);

 

3. WHERE- When retrieving data from a single table or combining several tables, the SQL WHERE clause is used to set a condition. Only a specific value from the database is returned if the provided condition is met. To filter the data and retrieve only the ones you need, use the WHERE clause.

Syntax-

SELECT column1, column2, columnN 

FROM table_name

WHERE [condition]

 

4. AND- The AND operator allows several conditions to occur in the WHERE clause of a SQL query.

 

Syntax- 
 

SELECT column1, column2, columnN 

FROM table_name

WHERE [condition1] AND [condition2]...AND [conditionN];

 

5. OR- The OR operator is used in the WHERE clause of a SQL query to combine several criterias.

 

Syntax- 

SELECT column1, column2, columnN 

FROM table_name

WHERE [condition1] OR [condition2]...OR [conditionN]

 

6. UPDATE- The UPDATE Query in SQL is used to update existing records in a table. If you use the WHERE clause with the Change query, you may update only the rows you want, rather than all of them.

 

Syntax-

UPDATE table_name SET column1 = value1, column2 = value2,... 

WHERE condition;

 

7. DELETE- To delete existing records from a table, use the SQL DELETE Query. If you use the WHERE clause with a DELETE query, you may delete only the rows you want, rather than all the data.

 

Syntax-

DELETE FROM table_name WHERE condition;

 

8. LIKE- The SQL LIKE clause compares a value to other values that are similar using wildcard operators. With the LIKE operator, there are two wildcards that may be used.

 

• The percent sign (%)

• The underscore (_)

 

Syntax-

SELECT column1, column2, ...

FROM table_name

WHERE columnN LIKE pattern;

 

9. ORDER BY- The SQL ORDER BY clause is used to sort data by one or more columns in ascending or descending order. By default, some databases sort query results in ascending order.

 

Syntax-

SELECT column1, column2, ...

FROM table_name

ORDER BY column1, column2, ... ASC|DESC;

 

10. GROUP BY- To organise identical data into groups, the SQL GROUP BY clause is used in conjunction with the SELECT command. In a SELECT statement, the GROUP BY clause comes after the WHERE clause and before the ORDER BY clause.

 

Syntax-

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

ORDER BY column_name(s);

 

(Referred blog: SQL for data scientist-Part 1)

 

 

Advantages of SQL

 

SQL has many advantages which makes it popular and highly demanded. It is a reliable and efficient language used for communicating with the database. Some advantages of SQL are as follows:

---

Advantages of SQL

---

1. Larger Data Retrieval - Large quantities of data are rapidly and efficiently retrieved. Insertion, deletion, and data modification may all be done in a matter of seconds.

2. No prior Coding Skills required –Data retrieval does not necessitate a significant number of lines of code, thus no coding skills are necessary. SQL uses all of the fundamental terms, such as SELECT, INSERT INTO, UPDATE, and so on, and the syntactical rules are simple, making it a user-friendly language.

3. Standardised Language - It gives a consistent platform to all of its users throughout the world as a result of documentation and years of establishment.

4. Portable –It is portable and may be used in programmes on PCs, servers, and laptops, regardless of platform (Operating System, etc). It may also be integrated with other programmes based on the need/requirement/use.

5. Interactive Language –Answers to complicated inquiries may be answered in seconds and are simple to learn and comprehend.

6. Multiple data views – Users that need to study the links between data categories might utilise a multidimensional database to get different perspectives of data sets.

 

 

Conclusion

 

Is SQL still useful today?

 

Despite the fact that tableless databases such as MongoDB and Firebase are growing more popular and are being advocated for by developers as well as being utilised in greater numbers, SQL remains essential — particularly in terms of data durability and scalable costs.

 

Having a set cost might assist some firms choose how much to devote to budgeting and future development. The fixed cost may appear higher at first, but SQL can prove to be less expensive in the long run for classic and tested situations. Tableless architectures, on the other hand, should not be completely dismissed. When used in combination with SQL, it may provide a layer that makes development work go faster and faster.