Answer to Question #197628 in Databases | SQL | Oracle | MS Access for TALHA AFZAL

Question

Question 1 

Write SQL command to create the SALARY table having the following columns and their constraints. You are required to choose the appropriate data type for each column by your own.

 

Column Name  Data Type Data Type

Month  - -

Basic-salary  - NOT NULL

House-allow  - NOT NULL

Medical-allow  - NOT NULL

Total-salary  - DEFAULT 500

Income-tax  - UNIQUE

Net-salary  - NOT NULL

EmpID  - -

Question 2 

Write the following SQL commands for the SALARAY table created in question 1.

⦁ Add the primary key constraint on Month & EmpID columns using ALTER command.

⦁ Add the foreign key constraint on EmpID column using ALTER command.

Note: EmpID is a primary key column in Employee table.

Answer 1

Question 1

CREATE TABLE SALARY(
  month INT,
  basicSalary DOUBLE NOT NULL,
  houseAllow TEXT NOT NULL,
  medicalAllow DOUBLE NOT NULL,
  totalSalary DOUBLE DEFAULT 500,
  incomeTask DOUBLE UNIQUE,
  netSalary DOUBLE NOT NULL,
  empID INT
  );

Question 2

ALTER TABLE SALARY
  ADD CONSTRAINT salary_pk 
    PRIMARY KEY (month,empID);


ALTER TABLE SALARY
ADD FOREIGN KEY(empID)
REFERENCES Employee(empID);