Scribd
Upload
40 views6 pages

Dbms Assignment-2: Name:Insha Mustafa Roll Number:120CS0191

Uploaded by

devraj changkakati

The document contains SQL queries that create a database and table, insert sample data, perform various operations like adding/updating/deleting columns, renaming tables and columns, granting and revoking user permissions. The key operations include: 1) Creating a database and table to store employee data with fields like ID, name, age, salary, department. 2) Inserting sample employee records and performing queries to view data. 3) Adding, updating and deleting columns like job code, changing data types, renaming columns and tables. 4) Granting and revoking user permissions on the table.

Copyright:

© All Rights Reserved
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt

Dbms Assignment-2: Name:Insha Mustafa Roll Number:120CS0191

Uploaded by

devraj changkakati
40 views6 pages
The document contains SQL queries that create a database and table, insert sample data, perform various operations like adding/updating/deleting columns, renaming tables and columns, granting and revoking user permissions. The key operations include: 1) Creating a database and table to store employee data with fields like ID, name, age, salary, department. 2) Inserting sample employee records and performing queries to view data. 3) Adding, updating and deleting columns like job code, changing data types, renaming columns and tables. 4) Granting and revoking user permissions on the table.

Original Title

120CS0191_DBMS_ASGN-2

Copyright

© © All Rights Reserved

Available Formats

DOCX, PDF, TXT or read online from Scribd

Did you find this document useful?

Is this content inappropriate?

The document contains SQL queries that create a database and table, insert sample data, perform various operations like adding/updating/deleting columns, renaming tables and columns, granting and revoking user permissions. The key operations include: 1) Creating a database and table to store employee data with fields like ID, name, age, salary, department. 2) Inserting sample employee records and performing queries to view data. 3) Adding, updating and deleting columns like job code, changing data types, renaming columns and tables. 4) Granting and revoking user permissions on the table.

Copyright:

© All Rights Reserved
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
40 views6 pages

Dbms Assignment-2: Name:Insha Mustafa Roll Number:120CS0191

Uploaded by

devraj changkakati
The document contains SQL queries that create a database and table, insert sample data, perform various operations like adding/updating/deleting columns, renaming tables and columns, granting and revoking user permissions. The key operations include: 1) Creating a database and table to store employee data with fields like ID, name, age, salary, department. 2) Inserting sample employee records and performing queries to view data. 3) Adding, updating and deleting columns like job code, changing data types, renaming columns and tables. 4) Granting and revoking user permissions on the table.

Copyright:

© All Rights Reserved
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 6

Name:Insha Mustafa

Roll Number:120CS0191

DBMS ASSIGNMENT-2
CREATE DATABASE trial

USE trial

CREATE TABLE Employee


(
Employee_ID varchar(10),
Name varchar(30),
Age int,
Salary int,
Department varchar(15)
);

INSERT INTO Employee VALUES('EXE1001', 'Adiya', 25, 30000, 'Executive'),


('ACC1002', 'Aditya', 28, 31000,
'Accountant'),
('SAL1003', 'Priyanka', 30, 32000,
'Salesman'),
('EXE1004', 'Anmol', 35, 31000,
'Executive'),
('ACC1005', 'Rahul', 31, 29000,
'Accountant'),
('SAL1006', 'Shubham', 29, 27000,
'Salesman'),
('SAL1007', 'Sam', 33, 32000,
'Salesman'),
('SAL1008', 'Rohan', 33, 28000,
'Salesman'),
('ACC1009', 'Priya', 23, 35000,
'Accountant'),
('EXE1010', 'Kabir', 29, 26000,
'Executive');

SELECT * FROM Employee;


DROP TABLE Employee;

ALTER TABLE Employee ADD Job_Code varchar(10);


SELECT * FROM Employee;

UPDATE Employee SET Job_Code ='EX01' WHERE Employee_ID = 'EXE1001';


UPDATE Employee SET Job_Code ='AC01' WHERE Employee_ID = 'ACC1002';
UPDATE Employee SET Job_Code ='SA01' WHERE Employee_ID = 'SAL1003';
UPDATE Employee SET Job_Code ='EX02' WHERE Employee_ID = 'EXE1004';
UPDATE Employee SET Job_Code ='AC02' WHERE Employee_ID = 'ACC1005';
UPDATE Employee SET Job_Code ='SA02' WHERE Employee_ID = 'SAL1006';
UPDATE Employee SET Job_Code ='SA03' WHERE Employee_ID = 'SAL1007';
UPDATE Employee SET Job_Code ='SA04' WHERE Employee_ID = 'SAL1008';
UPDATE Employee SET Job_Code ='AC03' WHERE Employee_ID = 'ACC1009';
UPDATE Employee SET Job_Code ='EX03' WHERE Employee_ID = 'EXE1010';
SELECT * FROM Employee;

ALTER TABLE Employee DROP COLUMN Age;


ALTER TABLE Employee ADD Age date;
EXEC sp_rename 'Employee.Age', 'DOB';
UPDATE Employee SET DOB ='1997-02-03' WHERE Employee_ID = 'EXE1001';
UPDATE Employee SET DOB ='1994-08-01' WHERE Employee_ID = 'ACC1002';
UPDATE Employee SET DOB ='1992-11-17' WHERE Employee_ID = 'SAL1003';
UPDATE Employee SET DOB ='1987-08-14' WHERE Employee_ID = 'EXE1004';
UPDATE Employee SET DOB ='1991-01-31' WHERE Employee_ID = 'ACC1005';
UPDATE Employee SET DOB ='1993-05-29' WHERE Employee_ID = 'SAL1006';
UPDATE Employee SET DOB ='1989-06-12' WHERE Employee_ID = 'SAL1007';
UPDATE Employee SET DOB ='1989-07-06' WHERE Employee_ID = 'SAL1008';
UPDATE Employee SET DOB ='1999-02-21' WHERE Employee_ID = 'ACC1009';
UPDATE Employee SET DOB ='1993-09-27' WHERE Employee_ID = 'EXE1010';

SELECT CONVERT(varchar(10),CONVERT(DATE,dob,105),105)AS DOB FROM Employee;

ALTER TABLE Employee ALTER COLUMN Department varchar(80);

ALTER TABLE Employee ADD Age int;


UPDATE Employee SET Age= DATEDIFF(YEAR, DOB, GETDATE())
WHERE DOB IN (SELECT DOB FROM Employee);
SELECT * FROM Employee;

EXEC sp_rename 'Employee.Salary', 'Net_Salary';


UPDATE Employee SET Net_salary=12*Net_Salary;
SELECT * FROM Employee;

DELETE FROM Employee WHERE Employee_ID = 'EXE1010';


SELECT * FROM Employee;

EXEC sp_rename 'Employee', 'Employee_Details';

TRUNCATE TABLE Employee_Details;


SELECT * FROM Employee_Details;

CREATE LOGIN new_login WITH PASSWORD = '1234';


CREATE USER new_user FOR LOGIN new_login;
GRANT SELECT ON Employee TO new_user;
REVOKE SELECT ON Employee FROM new_user;
ALTER TABLE Employee ADD Job_Code varchar(10);
SELECT * FROM Employee;

UPDATE Employee SET Job_Code ='EX01' WHERE Employee_ID = 'EXE1001';


UPDATE Employee SET Job_Code ='AC01' WHERE Employee_ID = 'ACC1002';
UPDATE Employee SET Job_Code ='SA01' WHERE Employee_ID = 'SAL1003';
UPDATE Employee SET Job_Code ='EX02' WHERE Employee_ID = 'EXE1004';
UPDATE Employee SET Job_Code ='AC02' WHERE Employee_ID = 'ACC1005';
UPDATE Employee SET Job_Code ='SA02' WHERE Employee_ID = 'SAL1006';
UPDATE Employee SET Job_Code ='SA03' WHERE Employee_ID = 'SAL1007';
UPDATE Employee SET Job_Code ='SA04' WHERE Employee_ID = 'SAL1008';
UPDATE Employee SET Job_Code ='AC03' WHERE Employee_ID = 'ACC1009';
UPDATE Employee SET Job_Code ='EX03' WHERE Employee_ID = 'EXE1010';
SELECT * FROM Employee;

ALTER TABLE Employee DROP COLUMN Age;


ALTER TABLE Employee ADD Age date;
EXEC sp_rename 'Employee.Age', 'DOB';
UPDATE Employee SET DOB ='1997-02-03' WHERE Employee_ID = 'EXE1001';
UPDATE Employee SET DOB ='1994-08-01' WHERE Employee_ID = 'ACC1002';
UPDATE Employee SET DOB ='1992-11-17' WHERE Employee_ID = 'SAL1003';
UPDATE Employee SET DOB ='1987-08-14' WHERE Employee_ID = 'EXE1004';
UPDATE Employee SET DOB ='1991-01-31' WHERE Employee_ID = 'ACC1005';
UPDATE Employee SET DOB ='1993-05-29' WHERE Employee_ID = 'SAL1006';
UPDATE Employee SET DOB ='1989-06-12' WHERE Employee_ID = 'SAL1007';
UPDATE Employee SET DOB ='1989-07-06' WHERE Employee_ID = 'SAL1008';
UPDATE Employee SET DOB ='1999-02-21' WHERE Employee_ID = 'ACC1009';
UPDATE Employee SET DOB ='1993-09-27' WHERE Employee_ID = 'EXE1010';

SELECT CONVERT(varchar(10),CONVERT(DATE,dob,105),105)AS DOB FROM Employee;

ALTER TABLE Employee ALTER COLUMN Department varchar(80);


ALTER TABLE Employee ADD Age int;
UPDATE Employee SET Age= DATEDIFF(YEAR, DOB, GETDATE())
WHERE DOB IN (SELECT DOB FROM Employee);
SELECT * FROM Employee;

EXEC sp_rename 'Employee.Salary', 'Net_Salary';


UPDATE Employee SET Net_salary=12*Net_Salary;
SELECT * FROM Employee;
DELETE FROM Employee WHERE Employee_ID = 'EXE1010';
SELECT * FROM Employee;

EXEC sp_rename 'Employee', 'Employee_Details';

TRUNCATE TABLE Employee_Details;


SELECT * FROM Employee_Details;

CREATE LOGIN new_login WITH PASSWORD = '1234';


CREATE USER new_user FOR LOGIN new_login;
GRANT SELECT ON Employee TO new_user;
REVOKE SELECT ON Employee FROM new_user;

You might also like