Answer to Question #266597 in Databases | SQL | Oracle | MS Access for saurav kumar

Question

Q2.

EMP_ID

FIRST_NAME

LAST_NAME

SALARY

DEPARTMENT

0011

Preeti

Raheja

100000

HR

0023

Geetika

Jindal

80000

Admin

0032

Suresh

Sai

300000

HR

0044

Yash

Bhatia

500000

Admin

0056

Kumar

Gautam

500000

Admin

0067

Kiran

SaiTeja

200000

Account

0073

Raj

Goyal

75000

Account

0089

Priya

Jindal

90000

Admin

Create the given database with the condition that EMP_ID and FIRST_NAME will be the primary key of table and all unique values in LAST_NAME (3 Marks)

Perform the following queries on created database.  

1. SQL query to fetch Last name of employees whose department is Account and salary is more than 80000.                                                                                   (2 Mark)

2. Write an SQL query to fetch “FIRST_NAME” from the table in upper case.                     (1 Mark)

3. SQL query to extract the average salary given to the employees.                                 (1 Mark)

4. Change the department of KUMAR from Admin to Account.                                                  (1 Mark)

5. Delete the FIRST_NAME column from the database.                                               (2 Marks).

Answer 1

SOLUTION TO THE ABOVE QUESTION

SOLUTION TO THE ABOVE QUESTION

Create the given database with the condition that EMP_ID and FIRST_NAME will be the primary 

key of table and all unique values in LAST_NAME (3 Marks)

ANSWER.

CREATE TABLE My_database(

  EMP_ID INT  NOT NULL,

  FIRST_NAME  VARCHAR (20) NOT NULL,

  LAST_NAME   VARCHAR (20)  NOT NULL,

  SALARY    DECIMAL (18, 2),       

  DEPARTMENT  VARCHAR(25) NOT NULL,,    

  PRIMARY KEY (EMP_ID )

  PRIMARY KEY (EMP_ID )

  UNIQUE LAST_NAME  

);

Perform the following queries on created database.  

1. SQL query to fetch Last name of employees whose department is Account and salary is more than 80000.

ANSWER.

SELECT LAST_NAME FROM My_database WHERE DEPARTMENT ='Account' AND SALARY > 80000;                                         (2 Mark)

2. Write an SQL query to fetch “FIRST_NAME” from the table in upper case.  (1 Mark)

ANSWER.

SELECT UPPER(FIRST_NAME) FROM My_database;

3. SQL query to extract the average salary given to the employees.  (1 Mark)

ANSWER.

SELECT AVG(SALARY) FROM My_database;

4. Change the department of KUMAR from Admin to Account.  (1 Mark)

ANSWER.

UPDATE My_database

SET DEPARTMENT = 'Account'

WHERE DEPARTMENT = 'KUMAR';

5. Delete the FIRST_NAME column from the database. 

ANSWER.

ALTER TABLE My_database

DROP COLUMN FIRST_NAME;