1.a.

Create a table named Students in SQL with the following constraints:

StudentID as the Primary Key

Email as Unique

Name as NOT NULL

Age with a CHECK constraint to ensure age is greater than or equal to 18?

Creating Tables with Constraints

In SQL, we can create tables with various constraints such as:

• PRIMARY KEY: Uniquely identifies a record.

• FOREIGN KEY: Enforces a link between two tables.

• NOT NULL: Ensures that a column cannot have a NULL value.

• UNIQUE: Ensures that all values in a column are unique.

• CHECK: Ensures that the value in a column satisfies a given condition.

• DEFAULT: Provides a default value if no value is provided.

CREATE It is used to create a new table in the database.

Syntax:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);

SQL:

CREATE TABLE Students (

StudentID INT PRIMARY KEY, -- Uniquely identifies each student

Email VARCHAR(100) UNIQUE, -- Email must be unique

Name VARCHAR(100) NOT NULL, -- Name is required (not null)

Age INT CHECK (Age >= 18) -- Age must be 18 or older

);
 b. Write the SQL command to alter the Students table by adding a new column Department of
type VARCHAR(50) with NOT NULL and a default value 'CSE'.

a) ALTER:

• It is used to alter the structure of the database.

• This change could be either to modify the characteristics of an existing attribute or
probably to add a new attribute.

Syntax:

ALTER TABLE table_name

ADD column_name datatype constraint;

SQL:
ALTER TABLE Students

ADD Department VARCHAR(50) NOT NULL DEFAULT 'CSE';

c. How can you insert multiple rows of data into the Students table while satisfying all the defined
constraints?

Definition:

Adds multiple new records (rows) into a table in a single statement.

Syntax:

INSERT INTO table_name (column1, column2, ...)

VALUES

(value1_row1, value2_row1, ...),

(value1_row2, value2_row2, ...);

SQL:
INSERT INTO Students (StudentID, Email, Name, Age, Department)

VALUES

(1, '[Link]@[Link]', 'John Doe', 20, 'CSE'),

(2, '[Link]@[Link]', 'Jane Smith', 22, 'ECE'),

(3, '[Link]@[Link]', 'Alex Jones', 19, 'ME');

d. What is the SQL query to drop an existing table named Students from the database?

Definition:

Completely deletes a table and all its data from the database.

Syntax:

DROP TABLE table_name;

SQL:
DROP TABLE Students;

e. Write an SQL statement to create a table Courses with a foreign key referencing the StudentID
column of the Students table.

Definition:

Creates a new table that includes a foreign key to enforce referential integrity between two tables.

Syntax:

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

FOREIGN KEY (column_name) REFERENCES other_table(column)

);

SQL:
CREATE TABLE Courses (

CourseID INT PRIMARY KEY, -- Unique ID for each course

CourseName VARCHAR(100) NOT NULL, -- Course name is required

StudentID INT, -- Refers to Students table

FOREIGN KEY (StudentID) REFERENCES Students(StudentID)

);
2.a. Convert salary column value to character

Definition: Converts a numeric column like salary into a character (string) format.

Syntax:

SELECT CAST(salary AS VARCHAR) FROM table_name; -- or

SELECT CONVERT(VARCHAR, salary) FROM table_name; -- SQL Server

Example:

SELECT CAST(salary AS VARCHAR) AS salary_str FROM Employees;

b. Display system date in dd/mm/yyyy format

Definition: Retrieves the current system date and formats it.

Syntax (MySQL):

SELECT DATE_FORMAT(CURDATE(), '%d/%m/%Y') AS current_date;

c. Convert column value to a number

Definition: Converts a character or other format value into a number.

Syntax:

SELECT CAST(column_name AS INT) FROM table_name; -- or

SELECT CONVERT(INT, column_name) FROM table_name; -- SQL Server

Example:

SELECT CAST(salary_str AS INT) AS salary_num FROM Employees;

d. Concatenate two column values into one

Definition: Joins values of two columns into a single string.

Syntax:

SELECT CONCAT(column1, column2) AS combined FROM table_name; -- MySQL/SQL Server

Example:

SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;

e. Padding functions (LPAD & RPAD)

Definition: Pads a string with specified characters from left or right.

 Syntax (Oracle/MySQL):

SELECT LPAD(Name, 10, '*') AS left_padded FROM Employees;

SELECT RPAD(Name, 10, '*') AS right_padded FROM Employees;

f. Trimming functions

Definition: Removes extra spaces or characters from strings.

Syntax:

SELECT TRIM(' ' FROM Name) AS trimmed_name FROM Employees; -- Standard

SELECT LTRIM(Name) AS left_trimmed FROM Employees; -- Leading

SELECT RTRIM(Name) AS right_trimmed FROM Employees; -- Trailing

g. Case conversion functions

Definition: Converts text case (upper/lower/title).

Syntax:

SELECT UPPER(Name) AS upper_name FROM Employees;

SELECT LOWER(Name) AS lower_name FROM Employees;

INITCAP(Name) -- Oracle only: Capitalizes the first letter of each word

h. Date functions

Definition: Used to extract or manipulate date values.

Examples:

SELECT SYSDATE AS today FROM DUAL; -- Current date

SELECT EXTRACT(YEAR FROM SYSDATE) AS year_only FROM DUAL; -- Extract year

SELECT ADD_MONTHS(SYSDATE, 2) AS after_two_months FROM DUAL; -- Add months

SELECT NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday FROM DUAL; -- Next weekday

i. Round a column value to nearest integer

Definition: Rounds a number to the nearest integer.

Syntax:

SELECT ROUND(salary) AS rounded_salary FROM Employees;

3.a. Retrieve employees who were hired before 2017

Definition: Selects all employees whose hire date is earlier than January 1, 2017.

Syntax:

SELECT *

FROM table_name

WHERE date_column < 'YYYY-MM-DD';

Example:

SELECT *

FROM Employees

WHERE HireDate < '2017-01-01';

b. Find employees with salary between 50,000 and 80,000

Definition: Filters records where the Salary is within a given range (inclusive).

Syntax:

SELECT *

FROM table_name

WHERE column BETWEEN value1 AND value2;

Example:

SELECT *

FROM Employees

WHERE Salary BETWEEN 50000 AND 80000;

c. List employees in the Finance department

Definition: Fetches employees based on a specific department.

Syntax:

SELECT *

FROM table_name

WHERE column = 'value';

Example:

SELECT *
FROM Employees

WHERE Department = 'Finance';

d. Sort employees by hire date (newest first)

Definition: Orders the result set in descending order of hire date.

Syntax:

SELECT *

FROM table_name

ORDER BY column DESC;

Example:

SELECT *

FROM Employees

ORDER BY HireDate DESC;

e. Find the average salary per department

Definition: Groups records by department and calculates average salary.

Syntax:

SELECT column, AGGREGATE_FUNCTION(column)

FROM table_name

GROUP BY column;

Example:

SELECT Department, AVG(Salary) AS AvgSalary

FROM Employees

GROUP BY Department;

f. Count employees hired after 2015

Definition: Counts the number of employees with a hire date after 2015.

Syntax:

SELECT COUNT(*)

FROM table_name
WHERE date_column > 'YYYY-MM-DD';

Example:

SELECT COUNT(*) AS NumEmployees

FROM Employees

WHERE HireDate > '2015-12-31';

g. Departments with average salary > 60,000

Definition: Filters groups using HAVING after grouping.

Syntax:

SELECT column

FROM table_name

GROUP BY column

HAVING AGGREGATE_FUNCTION(column) condition;

Example:

SELECT Department

FROM Employees

GROUP BY Department

HAVING AVG(Salary) > 60000;

h. Employees with salary greater than any in HR

Definition: Compares column value with values from a subquery using ANY.

Syntax:

SELECT *

FROM table_name

WHERE column > ANY (

SELECT column

FROM table_name

WHERE condition

);

Example:
SELECT *

FROM Employees

WHERE Salary > ANY (

SELECT Salary

FROM Employees

WHERE Department = 'HR'

);

i. Employees working in IT or Finance departments

Definition: Filters records using IN for multiple values.

Syntax:

SELECT *

FROM table_name

WHERE column IN (value1, value2, ...);

Example:

SELECT *

FROM Employees

WHERE Department IN ('IT', 'Finance');

j. List of employees from HR and IT

Definition:

This query retrieves all employee records where the Department is either HR or IT.

Syntax:

SELECT *

FROM table_name

WHERE column_name IN ('value1', 'value2');

Example:

SELECT *

FROM Employees

WHERE Department IN ('HR', 'IT');

4.a. Count the number of students in each department

Definition:

Returns the number of students grouped by each department.

Syntax:

SELECT column, COUNT(*)

FROM table_name

GROUP BY column;

Example:

SELECT Department, COUNT(*) AS StudentCount

FROM Students

GROUP BY Department;

b. Find the average marks of students per department

Definition:

Calculates the average of the Marks column, grouped by Department.

Syntax:

SELECT column, AVG(numeric_column)

FROM table_name

GROUP BY column;

Example:

SELECT Department, AVG(Marks) AS AvgMarks

FROM Students

GROUP BY Department;

c. Find the highest and lowest marks in each department

Definition:

Returns the MAX() and MIN() of marks for each department.

Syntax:

SELECT column, MAX(numeric_column), MIN(numeric_column)

FROM table_name

GROUP BY column;
 Example:

SELECT Department, MAX(Marks) AS Highest, MIN(Marks) AS Lowest

FROM Students

GROUP BY Department;

d. Find the total number of male and female students

Definition:

Groups records by gender and counts them.

Syntax:

SELECT column, COUNT(*)

FROM table_name

GROUP BY column;

Example:

SELECT Gender, COUNT(*) AS Total

FROM Students

GROUP BY Gender;

e. Find departments where the average marks are greater than 80

Definition:

Filters groups using HAVING after calculating the average.

Syntax:

SELECT column, AVG(numeric_column)

FROM table_name

GROUP BY column

HAVING AVG(numeric_column) > value;

Example:

SELECT Department, AVG(Marks) AS AvgMarks

FROM Students

GROUP BY Department

HAVING AVG(Marks) > 80;

f. Find the number of students from each city

Definition:

Groups by city and counts the students in each.

Syntax:

SELECT column, COUNT(*)

FROM table_name

GROUP BY column;

Example:

SELECT City, COUNT(*) AS StudentCount

FROM Students

GROUP BY City;

g. Find the total marks obtained by students in each department

Definition:

Uses SUM() function grouped by department.

Syntax:

SELECT column, SUM(numeric_column)

FROM table_name

GROUP BY column;

Example:

SELECT Department, SUM(Marks) AS TotalMarks

FROM Students

GROUP BY Department;

h. Find the average age of students in each department

Definition:

Calculates the average Age grouped by department.

Syntax:

SELECT column, AVG(numeric_column)

FROM table_name

GROUP BY column;

Example:
SELECT Department, AVG(Age) AS AvgAge

FROM Students

GROUP BY Department;

i. Find students from each department who scored above 90

Definition:

This query retrieves students who scored above 90 marks, grouped or filtered by their Department.

Syntax:

SELECT *

FROM table_name

WHERE numeric_column > value;

Example:

SELECT StudentID, Name, Department, Marks

FROM Students

WHERE Marks > 90;

SQL:

ORDER BY Department;

j. Find the department with the highest number of students

Definition:

This query counts students per department and finds the one with the maximum count.

Syntax (using ORDER BY and LIMIT):

SELECT column, COUNT(*)

FROM table_name

GROUP BY column

ORDER BY COUNT(*) DESC

LIMIT 1;

Example (MySQL / PostgreSQL / SQLite):

SELECT Department, COUNT(*) AS StudentCount

FROM Students

GROUP BY Department

ORDER BY StudentCount DESC

LIMIT 1;
5.a. Retrieve employees and their department names where both records exist (INNER JOIN)

Definition:

Returns only the rows where there is a match in both Employees and Departments.

Syntax:

SELECT columns

FROM table1

INNER JOIN table2 ON [Link] = [Link];

Example:

SELECT [Link], [Link], [Link]

FROM Employees e

INNER JOIN Departments d ON [Link] = [Link];

b. Fetch all employees and their departments, including those without any department (LEFT JOIN)

Definition:

Returns all employees, and matches from departments if available. If no department, still shows the
employee with NULL for department.

Syntax:

SELECT columns

FROM table1

LEFT JOIN table2 ON [Link] = [Link];

Example:

SELECT [Link], [Link], [Link]

FROM Employees e

LEFT JOIN Departments d ON [Link] = [Link];

c. Get all departments and their employees, including departments with no employees (RIGHT
JOIN)

Definition:

Returns all departments, and matches from employees if available. If no employee, still shows the
department with NULL for employee info.

Syntax:
SELECT columns

FROM table1

RIGHT JOIN table2 ON [Link] = [Link];

Example (for databases that support RIGHT JOIN):

SELECT [Link], [Link], [Link]

FROM Employees e

RIGHT JOIN Departments d ON [Link] = [Link];

d. Retrieve all employees and departments, including unmatched records on both sides (FULL
OUTER JOIN)

Definition:

Returns all records from both tables, with NULLs where no match exists.

Syntax:

SELECT columns

FROM table1

FULL OUTER JOIN table2 ON [Link] = [Link];

Example (works in PostgreSQL & SQL Server):

SELECT [Link], [Link], [Link]

FROM Employees e

FULL OUTER JOIN Departments d ON [Link] = [Link];

Sql:
Copy code

-- MySQL version

SELECT [Link], [Link], [Link]

FROM Employees e

LEFT JOIN Departments d ON [Link] = [Link]

UNION

SELECT [Link], [Link], [Link]

FROM Departments d
LEFT JOIN Employees e ON [Link] = [Link];

e. Find pairs of employees who share the same manager (SELF JOIN)

Definition:

Joins a table with itself to compare rows — used here to find employees with the same ManagerID.

Syntax:

SELECT [Link], [Link]

FROM table a, table b

WHERE a.common_key = b.common_key AND a.other_condition;

Example:

SELECT [Link] AS Employee1, [Link] AS Employee2, [Link]

FROM Employees e1

JOIN Employees e2 ON [Link] = [Link]

WHERE [Link] <> [Link];

This query finds pairs of employees who report to the same manager but excludes self-pairing.
6.a. Grant the SELECT privilege on the employees table to user john

Definition:

Allows the user john to read data from the employees table.

Syntax:

GRANT SELECT ON table_name TO user_name;

Example:

GRANT SELECT ON employees TO john;

b. Grant all privileges (SELECT, INSERT, UPDATE, DELETE) on the employee table to user john

Definition:

Gives user john full control (except ownership) over the employee table.

Syntax:

GRANT ALL PRIVILEGES ON table_name TO user_name;

Example:

GRANT ALL PRIVILEGES ON employee TO john;

c. Revoke the SELECT privilege on the employees table from user john

Definition:

Removes the previously granted SELECT permission.

Syntax:

REVOKE SELECT ON table_name FROM user_name;

Example:

REVOKE SELECT ON employees FROM john;

d. Revoke all privileges (SELECT, INSERT, UPDATE, DELETE) on the employee table from user john

Definition:

Takes back all data-access privileges from john.

Syntax:

REVOKE ALL PRIVILEGES ON table_name FROM user_name;

Example:
REVOKE ALL PRIVILEGES ON employee FROM john;

e. Save all the changes made in the current transaction

Definition:

Confirms and permanently saves all pending changes in the current session.

Syntax:

COMMIT;

Example:

COMMIT;

f. Undo all the changes made in the current transaction

Definition:

Cancels all uncommitted changes made in the current session.

Syntax:

ROLLBACK;

Example:

ROLLBACK;
a. Create a view named employee_view to show employee names and departments

Definition:

A view is a virtual table that contains a stored query. It can be used to simplify complex queries and
restrict access to certain columns.

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Example:

CREATE VIEW employee_view AS

SELECT Name, Department

FROM Employees;

b. Retrieve data from the employee_view

Definition:

This query retrieves data from the employee_view view just like querying a regular table.

Syntax:

SELECT columns FROM view_name;

Example:

SELECT * FROM employee_view;

c. Update data through a view (only works if the view is updatable)

Definition:

You can update data through a view only if the view meets certain criteria (such as being based on a
single table, no aggregation, etc.).

Syntax:

UPDATE view_name

SET column1 = value1, column2 = value2

WHERE condition;

Example:
UPDATE employee_view

SET Department = 'Finance'

WHERE Name = 'John Doe';

d. Remove the employee_view from the database

Definition:

To delete a view from the database.

Syntax:

DROP VIEW view_name;

Example:

DROP VIEW employee_view;

e. Create a view to display employees in the 'HR' department only

Definition:

This view will show only the employees who belong to the HR department.

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE department_column = 'HR';

Example:

CREATE VIEW hr_employee_view AS

SELECT Name, Department

FROM Employees

WHERE Department = 'HR';

a. Automatically assigns a default joining date if not provided

Definition:

Trigger checks if JoiningDate is NULL during insert and sets it to the current date.

MySQL Example:

DELIMITER $$

CREATE TRIGGER set_default_joining_date

BEFORE INSERT ON Employees

FOR EACH ROW

BEGIN

IF [Link] IS NULL THEN

SET [Link] = CURDATE();

END IF;

END$$

DELIMITER ;

b. Logs inserted student records into a students_log table

Definition:

On inserting into Students, the same data is copied to a log table.

MySQL Example:

DELIMITER $$

CREATE TRIGGER log_student_insert

AFTER INSERT ON Students

FOR EACH ROW

BEGIN

INSERT INTO students_log(StudentID, Name, Department, Marks)

VALUES ([Link], [Link], [Link], [Link]);

END$$

DELIMITER ;

c. Prevents salary reduction for employees

Definition:

This trigger blocks updates if the new salary is less than the old one.
 MySQL Example:

DELIMITER $$

CREATE TRIGGER prevent_salary_decrease

BEFORE UPDATE ON Employees

FOR EACH ROW

BEGIN

IF [Link] < [Link] THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'Salary reduction is not allowed';

END IF;

END$$

DELIMITER ;

d. Stores deleted employee records into a backup table before deletion

Definition:

Before deleting from Employees, the record is saved into Employees_Backup.

MySQL Example:

DELIMITER $$

CREATE TRIGGER backup_before_delete

BEFORE DELETE ON Employees

FOR EACH ROW

BEGIN

INSERT INTO Employees_Backup(EmployeeID, Name, Department, Salary)

VALUES ([Link], [Link], [Link], [Link]);

END$$

DELIMITER ;