Part:1 SQL(Structured Query Language)

13 min readApr 28, 2023

SQL:-

it is a programming language used to manage and manipulate relational databases. SQL is used to perform various operations on databases, such as creating, modifying, and deleting tables, inserting, updating and deleting data, and retrieving data from one or more tables based on various criteria.

SQL is also called the sequel to CLI language (Common Language Interface). This is the only language that can be used to communicate with any RDBMS (Relational Database Management System) product.

SQL is not Case Sensitive language that we Can write sql predefined queries or syntaxes in any Case characters. (either upper or lower).

Every SQL statement should ends with a Semicolon but it is optional in SQL server

Sublanguages of SQL

1. Data Definition language (DDL)

Create
Alter
Truncate
Drop

2. Data Manupulation language (DML)

Insert
Update
Delete

3. Data Query Language (DQL)

Select

4. Transaction Control language (TCL)

Commit
ROLLBack
Savepoint

5. Data Control Language ( DCL)

Grant
Revoke

1. Data Definition language (DDL)

This Language Commands are using to define, modify & drop on object of database from SQL server.

a. Create

Creating a new database or new table in SQL server

step 1: Create a new database in SQL server

syntax:- Create database < DB NAME > ;

Ex: Create database mydb ;

Step 2: Select the required database from SQL server

syntax:- Use <DB Name>

Ex: USe mydb;

Step 3: Create new table in database

syntax: create table <Table Name> [<column_Name1> <Data_Type><size>, <column_Name2> <Data_Type><size>,……….];

Ex: CREATE TABLE customers (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100) );

Step 4: To view the structure of table

syntax: DESCRIBE <Table_Name> ;

Ex:- SHOW COLUMNS FROM customers;

2. ALTER

To change or modify the structure of a table or a database. The ALTER TABLE statement is used to change the definition or structure of an existing table

Syntax:

ALTER TABLE “table_name”

[Specify Actions];

Following actions can be performed

Columns — Add, Delete (Drop), Modify or Rename
Constraints — Add, Drop
Index — Add, Drop

COLUMN — ADD & DROP

The basic syntax of an ALTER TABLE command to add/drop a Column in an existing table is as follows.

Syntax

ALTER TABLE “table_name”
ADD “column_name” “Data Type”;

ALTER TABLE “table_name”

DROP “column_name”;

COLUMN — MODIFY & RENAME

The basic syntax of an ALTER TABLE command to Modify/Rename a Column in an existing table is as follows.

Syntax

ALTER TABLE “table_name”
ALTER COLUMN “column_name” TYPE “New Data Type”;

ALTER TABLE “table_name”
RENAME COLUMN “column 1” TO “column 2”;

CONSTRAINT — ADD & DROP

The basic syntax of an ALTER TABLE command to add/drop a Constraint on a existing table is as follows.

Syntax

ALTER TABLE “table_name“ ALTER COLUMN “column_name” SET NOT NULL;
ALTER TABLE “table_name“ ALTER COLUMN “column_name” DROP NOT NULL;
ALTER TABLE “table_name” ADD CONSTRAINT “column_name” CHECK (“column_name”>=100);
ALTER TABLE “table_name” ADD PRIMARY KEY (“column_name”);
ALTER TABLE “child_table” ADD CONSTRAINT “child_column”FOREIGN KEY (“parent column”) REFERENCES “parent table”;

3. TRUNCATE

TRUNCATE is a SQL command that is used to delete all rows from a table. It is similar to the DELETE command, but it is faster because it does not log the individual row deletions. The TRUNCATE command can only be used on tables and not on views. Once a table is truncated, it cannot be undone, and all data in the table is permanently deleted.

Syntax: TRUNCATE TABLE table_name;

Example: TRUNCATE TABLE customers;

Note:

TRUNCATE TABLE is a DDL (Data Definition Language) operation, and it requires the DROP privilege on the table.
TRUNCATE TABLE cannot be rolled back.
TRUNCATE TABLE is faster and uses fewer system resources than DELETE.
TRUNCATE TABLE resets the identity counter of the table to its seed value.

4. DROP

DROP is a SQL command used to remove a database object like a table, view, or stored procedure. Once an object is dropped, it is permanently deleted and cannot be recovered. Therefore, it is important to exercise caution while using the DROP command.

Syntax: DROP TABLE table_name; DROP VIEW view_name; DROP PROCEDURE procedure_name;

Example: DROP TABLE customers;

Note:

DROP is a DDL (Data Definition Language) operation, and it requires the DROP privilege on the object being dropped.
The DROP command cannot be rolled back.
Be careful while using the DROP command, as it permanently deletes the object and all its data.
Before dropping an object, ensure that it is not being used by other database objects or applications.

2. Data Manipulation language (DML)

This Language commands are used to change or manipulate data in database table.

INSERT

INSERT is a SQL command used to add data to a database table. It is used to insert a new row or multiple rows of data into an existing table. The INSERT command is flexible and can be used to insert data into specific columns or all columns of a table.

Syntax1 : INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);

Example: INSERT INTO customers (id, first_name, last_name, email) VALUES (1, ‘John’, ‘Doe’, ‘johndoe@example.com’);

for multiple rows

Ex:- INSERT INTO users (id, username, password)
VALUES
(1, ‘john’, ‘password1’),
(2, ‘jane’, ‘password2’),
(3, ‘bob’, ‘password3’);

Syntax2 : INSERT INTO table_name VALUES (value1, value2, value3, …);

Ex: INSERT INTO employees VALUES (1, ‘John’, ‘Doe’, 50000);

UPDATE

The SQL UPDATE Query is used to modify the existing records in a table.

Syntax

UPDATE “table_name”
SET column_1 = [value1], column_2 = [value2], … WHERE “condition”;

Example

➢ Single row (with column names specified) UPDATE Customer_table
SET Age = 17, Last_name = ‘Pe’
WHERE Cust_id = 2;

➢ Multiple rows
UPDATE Customer_table
SET email_id = ‘gee@xyz.com
WHERE First_name = ‘Gee’ or First_name = ‘gee’;

Syntax

DELETE FROM “table_name” WHERE “condition”;

Example

➢ Single row
DELETE FROM CUSTOMERS WHERE ID = 6;

➢ Multiple rows
DELETE FROM CUSTOMERS WHERE age>25;

➢ All rows
DELETE FROM CUSTOMERS;

Difference between deleter and truncate

3. Data Query Language (DQL)

Select

The SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets.

Syntax

SELECT “column_name1”, “column_name2”, “column_name3” FROM “table_name”;

SELECT * FROM “table_name”;

Example

􏰀 Select one column
SELECT first_name FROM customer_table;

􏰀 Select multiple columns
SELECT first_name, last_name FROM customer_table;

􏰀 Select all columns
SELECT * FROM customer_table;

SELECT DISTINCT

The DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.

Syntax

SELECT DISTINCT “column_name” FROM “table_name”;

Example

Select one column
SELECT DISTINCT customer_name FROM customer_table;
Select multiple columns
SELECT DISTINCT customer_name, age FROM customer_table;

WHERE

The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table.

Syntax

SELECT “column_name” FROM “table_name” WHERE “condition”;

Example

Equals to condition
SELECT first_name FROM customer_table WHERE age = 25;
Less than/ Greater than condition
SELECT first_name, age FROM customer_table WHERE age>25;
Matching text condition
SELECT * FROM customer_table WHERE first_name 􏰁 􏰃􏰄􏰅

AND & OR

The SQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called as the conjunctive operators.

Syntax

SELECT “column_name”
FROM “table_name”
WHERE “simple condition”
{ [AND|OR] “simple condition”}+;

Example

SELECT first_name, last_name, age FROM customer_table
WHERE age>20
AND age< 30;
SELECT first_name, last_name, age FROM customer_table
WHERE age<20
OR age>30 OR first_name =’John’;

NOT

NOT condition is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

SELECT “column_name”
FROM “table_name”
WHERE NOT “simple condition”

Example

SELECT first_name,last_name, age FROM employee
WHERE NOT age=25
SELECT first_name,last_name, age FROM employee
WHERE NOT age=25
AND NOT first_name=’Jay’ ;􏰚􏰛􏰕􏰉

IN

IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

SELECT “column_name”
FROM “table_name”
WHERE “column_name” IN (‘value1’, ‘value2’, …);

􏰄Example

SELECT *
FROM customer
WHERE city IN (‘Philadelphia’ , ‘Seattle’)􏰎􏰆􏰏􏰐􏰑􏰒􏰓􏰐􏰔􏰆􏰏􏰑􏰕􏰖 􏰍􏰗􏰓􏰑􏰘􏰘􏰐􏰓􏰕􏰙
SELECT *
FROM customer
WHERE city = ‘Philadelphia’ OR city = ‘Seattle’ ;

BETWEEN

The BETWEEN condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

SELECT “column_name”
FROM “table_name”
WHERE “column_name” BETWEEN ‘value1’ AND ‘value2’;

Example

SELECT * FROM customer
WHERE age BETWEEN 20 AND 30;

Which is same as

SELECT * FROM customer WHERE age>= 20 AND age<= 30;
SELECT * FROM customer
WHERE age NOT BETWEEN 20 and 30;
SELECT * FROM sales
WHERE ship_date BETWEEN 2015–04–01 AND 2016–04–01';

LIKE

The SQL LIKE condition allows you to perform pattern matching using Wildcards.

Syntax

SELECT “column_name”
FROM “table_name”
WHERE “column_name” LIKE {PATTERN};

{PATTERN} often consists of wildcards

Example

A% means starts with A like ABC or ABCDE

%A means anything that ends with A
A%B means starts with A but ends with B

AB_C mean string start with AB, then there is one character, then there is C

Example

SELECT * FROM customer_table WHERE first_name LIKE ‘JO%’;􏰠 􏰵􏰄􏰅􏰶􏰕􏰉

SELECT * FROM customer_table WHERE first_name LIKE ‘%od%’;􏰅􏰒􏰶􏰕􏰉

SELECT first_name, last_name FROM customer_table WHERE first_name LIKE ‘Jas_n’;

SELECT first_name, last_name FROM customer_table WHERE last_name NOT LIKE ’J%’;􏰣􏰟 􏰞􏰊􏰩􏰠 􏰵􏰄􏰶􏰕􏰉

SELECT * FROM customer_table WHERE last_name LIKE ‘G\%’;

ORDER BY

The ORDER BY clause is used to sort the records in result set. It can only be used in SELECT statements.

Syntax

SELECT “column_name”
FROM “table_name”
[WHERE “condition”]
ORDER BY “column_name” [ASC, DESC];

It is possible to order by more than one column.
ORDER BY “column_name1” [ASC, DESC], “column_name2” [ASC, DESC]

Example

SELECT * FROM customer WHERE state = ‘California’ ORDER BY Customer_name;

Same as

SELECT * FROM customer WHERE state = ‘California’ ORDER BY Customer_name ASC;
SELECT * FROM customer ORDER BY 2 DESC;
SELECT * FROM customer WHERE age>25 ORDER BY City ASC, Customer_name DESC;
SELECT * FROM customer ORDER BY age;

LIMIT

LIMIT statement is used to limit the number of records returned based on a limit value.

Syntax

SELECT “column names” FROM “table name” [WHERE conditions]

[ORDER BY expression [ ASC | DESC ]] LIMIT row_count;

Example

SELECT * FROM customer WHERE age >= 25 ORDER BY age DESC LIMIT 8;

SELECT * FROM customer WHERE age >=25 ORDER BY age ASC LIMIT 10;

AS

The keyword AS is used to assign an alias to the column or a table. It is inserted between the column name and the column alias or between the table name and the table alias.

Syntax

SELECT column_name” AS “column_alias” FROM “table_name;

Example

SELECT Cust_id AS ‘Serial_No’ Customer_name as name, Age as Customer_age FROM Customer ;

Aggregate Commands

Count
Sum
Average
Min/Max

COUNT

Count function returns the count of an expression

Syntax

SELECT “column_name1”, COUNT (“column_name2”) FROM “table_name”

Example

SELECT COUNT(*) FROM sales;
SELECT COUNT (order_line) as “Number of Products Ordered”, COUNT (DISTINCT order_id) AS “Number of Orders” FROM sales WHERE customer_id = ‘CG-12520’;

SUM

Sum function returns the summed value of an expression

Syntax

SELECT sum(aggregate_expression) FROM tables
[WHERE conditions];

Example

SELECT sum(Profit) AS “Total Profit” FROM sales;
SELECT sum(quantity) AS “Total_Quantity” FROM orders where product_id=’FUR-TA-1000057' ;

AVERAGE

AVG function returns the average value of an expression.

Syntax

SELECT avg(aggregate_expression) FROM tables
[WHERE conditions];

Example

SELECT avg(age) AS “Average Customer Age” FROM customer;
SELECT avg(sales * 0.10) AS “Average Commission Value” FROM sales;

MIN/MAX

MIN/MAX function returns the minimum/maximum value of an expression.

Syntax

SELECT min(aggregate_expression) FROM tables
[WHERE conditions];

SELECT max(aggregate_expression) FROM tables
[WHERE conditions];

Example

SELECT MIN(sales) AS Min_sales_June15
FROM sales
WHERE order_date BETWEEN ‘2015–06–01’ AND ‘2015–06–30’;
SELECT MAX(sales) AS Min_sales_June15
FROM sales
WHERE order_date BETWEEN ‘2015–06–01’ AND ‘2015–06–30’;

GROUP BY

GROUP BY clause is used in a SELECT statement to group the results by one or more columns.

Syntax

SELECT “column_name1”, “function type” (“column_name2”) FROM “table_name”
GROUP BY “column_name1”;

Example

SELECT region, COUNT (customer_id) AS customer_count FROM customer GROUP BY region;
SELECT product_id, SUM (quantity) AS quantity_sold FROM sales GROUP BY product_id ORDER BY quantity_sold DESC;
SELECT customer_id, MIN(sales) AS min_sales, MAX(sales) AS max_sales, AVG(sales) AS Average_sales, SUM(sales) AS Total_sales FROM sales GROUP BY customer_id ORDER BY total_sales DESC LIMIT 5;

HAVING

HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE

Syntax

SELECT ColumnNames, aggregate_function (expression) FROM tables
[WHERE conditions]
GROUP BY column1 HAVING condition;

Example

SELECT region, COUNT(customer_id) AS customer_count FROM customer
GROUP BY region
HAVING COUNT(customer_id) > 200 ;

Conditional Statement

CASE

The CASE expression is a conditional expression, similar to if/else statements

Syntax

CASE WHEN condition THEN result

[WHEN …]

[ELSE result]

END

CASE expression
WHEN value THEN result

[WHEN …]
[ELSE result]

END

Example

SELECT *,
CASE WHEN age< 30 THEN ‘Young’

WHEN age>60 THEN ‘Senior Citizen’

ELSE ‘Middle aged’

END AS Age_category

FROM customer;

JOINS

JOINS are used to retrieve data from multiple tables. It is performed whenever two or more tables are joined in a SQL statement.

TYPES

INNER JOIN (or sometimes called simple join)
LEFT OUTER JOIN (or sometimes called LEFT JOIN)
RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
FULL OUTER JOIN (or sometimes called FULL JOIN)
CROSS JOIN (or sometimes called CARTESIAN JOIN)

INNER JOIN

INNER JOIN compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join- predicate. When satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example

SELECT employees.name, employees.department, salaries.salary
FROM employees
INNER JOIN salaries
ON employees.id = salaries.id;

The result of this query will be:

LEFT JOIN

The LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.

Syntax

SELECT table1.column1, table2.column2…
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

SELECT Customers.first_name, Customers.last_name, Orders.order_date
FROM Customers
LEFT JOIN Orders ON Customers.id = Orders.customer_id
ORDER BY Customers.last_name;

This will return a table like this:

RIGHT JOIN

The RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.

Syntax

SELECT table1.column1, table2.column2…
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

SELECT *
FROM Orders
RIGHT JOIN Customers
ON Orders.customer_id = Customers.id;

This will return a table like this:

FULL OUTER JOIN

The FULL JOIN combines the results of both left and right outer joins

Syntax

SELECT table1.column1, table2.column2…
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

table1

id | name | age
— -+ — — — + — — -
1 | Bob | 30
2 | Tom | 25
3 | Sue | 35

table2

id | address | phone
— -+ — — — — — -+ — — — — —
1 | New York | 555–1234
4 | Chicago | 555–5678
5 | Seattle | 555–9999

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.id = table2.id;

Then the result of the above query would be:

id | name | age | address | phone
— -+ — — — + — — -+ — — — — — -+ — — — — —
1 | Bob | 30 | New York | 555–1234
2 | Tom | 25 | NULL | NULL
3 | Sue | 35 | NULL | NULL
4 | NULL | NULL| Chicago | 555–5678
5 | NULL | NULL| Seattle | 555–9999