Scribd
Upload
63 views12 pages

SQL Commands and Functions Lab Guide

The document outlines a series of experiments conducted in a Database Management System Laboratory, focusing on various SQL commands and functionalities. Each experiment demonstrates the implementation of different SQL features such as DDL, DML, functions, operators, joins, subqueries, constraints, transactions, user management, PL/SQL, and triggers. Expected outputs for each experiment are also provided, indicating successful execution of the commands.

Uploaded by

kumarrohit06071999
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
63 views12 pages

SQL Commands and Functions Lab Guide

The document outlines a series of experiments conducted in a Database Management System Laboratory, focusing on various SQL commands and functionalities. Each experiment demonstrates the implementation of different SQL features such as DDL, DML, functions, operators, joins, subqueries, constraints, transactions, user management, PL/SQL, and triggers. Expected outputs for each experiment are also provided, indicating successful execution of the commands.

Uploaded by

kumarrohit06071999
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

Database Management System Laboratory

Experiment 1: Implementation of DDL commands of SQL

CREATE TABLE Students (ID INT, Name VARCHAR(50));

ALTER TABLE Students ADD Age INT;

DROP TABLE Students;

Expected Output:

Table created.

Table altered.

Table dropped.
Database Management System Laboratory

Experiment 2: Implementation of DML commands of SQL

INSERT INTO Students VALUES (1, 'Alice', 20);

UPDATE Students SET Age = 21 WHERE ID = 1;

DELETE FROM Students WHERE ID = 1;

Expected Output:

1 row inserted.

1 row updated.

1 row deleted.
Database Management System Laboratory

Experiment 3: Implementation of different types of functions

SELECT LENGTH('Database'); -- Number function

SELECT AVG(Age) FROM Students; -- Aggregate Function

SELECT UPPER(Name) FROM Students; -- Character Function

SELECT CAST('2024-01-01' AS DATE); -- Conversion

SELECT CURRENT_DATE; -- Date Function

Expected Output:

As per function type.


Database Management System Laboratory

Experiment 4: Implementation of different types of operators

SELECT 10 + 5 AS Sum; -- Arithmetic

SELECT * FROM Students WHERE Age > 18 AND Age < 25; -- Logical

SELECT * FROM Students WHERE Name = 'Alice'; -- Comparison

SELECT DISTINCT Name FROM Students; -- Special

SELECT Name FROM Students UNION SELECT Name FROM Teachers; -- Set

Expected Output:

Respective operator results.


Database Management System Laboratory

Experiment 5: Implementation of different types of Joins

SELECT * FROM Students INNER JOIN Marks ON [Link] = [Link];

SELECT * FROM Students LEFT JOIN Marks ON [Link] = [Link];

SELECT * FROM Students NATURAL JOIN Marks;

Expected Output:

Joined tables.
Database Management System Laboratory

Experiment 6: Study and Implementation of GROUP BY, ORDER BY, INDEX

SELECT Age, COUNT(*) FROM Students GROUP BY Age HAVING COUNT(*) > 1;

SELECT * FROM Students ORDER BY Name;

CREATE INDEX idx_name ON Students(Name);

Expected Output:

Grouped, ordered, and indexed data.


Database Management System Laboratory

Experiment 7: Study & Implementation of Subqueries and Views

SELECT * FROM Students WHERE Age = (SELECT MAX(Age) FROM Students);

CREATE VIEW young_students AS SELECT * FROM Students WHERE Age < 22;

Expected Output:

Subquery results and view created.


Database Management System Laboratory

Experiment 8: Study & Implementation of Constraints

CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Age INT CHECK (Age

>= 18));

Expected Output:

Table with constraints created.


Database Management System Laboratory

Experiment 9: Study & Implementation of Backup, Commit, Rollback

START TRANSACTION;

INSERT INTO Students VALUES (2, 'Bob', 19);

SAVEPOINT sp1;

ROLLBACK TO sp1;

COMMIT;

Expected Output:

Transaction processed with rollback and commit.


Database Management System Laboratory

Experiment 10: Creating Users and Managing Roles

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'pass';

GRANT SELECT ON DB.* TO 'new_user'@'localhost';

REVOKE SELECT ON DB.* FROM 'new_user'@'localhost';

Expected Output:

User created and privileges managed.


Database Management System Laboratory

Experiment 11: Study & Implementation of PL/SQL

DECLARE

v_name VARCHAR(50);

BEGIN

SELECT Name INTO v_name FROM Students WHERE ID = 1;

DBMS_OUTPUT.PUT_LINE(v_name);

END;

Expected Output:

PL/SQL block executed.


Database Management System Laboratory

Experiment 12: Study & Implementation of SQL Triggers

CREATE TRIGGER before_insert_students

BEFORE INSERT ON Students

FOR EACH ROW

SET [Link] = UPPER([Link]);

Expected Output:

Trigger created.

Common questions

Powered by AI

GROUP BY aggregates data based on one or more columns, allowing for operations like counting occurrences; ORDER BY sorts the resultant data set by specified columns, enhancing readability and analysis; INDEX increases query performance by allowing faster retrieval of records, though it may slow down data insertion and deletion .

SQL triggers execute predetermined actions automatically when specific database events, such as inserts, updates, or deletes, occur. For example, a 'before insert' trigger in a Students table might modify inserted data to ensure name capitalization to maintain data uniformity .

Arithmetic operators perform mathematical calculations, while logical operators filter data based on conditions; comparison operators are used to match exact values; special operators like DISTINCT ensure unique results; and set operators like UNION combine results from multiple queries .

INNER JOIN combines records from two tables where there are matching values in both; LEFT JOIN returns all records from the left table and matched records from the right; NATURAL JOIN automatically joins tables based on columns with the same name, returning all combinations .

Transactions are units of work executed as a single operation, ensuring consistency and integrity of the database. Savepoints allow setting intermediate points within a transaction to which one can rollback; ROLLBACK undoes transaction changes to a savepoint or entirely; COMMIT applies all changes permanently .

Constraints enforce rules for data stored in tables, ensuring its accuracy and reliability. Examples include PRIMARY KEY for unique identification, NOT NULL to prevent missing values, and CHECK constraints to enforce specific conditions, such as ensuring age is greater than or equal to 18 .

PL/SQL enables procedural logic within SQL queries by allowing blocks of code that can take advantage of variables, control structures like loops, and condition-testing inside SQL, enhancing the functionality and control over SQL query execution .

The expected outputs when performing basic DDL operations are as follows: when creating a table, the output should be 'Table created'; when altering the table, the output should be 'Table altered'; and when dropping the table, the output should be 'Table dropped' .

Mechanisms involved in user and role management include creating and defining users, assigning them specific privileges for accessing database objects, and revoking those privileges to control access. This ensures that only authorized individuals can perform operations, thus enhancing security .

Number functions return numeric values such as the length of a string; aggregate functions calculate values from multiple rows, e.g., AVG(Age) from the Students table; character functions transform text, such as converting all characters to uppercase; conversion functions change data types, e.g., casting a string to a date; and date functions provide current date values .

You might also like