DBMS-SQL

SQL DDL and DML Examples

Examples of DDL (Data Definition Language) and DML (Data Manipulation Language) commands in SQL.

DDL (Data Definition Language) Examples:

1. Create a new database:

CREATE DATABASE SchoolDB; 

2. Create a table:

CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Age INT ); 

3. Alter a table to add a new column:

ALTER TABLE Students ADD Email VARCHAR(100); 

4. Drop a table:

DROP TABLE Students; 

5. Rename a table:

ALTER TABLE OldTableName RENAME TO NewTableName; 

DML (Data Manipulation Language) Examples:

1. Insert data into a table:

 INSERT INTO Students (StudentID, FirstName, LastName, Age) VALUES (1, 'John', 'Doe', 20); 

2. Update data in a table:

UPDATE Students SET Age = 21 WHERE StudentID = 1; 

3. Delete data from a table:

DELETE FROM Students WHERE StudentID = 1; 

4. Select data from a table:

SELECT * FROM Students; 

5. Select data with conditions:

SELECT FirstName, LastName FROM Students WHERE Age >= 18;

SQL DDL and DML Examples

Additional DDL (Data Definition Language) Examples: 

1. Create an index on a table:

CREATE INDEX idx_LastName ON Students(LastName); 

2. Create a view:

CREATE VIEW StudentNames AS SELECT FirstName, LastName FROM Students; 

3. Create a unique constraint:

ALTER TABLE Students ADD CONSTRAINT UC_Email UNIQUE (Email); 

4. Create a foreign key constraint:

ALTER TABLE Enrollments ADD CONSTRAINT FK_StudentID FOREIGN KEY (StudentID) REFERENCES Students(StudentID); 

5.Truncate a table (remove all rows):

TRUNCATE TABLE Students; 

Additional DML (Data Manipulation Language) 

1. Insert multiple rows into a table:

INSERT INTO Students (StudentID, FirstName, LastName, Age) VALUES (2, 'Jane', 'Smith', 22), (3, 'Bob', 'Johnson', 19), (4, 'Alice', 'Brown', 20); 

2. Update data using a subquery:

UPDATE Students SET Age = Age + 1 WHERE StudentID IN (SELECT StudentID FROM Enrollments WHERE CourseID = 101); 

3. Delete all data from a table:

DELETE FROM Students; 

4.Select distinct values from a column:

SELECT DISTINCT LastName FROM Students; 

5. Use the BETWEEN operator for selecting data within a range:

SELECT * FROM Students WHERE Age BETWEEN 18 AND 25;

SQL- JOINS

INNER JOIN:

Retrieve a list of students and their enrolled courses.

SELECT Students.FirstName, Students.LastName, Courses.CourseName

FROM Students

INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID

INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

LEFT JOIN (or LEFT OUTER JOIN):

Retrieve a list of all students and the courses they are enrolled in, including students with no enrollments.

SELECT Students.FirstName, Students.LastName, Courses.CourseName

FROM Students

LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID

LEFT JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

RIGHT JOIN (or RIGHT OUTER JOIN):

Retrieve a list of all courses and the students who are enrolled in them, including courses with no students.

SELECT Students.FirstName, Students.LastName, Courses.CourseName

FROM Students

RIGHT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID

RIGHT JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

FULL OUTER JOIN:

Retrieve a list of all students and all courses, showing which students are enrolled in which courses.

SELECT Students.FirstName, Students.LastName, Courses.CourseName

FROM Students

FULL OUTER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID

FULL OUTER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

SELF JOIN:

Retrieve a list of employees and their managers (assuming there's a ManagerID column indicating the manager of each employee).

SELECT E.EmployeeName, M.EmployeeName AS ManagerName

FROM Employees AS E

LEFT JOIN Employees AS M ON E.ManagerID = M.EmployeeID;