Database Management ,(GTU)Gujarat Technological University Lab Manual Solution-8: Database Management ,(GTU)Gujarat Technological University Lab Manual Solution-8

Database Management ,(GTU)Gujarat Technological University Lab Manual Solution-8: Database Management ,(GTU)Gujarat Technological University Lab Manual Solution-8

Database Management System (DBMS) - 2130703, (GTU) Gujarat Technological University Lab Manual Solution - 6

Practical: 6

To apply the concept of Aggregating Data using Group functions.

(1)  List total deposit of customer having account date after 1-jan-96.

Select sum(amount)  from deposit where adate> ‘1-jan-96’;

(2)  List total deposit of customers living in city Nagpur.

select sum(amount) from (deposit inner join customers on

deposit.cname = customers.cname) where customers.city='nagpur';

(3)  List maximum deposit of customers living in bombay.

select max(amount) from (deposit inner join customers on

deposit.cname = customers.cname) where customers.city='bombay';

(4)  Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number.

select round(max(emp_sal)) "Maximum" , round(min(emp_sal)) "Minimum" , round(sum(emp_sal)) "Sum" , round(avg(emp_sal)) "Average" from employee;

(5)  Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE.

Select max(emp_sal)-min(emp_sal) as DIFFERENCE from employee;

(6)  Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998.

select count(actno) as total, count(decode(to_char(adate,'YYYY'),1995,'m1')) "1995", count(decode(to_char(adate,'YYYY'),1996,'m2')) "1996", count(decode(to_char(adate,'YYYY'),1997,'m3')) "1997", count(decode(to_char(adate,'YYYY'),1998,'m4')) "1998" from deposit;

(7)  Find the average salaries for each department without displaying the respective department numbers.

Select avg(emp_sal) from employee group by dept_no;

(8)  Write a query to display the total salary being paid to each job title, within each department.

Note: there is no relation between the tables employee table containing department and job table containing job title. So I have considered only job title part of the question in solution.

select sum(min_sal) from job group by job_id;

(9)  Find the average salaries > 2000 for each department without displaying the respective department numbers.

Select avg(emp_sal) from employee group by dept_no having avg(emp_sal)>2000;

(10)                    Display the job and total salary for each job with a total salary amount exceeding 3000, in which excludes president and sorts the list by the total salary.

select sum(min_sal),job_id from job group by job_id having

sum(min_sal) > 3000 order by sum(min_sal);

(11)                    List the branches having sum of deposit more than 5000 and located in city bombay.

selectbranch.bname from deposit inner join branch on deposit.bname=branch.bname where city='bombay';

Database Management System (DBMS) - 2130703, (GTU) Gujarat Technological University Lab Manual Solution - 5

                                                       Practical – 5

Displaying data from Multiple Tables (join)

(1)    Give details of customers ANIL.

Select * from deposit d inner join customer c on d.cname=c.cname where c.cname=’anil’;

(2)    Give name of customer who are borrowers and depositors and having 

living city Nagpur.

Select b.cname from borrow b inner join deposit d on b.cname=d.cname where (b.bname=’vrce’ or b.bname=’ajni’)and(d.bname=’vrce’ or d.bname=’ajni’);

Note: Consider bname(vrce & ajni)which having city Nagpur at the place of city(Nagpur)

(3)    Give city as their city name of customers having same living branch.

SELECT B.CITY  “CITY NAME OF CUSTOMERS” FROM BRANCH B, CUSTOMERS C WHERE B.CITY=C.CITY;

(4)    Write a query to display the last name, department number, an department name for all employees.

SELECT e.last_name , e.department_id, d.department_name FROM Employee e , Department d WHERE e.department_id=d.department_id;

(5)    Create a unique listing of all jobs that are in department 30. Include the location of the department in the output.

 SELECT DISTINCT job_id, location_id FROM Employee, Department WHERE Employee.department_id=Department.department_id AND Emploee.department_id = 30 ;

(6)    Write a query to display the employee name, department number, and department name for all employees who work in NEW YORK.

SELECT e.last_name, e.job_id, e.department_id, d.department_name FROM Employee e JOIN department d ON (e.department_id=d.department_id) JOIN location l ON (d.location_id=l.location_id) WHERE  l.city= 'NEW YORK' ;

(7)    Display the employee last name and employee number along with their manager’s last name and   manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.

SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#" FROM Employee w JOIN Employees m ON (w.manager_id = m.employee_id) ;

(8)    Create a query to display the name and hire date of any employee hired after employee SCOTT.

SELECT e.last_name, e.hire_date FROM employee e, employee SCOTT WHERE SCOTT.last_name='SCOTT' AND SCOTT.hire_date  <  e.hire-date ;

            OR

SELECT e.last_name, e.hire_date FROM Employee e JOIN Employee SCOTT ON (SCOTT.last_name = 'SCOTT' )  WHERE SCOTT.hire_date < e.hire_date;

Database Management System (DBMS) - 2130703, (GTU) Gujarat Technological University Lab Manual Solution - 4

Practical -4

To study Single-row functions

1.     Write a query to display the current date. Label the column Date

SELECT sysdate "Date" FROM dual;

2.     For each employee, display the employee number, job, salary, and salary increased by 15% and expressed as a whole number. Label the column New Salary.

SELECT emp_n,emp_sal ,emp_sal+ROUND (emp_sal * 0.15) As New Salary FROM employee;

3.     Modify your query no 4.(2) to add a column that subtracts the old salary from the new salary. Label the column Increase.

SELECT emp_n,emp_ sal, ROUND (emp_sal+emp_sal * 0.15 emp_sal)"Increase" FROM employee;

4.     Write a query that displays the employee’s names with the first letter capitalized and all other letters lowercase, and the length of the names, for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names.

SELECT INITCAP(emp_name) "Name", LENGTH(emp_name) "Length" FROM employee WHERE emp_name LIKE 'J%' OR emp_name LIKE  'M%' OR emp_name LIKE 'A%' order by emp_name.

5.     Write a query that produces the following for each employee:<employee last name> earns <salary> monthly.

SELECT emp_name ||' earns ' || emp_sal ||’monthly’ from employee.

6.     Display the name, hire date, number of months employed and day of the week on which the employee has started. Order the results by the day of the week starting with Monday.

SELECT cname,months_between(sysdate,adate) as months  ,to_char(adate,’day’) as day from deposit order by to_char(adate -  1,‘day’)

7.     Display the hiredate of emp in a format that appears as Seventh of June 1994 12:00:00 AM.

SELECT TO_CHAR(adate,'fmDdspth "of" Month YYYY fm HH:MI:SS AM') Result FROM employee;

8.     Write a query to calculate the annual compensation of all employees (sal+comm.)

SELECT EMP_SAL + EMP_COMM FROM EMPLOYEE

Database Management System (DBMS) - 2130703, (GTU) Gujarat Technological University Lab Manual Solution - 3

Practical – 3

 To Perform various data manipulation commands, aggregate functions and sorting concept on all created tables.

(1)  List total deposit from deposit.

SELECT SUM(AMOUNT) FROM DEPOSIT

(2)  List total loan from karolbagh branch

SELECT SUM(AMOUNT) FROM  BRROW WHERE BNAME=’karolbagh’

(3)  Give maximum loan from branch vrce.

SELECT MAX(AMOUNT) From BRROW WHERE BNAME=’vrce’

(4)  Count total number of customers

SELECT COUNT(CNAME) FROM CUSTOMERS

(5)  Count total number of customer’s cities.

SELECT COUNT (CITY) FROM CUSTOMERS

(6)  Create table supplier from employee with all the columns.

CREATE TABLE SUPPLIER (sup_no, sup_name, sup_sal, sup_comm, dept_no )  AS SELECT emp_no, emp_name, emp_sal, emp_comm, dept_no FROM EMPLOYEE;

(7)  Create table sup1 from employee with first two columns.

CREATE TABLE SUP1 (sup_no, sup_name)  AS SELECT emp_no, emp_name FROM EMPLOYEE;

(8)  Create table sup2 from employee with no data

CREATE TABLE SUPPLIER (sup_no, sup_name, sup_sal, sup_comm, dept_no)  AS SELECT emp_no, emp_name, emp_sal, emp_comm, dept_no FROM EMPLOYEE WHERE EMP_NO=1;

(9)  Insert the data into sup2 from employee whose second character should be ‘n’ and string should be 5 characters long in employee name field.

INSERT INTO SUP2 SELECT * FROM EMPLOYEE WHERE EMP_NAME LIKE ‘_n___’

(10)                    Delete all the rows from sup1.

DELETE FROM SUP1

(11)                    Delete the detail of supplier whose sup_no is 103.

DELETE FROM SUP1 WHERE SUP_NO=103

(12)                    Rename the table sup2.

RENAME SUP2 TO SUPPLIER2;

(13)                    Destroy table sup1 with all the data.

 DROP TABLE SUP1;

(14)                    Update the value dept_no to 10 where second character of emp. name is ‘m’.

UPDATE EMPLOYEE SET DEPT_NO =10 WHERE EMP_NAME LIKE ‘_m%’

(15)                    Update the value of employee name whose employee number is 103.

UPDATE EMPLOYEE SET EMP_NAME=’ABC’ WHERE DEPT_NO=103;

Database Management System (DBMS) - 2130703, (GTU) Gujarat Technological University Lab Manual Solution - 2

Practical-2

Perform following queries

(1) Retrieve all data from employee, jobs and deposit.

Select * from employee;

Select * from jobs;

Select * from deposit;

(2) Give details of account no. and deposited rupees of customers having account opened between dates 01-01-06 and 25-07-06.

Select a_no,amount from deposit  where to_date(d_date,'DD/MM/YYYY') 

BETWEEN TO_DATE('01-01-06 ', 'DD-MM-YYYY')AND TO_DATE('25-07-06', 'DD-MM-YYYY');

(3) Display all jobs with minimum salary is greater than 4000.

Select min(min_sal) from job where min_sal>4000;

(4) Display name and salary of employee whose department no is 20. Give alias name to name of employee.

Select emp_name “Name of Employee” ,emp_sal from employee where dept_no =20

(5) Display employee no,name and department details of those employee whose department lies in(10,20)

Select emp_no,emp_name ,dept_no from employee where dept_no in(10,20)

To study various options of LIKE predicate

(1) Display all employee whose name start with ‘A’ and third character is ‘ ‘a’.

Select * from employee where emp_namelike(‘A_a%’)

(2) Display name, number and salary of those employees whose name is 5 characters long and first three characters are ‘Ani’.

            Select emp_name,emp_no,emp_sal where emp_name like (‘Ani__’)

(3) Display the non-null values of employees and also employee name second character  should be ‘n’ and string should be 5 character long.

Select emp_name from employee where emp_name IS NOT NULL AND emp_name LIKE (_n%___)

(4) Display the null values of employee and also employee name’s third character should be ‘a’.

Select emp_name from employee where emp_nameIS  NULL AND emp_name LIKE (__a%)