Learn Structured Query Language (SQL) With Me

Hello everyone, my name is Rishabh Deshpande. Today, we will explore SQL from the basics to advanced concepts in the simplest and easiest way. Let’s dive in!

What is Database and there types ?

A database is a structured collection of data that is stored and managed in a way that allows for easy access, retrieval, and manipulation. Think of it like a digital filing system where information is organized into tables, much like folders in a filing cabinet. Each table holds related data in rows (records) and columns (fields), making it easy to find and use the information you need. Databases are used in a wide range of applications, from websites and apps to business operations, to store everything from customer information to inventory details.

There are two types of database i.e relational and non relatinal types database

Relational	Non-Relational
Use tables to store data in rows and columns	Designed for unstructured or semi-structured data. Includes document, key-value, column-family, and graph databases.(no-SQL)
ex: MySQL, PostgreSQL, Oracle, SQL Server	ex: MongoDB, Cassandra, Redis, CouchDB

What is Datatype ?

A data type in SQL (Structured Query Language) is an attribute that specifies the type of data that can be stored in a column of a database table.

Data types help define the operations that can be performed on the data, how it is stored, and how it is retrieved. They are crucial for ensuring data integrity, optimizing storage, and improving query performance.

DATATYPE	DESCRIPTION	USAGE
CHAR	string(0-255), can store characters of fixed length	CHAR(50)
VARCHAR	string(0-255), can store characters up to given length	VARCHAR(50)
BLOB	string(0-65535), can store binary large object	BLOB(1000)
INT	integer(-2,147,483,648 to 2,147,483,647)	INT
TINYINT	integer(-128 to 127)	TINYINT
BIGINT	integer(-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)	BIGINT
BIT	can store x-bit values. x can range from 1 to 64	BIT(2)
FLOAT	Decimal number – with precision to 23 digits	FLOAT
DOUBLE	Decimal number – with 24 to 53 digits	DOUBLE
BOOLEAN	Boolean values 0 or 1	BOOLEAN
DATE	date in format of YYYY-MM-DD ranging from 1000-01-01 to 9999-12-31	DATE
YEAR	year in 4 digits format ranging from 1901 to 2155	YEAR

unsigned : TINYINT UNSIGNED (0 To 255)

What are the types of SQL commands

SQL (Structured Query Language) commands are used to interact with and manipulate databases. They are categorized into different types based on their functionality. Here is a detailed breakdown of SQL command types:

Type of SQL Command	Full Form	Description	Commands
DDL	Data Definition Language	Defines and modifies the structure of database objects	create, alter, rename, truncate, drop
DQL	Data Query Language	Retrieves data from the database	select
DML	Data Manipulation Language	Manipulates data within existing database objects	select, insert, update, delete
DCL	Data Control Language	Controls access to data in the database	grant, revoke
TCL	Transaction Control Language	Manages transactions in the database	start transaction, commit, rollback, etc.

This table provides a clear and organized view of the types of SQL commands, their full forms, descriptions, and examples of each type.

Let’s learn about all basic functions

Before starting we must know that SQL is not case sensitive language. Let’s start the process.

CREATE BASIC STRUCTURE OF TABLE : In this we will see basic structure of table, once we done by creating table then we will learn each and every term in detail.

BASIC SYNTAX

USE db_name;

CREATE TABLE table_name (
column_name1 datatype constraint,
column_name2 datatype constraint,
column_name2 datatype constraint
);

CREATE DATABASE college;

USE college;

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT NOT NULL
);

INSERT INTO student VALUES(1, "Rishabh", 26);
INSERT INTO student VALUES(2, "Rohit", 26
INSERT INTO student VALUES(2, "Manthan", 26);

SELECT * FROM student;

DATATYPE : In SQL, data types define the type of data that can be stored in a column of a table. They are essential for defining the kind of data that a column can hold, ensuring data integrity and optimizing storage.

DATABASE RELATED QUERIES :

CREATE DATABASE IF NOT EXISTS college;

It is best practice to add a database to the server carefully to avoid conflicts with existing databases. To prevent errors if a database already exists, use the following query:

Similarly , We can use the DROP DATABASE IF NOT EXISTS college ;

TABLE RELATED QUERIES : It helps us to design (schema) of the table.

CREATE

BASIC SYNTAX
CREATE TABLE table_name (
column_name1 datatype constraint,
column_name2 datatype constraint,
);

INSERT

BASIC SYNATX
INSERT INTO table_name
(colname1, colname2);
VALUES
(col1_v1, col2_v1),
(col1_v2, col2_v2);

PRIMARY KEY : It is a column (or set of columns) in a table that uniquely identifies each row. (a unique id) There is only 1 PK & it should be NOT null.

FOREIGN KEY : A foreign key is a column (or set of columns) in a table that refers to the primary key in another table. There can be multiple FKs. FKs can have duplicate & null values.

CONSTRAINS : SQL constraints are used to specify rules for data in a table.

Constraint	Description
NOT NULL	Values cannot be null.
UNIQUE	Values cannot match any older value.
PRIMARY KEY	Used to uniquely identify a row.
FOREIGN KEY	References a row in another table.
CHECK	Validates condition for new value.
DEFAULT	Sets default value if not passed.

FOREIGN KEY : prevent actions that would destroy links between tables.

Customers and Orders. The Orders table will have a foreign key that references the primary key in the Customers table.

Lets practice different commands and queries.

CREATE DATABASE COLLEGE;
USE COLLEGE ;

CREATE TABLE student (
    rollno INT PRIMARY KEY,
    name VARCHAR(50),
    marks INT NOT NULL,
    grade VARCHAR(1),
    city VARCHAR(20)
);

INSERT INTO student (rollno, name, marks, grade, city)
VALUES
    (101, "anil", 78, "C", "Pune"),
    (102, "bhumika", 93, "A", "Mumbai"),
    (103, "chetan", 85, "B", "Mumbai"),
    (104, "dhruv", 96, "A", "Delhi"),
    (105, "emanuelt", 12, "F", "Delhi"),
    (106, "farah", 82, "B", "Delhi");

SELECT: used to select any data from the database.

Basic Syntax

SELECT col1, col2 FROM table_name;

we can use clause with select command

Where Clause :

BASIC SYNTAX

SELECT col1, col2 FROM table_name WHERE conditions;

Where Clause	Using Operators in WHERE
Arithmetic Operators	+ (addition), – (subtraction), * (multiplication), / (division), % (modulus)
Comparison Operators	= (equal to), != (not equal to), >, >=, <, <=
Logical Operators	AND, OR, NOT, IN, BETWEEN, ALL, LIKE, ANY
Bitwise Operators	& (Bitwise AND), | (Bitwise OR)

LIMIT Clause : Sets an upper limit on number of (tuples)rows to be returned.

BASIC SYNTAX
SELECT col1, col2 FROM table_name LIMIT number;

ORDER BY Clause : To sort in ascending (ASC) or descending order (DESC)

SELECT col1, col2 FROM table_name ORDER BY col_name(s) ASC;

AGGREGATE FUNCTION : Aggregate functions perform a calculation on a set of values, and return a single value.

• COUNT( )
• MAX( )
• MIN( )
• SUM( )
• AVG( )

GROUP BY Clause : Groups rows that have the same values into summary rows. It collects data from multiple records and groups the result by one or more column.

NOTE : GROUP BY clause default use ASC order, If we want DESC order then we have to mention in query.

FOR EX : SELECT city, AVG(marks) FROM student ORDER BY city GROUP BY AVG(marks) DESC;

HAVING Clause : Similar to Where i.e. applies some condition on rows. Used when we want to apply any condition after grouping.

GENERAL ORDER TO WRITE A COMMANDS.

SELECT column(s)
FROM table_name
WHERE condition     -- WHERE apply condition on ROWS.
GROUP BY column(s)
HAVING condition    -- HAVING apply condition on group ROWS.
ORDER BY column(s) ASC;

TABLE RELATED QUERIES

• UPDATE (to update existing rows)

UPDATE table_name
SET col1 = val1, col2 = val2
WHERE condition;

• DELETE (to delete existing rows)

DELETE FROM table_name
WHERE condition;

TABLE RELATED QUERIES :

• Alter : (to change the schema)

• ADD COLUMN

Syntax

ALTER TABLE table_name
ADD COLUMN column_name datatype constraint;

• DROP COLUMN

ALTER TABLE table_name
DROP COLUMN column_name;

• RENAME TABLE

ALTER TABLE table_name
RENAME TO new_table_name;

• CHANGE COLUMN (RENAME)

ALTER TABLE table_name
CHANGE COLUMN old_name new_name new_datatype new_constraint;

• TRUNCATE TABLE (to delete table’s data))

• INNER JOIN : Returns records that have matching values in both tables

Syntax
SELECT column(s)
FROM tableA
INNER JOIN tableB
ON tableA.col_name = tableB.col_name;

CREATE DATABASE example;
USE example;

CREATE TABLE Stu(
id INT PRIMARY KEY,
name VARCHAR(50)
); 
INSERT INTO stu(id,name)
VALUES
(100,"rishabh deshpande"),
(101,"manthan rao"),
(102,"rohit gajbhiye");

CREATE TABLE course(
id INT PRIMARY KEY,
course VARCHAR(50)
);

INSERT INTO course(id,course)
VALUES
(101,"science"),
(102,"mathematics"),
(104,"biology");

• LEFT (OUTER )JOIN : Returns all records from the table A , and the matched records from the table B.

• RIGHT (OUTER) JOIN : Returns all records from the table B, and the matched records from the table A.

• FULL (OUTER) JOIN :- Returns all records when there is a match in either A or B