SQL Question Practical 2018

aa.  Write the SQL to create the ‘employee’ table by using datatype, size, and constraint.

CREATE TABLE "employee" (

      "EmpNo"     VARCHAR(6) NOT NULL,

      "Name"      VARCHAR(20) NOT NULL,

      "Address"   VARCHAR(20) NOT NULL,

      "Department"      VARCHAR(10) NOT NULL,

      "Allowance" NUMERIC(18,2) NOT NULL,

      "Basic_Salary"    NUMERIC(18,2) NOT NULL,

      PRIMARY KEY("EmpNo")

);

bb.  Display the employee table

SELECT * FROM `employee`

 

cc.  Sql to list the employee whose salary is more than 20,000

SELECT * FROM `employee` WHERE `Basic_salary`>20000

dd.      SQL to list the employee who are working in the account department

SELECT * FROM `employee` WHERE `Department` = 'account'

ee.      Sql to modify “Name” column into “FirstName”

ALTER TABLE `employee` CHANGE `Name` `FirstName` VARCHAR(20) ;

f f. Find out the employee whose department account and salary greater then 25,000

SELECT * FROM `employee` WHERE `Department` = 'account' and `Basic_salary`>25000

 

fg.       Find out the maximum allowance of the employee

select * from employee where Allowance = (select max(`Allowance`) from employee)