EXE -11 To create simple tables , with only the primary key constraint ( as a table level constraint & as a field level constraint) (include all data types)

Create database:


1  -  Create a table with details as given below Table Name=> PLAYER

Columns	Column Name	Column Data Type	Constraints
1	player_id	Integer	Primary key
2	Name	varchar (50)
3	Birth_date	date
4	Birth_place	varchar(100)

Table level constraint-No

Ans.

create table player(

player_id integer primary key,

name varchar(50),

birth_date date,

birth_place varchar(100)

);

player;

 ----------------------------------------------------------------------------------------------------------------------------

2 - Create a table with details as given below  Table Name=> Student

Columns	Column Name	Column Data Type	Constraints
1	Roll_no	integer
2	Class	varchar (20)
3	Weight	numeric (6,2)
4	Height	numeric (6,2)

 

Table level constraint=> Roll_no and class as primary key

Ans.

                 create table student(

Roll_no integer,

class varchar(20),

Weight numeric(6,2),

Height numeric(6,2),

primary key(Roll_no,class)

);

---------------------------------------------------------------------------------------------------------------------------- 

Exe-15 - Part 1

 
Create the following tables ( primary keys are underlined.).

  Property(pno,description, area)
  Owner(oname,address,phone)

An owner can have one or more properties, but a property belongs to exactly one owner .

 Create  the relations accordingly ,so that the relationship is handled properly and the relations are in
normalized form (3NF).

a) Insert two records into owner table.
b) insert 2 property records for each owner .
c) Update phone no of “Mr. Nene” to 9890278008
d) Delete all properties from “pune” owned by “ Mr. Joshi”     


ans.

create table owner(
oname text primary key,
address text,
phoneno int
);

create table property(
oname text ,
foreign key (oname) references owner (oname) on delete cascade on update cascade,
pno int primary key ,
desc1 text,
area text
);

create table own_pro(
oname text,
 foreign key (oname) references owner (oname) on delete cascade on update cascade,
pno int ,
foreign key (pno) references property (pno) on delete cascade on update cascade  ,
primary key(oname,pno)
);

a)   insert into owner values('abc','pune',12345678);
      insert into owner values('pqr','pune',98764453);
      insert into owner values('Nene','pune',1234);

b)   insert into property values('abc',233,'new ','pune');
      insert into property values('abc',401,'new ','pune');

c)  alter table owner alter column phoneno type varchar(10) ;
     update owner set phoneno=9890278008 where oname='Nene';

d) delete from owner  where address='pune' and name='abc';

***************************************************************

Exe-17

Set A-1

Create the following relations, for an investment firm
emp( emp-id ,emp-name, address, bdate)
Investor( inv-name , inv-no, inv-date, inv-amt)

An employee may invest in one or more investments, hence he can be an investor.
But an investor need not be an employee of the firm.
Create the Relations accordingly, so that the relationship is handled properly and the relations are
in normalized form (3NF).
Assume appropriate data types for the attributes. Add any new attributes , as required by the
queries. Insert sufficient number of records in the relations / tables with appropriate values as
suggested by some of the queries.

Write the following queries & execute them.
 1. List the distinct names of customers who are either employees, or investors or both.
2. List the names of customers who are either employees , or investors or both.
3. List the names of emloyees who are also investors.
4. List the names of employees who are not investors.

Ans.

Create table emp(
emp_id int primary key,
emp_name varchar(20),
address text,
bdate date
);

create table investor
(
inv_name varchar(30),
inv_no int primary key,
inv_date date,
inv_amt numeric(8),
emp_id int,
constraint fk foreign key(emp_id) references emp(emp_id) on delete cascade on update cascade
);

insert into emp values(1,'Raj Hawaldar','Sahakar Nagar','08/24/1995');
insert into emp values(2,'Sanjay Pawar','Shaniwar Peth','03/2/1995');
insert into emp values(3,'Samir Shukla','Laxmi Nagar','08/6/1990');
insert into emp values(4,'Sagar Sharma','Kothrud','05/1/1985');
insert into emp values(5,'Kiran Shinde','Sahakar Nagar','08/2/1992');



insert into investor values('Home',1,'3/23/2015',4000000.0,1);
insert into investor values('Car',2,'3/2/2015',900000.0,1);
insert into investor values('Business',3,'3/23/2015',9000000.0,2);
insert into investor values('Home',4,'3/23/2015',4000000.0,4);
insert into investor values('Car',5,'3/23/2015',4000000.0,1);
insert into investor values('Business',6,'3/23/2015',4000000.0,2);
insert into investor values('Home',7,'3/23/2015',4000000.0,3);


 1. List the distinct names of customers who are either employees, or investors or both.

Ans.

  select  emp_name from emp,investor 
where emp.emp_id=investor.emp_id
 union select emp_name from emp;

2. List the names of customers who are either employees , or investors or both.

Ans

select  emp_name from emp,investor
 where emp.emp_id=investor.emp_id
 union all select emp_name from emp;

3. List the names of emloyees who are also investors.

Ans.
select emp_name from emp Intersect
select emp_name from emp,investor
 where emp.emp_id=investor.emp_id;

4. List the names of employees who are not investors.

Ans
select emp_name from emp
except select emp_name from emp,investor where emp.emp_id=investor.emp_id;

Exp 15 - Part 3

Create the following tables ( primary keys are underlined.).

Sales_order(s_order_no,s_order_date)
Client(client_no, name, address)

A clinet can give on e or more sales_orders , but a sales_order belongs to exactly one client.
Create the Relations accordingly, so that the relationship is handled properly and the relations are
in normalized form (3NF).

a) insert 2 client records into client table
b) insert 3 sales records for each client
c) change order date of client_no ’C004’ to 12/4/08
d) delete all sale records having order date before 10th feb. 08
e) delete the record of client “joshi”

ans.

create table client(
cno int primary key,
name text,
address text
);

create  table osales(
cno int ,
ono integer primary key ,
odate date,
foreign key (cno) references client (cno)on delete cascade on update cascade
);

a) insert 2 client records into client table
insert into client values(1,'abc','pune');
insert into client values(2,'pqr','pune');

b) insert 3 sales records for each client
insert into osales values(1,101,'2-2-2015');
insert into osales values(1,102,'2-4-2014');
insert into osales values(1,103,'2-6-2013');
insert into osales values(2,201,'2-7-2012');
insert into osales values(2,202,'2-2-2011');
insert into osales values(2,203,'2-2-1017');

select *from client;
select *from osales;

c) change order date of client_no ’C004’ to 12/4/08

update  osales set odate='12/4/8' where cno='1';

d) delete all sale records having order date before 10th feb. 08

delete from client where name='joshi';

Exe-14

1. Remove employee table from your database. Create table employee( eno, ename, sal). Add
designation column in the employee table with values restricted to a set of values.

Ans.

drop table employee;

create table employee(
eno int,
ename text,
sal int
);

alter table employee add column desg varchar(30) check(desg in ('worker','maneger','staff'));
****************************************

 2. Remove student table from your database. Create table student( student_no, sname,
date_of_birth). Add new column into student relation named address as a text data type with NOT
NULL integrity constraint and a column phone of data type integer.

ans.

drop table student;

create table student(
no int,
sname text,
dob date
);

alter table student add column address text not null;
alter table student add column phone integer;
****************************************
3. Consider the project relation created in the assignment 12. Add a constraint that the project
name should always start with the letter ‘R’

ans.
create table project (
pid int primary key
);

alter table project add column rname text check(substring(rname,1,1)='R');

insert into  project values(1,'R_project1');

****************************************

Exe-18

Set A-1

Create the following relations :

Emp(eno,name,dno,salary)
Project(pno,pname,control-dno,budget)

Each employee can work on one or more projects, and a project can have many employees
working in it. The number of hours worked on each project , by an employee also needs to be stored.

Create the Relations accordingly, so that the relationship is handled properly and the relations are in normalized form (3NF).

Assume appropriate data types for the attributes. Add any new attributes , new relations as
required by the queries.

Insert sufficient number of records in the relations / tables with appropriate values as suggested by some of the queries.

Write the queries for following business tasks & execute them.

Ans.

create table emp(
e_no int primary key,
name char(20),
d_no int,
salary int,
city char(20),
gender char
);

create table project(
p_no int primary key,
p_name char(20),
control_dno int,
budget float,
d_name char(20)
);

 create table emp_project(
 p_no int references project(p_no)on delete cascade,
 e_no int references emp(e_no)on delete cascade,no_hours int
 );

insert into emp values(01,'ankur',101,49000,'pune','m');
insert into emp values(02,'aniket',102,9000,'karad','m');
insert into emp values(03,'piyush',103,29000,'satara','m');
insert into emp values(04,'prashant',104,21000,'wakad','m');
insert into emp values(05,'omkar',105,2100,'hadapsar','m');

insert into project values(1001,'codec',105,50000,'comp sci');
insert into project values(1002,'dream code',101,50000,'comp sci');
insert into project values(1003,'sci_craft',102,5000,'sci');
insert into project values(1004,'tally',103,50200,'commerce');
insert into project values(1005,'scilab',104,502001,'math');
insert into project values(1006,'sales',104,502001,'math');

Insert into emp_project values(1001,1,2);
Insert into emp_project values(1002,2,3);
Insert into emp_project values(1003,3,4);
Insert into emp_project values(1005,5,5);
insert into emp_project values(1001,2,4);
insert into emp_project values(1001,4,4);


1.list the names of departments that controls projects whose budget is greater than 50000.

Ans.

select d_name from project where budget>50000;

2.list the names of projects, controlled by department No 101, whose budget is greater than
atleast one project controlled by department No 102.

Ans.

select p_name from project where control_dno='101'
and budget>some(select budget from project where control_dno='102') ;

3. list the details of the projects with second maximum budget

Ans.

select* from project where budget=(select max (budget) from
 project where budget<(select max(budget)from project)) limit 1;

4. list the details of the projects with third maximum budget.

Ans.

select* from project where budget=(select max (budget) from
 project where budget<(select max(budget)from project where
 budget<(select max (budget) from project))) limit 1;

5. list the names of employees, working on some projects that employee number __ is working.

Ans.

select name from emp where e_no in(select distinct e_no from
emp_project where p_no in(select e_no from emp_project where e_no='1'));

6. list the names of employees who do not work on any project that employee number __ works on

Ans.

select name from emp where e_no in(select distinct e_no from
emp_project where p_no not in(select e_no from emp_project where e_no='1'));

 7. list the names of employees who do not work on any project controlled by ‘comp sci’
department

Ans.

select name from emp,emp_project where emp.e_no=emp_project.e_no and p_no not in
(select p_no from project where d_name='comp sci');

 8. list the names of projects along with the controlling department name, for those projects
which has atleast __ employees working on it.

Ans.

select * from project where p_no in(select p_no from emp_project group by p_no having count(e_no)>=1);

9. list the names of employees who is worked for more than 10 hrs on atleast one project
controlled by ‘comp sci’ dept.

Ans.

select name from emp_project,emp where emp.e_no=emp_project.e_no and p_no not in
(select p_no from project where d_name='comp sci' and no_hours>7);

 10. list the names of employees , who are males , and earning the maximum salary in their
department.

Ans.

select name from emp_project,emp where emp.e_no=emp_project.e_no and p_no in
(select p_no from project where d_name='comp sci' and no_hours>7);


11. list the names of employees who work in the same department as ‘comp sci’.

Ans.

select emp.name from emp,emp_project,project
where emp.e_no=emp_project.e_no and project.p_no=emp_project.p_no
and d_name='comp sci';

12. list the names of employees who do not live in pune or karad.

Ans.

select * from emp where city!='pune' and city!='karad';

EXE-13

1]
Create a table with details as given below
Table Name Machine
Columns Column Name    Column Data Type       Constraints
1       Machine_id     integer                Primary key
2       Machine_name   varchar (50)           NOT NULL, uppercase
3       Machine_type   varchar(10)            Type in ( ‘drilling’, ‘milling’,‘lathe’, ‘turning’, ‘grinding’)
4       Machine_price  float                  Greater than zero
5       Machine_cost   float
Table level constraint Machine_cost less than Machine_price
ans.
1]
create table machine(
id integer primary key,
name varchar(50) not null  ,
type varchar(50) check (type in ('drilling', 'milling','lathe', 'turning', 'grinding')),
price float check (price>0),
cost float,
check(name=upper(name))
);
or
create table machine(
id integer primary key,
name varchar(50) not null check(name=upper(name))  ,
type varchar(50) check (type in ('drilling', 'milling','lathe', 'turning', 'grinding')),
price float check (price>0),
cost float

);


insert into machine values(1,'ABC','drilling',22,234);
insert  into machine values(2,'PQR','milling',200,300);

select *from machine;

****************************************
2]
Create a table with details as given below
Table Name Employee
Columns Column Name          Column Data Type   Constraints
1       Employee_id          integer            Primary key
2       Employee_name        varchar (50)       NOT NULL, uppercase
3       Employee_desg        varchar(10)        Designation in ( ‘Manager’,‘staff’, ‘worker’)
4       Employee_sal         float              Greater than zero
5       Employee_uid         text               Unique
Table level constraint Employee_uid not equal to Employee_id

ans.
create table employee(
id integer primary key,
name varchar(50) not null check(name=upper(name)),
desg varchar(10) check(desg in ('Manager','staff', 'worker')),
sal float check (sal>0),
uid text unique

);

Exp 15 - Part 2 -Create the following tables ( primary keys are underlined.)

 

2.  Create the following tables ( primary keys are underlined.).

Emp(eno,ename,designation,sal)
Dept(dno,dname,dloc)
There exists a one-to-many relationship between emp & dept.

Create the Relations accordingly, so that the relationship is handled properly and the relations are
in normalized form (3NF).

a) Insert 5 records into department table
b) Insert 2 employee records for each department.
c) increase salary of “managers” by 15%;
d) delete all employees of deparment 30;
e) delete all employees who are working as a “clerk”
f) change location of department 20 to ‘KOLKATA’

Ans.

create table dept (
dno int primary key,
dname text,
dloc text
);

create table emp(
dno int,
eno int primary key,
ename text,
designation text,
sal money,
foreign key (dno) references dept (dno) on delete cascade
);

a] Insert 5 records into department table

insert into dept values(11,'computer','pune');
insert into dept values(22,'computer1','pune');
insert into dept values(33,'computer2','pune');
insert into dept values(44,'computer3','pune');
insert into dept values(55,'computer4','pune');

b] Insert 2 employee records for each department.

insert into emp values(11,1,'xyz','manager',7000);
insert into emp values(11,2,'xyz','staff',7000);
insert into emp values(22,3,'pqr','clerk',2000);

c] increase salary of “managers” by 15%;

update emp set sal=sal*15/100 where ename='staff';

d] delete all employees of deparment 30;

delete from emp where dno=11;

e] delete all employees who are working as a “clerk”

delete from emp where designation='clerk';

f] change location of department 20 to ‘KOLKATA’

update  dept set dloc='KOLKATA' where dno=20;

******************************************************************************

EXE 12 - Part 1 - To create more than one table, with referential integrity constraint, PK constraint

1. Create tables for the information given below by giving appropriate integrity constraints         as specified.

 Create the following tables :

Table Name Property

Columns	Column Name	Column Data Type	Constraints
1	Pnumber	Integer	Primary key
2	description	varchar (50)	Not NULL
3	Area	Char(10)

Table Name Owner

Columns	Column Name	Column Data Type	Constraints
1	Owner-name	Varchar(50)	Primary key
2	Address	varchar (50)
3	Phoneno	Integer

 // [4 byte capacity -2147483648 to +2147483647]

Relationship - A one-many relationship between owner & property. Define reference keys
accordingly.

Ans.

1]
create table owner(
oname varchar(50) primary key,
address varchar(50),
phoneno bigint
);

create table property(
pnumber integer primary key,
description varchar(50) not null,
area char(10),
oname varchar(50),
foreign key (oname) references owner(oname) on delete cascade
);


insert into owner values('dee','pune',9876451395);
insert into property values(1,'rural','pune','dee');
insert into property values(2,'rural','satara','dee');

select *from owner;
select *from property;
****************************************

2. Create the following tables :

Table Name Hospital 

Columns	Column Name	Column Data Type	Constraints
1	Hno	Integer	Primary key
2	Name	varchar (50)	Not null
3	City	Char(10)

 

Table Name Doctor 

Columns	Column Name	Column Data Type	Constraints
1	Dno	Integer	Primary key
2	Dname	varchar (50)
3	City	Char(10)

Relationship- A many-many relationship between hospital & doctor.

Ans.

1]
create table hospital(
hno integer primary key,
name varchar(50) not null,
city char(10)
);

create table doctor(
dno integer primary key,
dname varchar(50),
city char(10)
);
create table hos_doc(
hno integer references hospital(hno),
dno integer references doctor(dno)ON DELETE CASCADE ON UPDATE CASCADE,
primary key(hno,dno)
);

insert into hospital values(11,'abc','pune');
insert into  hospital values(22,'abc1','pune');
insert into  hospital values(33,'pqr','nashik');

insert into  doctor values(102,'x','pune');
insert into doctor values(103,'y','nashik');
insert into  doctor values(104,'z','nagar');

insert into  hos_doc values(11,102);
insert into  hos_doc values(11,103);
insert into  hos_doc values(22,103);
insert into  hos_doc values(22,104);


select *from hospital;
select *from doctor;
select *from hos_doc;

****************************************

3. Create the following tables :

Table Name Patient

Columns	Column Name	Column Data Type	Constraints
1	pno	Integer	Primary key
2	Name	varchar (50)	Not null
3	Address	varchar (50)

 Table Name Bed 

Columns	Column Name	Column Data Type	Constraints
1	Bedno	integer	Primary key
2	Roomno	integer	Primary key
3	description	Varchar(50)

 

Relationship a one–to-one relationship between patient & bed.

Ans.

create table bed(
bedno int,
roomno integer ,
description varchar(50),
primary key(bedno)
);

create table patient(
pno integer primary key,
name varchar(50) ,
address varchar(50),
bedno int unique  ,
 FOREIGN key (bedno) references bed(bedno)
);

insert into bed values(101,11,'abcd');
insert into bed values(102,33,'abcd');

insert into patient values(1,'x','pune',101);
insert into patient values(2,'y','pun e',102);

select *from bed ;
select *from patient;