Scribd
Upload
4 views8 pages

Employee Database Management Overview

The document outlines various SQL commands and their functionalities, including Data Manipulation Language (DML) commands like DELETE, Data Definition Language (DDL) commands such as DROP and TRUNCATE, and Data Control Language (DCL) commands like GRANT and REVOKE. It also discusses the characteristics of CHAR and VARCHAR data types, as well as foreign key constraints like ON DELETE CASCADE and ON DELETE SET NULL. Additionally, it provides examples of employee data and their statuses.

Uploaded by

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

Employee Database Management Overview

The document outlines various SQL commands and their functionalities, including Data Manipulation Language (DML) commands like DELETE, Data Definition Language (DDL) commands such as DROP and TRUNCATE, and Data Control Language (DCL) commands like GRANT and REVOKE. It also discusses the characteristics of CHAR and VARCHAR data types, as well as foreign key constraints like ON DELETE CASCADE and ON DELETE SET NULL. Additionally, it provides examples of employee data and their statuses.

Uploaded by

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

EMPLOYEE Manager

Employee_id Name Employee_id Name


1 Ram 1 Ram
2 Shyam 4 Vidit
3 Joya
4 Vidit
1 Ram
4 Vidit

EMPLOYEE
Employee_id Name
2 Rohit
3 Shyam
8 Joya
1 Vidit

6 Ram

Parameter
Language

Purpose

Syntax
ROLLBACK
Data

DETAILS EMPLOYEE
EMP_ID DET_ID SALARY EMP_ID EMP_NAME
EMPLOYEE
IsActive
Yes
Yes
No

Yes
No

Delete
Data Manipulation Language Command (DML)

Used to delete content in rows of a table

DELETE from;(to delete all the rows of the table)


DELETE FROM table_name WHERE condition; (to delete the row of the table
as per the condition)
Can be Rollback
Removes specific rows depending upon condition
ID NAME
1 Ramesh
2 Khilan
3 Kaushik
4 Chaitali
5 Hardik
6 Komal
7 Muffy

Drop Truncate
Data Definition Language Command (DDL) Data Definition Language Command (DDL)
Used to delete entire content of table Used to delete entire content of table
along with the table structure. leaving the table structure.

DROP table <table_name>; TRUNCATE table <table_name>;


Cannot be Rollback Cannot be Rollback
Removes the entire data immediately. Removes all rows
AGE ADDRESS SALARY NAME SALARY
21 AB 1000 Ramesh 1000
22 CD 2000 Khilan 2000
23 EF 3000 Kaushik 3000
24 GF 4000
25 IJ 5000 Points
26 KL 6000 1
27 MN 7000 2
3
EMPLOYEE 4
Employee_id Name IsActive IsActive Count(*)
1 Ram Yes Yes 3
3 Shyam Yes No 2 BEHAVIOR
5 Joya No Effect on child records
6 Vidit
Yes Syntax
8 Ram No

DEPARTMENT
DET_ID DEPT_NAME MANAGER
EID
1
2
3

CHAR
CHAR stands for “Character”
CHAR datatype is used to store character strings of fixed length
Static memory allocation
Faster

ON DELETE CASCADE
Child records are automatically deleted.
FOREIGN KEY (parent_id) REFERENCES
parent_table_p(id) ON DELETE CASCADE

Second Highest salary


NAME AGE
Varun 32
Mahesh 22
Suresh 43

VARCHAR
VARCHAR stands for “Variable Character”
Character lenth is not fixed.
Dynamic Memory Allocation
Slower

ON DELETE SET NULL


Foreign key values in child records are set to NULL
FOREIGN KEY (parent_id) REFERENCES
parent_table_p(id) ON DELETE SET NULL

[Link]
1
2

4
CITY
Ahmedabad
Kashmir
Kerala

Grant
This DCL command grants permissions to the user on the database objects.
For each user you need to specify the permissions.

It assigns access rights to users.

Example: grant insert,


select on accounts to Ram
CONTACT
12345
34235
12355

Revoke
This DCL command removes permissions if any granted to the users on database objects.
If access for one user is removed; all the particular permissions provided by that users to others will b

It revokes the useraccess rights of users.

Example: revoke insert,


select on accounts from Ram

You might also like