SQL stands for structured query language. it is the standard language for managing relational databases. it allows users to interact with a database by creating,reading,updating abd deleting data stored in tables. it is not only used by software developer but it is also widely used by quality analyst,data analyst,business analyst,data engineer and many more.

=> performs opeartions such as insertion,retrival,modification and deletion

=> used to interact databases and structure and manipulate data stored in tables.

=> used to create,modified and delete databases,tables,indexex,views and other objects.

=> manages relationships between tables and enforces data integrity with constraints.

=> it can also be used to analyze and protect data.

you can download mysql workbench and preform all operations.

SQL can be classified into four types

1.DDL(Data Defination Language)

2.DML (Data Manipulation language)

3.DCL (Data Control languaga)

4.TCL (Transaction Control Language)

1.DDL

it is used to define database schema(table) and specify the structure of the database.

commands:CREATE,ALTER,DROP,TRUNCATE

syntax:

CREATE DATABASE db_name;
CREATE TABLE table_name (column_name1 data_type,column_name2 data_type,.......);
ALTER TABLE table_name ADD column_name data_type;
DROP TABLE table_name;
TRUNCATE TABLE table_name;

create:used to create a new table,view or index

alter:used to modify structure of table

drop:used to delete a table,view or index.

2.DML

it is used to manage the data stored in the database.

commands:SELECT,INSERT,UPDATE,DELETE,MERGE

syntax:

SELECT * FROM table_name;
SELECT * FROM table_name WHERE country="Nepal" 
SELECT column_name1,column_name2,...... FROM table_name;
SELECT DISTINCT column_name1,column_name2,...... FROM table_name;
SELECT * FROM Customer WHERE country="Nepal" AND (OR) city="Kathmandu"
SELECT * FROM Customer WHERE NOt country="Nepal"
SELECT * FROM Customer ORDER BY Country,CustomerName DESC;
SELECT * FROm Customer WHERE Address IS NOT NULL;
SELECT * FROM Customer LIMIT 3;
SELECT * FROM Customer where name="Amrit" LIMIT 3;



INSERT INTO table_name (column_name1,column_name2,.....) VALUES (value1,value2,...);
INSERT INTO table_name VALUES(valueq,value2) --you can give directly as well but need to remember position

UPDATE table_name SET column_name1=value1,column_name2=value2,... WHERE condition;
UPDATE table_name SET column_name=value -- it will update all data as you donot mention WHERE condition

DELETE FROM table_name WHERE condition;
DELETE FROM table_name; --to delete all record

3.DCL

it is used to control access to the data stored in the databases.

commands:GRANT,REVOKE

syntax:

GRANT privilege_name ON object_name TO user_name;
REVOKE privilege_name ON object_name FROM user_name;

4.TCL

it is used to manage transactions in the databases.

commands:COMMIT,ROLLBACK,SAVEPOINT

syntax:

COMMIT;
ROLLBACK:
SAVEPOINT savepoint_name;

View

A view ina database management system is a virtual table that is derived from one or more tables. views provide a way to access data in a database without having to physically store the data in a separate table.

view can be used to:

=> Simplify complex queries by breaking them down into samller,more manageable pieces.

=> Provide a way to access data that is otherwise difficult or impossible to access.

=> Restrict access to sensitive data by presenting only subset of the data in a table.

=> Simplify data maintenance by encpasulating complex data structures and relationships.

Assertions and Triggers

it is used to enforce constraints and ensure data integrity.

Assertions:

Statement defining constraint on data in databases,system checks data to ensure it meets constraint.

Triggers:

A set of instractions automatically executed when specified event occurs in a database,used to enforce constraints and ensure data integrity by executing actions.

Relation Algebra

Type of relation algebra:

1.Unary relation algebra

These operation operate in single relation and include:

=> SELECT: filters the rows of a relation based on conditions.

=> PROJECT: selects a subset of the colums in a relation

2.Binary relation Algebra

These operations operate on two relations and includes:

=>JOIN: combines two relations based on a common relation.

=>UNION: combine two relations into single relation.

=>INTERSECTION: returns the rows that are common to two relations.

=>DIFFERENCE: returns the rows that are in one relation but not in another.

Aggregate functions

=>SUM: calculates the sum of value in a column.

=>AVG: calculates the average of value in a column.

=>MIN: returns  the minimum value in a column.

=>MAX: return maximum value in a column.

Query Cost Estimation: