Question

Create table, create primary and foreign key constraints. Create index on the table to satisfy a query with aggregate functions.

Answer 1

Note: Using SQL*Plus

            __________ ---> Primary key

            _ _ _ _ _ _ _ _ ---> Foreign key

Database:

Department (dname, dnumber)

Employee (ssn, name, salary, sex, address, dno)

To Create two tables (referencing and referenced)

1. Creating referenced table Department first:

    Create table Department (

    dname varchar(15) unique not null,

    dnumber int ,

    Primary key (dnumber));

desc Department;

2. Inserting data into Department Table

     Insert into Department values ('Research',1);

     Insert into Department values ('HR',2);

    Insert into Department values ('Development',3);

     Insert into Department values ('Testing',4);

select * from department;

3. Creating referencing table Employee first

   Create table Employee(

   ssn char(9),

   name varchar(15) not null,

   salary decimal(10,2),

   sex char,

   address varchar(30),

   dno int not null,

   primary key(ssn),

   foreign key(dno) references Department(dnumber));

  

desc Employee

4. Inserting values into Employees

   Insert into Employee values('emp001','Ram',30000,'M','RT Nagar, Blore',3);

   Insert into Employee values('emp002','Sudha',75000,'F','Hebbal, Blore',2);

   Insert into Employee values('emp003','Ravi',20000,'M','Hebbal, Blore',4);

   Insert into Employee values('emp004','Rohan',80000,'M','RT Nagar, Mysore',1);

   Insert into Employee values('emp005','Amar',35000,'M','MG Road, Mysore',3);

   Insert into Employee values('emp006','Anil',45000,'M','MG Road, Noida',3);

   Insert into Employee values('emp007','Tanya',35000,'F','Yelahanka, Blore',3);

   Insert into Employee values('emp008','Kavita',50000,'F','Baglur, Blore',1);

   Insert into Employee values('emp009','John',45000,'M','RT Nagar, Blore',4);

  

select * from employee;

5. Create index on employyes table on salary column to use aggregate functions

   Create Index idx on Employee(salary);

  

6. Use aggregate function

   select sum(salary) from employee;