SQL Tutorial For Beginners

1. SQL Tutorial For Beginners | Learn SQL Welcome to SQL Tutorial. You will learn introduction to SQL, history of SQL, Uses of SQL and much more. Let’s start!!! What is SQL? SQL, which goes by the full form – Structured Query Language is programming, or we can say a query language that is used to deal with the relational database operations and queries which could involve manipulating or updating a database or creating a new database. Major operations on a database are known as CRUD operations i.e., Create, Read, Update and Delete and are a widely used database in the technological world. The database is required to store the large volumes of data being produced today. SQL is a powerful tool required by anyone who deals with data on a day-to-day basis. Sql can insert data into a database. It can search for some data in the database and also it supports updation and deletion of data stored in the database. History of SQL

2. SQL was first brought into origin by IBM Researcher’s – Raymond F. Boyce, and Donald D. Chamberlin in the 1970’s and the initial version created by them was called SEQUEL or Structured English Query Language which worked on manipulation and retrieving data from IBM databases. After commercial testing, IBM released various versions like System/38, SQL/DS, and DB2 in 1979, 1981, and 1983, respectively. In 1986 making a breakthrough, ANSI and ISO adopted the Standard “Database Language SQL”. Working of SQL SQL works on commands and queries made to a Relational database. A relational database signifies that the data would be stored and retrieved in terms of relations or tables. When we run a command on SQL workbench or similar software, the command returns us the required results in the form of a table. Next, we would learn what SQL commands and the types of SQL commands are. What Can SQL Do? We can use SQL for various operations. Some of them are stated below 1. We can use SQL to run queries on a database. 2. SQL is used to perform CRUD(Create, Retrieve, Update, Delete) operations on a database.

3. 3. SQL is required to create and manage databases. 4. We use SQL to update and manipulate the existing data in the database. 5. SQL can be used to create views over an already existing database table. 6. SQL comes in handy when we need to divide permissions among different users of a database. 7. We are able to perform transactions easily on databases using SQL as it is compatible with almost all programming languages like – C++, Java, Python, etc. Rules to Write SQL Query We need to follow certain rules while writing a SQL query : 1. SQL queries are not case sensitive, but generally, we write SQL keywords in Uppercase for better understanding. 2. SQL statements can span in multi lines. 3. SQL follows the principle of tuple relational calculus and the rules of relational algebra. 4. SQL queries are capable of performing almost all actions on the database. SQL Processes 1. When we run a SQL query on the Dbms or the RDBMS, the system looks into the request and tries to find the best possible way to execute it. 2. While a process is executed we need to take care of many components like – Query dispatcher and engine, optimization engine, etc. 3. SQL queries are not designed to handle the logical files; non SQL queries are interpreted by the classical query engine of sql.

4. Uses of SQL Some of the uses of SQL are as follows: 1. Database Transaction Management: It is used to manage and configure the DBMS containing all sorts of data. 2. Reporting Purpose: SQL queries prove to be highly beneficial when it comes to reporting the facts and figures out of the bulk data stored in the relational Dbms. 3. Manual Analysis: SQL and its queries prove to be highly beneficial when we need to find problems in stored data, and human intervention is needed to correct the same. 4. Business Friendly: SQL provides many built-in aggregate and arithmetic functions which make it easy to use when dealing with large inflow of data and thus heavy calculations. SQL Commands and Types

5. SQL works by running the commands provided by the user. We can divide the SQL commands into five distinctions based on the functionality they provide: 1. DDL (Data Definition Language) DDL is used to define the structure or schema of the database and to specify additional properties of the data. These statements define the implementation of the schema and are mostly hidden from the user, also the data stored in the database is made to follow certain constraints. Some of the DDL commands are as follows : 1. Create: to create objects in the database 2. Alter: alters the structure of the database 3. Drop: delete objects from the database 4. Rename: rename an object 5. Truncate: deletes all the data stored in a table CREATE Command in SQL Let us make a Student database here to study various available queries. Syntax: CREATE TABLE tableName ( columnName1 , datatype(length) , columnName2 , datatype(length), columnName3 , datatype(length));

6. Command to create Student database : CREATE TABLE DataFlair_Employee ( name_emp varchar(50), post_emp varchar(50), email varchar(50), age int, salary varchar(10) ); Execution of the above stated DDL command creates the DataFlair_employee table consisting of four columns i.e. Name, Post, Email, Age and Salary. 2. DML(Data Manipulation Language)

7. DML is used for managing data contained in the schema objects. DML’s can be divided into two subtypes : 1. Procedural DML: In these types of DML’s we need to specify the type of data we need and how to get that data. 2. Declarative or Non-Procedural DML: Here, we only need to know what data is required, but the specifications on the process to get the data is not required. The database, on its own, figures out how to get the data efficiently. Examples of DML Commands : 1. Insert: used to insert data in the database 2. Update: used to modify the existing data in the database 3. Delete: used to delete all the records contained (Note: The space used is not deleted) 4. Merge: used to merge two or more tables or rows. Example : a. Insert Command in SQL Insert command is used to populate and add data to the database already existing. We can use the following Syntax: Insert into tableName (columnName1, columnName2, columnName3….) Values (value1,value2,value3,….) ;

8. Let us populate the database by using the insert command : Insert into DataFlair_Employee (name_emp , post_emp , email , age , salary) Insert into DataFlair_Employee (name_emp , post_emp , email , age , salary) Values ('Ram' , "Intern", 'ram@dataflair.com', 21 , '10000' ), ('Shyam', "Manager", 'shyam@dataflair.com' , 25 , '25000'), ('Ria', "Analyst" , 'ram@dataflair.com', 23 , '20000'), ('Kavya', "Senior Analyst" , 'kavya@dataflair.com', 31 , '30000'), ('Aman', "Database Operator",' rish@dataflair.com' , 26 , '15000') ; Executing this Insert statement populates our database and inserts the record of five employees in our database. b. Update Command in SQL

9. The update command is used to change the data that is already saved into the database. This can be done by the following Syntax: UPDATE tableName SET column1 = value1, column2 = value2,… where condition; Let us update the salary of Ram and change his age to 26 , the command is as follows : Update dataflair_employee SET salary = 12000 , age = 26 where name_emp = 'Ria' ; c. Delete Command in SQL Delete command is used to delete the existing records of a database. This can be done by the following

10. Syntax: DELETE FROM table_name WHERE condition; Let us delete the record corresponding to Kavya; we can get to this by the following command DELETE from dataflair_employee WHERE name_emp = 'Kavya' ; 3. DQL (Data Query Language) DQl is also known as the Data Query language. It is used to retrieve the data stored in the database created by us and the data we store in the database. a. Select Command in SQL Let us now display the Student Table that we created. Syntax:

11. Select * from tableName ; After executing this command, we can view all the columns and all the data stored in the table. Example: a. SELECT * from DataFlair_employee : helps in viewing all the data i.e., all rows and columns. b. SELECT * from DataFlair_employee LIMIT 2; returns data contained in two rows of the database.

12. c. Select name_emp , Age from DataFlair_employee : returns the data stored in the studentName and Age column of the Student database. These statements help in viewing the database in various aspects and different views.

13. 4. TCL (Transaction Control Language) TCL commands are used to handle transactions in the database. These are used to manage and track the changes made by DML statements , these can be grouped together into logical transactions. Example: 1. Commit: used to permanently save any transaction into the database. 2. Rollback: used to restore the database to the last committed state and is also used with the Savepoint command to move over to any saved state in a transaction. 3. Savepoint: It is used to temporarily store a transaction so that we can move to the point we need whenever desired. 5. DCL (Data Control Language) DCL commands are similar to the computer programming language in syntax. They are a component of the Structured Query Language. While using DCL commands, we need to be cautious as some database’s don’t allow rollback commands on the DCL command. One such example is the Oracle database – when we execute DCL on an oracle database it results in an implicit commit and thus rollback is not allowed. Example : 1. Grant: used to allow a specific set of users to perform some specified tasks. 2. Revoke: used to cancel all the previously granted or denied permissions.

14. Summary In this article, we have understood the basic introduction of what SQL is and how it is useful to us and where it is used. We have studied the history of SQL and types of queries available in SQL i.e. 1. DDL – Data Definition Language 2. DML – Data Manipulation Language 3. DQL – Data Query Language 4. TCL – Transaction Control Language 5. DCL – Data Control Language