[go: up one dir, main page]
Scribd
Upload
2 views3 pages

Pratical_no_15

Uploaded by

atharvashinde0311

Copyright:

© All Rights Reserved
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt

Pratical_no_15

Uploaded by

atharvashinde0311
2 views3 pages

Copyright

© © All Rights Reserved

Available Formats

DOCX, PDF, TXT or read online from Scribd

Did you find this document useful?

Is this content inappropriate?

Copyright:

© All Rights Reserved
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
2 views3 pages

Pratical_no_15

Uploaded by

atharvashinde0311

Copyright:

© All Rights Reserved
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 3

PRATICAL NO.

15

Create Two Tables Emp and Dept, Consider the following schema
Emp (Emp_no as primary key, E_name, Dept_no, Dept_name, name, Job_id, Salary)
and Dept (Dept_no as primary key, emp_no foreign key, deptname, location)

create table emp8(empno number(10) primary key,ename varchar(20),dept_no number(10),dept_name


varchar(20),job_id number(20),sal number(10));

insert into emp8 values(19,'Atharva',8,'HR',01,1000000);

insert into emp8 values(51,'Siddharth',9,'management',02,500000);

insert into emp8 values(52,'ganesh',7,'CO',03,20000);

insert into emp8 values(53,'piyush',6,'CW',04,10000);

select *from emp8;

create table dept(dept_no number(10)primary key , empno number(10) references emp8(empno), dept_name
varchar(20) , location varchar(20));

insert into dept values(8,19,'HR','kop');

insert into dept values(9,51,'management','pune');

insert into dept values(7,52,'CO','mumbai');

insert into dept values(6,53,'CW','nashik');

select *from dept;


Execute the SQL queries using SELECT, WHERE, GROUP BY and HAVING clause.

select dept_no,empno from emp8 where dept_no=8;

select dept_name from dept d group by dept_name;

Excersise
CREATE TABLE employees ( emp_no NUMBER (50) PRIMARY KEY,emp_name VARCHAR2(50) ,salary NUMBER (20),
dept_no NUMBER (20));

Table created.

select * from employees

1) Display minimum salary of employee from every department.

SELECT dept_no, MIN(salary) AS min_salary FROM employees GROUP BY dept_no;


2) Display total salary of every department.
SELECT dept_no, SUM(salary) AS total_salary FROM employees GROUP BY dept_no;

3) Display the department having total employees more than 5.


SELECT dept_no FROM employees GROUP BY dept_no HAVING COUNT(emp_no) > 5;

4) Display details of employees with employee name in ascending order.


SELECT * FROM employees ORDER BY emp_name ASC;

5) Display emp_no, dept_no from dept Group By deptname


SELECT emp_no, dept_no FROM employees GROUP BY emp_no, dept_no;

You might also like