Scribd
Upload
6 views3 pages

SQL Lab: Employee Management Operations

The document outlines a sample lab work assignment focused on database operations, including table creation, constraints, DML operations, queries, joins, views, procedures, functions, and privileges. It specifies tasks such as creating and modifying tables, inserting and updating records, and managing user privileges. The lab work is designed for 1 credit and consists of 30 hours of practical exercises.

Uploaded by

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

SQL Lab: Employee Management Operations

The document outlines a sample lab work assignment focused on database operations, including table creation, constraints, DML operations, queries, joins, views, procedures, functions, and privileges. It specifies tasks such as creating and modifying tables, inserting and updating records, and managing user privileges. The lab work is designed for 1 credit and consists of 30 hours of practical exercises.

Uploaded by

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

Sample Lab Work (Indicative)

(1 Credits, 30 Hours)

➡ Table Operations
• Create a table to store employee information:

Columns: employee_id, name, department, salary

• Add a new column to the employee table:

Column: email address

• Drop the customer table.

• Modify the order table:

Change data type of price from INTEGER to DECIMAL(10,2)

➡ Constraints
• Create a PRIMARY KEY on:

employee_id column in employee table

• Create a FOREIGN KEY on:

customer_id column in order table references customer_id in customer table

• Create a UNIQUE constraint on:

product_id column in product table

➡ DML Operations (INSERT / UPDATE / DELETE)


• Insert records into employee table:

employee_id = 100, name = 'John Doe', department = 'IT', salary = 50000

(Use different values to insert multiple employees)

• Update salary:

Set salary = 60000 where employee_id = 100

• Delete employee:
where employee_id = 100

➡ Queries (SELECT)
• Select all employees in ‘IT’ department.

• Select average salary of employees in ‘Sales’ department.

• Select employee with highest salary.

• Insert new order record:

order_id = 100, customer_id = 1, product_id = 1, quantity = 1, price = 100

• Update quantity of order:

Set quantity = 2 where order_id = 100

• Delete order:

where order_id = 100

• Select all orders placed by customer with ID 1.

➡ Joins & Subqueries


• Join employee & order tables to find all employees who have placed an order.

• Use a subquery to find employees who have placed an order for a product with price >
100.

➡ Views, Procedures, Functions


• Create a view showing:

name, salary of employees in 'IT' earning > 50000

• Write a procedure to insert a new order record.

• Write a function to calculate total price of an order.

➡ Privileges & Roles


• Grant SELECT privilege on employee table to user ‘public’.

• Grant INSERT privilege on order table to user ‘sales’.

• Revoke UPDATE privilege on customer table from user ‘marketing’.


• Create a role ‘manager’ with SELECT, INSERT, UPDATE, DELETE on all tables.

• Grant ‘manager’ role to user ‘admin’.

✅ End of Lab Work

You might also like