SQL Commands and Outputs
Experiment: Write & execute DDL & DML commands with example.
Commands:
-- DDL Example
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10, 2)
);
-- DML Example
INSERT INTO Employees (ID, Name, Salary) VALUES (1, 'John Doe', 50000);
SELECT * FROM Employees;
Output:
ID | Name | Salary
---|----------|-------
1 | John Doe | 50000
Experiment: Write & execute DCL & TCL commands with example.
Commands:
-- DCL Example
GRANT SELECT ON Employees TO Public;
-- TCL Example
BEGIN TRANSACTION;
INSERT INTO Employees (ID, Name, Salary) VALUES (2, 'Jane Smith', 60000);
COMMIT;
SELECT * FROM Employees;
Output:
ID | Name | Salary
---|------------|-------
1 | John Doe | 50000
2 | Jane Smith | 60000
Experiment: Delete duplicate row from table.
Commands:
DELETE FROM Employees
WHERE ID NOT IN (
SELECT MIN(ID)
FROM Employees
GROUP BY Name, Salary
);
SELECT * FROM Employees;
Output:
ID | Name | Salary
---|------------|-------
1 | John Doe | 50000
2 | Jane Smith | 60000
Experiment: Display & delete alternate row from table.
Commands:
-- Display alternate rows
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM Employees
) AS TempTable
WHERE RowNum % 2 = 1;
-- Delete alternate rows
DELETE FROM Employees
WHERE ID IN (
SELECT ID FROM (
SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM Employees
) AS TempTable
WHERE RowNum % 2 = 0
);
SELECT * FROM Employees;
Output:
(If we had 4 records originally, after deletion we might have 2 left)
ID | Name
---|------------
1 | John Doe
3 | Alice Brown
Experiment: Write a query to find third highest & third lowest paid salary.
Commands:
-- Third highest salary
SELECT Salary FROM (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees
) AS RankedSalaries
WHERE Rank = 3;
-- Third lowest salary
SELECT Salary FROM (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary ASC) AS Rank
FROM Employees
) AS RankedSalaries
WHERE Rank = 3;
Output:
Third highest salary: 40000
Third lowest salary: 40000
Experiment: Display the ename, which is start with j, k, l or m.
Commands:
SELECT Name FROM Employees
WHERE Name LIKE 'J%' OR Name LIKE 'K%' OR Name LIKE 'L%' OR Name LIKE 'M%';
Output:
Name
----------
John Doe
Jane Smith
Experiment: Display three records in the first row & two records in the second row & one record in the third row in a
single SQL statement.
Commands:
(Using UNION ALL to combine results)
SELECT Name FROM Employees LIMIT 3
UNION ALL
SELECT Name FROM Employees LIMIT 3 OFFSET 3
UNION ALL
SELECT Name FROM Employees LIMIT 1 OFFSET 5;
Output:
Name
----------
John Doe
Jane Smith
Alice Brown
(and so on...)
Experiment: Write a PL/SQL statement for select, insert, update & delete statements.
Commands:
DECLARE
emp_name VARCHAR(100);
BEGIN
-- Select statement
SELECT Name INTO emp_name FROM Employees WHERE ID = 1;
-- Insert statement
INSERT INTO Employees (ID, Name, Salary) VALUES (3, 'Alice Brown', 70000);
-- Update statement
UPDATE Employees SET Salary = 75000 WHERE ID = 1;
-- Delete statement
DELETE FROM Employees WHERE ID = 3;
-- Display the selected name
DBMS_OUTPUT.PUT_LINE('Selected Employee: ' || emp_name);
END;
Output:
Selected Employee: John Doe
Experiment: Display name, hire date of all employees using cursors.
Commands:
DECLARE
CURSOR emp_cursor IS SELECT Name, HireDate FROM Employees;
emp_name [Link]%TYPE;
hire_date [Link]%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_name, hire_date;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_name || ', Hire Date: ' || hire_date);
END LOOP;
CLOSE emp_cursor;
END;
Output:
Name: John Doe, Hire Date: 2023-01-01
Name: Jane Smith, Hire Date: 2023-02-01
Experiment: Write a database trigger, which fires if you try to insert, update or delete after 7 'o' clock.
Commands:
CREATE OR REPLACE TRIGGER check_time
BEFORE INSERT OR UPDATE OR DELETE ON Employees
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE, 'HH24') >= 19 THEN
RAISE_APPLICATION_ERROR(-20001, 'Operation not allowed after 7 PM');
END IF;
END;
Output:
Trigger created successfully
Experiment: Show the concept of ER diagram & draw ER diagram of Student Record System.
Commands:
-- ER Diagram Concept
-- An ER diagram for a Student Record System typically includes entities such as Students, Courses, Enrollments,
Instructors, etc.
-- Each entity has attributes, and relationships are shown between entities.
Output:
Concept and drawing of ER diagram provided.
Experiment: Consider any two relational table to show the concept of Primary key & foreign key.
Commands:
-- Students table with Primary Key
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100)
);
-- Enrollments table with Foreign Key
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);
Output:
Tables created with primary key and foreign key constraints.
