mydb.sql

-- DATA DEFINITION LANGUAGE
USE mydb;
DROP TABLE STUDENT;

SELECT * FROM STUDENT;
CREATE TABLE STUDENT
(SNO INT(5),
SNAME VARCHAR(255),
MARKS INT(5));

SELECT * FROM STUDENT;
INSERT INTO STUDENT(SNO,SNAME,MARKS)
VALUES
(101,'PRAKHAR',88),
(102,'JOHN',88),
(103,'SAURABH',88),
(104,'ASHISH',88),
(105,'RAJ',88),
(106,'ROHIT',88),
(107,'DAVE',88),
(108,'AYUSH',98);


COMMIT;

-- ALTER
-- 	-> ADD NEW COLUMN
--  -> DROP A COLUMN
--  -> MODIFYING EXISTING COLUMN
--  -> REMANE THE COLUMN

DESCRIBE STUDENT;
ALTER TABLE STUDENT ADD(AGE INT(2));
ALTER TABLE STUDENT DROP COLUMN AGE;
ALTER TABLE STUDENT MODIFY COLUMN SNAME VARCHAR(20);
ALTER TABLE STUDENT RENAME COLUMN SNAME TO STUNAME;

-- Delete- Tables will not be removed but data will be temporary deleted, we can rollback furthur.

SET autocommit=0;
SET sql_safe_updates=0;
SELECT * FROM STUDENT;
DELETE FROM STUDENT;
-- COMMIT;- If we use this command then the data will not be retrieved that means rollback function will not work
ROLLBACK;

-- TRUNCATE- Tables will not be removed but data will be peramanently get deleted, we can;t rollback furthur.

TRUNCATE TABLE STUDENT;

-- DROP- Tables and data all will be deleted

DROP TABLE STUDENT;

-- Rename a table

USE WORLD;
RENAME TABLE CITIES TO CITY;

UPDATE STUDENT
SET MARKS='100' WHERE SNO=108;

-- Set Operators


CREATE TABLE STUDENT2 (MARKS INT(5));
SELECT * FROM STUDENT2;
INSERT INTO STUDENT2(MARKS) VALUES (70), (80), (98), (74), (75), (76), (100), (94);

SELECT MARKS FROM STUDENT UNION SELECT MARKS FROM STUDENT2;

-- For Duplicate values UNION ALL

SELECT MARKS FROM STUDENT UNION ALL SELECT MARKS FROM STUDENT2;

-- Inner Join

SELECT * FROM STUDENT;
SELECT * FROM STUDENT2;

SELECT * FROM STUDENT AS ST1
INNER JOIN STUDENT2 AS ST2
ON ST1.MARKS= ST2.MARKS
ORDER BY SNO ASC;

-- Left Join

SELECT * FROM STUDENT AS ST1
LEFT JOIN STUDENT2 AS ST2
ON ST1.MARKS= ST2.MARKS
ORDER BY SNO ASC;

-- Right Join

SELECT * FROM STUDENT AS ST1
LEFT JOIN STUDENT2 AS ST2
ON ST1.MARKS= ST2.MARKS
ORDER BY SNO ASC;

-- Full Outer Join

-- Left Join

SELECT * FROM STUDENT AS ST1
LEFT JOIN STUDENT2 AS ST2
ON ST1.MARKS= ST2.MARKS

UNION

SELECT * FROM STUDENT AS ST1
LEFT JOIN STUDENT2 AS ST2
ON ST1.MARKS= ST2.MARKS
ORDER BY SNO DESC;