DBMS Solution Slip 1

SQL>Connect

Enter user name:Admin

Enter Password:

Connected

SQL>create table dept(dno number(10) primary key,dname varchar2(20), loc varchar2(20));

Table created

SQL>create table emp(eno number(10) primary key,ename varchar2(20) not null,desg varchar2(20),sal number(10) check (salary>0) ,doj date,dno number(10) references dept(dno));

Table created

SQL>insert into dept values(1,'Account','Pune');

1 row created

SQL>insert into dept values(2,'Quality','Pune');

1 row created

SQL>insert into dept values(3,'computer science','pimpri');

1 row created

SQL>insert into dept values(4,'computer science','Mumbai');

1 row created

SQL>insert into dept values(5,'Quality','Mumbai');

1 row created

SQL>insert into emp values(1,'Mr. Advait','Accountant',10000,12-2-1999,2);

1 row created

SQL>insert into emp values(2,'Mr. Roy','CEO',51000,15-6-2019,2);

1 row created

SQL>insert into emp values(3,'Akshay','Manager',8000,10-6-2016,4);

1 row created

SQL>insert into emp values(4,'Ram','Designer',20000,15-5-2011,4);

1 row created

SQL>insert into emp values(5,'Ramesh','Tester',15000,25-2-2010,3);

1 row created

SQL>insert into emp values(6,'Gargi','Developer',15000,15-2-2010,1);

1 row created

Q.1 Add column phone_No into Emp table with data type int.

  SQL>alter table emp add phone_no int;

   Table created

SQL>update emp set phone_no=9970676878;

 1 row created

Q.2 Delete the details of Employee whose designation is ‘Manager’.

SQL> delete from emp

           where desg='Manager';

  1 row deleted

1. Display the count of employees department wise.

SQL> select dname , count(eno)

           from dept ,emp 

           where dept.dno=emp.dno 

           group by dname;

2. Display the name of employee who is ‘Manager’ of “Account Department”.

SQL> select eno,ename,desg,dname

           from dept ,emp 

           where dept.dno=emp.dno and

           desg='Manager' and desg='Account';

3.Display the name of department whose location is “Pune” and “Mr. Advait” is working in it.

SQL> select dname

           from dept ,emp

           where dept.dno=emp.dno and

           loc='Pune' and ename='Mr. Advait';

4.Display the names of employees whose salary is greater than 50000 and department is “Quality”.

SQL> select ename

           from dept ,emp

           where dept.dno=emp.dno and

           sal>50000 and dname='Quality';

5. Update Dateofjoining of employee   to ‘15/06/2019’ whose department is ‘computer science’ and name is “Mr. Roy’.

SQL> update emp set doj='15-6-2019'

           where ename='Mr. Roy' and

            dno in (select dno from where                

             dname='computer science');