Shraddha's Computer Notes: DBMS- Assignment 4

Assignment 4

Objectives :- To drop a table, alter schema of a table, insert / update / delete records using tables created in previous Assignments. ( use simple forms of insert / update / delete statements)

The Drop & Alter DDL statements :

1 Drop: Deletes an object (table/view/constraint) schema fromthe database.

Syntax: Drop object-type object-name

2 Alter: ALTER TABLE command of SQL is used to modify the structure of the table It can be used

for following purposes:

a) adding new column

b) modifying existing columns

c) add an integrity constraint

d) To redefine a column

Restrictions on the alter table are that, the following tasks cannot be performed with this clause

a) Change the name of the column

b) Drop a column

c) Decrease the size of a column if table data exists

Syntax: a. Alter table tablename Add (new columnname datatype (size), new columnname

datatype(size)…);

c .Alter table tablename modify (columnname new datatype(new size),..);

Insert / update / delete DML statements:

The different DML statements with their syntax are given below:( insert / update / delete statements)

1 Insert:

The insert statement is used to insert tuples or records into a created table or a relation. We specify a

list of comma-separated column values, which must be in the same order as the columns in the table.

To insert character data we must enclose it in single quotes(‘). If a single quote is part of the string

value to be inserted, then precede it with a backslash(\).When we don’t have values for every column

in the table, or the data given in insert is not in the same column order as in the table, then we specify

the column names also along with the values (2nd syntax).

Syntax: INSERT INTO tablename VALUES (list of column values);

INSERT INTO tablename(list of column names) VALUES (list of column values

corresponding to the column names );

2. Update:

The UPDATE command is used to change or modify data values in a table. To specify update of

several columns at the same time, we simply specify them as a comma separated list.

Syntax: UPDATE tablename

SET columnname = value where condition;

3.Delete: The DELETE statement is used to remove data from tables.

Syntax: DELETE FROM table name where condition;

Set A

The Drop & Alter DDL statements

1. Consider the employee table created in assignment 3 and add designation column in the employee table with values restricted to a set of values.

Query:-

Query to create Employee table :-

Create Table Employee (Employee_id int primary key ,
Employee_name varchar(20) NOT NULL CHECK (Employee_name = upper (Employee_name )), Employee_desig varchar(10) CHECK (Employee_desig IN ('Manager', 'staff', 'worker')),Employee_sal float CHECK (Employee_sal >0), Employee_uid int Unique ,
Constraint Emp_id CHECK(Employee_uid <> Employee_id ));
CREATE TABLE

Alter table Employee Add designation varchar(20) check(designation in ('manager','Accountant','Employee')) ;

2. 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.

Query:- Create table Student (sno int primary key , sname varchar(50) , date_of_birth date);

Alter table Student add address text NOT NULL,add phone integer;

3. Consider the project relation created in the assignment 2. Add a constraint that the project name should always start with the letter ‘R’

Query:-

Create Project table If not avaoilabale as :

Create table Project(pno int primary key, pname varchar(50), status boolean );

Alter table Project add constraint pname_chk check (pname like 'R%') ;

4. Consider the relation hospital created in assignment 2. Add a column hbudget of type int , with the constraint that budget of any hospital should always > 50000.

Query:-

Create table Hospital if not available as:-

Create table Hospital(hno int primary key , name varchar(50), city varchar(10));

Alter table hospital add (new hbudget int CHECK (hbudget >50000));

Insert / update / delete DML statements:

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”

Queries:-

Create table Owner (oname varchar(50) primary key , address text, phone integer );

Create table Property (pno int primary key , descr text, area varchar(50), oname varchar(50) references Owner(oname) on delete cascade on update cascade );

a) Insert into Owner values('Mr. Nene', 'pune' , 8822446633);

Insert into Owner values('Mr. Sane', 'Nashik' , 9563214785);

Insert into Owner values('Mr. Joshi', 'pune' , 8754123695);

Insert into Owner values('Mr. Sathe', 'Mumbai' , 7744556633);

b) Insert into Property Values( 101, 'new Bungalow', 'pune' ,'Mr. Joshi');

Insert into Property Values( 102, 'new House', 'pune' ,'Mr. Joshi');

Insert into Property Values( 103, 'Old Bungalow', 'Nashik' ,'Mr. Sane');

Insert into Property Values( 104, 'new Bungalow', 'Nashik' ,'Mr. Sane');

c) update Owner set phone=9890278008 where oname='Mr. Nene';

d) delete from Owner where address='pune' and oname='Mr. Joshi';

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 department 30;

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

f) change location of department 20 to ‘KOLKATA’

Queries:-

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 );

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');

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] update emp set sal=sal*15/100 where designation='manager';

sample=# select * from emp;
dno | eno | ename | designation |    sal
-----+-----+-------+-------------+-----------
11 |   2 | xyz   | staff       | ₹7,000.00
22 |   3 | pqr   | clerk       | ₹2,000.00
11 |   1 | xyz   | manager     | ₹1,050.00
(3 rows)

d] delete from emp where dno=11;

sample=# select * from emp;
dno | eno | ename | designation | sal
-----+-----+-------+-------------+-----------
22 | 3 | pqr | clerk | ₹2,000.00
(1 row)

e] delete from emp where designation='clerk';

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

Set B

The Drop & Alter DDL statements

1. Create table driver (licence_no, Name, Address) and perform the following queries

1. Add new column age with constraint that age should be greater than 20 years.

2. Alter table by modifying driver_name to varchar(50));

3. Alter table driver ,drop the column age.

4. Remove the driver table from the database.

Queries:-

Create table Driver (licence_no int primary key , Name varchar(20) , address text);

1) Alter table Driver add age int CHECK ( age > 20);

2) alter table Driver alter column Name Type varchar(50);

3) alter table Driver drop age;

4) drop table Driver;

2. Create table Game (name, no-of-players, captain_name) and perform the following queries

1. Add new column game_no with constraint primary key.

2. Alter table by adding constraint uppercase to captain_name.

3. Modify table by adding the column game_duration.

4. Add column game_type with values cricket,hockey,tennis.

5. Remove game table from the database

Queries:-

Create table Game (name varchar(50), no_of_players int , captain_name varchar(50));

1) alter table Game add game_no int primary key;

2) Alter table Game add constraint cap_name_chk CHECK(captain_name = upper(captain_name ));

3) Alter table Game add game_duration int;

4) Alter table Game add game_type varchar(20) CHECK( game_type IN ( 'cricket' , 'hockey' , 'tennis' ));

5) Drop table Game;

Insert / update / delete DML statements:

1. Consider the following student database .

Tables:

Student(stud_id int , Stud_name varchar(20), S_address varchar(20), status varchar(20))

Teacher(Teacher_id int, Tname varchar(20),T_address varchar(20))

Subject(Subject_id int, subject_name varchar(20) )

Relationship :

Teacher –subject : 1-M

Student-Subject : M-M, with descriptive attributes marks int , grade char.

Constraint :

1. status that should be Pass or Fail.

2. subject name should be not null

Create normalized tables (3NF) for the above and solve the following queries. Write &

execute insert/ update / delete statements for following business tasks

a) Insert 5 records in the student table.

b) Insert 3 records in the teacher table.

c) Insert appropriate record in subject and marks table.

d) Change the marks of ‘Ashok’ for maths subject to 75.

e) Change the subject from ‘Drawing’ to ‘Computers’.

f) Delete the record of teacher ‘Sarika’.

g) Delete all students whose status is fail.

Queries:-

Create table Student(stud_id int primary key , Stud_name varchar(20), S_address varchar(20), status varchar(20) CHECK (status IN ('pass','fail')));

Create table Teacher(Teacher_id int primary key , Tname varchar(20) ,T_address varchar(20));

Create table Subject(Subject_id int primary key , subject_name varchar(20) NOT NULL , Teacher_id int references Teacher (Teacher_id) on delete cascade on update cascade );

Create table Stud_subj (stud_id int references Student(stud_id) on delete cascade on update cascade , Subject_id int references Subject(Subject_id) on delete cascade on update cascade ,marks int , grade char);

a) Insert into Student Values ( 101, 'Ashok', 'Nashik', 'pass');

Insert into Student Values ( 102, 'Asha', 'Pune', 'pass');

Insert into Student Values ( 103, 'Alok', 'Nashik', 'fail');

Insert into Student Values ( 104, 'Kartik', 'Mumbai', 'pass');

Insert into Student Values ( 105, 'Sonali', 'Pune ', 'fail');

b)Insert into Teacher Values (201, 'Sarika' ,'Nashik');

Insert into Teacher Values (202, 'Akshay' ,'Mumbai');

Insert into Teacher Values (203, 'Amar' ,'Pune');

Insert into Teacher Values (204, 'Ankita' ,'Pune');

Insert Into Subject Values(301, 'maths', 201);

Insert Into Subject Values(302, 'Drawing', 203);

Insert Into Subject Values(303, 'Electronics', 202);

Insert Into Subject Values(304, 'Stats', 204);

Insert into Stud_subj Values(101,301, 50,'B');

Insert into Stud_subj Values(104,302, 80,'O');

Insert into Stud_subj Values(102,303, 60,'A');

Insert into Stud_subj Values(103,304, 90,'O');

d) Change the marks of ‘Ashok’ for maths subject to 75.

update Stud_subj set marks=75
where Subject_id = (select Subject_id from Subject
Where subject_name ='maths')
and stud_id =(select stud_id from Student
where stud_name='Ashok');

e) Change the subject from ‘Drawing’ to ‘Computers’.

update Subject

Set subject_name = 'Computers'

Where subject_name ='Drawing';

f) Delete the record of teacher ‘Sarika’.

delete from Teacher where tname ='Sarika';

g) Delete all students whose status is fail.

delete from Student where status='fail';

Shraddha's Computer Notes

Menu

Wednesday, January 12, 2022

DBMS- Assignment 4

No comments:

Post a Comment