1.
Data Definition Language (DDL) Commands:
CREATE DATABASE: To create a new database.
Code
CREATE DATABASE school;
USE: To select a database to work with.
Code
USE school;
CREATE TABLE: To create a new table within the selected database.
Code
CREATE TABLE Students (
RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
DOB DATE,
Marks INT
);
ALTER TABLE: To modify an existing table (e.g., add a column, change a
data type).
Code
ALTER TABLE Students ADD COLUMN City VARCHAR(30);
(A) Add primary key to a relation
mysql> ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);
(B) Add foreign key to a relation
Syntax: ALTER TABLE table_name ADD FOREIGN KEY(attribute name) REFERENCES
referenced_table_name (attribute name);
(C) Add constraint UNIQUE to an existing attribute
Syntax: ALTER TABLE table_name ADD UNIQUE (attribute name);
(D) Add an attribute to an existing table
ALTER TABLE table_name ADD attribute_name DATATYPE;
(E) Modify datatype of an attribute
Syntax: ALTER TABLE table_name MODIFY attribute DATATYPE;
(F) Modify constraint of an attribute
Syntax: ALTER TABLE table_name MODIFY attribute DATATYPE NOT NULL:
(H) Remove an attribute
ALTER TABLE table_name DROP attribute;
(I) Remove primary key from the table
Syntax: ALTER TABLE table_name DROP PRIMARY KEY;
DROP TABLE: To delete an entire table.
Code
DROP TABLE Students;
DROP DATABASE: To delete an entire database.
Code
DROP DATABASE school;
2. Data Manipulation Language (DML) Commands:
INSERT INTO: To add new rows (records) into a table.
Code
INSERT INTO Students (RollNo, Name, DOB, Marks) VALUES (101,
'Aman', '2005-05-12', 85);
UPDATE: To modify existing data in a table.
Code
UPDATE Students SET Marks = 90 WHERE RollNo = 101;
DELETE FROM: To remove rows from a table.
Code
DELETE FROM Students WHERE RollNo = 101;
3. Data Query Language (DQL) Commands:
SELECT: To retrieve data from a table.
Code
SELECT * FROM Students; -- Select all columns and all rows
SELECT Name, Marks FROM Students WHERE Marks > 80 ORDER BY Name
ASC; -- Select specific columns with conditions and ordering
(A) Retrieve selected columns
mysql> SELECT EmpNo
-> FROM EMPLOYEE;
+-------+
| EmpNo |
+-------+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
| 106 |
| 107 |
| 108 |
| 109 |
| 110 |
+-------+
10 rows in set (0.41 sec)
(B) Renaming of columns
mysql> SELECT EName AS Name
-> FROM EMPLOYEE;
+----------+
| Name |
+----------+
| Aaliya |
| Kritika |
| Shabbir |
| Gurpreet |
| Joseph |
| Sanya |
| Vergese |
| Nachaobi |
| Daribha |
| Tanya |
+----------+
10 rows in set (0.00 sec)
(C) DISTINCT Clause
mysql> SELECT DISTINCT DeptId
-> FROM EMPLOYEE;
+--------+
| DeptId |
+--------+
| D02 |
| D01 |
| D04 |
| D03 |
| D05 |
+--------+
5 rows in set (0.03 sec)
(D) WHERE Clause
mysql> SELECT DISTINCT Salary
-> FROM EMPLOYEE
-> WHERE Deptid='D01';
As the column DeptId is of string type, its values are
enclosed in quotes ('D01').
+--------+
| Salary |
+--------+
| 60000 |
| 45000 |
| 15000 |
+--------+
3 rows in set (0.02 sec)
(E) MEMBERSHIP OPERATOR IN
mysql> SELECT *
-> FROM EMPLOYEE
-> WHERE DeptId IN ('D01', 'D02' , 'D04');
+-------+----------+--------+-------+--------+
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+----------+--------+-------+--------+
| 101 | Aaliya | 10000 | 234 | D02 |
| 102 | Kritika | 60000 | 123 | D01 |
| 103 | Shabbir | 45000 | 566 | D01 |
| 104 | Gurpreet | 19000 | 565 | D04 |
| 106 | Sanya | 48000 | 695 | D02 |
| 107 | Vergese | 15000 | NULL | D01 |
| 109 | Daribha | 42000 | NULL | D04 |
+-------+----------+--------+-------+--------+
7 rows in set (0.00 sec)
(F) ORDER BY Clause
mysql> SELECT *
-> FROM EMPLOYEE
-> ORDER BY Salary;
+-------+----------+--------+-------+--------+
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+----------+--------+-------+--------+
| 101 | Aaliya | 10000 | 234 | D02 |
| 107 | Vergese | 15000 | NULL | D01 |
| 104 | Gurpreet | 19000 | 565 | D04 |
| 108 | Nachaobi | 29000 | NULL | D05 |
| 105 | Joseph | 34000 | 875 | D03 |
| 109 | Daribha | 42000 | NULL | D04 |
| 103 | Shabbir | 45000 | 566 | D01 |
| 106 | Sanya | 48000 | 695 | D02 |
| 110 | Tanya | 50000 | 467 | D05 |
| 102 | Kritika | 60000 | 123 | D01 |
+-------+----------+--------+-------+--------+
10 rows in set (0.05 sec)
4. Constraints:
PRIMARY KEY: Uniquely identifies each record in a table.
NOT NULL: Ensures that a column cannot have a NULL value.
UNIQUE: Ensures that all values in a column are different.
DEFAULT: Provides a default value for a column when no value is specified.