SQL-> Structured Query Language

Before jumping into SQL we need to know about database.

What is Database?

Database is defined as the collection of data that can be accessed digitally.

What is DBMS?

DBMS-> Database management System

DBMS is a software that is used to manage database. It stores the data in tables

Relational Database also known as Relational database management system.

WHAT IS SQL?

SQL-> STRUCTURED QUERY LANGUAGE

SQL is important topic for placements and for data related fields.

SQL is a programming language used to interact with relational database.

Used to perform CRUD operations

C- CREATE

R- READ

U- UPDATE

D- DELETE

Important SQL commands are:

1. DDL

2. DQL

3. DML

4. TQL

DDL-> Data Definition Language

-CREATE

-TRUNCATE

-RENAME

-DROP

-ALTER

DQL-> Data QUERY Language

-SELECT

DML-> Data manipulation language

-INSERT

-DELETE

-UPDATE

TCL-> Transaction Control Language

-ROLLBACK

-COMMIT

-SAVEPOINT

DCL-> Data Control Language

-REVOKE

-GRANT

How to create a Database in MySQL?

-CREATE DATABASE (database_name)

-USE (database_name)

HOW TO CREATE A TABLE?

-CREATE TABLE table_name(Column_name datatype constraint);

Insert values into tables

-INSERT INTO (table_name) VALUES();

Aggregate functions:

Functions are like blocks of code used to execute statements

-MIN()

-MAX()

-AVG()

-SUM()

-COUNT()

SELECT -> used to view the tables

CLAUSES

WHERE- used for conditions

ORDER BY- used to view in ascending or descending (ASC or DESC)

GROUP BY- used to group two or more columns

LIMIT- sets an upper limit on the number

HAVING- similar to WHERE used to apply any condition after grouping.

WHAT IS THE GENERAL ORDER TO BE FOLLOWED IN MYSQL?

-SELECT

-FROM

-WHERE

-GROUP BY

-HAVING

-ORDER BY

Operators

Arithmetic operators-> +,-,*,/,%

Logical operators-> AND, OR, NOT, IN , LIKE, ALL, BETWEEN, ANY

Comparison Operators-> =,!=,>,<,>=,<=

Bitwise Operators-> &(Bitwise AND) , |(BITWISE OR)

KEYS

WHAT IS PRIMARY KEY?

-It is a column (or set of columns) in a table that uniquely identifies each row(a unique id). There is only one primary key & it should not be NULL.

WHAT IS FORIEGN KEY?

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

DIFFERENCE BETWEEN DROP VS TRUNCATE

• TRUNCATE-> It is used to delete the data present in the table

• DROP -> It is used to delete the entire table

JOINS IN SQL

-INNER JOIN

-OUTER JOIN

• RIGHT JOIN

• LEFT JOIN

• FULL JOIN

WHAT IS MYSQL VIEWS?

• MYSQL views is a virtual table or temporary table.

• A view is a virtual table based on the result set of an sql statement.

-CREATE VIEW view1 AS SELECT column_name FROM table_name

-SELECT * FROM view1;

WHAT IS CASCADING?

-ON DELETE CASCADE

-ON UPDATE CASCADE

ON DELETE CASCADE

• When we create a foreign key using this option, it deletes the referencing rows in the child table when referenced row is deleted in the parent table which has the primary key.

ON UPDATE CASCADE

• When we create a foreign key using cascade the referencing row are updated in the child table when the referenced row is updated in the parent table which has the primary key.

CREATE TABLE table_name(

column_name datatype constraint

column_name2 datatype constraint

FOREIGN KEY (column_name) REFERENCES (column_name)

ON DELETE CASCADE

ON UPDATE CASCADE);

---