Thursday, April 29, 2021

Slip2 solution

 SQL>create table client(cno varchar2(10) primary key, cname varchar2(20), address varchar2(20));
 Table Created

SQL>create table sales_order(ordno number(10) primary key, odate date not null , cno varchar2(10) references client(cno) on delete cascade);
Table Created

SQL>insert into client values('CN001','Patil','Pimpri');
1 row created

SQL>insert into client values('CN002','Mr. Roy','Pune');
1 row created

SQL>insert into client values('CN003','Mr. Aarav','Satara');
1 row created

SQL>insert into client values('CN004','Meera','Maval');

1 row created

SQL>insert into client values('CN005','Gouari','Punel');
1 row created

SQL>insert into sales_order values(1,'10-Feb-2018','CN001');
1 row created

SQL>insert into sales_order values(2,'09-Aug-2019','CN001');
1 row created

SQL>insert into sales_order values(3,'15-Aug-2019','CN002');
1 row created

SQL>insert into sales_order values(4,'15-March-2018','CN002');
1 row created

SQL>insert into sales_order values(5,'05-Jan-2018','CN003');
1 row created

SQL>insert into sales_order values(6,'05-Feb-2019','CN003');
1 row created

SQL>insert into sales_order values(7,'25-April-2020','CN004');
1 row created

SQL>insert into sales_order values(8,'9-Feb-2018','CN004');
1 row created

SQL>insert into sales_order values(9,'16-Dec-2018','CN005');
1 row created

SQL>insert into sales_order values(10,'16-July-2018','CN005');
1 row created

Q.1. Add column amount into Sales_order table with data type int.

SQL>alter table sales_order add amount int;
Table altered

SQL>update sales_order set amount=100;
10 rows updated.

Q. 2. Delete the details of the clients whose names start with ‘A’ character.

SQL>delete from client where cname like'A%';
1 row deleted.

Q4. Consider the above tables and execute the following queries:

1. Delete sales order details of client whose name is “Patil” and order date is

“09/08/2019”.

SQL>delete from sales_order where odate="09-Aug-2019' and 
cno in (select cno from client where cname='Patil');

2. Change order date of client_No ‘CN001’ to ‘18/03/2019’.

SQL>update sales_order 
         set odate='18 March 2019'
          where cno='CN001';
1 rows updated

3. Delete all sales_record having order date is before ‘10 /02/2018’.

SQL>delete from sales_order 
          where odate<'10-Feb-2018';
1 row deleted

4. Display date wise sales_order given by clients.

SQL>select odate,ono,amount,cno
          from sales_order 
           order  by odate;
10 rows selected

5. Update the address of client to “Pimpri” whose name is ‘Mr. Roy’.

SQL>update client set address='Pimpri'
         where cname='Mr. Roy';
 1 row updated


Monday, April 19, 2021

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



DBMS Slip


 Create a RDB in 3 NF with  appropriate data types and Constraints. 

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.

Consider the above tables and Execute the following queries:

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

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


1.Display the count of employees department wise.

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

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

working in it.

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

is “Quality”.

5. Update Dateofjoining of employee to ‘15/06/2019’ whose department is ‘computer

science’ and name is “Mr. Roy’.



 Slip 2 . Create a RDB in 3 NF with  appropriate data types and Constraints. 

Sales_order (ordNo, ordDate)

Client (clientNo, ClientName, addr)

The relationship between Client & Sales_order is one-to-many.

Constraints: - Primary Key, ordDate should not be NULL.

Consider the above tables and execute the following queries:

1. Add column amount into Sales_order table with data type int.

2. Delete the details of the clients whose names start with ‘A’ character.


Q4. Consider the above tables and execute the following queries:

1. Delete sales order details of client whose name is “Patil” and order date is

“09/08/2019”.

2. Change order date of client_No ‘CN001’ to ‘18/03/2019’.

3. Delete all sales_record having order date is before ‘10 /02/2018’.

4. Display date wise sales_order given by clients.

5. Update the address of client to “Pimpri” whose name is ‘Mr. Roy’.

Slip 10. Write a jQuery code to make first word of each statement to bold.

  Slip 10. Write a jQuery code to make first word of each statement to bold.  <!DOCTYPE html> <html>   <head>     <scri...