Dear Friends
In this post, I am going to share with you a list of SQL Commands with examples for your SQL Project and Practical File. I hope this will be very helpful for you.
Keep Visiting and Keep Learning!
Table of Contents
| TOPIC [MYSQL] |
| 1) SHOW DATABASES 2) CREATE A DATABASE 3) USE DATABASE 4) SHOW TABLES 5) CREATE TABLE (with different data types) 6) INSERT INTO TABLE ( put null also) 7) SELECT * FROM TABLE NAME 8) DESCRIBING THE TABLE 9) ALTER TABLE (ADD , MODIFY , DROP , RENAME) 10) SELECT * ______ WHERE ( >,<,>=,<=,=,<>) 11) SELECT ______ (AND,OR,NOT) 12) SELECT______ (IN,NOT IN) 13) SELECT______ (BETWEEN,NOT BETWEEN) 14) SELECT______ (IS NULL, IS NOT NULL) 15) SELECT______ (DISTINCT COUNT) 16) SELECT______ (ORDER BY (ASCENDING,DESCENDING)) 17) SELECT______ (GROUP BY HAVING) 18) SELECT______ (+ , – , * , / , %) [ARITHMETIC OPERATORS] 19) SELECT______ (COLUMN ALIAS) 20) SELECT______ (TABLE ALIAS) 21) SELECT______ (TEXT IN QUERY) 22) SELECT______ (LIKE , NOT LIKE)(% , – , % , -) 23) SELECT______ (+ , – , PERCENTAGE, STRING BASED) 24) AGGREGATE FUNCTIONS (5 FUNCTIONS) 25) CREATE INDEX 26) MODIFY INDEX 27) DROP INDEX 28) RENAME INDEX 29) ALTER TABLE (ADD CONSTRAINT , MODIFY CONSTRAINT , DROP CONSTRAINT) 30) DELETE TABLE 31) TRUNCATE TABLE 32) DROP TABLE 33) SINGLE ROW FUNCTIONS (25 FUNCTIONS) 34) TAKE 2 TABLES AND SHOW EQUI JOIN 35) SHOW CARTESIAN PRODUCT OF 2 TABLES |
1) SHOW DATABASES
mysql> SHOW DATABASES;
2) CREATE A DATABASE
mysql> CREATE DATABASE IF NOT EXISTS NYRA;
3) USE DATABASE
mysql> USE NYRA;
4) SHOW TABLES
mysql> SHOW TABLES;
5) CREATE TABLE (with different data types)
mysql> CREATE TABLE STUDENT (
-> SID INT(3) PRIMARY KEY,
-> NAME VARCHAR(25) NOT NULL,
-> ADDRESS VARCHAR(30),
-> MOBILE CHAR(12),
-> DOB DATE,
-> FEE DECIMAL(10,2));
Query OK, 0 rows affected (0.47 sec)
6) INSERT INTO TABLE ( put null also)
mysql> INSERT INTO STUDENT VALUES(101, ‘NYRA’, ‘GURUGRAM’, ‘989898989898’, ‘2004-06-03’, 12650.00);
Query OK, 1 row affected (0.26 sec)
mysql> INSERT INTO STUDENT VALUES(102, ‘ZARIN’, NULL, ‘97898986989’, ‘2005-10-04’, 15600.00);
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO STUDENT VALUES(103, ‘TANMAY’, NULL, ‘9990569899’, ‘2003-12-15’, NULL);
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO STUDENT VALUES(104, ‘MONISHIKHA’, ‘DELHI’, NULL, ‘2004-11-25’, 8500.00);
Query OK, 1 row affected (0.13 sec)
7) SELECT * FROM TABLE NAME
mysql> SELECT * FROM STUDENT;
8) DESCRIBING THE TABLE
mysql> DESC STUDENT;
9) ALTER TABLE (ADD , MODIFY , DROP , RENAME).
mysql> ALTER TABLE STUDENT ADD SEX CHAR(1);
Query OK, 4 rows affected (1.20 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE STUDENT MODIFY SEX CHAR(8);
Query OK, 4 rows affected (1.20 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE STUDENT RENAME COLUMN SEX TO GENDER;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE STUDENT DROP GENDER;
Query OK, 0 rows affected (1.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
10) SELECT * ______ WHERE ( >,<,>=,<=,=,<>)
mysql> SELECT * FROM STUDENT WHERE FEE > 12600;
mysql> SELECT * FROM STUDENT WHERE FEE < 12600;
mysql> SELECT * FROM STUDENT WHERE FEE <= 12650;
mysql> SELECT * FROM STUDENT WHERE FEE >= 12650;
mysql> SELECT * FROM STUDENT WHERE FEE <> 12650;
mysql> SELECT * FROM STUDENT WHERE FEE = 15600;
11) SELECT ______ (AND, OR, NOT)
mysql> SELECT * FROM STUDENT WHERE NAME = ‘NYRA’ AND FEE = 12650.00;
mysql> SELECT * FROM STUDENT WHERE NAME=’NYRA’ OR NAME = ‘TANMAY’;
mysql> SELECT * FROM STUDENT WHERE NOT NAME = ‘NYRA’;
12) SELECT______ (IN, NOT IN)
mysql> SELECT * FROM STUDENT WHERE NAME IN (‘NYRA’, ‘MONISHIKHA’);
mysql> SELECT * FROM STUDENT WHERE NAME NOT IN (‘NYRA’, ‘MONISHIKHA’);
13) SELECT______ (BETWEEN, NOT BETWEEN)
mysql> SELECT * FROM STUDENT WHERE DOB BETWEEN ‘2004-01-01’ AND ‘2004-12-31’;
mysql> SELECT * FROM STUDENT WHERE DOB NOT BETWEEN ‘2004-01-01’ AND ‘2004-12-31’;
14) SELECT______ (IS NULL, IS NOT NULL)
mysql> SELECT * FROM STUDENT WHERE ADDRESS IS NULL;
mysql> SELECT * FROM STUDENT WHERE ADDRESS IS NOT NULL;
15) SELECT______ (DISTINCT COUNT)
mysql> SELECT COUNT(ADDRESS) FROM STUDENT;
mysql> SELECT DISTINCT(ADDRESS) FROM STUDENT;
OR
mysql> SELECT DISTINCT ADDRESS FROM STUDENT;
mysql> SELECT COUNT(DISTINCT(ADDRESS)) FROM STUDENT;
16) SELECT______ (ORDER BY (ASCENDING, DESCENDING))
mysql> SELECT * FROM STUDENT ORDER BY NAME;
mysql> SELECT * FROM STUDENT ORDER BY NAME ASC;
mysql> SELECT * FROM STUDENT ORDER BY NAME DESC;
mysql> SELECT * FROM STUDENT ORDER BY FEE;
mysql> SELECT * FROM STUDENT ORDER BY FEE DESC;
17) SELECT______ (GROUP BY HAVING)
mysql> SELECT ADDRESS, COUNT(*) FROM STUDENT GROUP BY ADDRESS;
mysql> SELECT ADDRESS, COUNT(*) FROM STUDENT GROUP BY ADDRESS HAVING COUNT(*)< 2;
mysql> SELECT ADDRESS, COUNT(*) FROM STUDENT GROUP BY ADDRESS HAVING COUNT(*) > 1;
18) SELECT______ (+ , – , * , / , %) [ARITHMETIC OPERATORS]
mysql> SELECT NAME, FEE+2000 FROM STUDENT;
mysql> SELECT NAME, FEE – 1000 FROM STUDENT;
mysql> SELECT NAME, FEE + FEE * 0.5 FROM STUDENT;
mysql> SELECT NAME, FEE/100 FROM STUDENT;
mysql> SELECT NAME, FEE % 100 FROM STUDENT;
19) SELECT______ (COLUMN ALIAS)
mysql> SELECT SID AS ‘STUDENT ID’, NAME AS ‘STUDENT NAME’ FROM STUDENT;
20) SELECT______ (TABLE ALIAS)
mysql> SELECT S.SID, S.NAME, S.FEE FROM STUDENT S;
21) SELECT______ (TEXT IN QUERY)
mysql> SELECT NAME, ‘HAS TO PAY RS. ‘, FEE FROM STUDENT;
22) SELECT______ (LIKE , NOT LIKE) with wildcard characters % & –
mysql> SELECT * FROM STUDENT WHERE NAME LIKE ‘____’;
mysql> SELECT * FROM STUDENT WHERE NAME LIKE ‘%I%’;
mysql> SELECT * FROM STUDENT WHERE NAME LIKE ‘%A’;
23) UPDATE _______ RECORDS
mysql> UPDATE STUDENT SET ADDRESS = ‘GURUGRAM’ WHERE SID = 103;
24) AGGREGATE FUNCTIONS (5 FUNCTIONS)
mysql> SELECT MAX(FEE) FROM STUDENT;
mysql> SELECT MIN(FEE) FROM STUDENT;
mysql> SELECT SUM(FEE) FROM STUDENT;
mysql> SELECT AVG(FEE) FROM STUDENT;
mysql> SELECT COUNT(FEE) FROM STUDENT;
25) CREATE AN INDEX
mysql> CREATE INDEX idxName ON STUDENT(NAME);
26) MODIFY THE INDEX
mysql> ALTER TABLE STUDENT RENAME INDEX idxName TO NAMEIDX;
27) DROP INDEX
mysql> ALTER TABLE STUDENT DROP INDEX NAMEIDX;
28) RENAME THE INDEX
mysql> ALTER TABLE STUDENT RENAME INDEX idxName TO NAMEIDX;