SQL in one video

• DATABSE SOFTWARE

WE USE DATABASE SOFTWARE TO STORE DATA

• DATABSE LANGUAGE

WE USE DATABASE LANGUAGE (SQL) TO CONTROL THE

BEHAVIOR OF DATABASE SOFTWARE (MYSQL)

RDBMS PROGRAM
(RELATIONAL DATABASE MANAGEMENT

SYSTEM) RDBMS PROGRAM STORES THE

DATA AS TABLE FORMAT

No Nam Data- Data-

e 1 1
1 A -- --
2 B -- --

Channel: Different Developer Admin: Arsil Malek

SQL in one video

SQL
SQL STATEMENTS ARE KNOWN AS
SQL QUERY

1. Create database

CREATE DATABASE demo;

or
create database demo;

2. Delete database

drop database demo;

3. Create table

CREATE TABLE admission(

adm_no int(10),
student`s_name varchar(50),
mobile_no int(12)
);

4. Insert data in table

INSERT INTO admission(adm_no,student`s_name,mobile_no)

VALUES(1,”Arsil Malek”,12454889)

5. How to display data from database?

SELECT * FROM admission;
adm_no,students_name FROM admission;

6. How to apply conditions in select query ?

SELECT * FROM admission WHERE no = “1”;

SELECT students_name FROM admission WHERE no = “1”;

Channel: Different Developer Admin: Arsil Malek

SQL in one video

7. How to update data in table ?

UPDATE admission
SET no = “1”;

8. How to update data with conditions in table ?

UPDATE admission
SET students_name = “Arsil Malek”
WHERE no = “1”;

9. How to delete all row from table ?

DELETE FROM admission;

10. How to delete specific row from table ?

DELETE FROM
admission WHERE
no = “1”;

11. How to set limit during select ?

SELECT * FROM
admission LIMIT 2;

12. How to sort table data in descending order?

SELECT * FROM
admission ORDER BY no
DESC;

13. How to sort table data in ascending order?

SELECT * FROM
admission ORDER BY no
ASC;

Channel: Different Developer Admin: Arsil Malek

SQL in one video

14. How to display last row data?

SELECT * FROM admission

ORDER BY
no DESC LIMIT 1;

15. How to add column data?

SELECT SUM(adm_no)
FROM admission;

16. How to count column data ?

SELECT COUNT(adm_no)
FROM admission;

17. How to do duplication data sorting ?

SELECT DISTINCT students_name FROM admission;

18. How to do count duplication data sorting ?

SELECT COUNT(DISTINCT students_name) FROM admission;

OPERATORS FOR WHERE QUERY

1. = equal to
2. > greater than
3. < smaller than
4. >= greater than or equal
5. <= smaller than or equal
6. <> is not equal
7. BETWEEN
8. IN()
9. AND
10. OR
11. NOT
12. IN

Channel: Different Developer Admin: Arsil Malek

SQL in one video

Example

CREATE A TABLE product_list AND STORE THESE ITEMS

products price
HP 25000
DELL 20000
Samsung 54000
Apple 100000
acer 33000

Q1 * : how to display 20000 products ?

SELECT * FROM product_list

WHERE price = “20000”;

SELECT * FROM product_list

WHERE price IN (‘30000’,’20000’);

LIKE

Pattern Meaning

‘w%’ w at first position

‘%w’ w at last position

‘%w%’ w at any position

Channel: Different Developer Admin: Arsil Malek

SQL in one video

‘_w%’ w at second position

‘w%p’ w at first position and p at last position

‘*abc+%’ find string who starts from a, b and c

‘*a-s+%’ find string who starts from a to s

Example :

SELECT * FROM product_list WHERE product_name LIKE ‘w%’

19. How to create auto_increment column ?

CREATE TABLE demo(

adm_no INT(6) NOT NULL AUTO_INCREMENT,
s_name VARCHAR(50), PRIMARY KEY(adm_no)
);

20. How to apply case conditions ?

SELECT *, CASE
WHEN marks < 10 THEN “marks is
less than 10” WHEN marks > 10
THEN “marks is greater than 10”
WHEN marks = 10 THEN “marks is
equal to 10” ELSE : “some wrong”
END AS result
FROM result;

21. How to calculate min and max?

SELECT s_name, MIN(marks) AS poor_student;

SELECT s_name, MAX(marks) AS amazing_student;

CONCAT()

CREATE A STUDENT TABLE

name town district state country pincode

Channel: Different Developer Admin: Arsil Malek
SQL in one video

Arsil Gazipur baroda Gujarat India 387001

22. How to combined each other ?

Arsil,Gazipur, baroda, Gujarat, India 387001

SELECT CONCAT(name,”, ”,town,”, ”,district,”, ”,state,”, ”,country,”, ”,pincode) AS

permanent_address FROM students;

COPY TABLE DATA

INSERT INTO admission(s_name)

SELECT name FROM data;

COPY TABLE DATA WITH COLUMN NAME

CREATE TABLE new AS

SELECT * FROM admission;

INSERT MULTIPLE VALUES IN A COLUMN AT ONE TIME

INSERT INTO admission(s_name) VALUES(“arsil”),(“humayu”);

TABLE RELATION

Admission

adm_no name
1 Arsil Malek
2 Humayu Shaikh
4 Montu
8 Dipak

Fee

adm_no fee
1 500
2 400
3 600
6 700

Channel: Different Developer Admin: Arsil Malek

SQL in one video

JOIN OPERATIONS

1. INNER JOIN
SELECT admission.name, name fee
fee.fee FROM admission a 500
INNER JOIN fee b 200
ON admission.adm_no = fee.adm_no;

2. LEFT JOIN

SELECT admission.name, name fee

fee.fee FROM admission a 500
LEFT JOIN fee b NULL
ON admission.adm_no = fee.adm_no;

3. RIGHT JOIN
name fee
SELECT admission.name,
a 500
fee.fee FROM admission NULL 200
RIGHT JOIN fee
ON admission.adm_no = fee.adm_no;

4. FULL JOIN (not supported in mysql) name fee

SELECT admission.name, fee.fee FROM admission a 500
FULL OUTER JOIN fee NULL NULL
ON admission.adm_no = fee.adm_no;

5. SELF JOIN

Mem_id Mem_name Rel_id

1 Arsil 2
2 Montu 3
3 Humayu 1

Output :-

Mem_name Rel_member
Arsil Montu
Montu Humayu
Humayu Arsil

Channel: Different Developer Admin: Arsil Malek

SQL in one video

SELECT a.mem_name AS member_name,b.mem_name AS related_member

FROM members

AS a INNER JOIN

members AS b ON

a.rel_id =

b.mem_id

GROUP DUPLICATE DATA

CREATE TABLE classes

adm_no s_name course

1 Salman bca
2 Arsil mca
3 Misbah bca

➔ SELECT
course
FROM
admission
GROUP
BY course

(Apply conditions in group)

- SELECT
count(adm_no),course
FROM admission
GROUP BY course
HAVING COUNT(adm_no) > 6

CONSTRAINTS

1. NOT NULL : remove null when field is empty

2. PRIMARY KEY : create unique key when auto_increment

3. UNIQUE : it also creates unique key

4. FOREIGN KEY : works on the basis of primary key

Ex : FOREIGN KEY (adm_no) REFERENCES admission(adm_no)

Channel: Different Developer Admin: Arsil Malek

SQL in one video

5. CHECK : apply conditions for a particular column [not supported in mysql]

6. DEFAULT : set default value in column

PRIMARY KEY VS UNIQUE KEY

PRIMARY KEY UNIQUE KEY

ONLY ONE IN TABLE YOU CAN MORE THAN ONE
ONLY NOT NULL ACCEPTED ACCEPTED NULL VALUES

Mysql DATA TYPES

DATA TYPE MAX LENGTH

VARCHAR 65535
BLOB 65535 BYTES
MEDIUMBLOB 16MB
LONGBLOB 4GB
INT 255
FLOAT 0 – 23
DOUBLE 24 - 53
DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MM:SS
TIME HH:MM:SS
YEAR YYYY

THANK YOU

Channel: Different Developer Admin: Arsil Malek