SQL By Vigor Council

Structured Query Language (SQL) is the standard language for storing, manipulating and retrieving data in databases using special software known as Relational Database Management Systems (RDBMS). There are many RDBMS softwares available to store and manage the structured data. Some of the RDBMS softwares are MySQL, MariaDB, Oracle Database, Microsoft SQL Server, PostgreSQL, SQLite etc. SQL is case insensitive language. SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987

SQL can  perform various database operations like ·          Create and remove of database ·          Create, alter and remove the tables ·          Insert, update, delete and retrieve the records ·          Controlling the user access ·          Managing transactions during insertion, updation and deletion of records

 Most of the RDBMS softwares are proprietary and some of them are open source. For the proprietary software like Oracle Database, Microsoft SQL Server you have to take the license to use by paying a good amount. For the open source software or community editions of the proprietary softwares, you have not to pay any amount. You can use them for learning purpose but not for commercial purpose. To learn the SQL statements, you can install MySQL Community Edition Server and Workbench software from the following links https://dev.mysql.com/downloads/mysql/ https://dev.mysql.com/downloads/workbench/ You can also refer the video, How to Install MySQL Community Edition , if getting an issue in installation process.

These SQL statements are divided into four major categories depending on their actions ·          DDL (Data Definition Language) ·          DML (Data Manipulation Language) ·          DCL (Data Control Language) ·          TCL (Transaction Control Language)   DDL is used to manage the structure of the tables. The main keywords under DDL are CREATE, ALTER and DROP. DML is used to manage the records inside the tables. The main keywords under DML are INSERT, UPDATE, DELETE and SELECT. DCL is used to manage the user access control. The main keywords under DCL are GRANT and REVOKE. TCL is used to manage the changes made by DML statements in the database. Themain keywords under TCL are COMMIT, ROLLBACK and SAVEPOINT.

 To create a database you have to use the SQL statement, CREATE DATABASE with following syntax Syntax CREATE DATABASE databasename; Example CREATE DATABASE demodb;

Before start working on a database, you need to select that database and need to make the current working database using the SQL statement, USE with the following syntax Syntax USE databasename; Example USE demodb;

  A table is used to hold your records or rows related to some entity . An entity is something about which we manage some data e.g. Books in a library, Customer of an e-commerce website, User having an Instagram account, Student in a college. We need to indentify the columns or the fields to be stored while managing records of such entities e.g. Book can have columns or fields like Book Number, Title, Author and Price Book Number Title Author Price 101 Data Analytics for Beginners Rohit Kumar 400 102 Data Analytics for Professional Sanjay Sharma 500 103 Business Analytics for All Neeraj Verma 500 104 SQL Programming Dr B P Sharma 700 105 Python Programming Dr B P Sharma 900   Student can have columns ...

Every column or field related to some entity has to manage a kind of data known as data type. In general, data can be of three types ·          Numeric type Data ·          String type Data ·          Date type Data Different RDBMS software provides different keywords to define the data type e.g. MySQL provides data types such as ·          INT for integer data numeric data e.g. Book Number ·          FLOAT for floating type numeric data e.g. Price ·          VARCHAR for variable length strings e.g. Name, Email ·          CHAR for fixed length strings e.g. Pin Code, Mobile Number ·          DATE for managing dates e.g. Date of Birth Remember that, ...

  To create a table, you need to apply the SQL statement CREATE TABLE with following syntax. Syntax CREATE TABLE (columnname datatype(size), columnname datatype(size)…); Example USE demodb; CREATE TABLE Book(BookNumber INT, Title VARCHAR(100), Author VARCHAR(100), Price FLOAT); CREATE TABLE Student(RollNumber INT, Name VARCHAR(100), Email VARCHAR(50), Mobile CHAR(10), Fees INT); CREATE TABLE IssueBooks(BookNumber INT, RollNumber INT, IssueDate DATE);

Once the table is created, you can also see the structure of the table using the SQL statement, DESC with following syntax Syntax DESC tablename; Example Write the SQL statement to view the structure of book table. USE demodb; DESC book;

Once the structure of the table is ready, now we can insert records to the table. To insert the records, you have to use the SQL statement, INSERT INTO with following Syntax Syntax INSERT INTO tablename VALUES(value1, value2, value3…); Remember that, ü While passing string and date kind of data, enclose that data in single quote ü Default date format of MySQL is yyyy-mm-dd Example USE demodb; INSERT INTO book VALUES(101, 'Data Analytics for Beginners','Rohit Kumar',400); INSERT INTO book VALUES(102, 'Data Analytics for Professional','Sanjay Sharma',500); INSERT INTO book VALUES(103, 'Business Analytics for All','Neeraj Verma',500); INSERT INTO book VALUES(104, 'SQL Programming','Dr B P Sharma',700); INSERT INTO book VALUES(105, 'Python Programming','Dr B P Sharma',900);   INSERT INTO student VALUES(1234,'Harish Kumar','harish@gmail.com','9899999999',89000);...

To view specific records from table, we need to specify the criteria by using special operators called as relational and logical operators. The relational operators are used to compare two values and logical operators are used to combine two conditions. SQL provides WHERE clause to apply the criteria or the condition. Relational Operators =                                              Equals to <>                                            Not equals to >                    ...

We can also retrieve data from multiple tables in same SQL query based on column names. Example Display roll number and name of the student; book number and title of the books and issue date SELECT student.RollNumber,student.Name,book.BookNumber,book.Title,issuebooks.IssueDate FROM student, book,issuebooks WHERE issuebooks.RollNumber=student.RollNumber and issuebooks.BookNumber=book.BookNumber;   RollNumber Name BookNumber Title IssueDate 1234 Harish Kumar 101 Data Analytics for Beginners 2023-11-12 1235 Kapil Sharma 102 Data Analytics for Professional 2023-11-16 1236 Nitin Verma 103 Business Analytics for All 2023-11-20