Beginner’s Approach to SQL

766

What is SQL?

SQL stands for ‘Structured Query Language‘ which is a widely used commercial database language used to create and operate on ^*relational databases.

A Relational Database is a type of database that stores the related information in the form of tables.

SQL actually consists of three sub-languages:

• DDL (Data Definition Language): It provides a set of definitions that are used to specify the database scheme, storage structure, and access methods. The result of the compilation of DDL statements is a set of tables also called relations.
• DML (Data Manipulation Language): It enables the users to access or manipulate data organized in the form of appropriate data models.
• DCL (Data Control Language): It is used to provide controlled access to the databases and therefore it is essential to the system.

SQL became the standard of the ‘International Organization for Standardization‘ (ISO) in the year 1987.

The famous Relational Database Management System using SQL includes MySQL, Microsoft SQL, and PostgreSQL.

Also read Types of Looping statements in C/C++

What should be the Beginner’s approach to learn SQL?

As a beginner one should start with the following topics:

1. Concept of Data Types

The following are the commonly used SQL data types:

• INT -> It represents a number without a decimal point e.g. 1, 2, 3, 11, etc.
• DEC -> It represents a fractional number without a decimal point e.g. 11.23, 88.555, etc.
• CHAR -> It represents a character string of fixed length e.g. ECE, CSE, ICE, EE, etc.
• VARCHAR -> It represents a character string of variable length e.g. Ravi, Mohan, etc.
• DATE -> It represents various standard data formats that can be added, subtracted, or compared.
• TIME -> It represents various standard time formats that can be added, subtracted, or compared.

2. SQL Commands

These are some of the most commonly used SQL commands:

• – – This is a single line comment
• /* This is a multiline comment */
• SHOW DATABASES: It shows all the available databases on the SQL server.
• CREATE DATABASE database_name: It creates a new database with the provided name.
• USE database_name: It uses the specific database for further query processing and operations.
• DROP DATABASE database_name: It is used to delete or drop a specific database.
• CREATE TABLE: It creates a table in the specific database schema.

CREATE TABLE student

 ( roll_no INT,

   name VARCHAR(30),

   email_id VARCHAR(25),

   branch CHAR(5),

   admission_date DATE);

• DROP TABLE table_name: It is used to delete or drop a specific table in the database.
• INSERT: It is used to insert a single record or multiple records as rows in the tables.

INSERT INTO batch2k21.student (roll_no, name, email_id, branch, admission_date)

VALUES

(86, "SANJAY KUMAR", "sanjayk.ec.19@nitj.ac.in", "ECE", 2019-06-11),

(02, "ABHISHEK KUMAR", "abhishekk.ee.19@nitj.ac.in", "EE'', 2019-06-05),

(101, "SHREYA MOHAN", "shreyam.ec.19@nitj.ac.in", "ECE", 2019-06-07);

• UPDATE: It is used to update single or multiple records in the tables.

UPDATE batch2k21.student SET branch = "IT" WHERE (roll_no = 41);

UPDATE batch2k21.student SET branch = "ME" WHERE (name = "RAHUL SINGH");

• SELECT: It is used to select/get only the desired records (rows of a table) by filtering the table data (rows) according to certain conditions.

-- This selects all the rows in the student table of batch2k21

SELECT * FROM batch2k21.student;

-- This selects only 10 rows in the student table of batch2k21

SELECT * FROM batch2k21.student limit 10;

-- This selects all rows in the student table of batch2k21 where branch is "ECE"

SELECT * FROM batch2k21.student WHERE branch = "ECE";

• DELETE: It is used to delete/drop single or multiple records (rows) of a table by filtering the table data (rows) according to certain conditions.

-- This deletes all the rows in the student table of batch2k21

DELETE * FROM batch2k21.student;

-- This deletes the rows with selected roll nos in the student table of batch2k21

DELETE * FROM batch2k21.student WHERE roll_no IN (10, 33, 76);

-- This deletes all rows in the student table of batch2k21 where branch is "ECE"

DELETE * FROM batch2k21.student WHERE branch = "ECE";

3. Constraints in SQL

The SQL constraints are the keywords that ensure the integrity of the database, also called Database Integrity Constraints.

These are the most commonly used SQL constraints:

• NOT NULL: It ensures that a column can not have NULL values i.e. it can not be left blank.
• UNIQUE: It ensures that any two or more columns cannot have the same values.
• DEFAULT: It specifies a default value to a column.
• CHECK: It imposes a limit on the column values that can be inserted into it.
• PRIMARY KEY: It declares a column or group of columns as the ^*primary key of the table.

A Primary Key is used to uniquely identify the records (rows) in a table and prevents redundancy (duplication of records).

CREATE TABLE student

 ( roll_no INT PRIMARY KEY,

   name VARCHAR(30) NOT NULL,

   email_id VARCHAR(25) UNIQUE ,

   branch CHAR(5) DEFAULT = 'NITJ',

   admission_date DATE CHECK (admission_date > 2020-12-31));

/* -> Student's Roll No. is declared as the Primary Key of the student table.
    -> A student record cannot be inserted without his/her name.
    -> Every Student will be provided with a unique Email-Id.
    -> By default branch of every student is initialized with college code 'NITJ'.
    -> Admission date must be after 31-Dec-2020. */

4. SQL Functions

There are so many functions provided by SQL to perform different operations on the data stored in the form of tables (relations).

The most commonly used SQL aggregate functions includes:

1. sum: It finds the sum total of a column’s values.
2. count: It counts the total no. of non-null values in a column.
3. avg: It finds the average of all the column values.
4. min: It finds the minimum of the column values.
5. max: It finds the maximum of the column values.

-- This counts the no. of students in the 2021 batch.

SELECT count(*) FROM batch2k21.student;


/* This counts the no. of students in the 2021 batch whose branch is Mechanical Engineering (ME) */

SELECT count(*) FROM batch2k21.student WHERE branch = "ME";


-- This returns the maximum or the highest roll_no in the ECE branch

SELECT max(roll_no) FROM batch2k21.student WHERE branch = "ECE";


-- This returns the minimum or the lowest roll_no in the CSE branch

SELECT min(roll_no) FROM batch2k21.student WHERE branch = "CSE";