SLIP1
Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Emp(eno ,ename ,designation ,salary, Date_Of_Joining)
Dept(dno,dname ,loc)
The relationship between Dept & Emp is
one-to-many.
Constraints: - Primary Key, ename should
not be NULL, salary must be greater than 0.
Solution
SQL>create table Emp(eno int primary key ,ename varchar(10),designation varchar(10),salary int,dateofjoining varchar(15));
SQL>insert into Emp
values(101,'amit','HR',23000,'16-3-2020');
SQL>Select * from Emp;
SQL>create table Dept(dno int primary key ,dname varchar(10),loc varchar(10),eno int);
SQL>insert into Dept
values(1,'commerce','Pune',101);
SQL>Select
* from Dept;
Consider the above tables and Execute the following queries:
1. Add column phone_No into Emp table with
data type int.
SQL>ALTER TABLE EMP ADD phone_no int;
2. Delete the details of Employee whose designation is ‘Manager’.
SQL>DELETE FROM Emp WHERE designation =
‘Manager’;
Q4. Consider the above database and execute the following queries: [25 Marks]
1. Display the count of employees department wise.
SQL>select count(emp.eno),dname from
Emp,Dept where Emp.eno=Dept.eno group by dname;
2. Display the name of employee who is
‘Manager’ of “Account Department”.
SQL>select ename from Emp,Dept where Emp.eno=Dept.eno and designation='manager'and dname='Account';
3. Display the name of department whose location is “Pune” and “Mr. Advait” is
working in it.
SQL>select dname from Emp,Dept where
Emp.eno=Dept.eno and loc='pune'and ename='Advait';
4. Display the names of employees whose salary is greater than 50000 and department is “Quality”.
SQL> select ename from emp,dept where
emp.eno=dept.eno and salary>50000 and dname='Quqlity';
5. Update Dateofjoining of employee to ‘15/06/2019’ whose department is ‘computer science’ and name is “Mr. Roy’.
SQL>update emp set
date_of_joining='15/06/2019' where ename='Roy' and dno in(select dno from dept
where dname='computer science');
SQL>update emp set dateofjoining='15-06-2019' where exists ( select dname from Dept where dname='commerce' and Emp.eno=Dept.eno );
SQL>UPDATE Emp SET Dateofjoining = '15/06/2019'
WHERE eno IN ( SELECT e.eno FROM Emp e JOIN Dept d ON e.eno = d.eno WHERE
e.ename = 'Mr. Roy' AND d.dname = 'computer science' );
