List of all commands (with detailed info) for the SQL workshop . Happy Querying :) Feel free to post any queries

sql_commands_with_syntax_and_description.sql

-- is used for single line comment
/* some sql code */   -> is used for multiline comment


--CREATE A DATABASE
--SYNTAX : CREATE DATABASE db_name;
CREATE DATABASE STUDENT;



--SELECT DATABASE TO BE USED FOR QUERYING
--SYNTAX : USE db_name;
USE STUDENT;



--CREATE A TABLE
/*
	SYNTAX:
	CREATE TABLE table_name (
		column1 datatype,
		column2 datatype,
		column3 datatype,
		....
	);
*/
CREATE TABLE STUDENT_DETAILS (
	ROLL_NO INT PRIMARY KEY,
	NAME VARCHAR(10),
	ADDRESS VARCHAR(20),
	CONTACT_NO INT
);
--OR
CREATE TABLE STUDENTS (
	ROLL_NO INT,
	NAME VARCHAR(10),
	ADDRESS VARCHAR(20),
	CONTACT_NO INT, 
	PRIMARY KEY(ROLL_NO)
);


--VIEW STRUCTURE(i.e name of columns and data types assigned to them) OF CREATED TABLE
--SYNTAX : DESC table_name;
DESC STUDENTS;


--CREATING ANOTHER TABLE FOR STORING STUDENT MARKS
CREATE TABLE STUDENT_MARKS (
	ST_ID INT PRIMARY KEY AUTO_INCREMENT, --AUTOINCREMENTS VALUE OF ST_ID FROM 1
	ROLL_NO_M INT REFERENCES STUDENT_DETAILS (ROLL_NO),
	PHYSICS INT,
	CHEM INT,
	MATH INT
	PRIMARY KEY(ROLL_NO)
);
--OR
CREATE TABLE STUDENT_MARKS (
	ST_ID INT PRIMARY KEY AUTO_INCREMENT,
	ROLL_NO_M INT,
	PHYSICS INT,
	CHEM INT,
	MATH INT,
	PRIMARY KEY(ROLL_NO),
	FOREIGN KEY (ROLL_NO_M) REFERENCES STUDENT_DETAILS (ROLL_NO)
);

DESC STUDENT_MARKS;



--RENAME A COLUMN AND CHANGE DATATYPE (here as INT DATATYPE can't take values greater than 2147483647 we are changing the DATATYPE TO BIGINT)
--SYNTAX : ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
ALTER TABLE STUDENT_DETAILS CHANGE CONTACT_NO PHONE_NO BIGINT;




--RENAME A TABLE
--SYNTAX : ALTER TABLE table_name RENAME TO new_table_name;
ALTER TABLE STUDENT_DETAILS1 RENAME TO STUDENT_DET;



--MODIFY DATA TYPE OF COLUMN(S) CREATED
--SYNTAX : ALTER TABLE table_name MODIFY column_name data_type;
ALTER TABLE STUDENT_DETAILS MODIFY NAME VARCHAR(15);


--REMOVE TABLE FROM DATABASE
--SYNTAX : DROP TABLE table_name;
DROP TABLE STUDENT_DETAILS1    --DELETING TABLE CREATED USING PHPMYADMIN INTERFACE HAVING NAME STUDENT_DETAILS1




--ADD RECORDS TO THE TABLE
--SYNTAX : INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN);
INSERT INTO STUDENT_DETAILS (ROLL_NO,NAME,ADDRESS,PHONE_NO) VALUES (1,'ANTO','THANE',8652365415);
INSERT INTO STUDENT_DETAILS (ROLL_NO,NAME,ADDRESS,PHONE_NO) VALUES (2,'AKASH','VIDYAVIHAR',9541365415);
INSERT INTO STUDENT_DETAILS (ROLL_NO,NAME,ADDRESS,PHONE_NO) VALUES (3,'SRIJAN','VIDYAVIHAR',9996541215);
INSERT INTO STUDENT_DETAILS (ROLL_NO,NAME,ADDRESS,PHONE_NO) VALUES (4,'JAFFREY','SION',9899652365);




--CHANGE VALUE OF RECORDS IN THE TABLE
--SYNTAX : UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN  WHERE CONDITION ;
UPDATE STUDENT_DETAILS SET ADDRESS = 'KALYAN' WHERE ROLL_NO = 1;



--DELETE RECORDS FROM TABLE
--SYNTAX : DELETE FROM table_name WHERE CONDITION;
DELETE FROM STUDENT_DETAILS WHERE ROLL_NO = 1;



--VIEW TABLE VALUES
--SYNTAX FOR VIEWING THE ENTIRE TABLE : SELECT * FROM table_name;
SELECT * FROM STUDENT_DETAILS;


--SYNTAX FOR VIEWING THE TABLE VALUES BASED ON A CONDITION : SELECT * FROM table_name WHERE condition;

--THE COMMAND BELOW IS USED TO GET NAMES OF STUDENT STAYING AT VIDYAVIHAR
SELECT NAME FROM STUDENT_DETAILS WHERE ADDRESS = 'VIDYAVIHAR';

--THE COMMAND BELOW IS USED TO GET THE NAME OF THE STUDENT STAYING AT VIDYAVIHAR AND HAVING A PHONE NO I.E 9541365415
SELECT NAME FROM STUDENT_DETAILS WHERE ADDRESS = 'VIDYAVIHAR' AND PHONE_NO = 9541365415;

--SYNTAX FOR COUNTING THE TABLE VALUES BASED ON A CONDITION : SELECT COUNT(* OR column_name) FROM table_name WHERE condition;
--THE COMMAND BELOW IS USED TO GET THE  NO. OF STUDENTS WHO STAY AT VIDYAVIHAR
SELECT COUNT(*) FROM STUDENT_DETAILS WHERE ADDRESS = 'VIDYAVIHAR';

--SYNTAX FOR VIEWING THE TABLE VALUES BASED ON A CONDITION (USING LIKE): SELECT * FROM table_name WHERE column1 LIKE something;
--THE COMMAND BELOW IS USED TO GET DETAILS OF STUDENT WHOSE NAME BEGINS WITH THE CHARACTERS 'JAFF'
SELECT * FROM STUDENT_DETAILS WHERE NAME LIKE 'JAFF%';

--SYNTAX FOR VIEWING THE TABLE VALUES IN A SPECIFIED ORDER: SELECT * FROM table_name ORDER BY column_name;
--THE COMMAND BELOW IS USED TO GET DETAILS OF STUDENT IN ALPHABETICAL ORDER
SELECT * FROM STUDENT_DETAILS ORDER BY NAME;
--THE COMMAND BELOW IS USED TO GET DETAILS OF STUDENT IN DESCENDING ALPHABETICAL ORDER
SELECT * FROM STUDENT_DETAILS ORDER BY NAME DESC;

--SYNTAX FOR VIEWING THE TABLE VALUES BASED ON A SPECIFIED RANGE: SELECT * FROM table_name WHERE ROLL_NO BETWEEN value1 AND value2;
--THE COMMAND BELOW IS USED TO GET DETAILS OF STUDENTS with Roll No's 3 & 5 and all details of all students with roll nos. in BETWEEN 3 and 5
SELECT * FROM STUDENT_DETAILS WHERE ROLL_NO BETWEEN 3 AND 5;

sql_example_commands.sql

CREATE DATABASE STUDENT;

USE STUDENT;

CREATE TABLE STUDENT_DETAILS (
	ROLL_NO INT PRIMARY KEY,
	NAME VARCHAR(10),
	ADDRESS VARCHAR(20),
	CONTACT_NO INT
);

ALTER TABLE STUDENT_DETAILS1 RENAME TO STUDENT_DET;

ALTER TABLE STUDENT_DETAILS CHANGE CONTACT_NO PHONE_NO BIGINT;

ALTER TABLE STUDENT_DETAILS MODIFY NAME VARCHAR(15);



INSERT INTO STUDENT_DETAILS (ROLL_NO,NAME,ADDRESS,PHONE_NO) VALUES (1,'ANTO','THANE',8652365415);


ALTER TABLE STUDENT_DETAILS MODIFY PHONE_NO BIGINT;


DELETE FROM STUDENT_DETAILS WHERE ROLL_NO = 1;




UPDATE STUDENT_DETAILS SET ADDRESS = 'KALYAN' WHERE ROLL_NO = 1;

DELETE FROM STUDENT_DETAILS WHERE ROLL_NO = 1;

SELECT * FROM STUDENT_DETAILS;

SELECT NAME FROM STUDENT_DETAILS WHERE ADDRESS = 'VIDYAVIHAR';

SELECT NAME FROM STUDENT_DETAILS WHERE ADDRESS = 'VIDYAVIHAR' AND PHONE_NO = 9541365415;

SELECT COUNT(*) FROM STUDENT_DETAILS WHERE ADDRESS = 'VIDYAVIHAR';

SELECT * FROM STUDENT_DETAILS WHERE NAME LIKE 'JAFF%';

SELECT * FROM STUDENT_DETAILS ORDER BY NAME;

SELECT * FROM STUDENT_DETAILS ORDER BY NAME DESC;

SELECT * FROM STUDENT_DETAILS WHERE ROLL_NO BETWEEN 3 AND 5;