Scribd
Upload
7 views3 pages

DBMS Lab Experiments SQL

The document provides a series of SQL experiments covering essential database operations such as creating tables, inserting, updating, and deleting records. It also includes examples of using WHERE clauses, aggregate functions, joins, user-defined functions, stored procedures, transaction control commands, triggers, views, and XML schema. Each section contains SQL code snippets demonstrating the concepts discussed.

Uploaded by

gg9244260
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views3 pages

DBMS Lab Experiments SQL

The document provides a series of SQL experiments covering essential database operations such as creating tables, inserting, updating, and deleting records. It also includes examples of using WHERE clauses, aggregate functions, joins, user-defined functions, stored procedures, transaction control commands, triggers, views, and XML schema. Each section contains SQL code snippets demonstrating the concepts discussed.

Uploaded by

gg9244260
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DBMS Lab Experiments with SQL Examples

1. Create Table, Add Constraints, Insert, Update, Delete


CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
Age INT CHECK (Age >= 17)
);

INSERT INTO Student VALUES (1,'Arun','arun@[Link]',18);


INSERT INTO Student VALUES (2,'Priya','priya@[Link]',19);

UPDATE Student SET Age = 20 WHERE StudentID = 1;

DELETE FROM Student WHERE StudentID = 2;

2. WHERE Clause and Aggregate Functions


SELECT * FROM Student WHERE Age > 18;

SELECT COUNT(*) FROM Student;


SELECT AVG(Age) FROM Student;
SELECT MAX(Age) FROM Student;
SELECT MIN(Age) FROM Student;

3. Subqueries and Simple Join


CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50),
StudentID INT
);

SELECT Name FROM Student


WHERE StudentID IN (SELECT StudentID FROM Course);

SELECT [Link], [Link]


FROM Student
JOIN Course ON [Link] = [Link];

4. Natural, Equi and Outer Joins

-- Natural Join
SELECT * FROM Student NATURAL JOIN Course;

-- Equi Join
SELECT [Link], [Link]
FROM Student, Course
WHERE [Link] = [Link];

-- Left Outer Join


SELECT [Link], [Link]
FROM Student
LEFT OUTER JOIN Course
ON [Link] = [Link];
5. User Defined Function
CREATE FUNCTION GetStudentAge (@id INT)
RETURNS INT
AS
BEGIN
DECLARE @age INT
SELECT @age = Age FROM Student WHERE StudentID = @id
RETURN @age
END

6. Stored Procedure

CREATE PROCEDURE GetStudentDetails


AS
BEGIN
SELECT * FROM Student
END

7. DCL and TCL Commands

-- Transaction Control
BEGIN TRANSACTION;
UPDATE Student SET Age = 21 WHERE StudentID = 1;
COMMIT;

-- Data Control
GRANT SELECT ON Student TO user1;
REVOKE SELECT ON Student FROM user1;

8. SQL Trigger

CREATE TRIGGER StudentInsertTrigger


ON Student
AFTER INSERT
AS
BEGIN
PRINT 'New Student Record Inserted'
END

9. View and Index

CREATE VIEW StudentView AS


SELECT Name, Age FROM Student;

CREATE INDEX idx_student_name


ON Student(Name);

10. XML Database and XML Schema Example


-- XML Example
<Students>
<Student>
<ID>1</ID>
<Name>Arun</Name>
<Age>18</Age>
</Student>
</Students>

-- XML Schema Example


<xsd:schema xmlns:xsd="[Link]
<xsd:element name="Student">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ID" type="xsd:int"/>
<xsd:element name="Name" type="xsd:string"/>
<xsd:element name="Age" type="xsd:int"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

You might also like