Procedure:
Step-1:
CREATE TABLE Employees (
EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50)
);
Step-2;
DELIMITER //
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)
BEGIN
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
WHERE EmployeeID = EmployeeID;
END //
DELIMITER ;
Step-3
CALL GetEmployeeDetails(1);
Another pbm:
Step-2:
DELIMITER //
CREATE PROCEDURE ManageEmployee(
IN Action VARCHAR(10),
IN EmployeeID INT,
IN FirstName VARCHAR(50),
IN LastName VARCHAR(50),
IN Department VARCHAR(50)
BEGIN
IF Action = 'INSERT' THEN
INSERT INTO Employees (FirstName, LastName, Department)
VALUES (FirstName, LastName, Department);
ELSEIF Action = 'UPDATE' THEN
UPDATE Employees
SET FirstName = FirstName, LastName = LastName, Department = Department
WHERE EmployeeID = EmployeeID;
ELSEIF Action = 'DELETE' THEN
DELETE FROM Employees
WHERE EmployeeID = EmployeeID;
END IF;
END //
DELIMITER ;
Step-3
CALL ManageEmployee('INSERT', NULL, 'John', 'Doe', 'HR');
CALL ManageEmployee('UPDATE', 1, 'Jane', 'Smith', 'Finance');
CALL ManageEmployee('DELETE', 1, NULL, NULL, NULL);
Date:
.MySQL comes with the following data types for storing a date or a date/time value in the database:
● DATE -> format YYYY-MM-DD
● DATETIME -> format: YYYY-MM-DD HH:MI:SS
● TIMESTAMP -> format: YYYY-MM-DD HH:MI:SS
● YEAR -> format YYYY or YY
CREATE TABLE DateTimeExample (
ID INT AUTO_INCREMENT PRIMARY KEY,
EventDateTime DATETIME
);
INSERT INTO DateTimeExample (EventDateTime) VALUES ('2024-05-23 14:30:00');
SELECT * FROM DateTimeExample;
AUTO INCREMENT
● Auto-increment allows a unique number to be generated automatically when a new record is
inserted into a table.
● Often this is the primary key field that we would like to be created automatically every time a new
record is inserted.
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
CREATE INDEX
CREATE INDEX index_name
ON table_name (column1, column2, ...);
25
Constraints
● used to specify rules for data in a table
● used to limit the type of data that can go into a table
The following constraints are commonly used in SQL:
● NOT NULL - Ensures that a column cannot have a NULL value
● UNIQUE - Ensures that all values in a column are different
● PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row
in a table
● FOREIGN KEY - Prevents actions that would destroy links between tables
● CHECK - Ensures that the values in a column satisfies a specific condition
● DEFAULT - Sets a default value for a column if no value is specified
● CREATE INDEX - Used to create and retrieve data from the database very quickly
5.In SQL, a view is a virtual table that represents the result set of a SELECT query.
Unlike physical tables, which store data, views do not store data themselves; instead,
they are dynamically generated based on the underlying tables' data and the query used
to create the view.
CREATE VIEW EmployeeDetails AS
SELECT CONCAT(FirstName, ' ', LastName) AS FullName, DepartmentID
FROM Employees;
Example:
CREATE TABLE Employees (
EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2)
);
INSERT INTO Employees (FirstName, LastName, Department, HireDate, Salary)
VALUES
('John', 'Doe', 'HR', '2022-01-15', 50000),
('Jane', 'Smith', 'Finance', '2023-03-20', 60000),
('Michael', 'Brown', 'IT', '2021-11-05', 70000),
('Emily', 'Davis', 'Marketing', '2020-09-12', 55000);
CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees;
