Scribd
Upload
45 views

Rai SQLassignment[1]

file

Uploaded by

Shashwat Yadav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
45 views

Rai SQLassignment[1]

file

Uploaded by

Shashwat Yadav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 16

Name: Aditya Rai Section: D Roll no :7

Q1- Create the following table Employees

Column Name Datatype Size

Emp_id (Primary Key) Number 10

Emp_name Varchar2 20

Dept_id Number 20

Query:-

CREATE TABLE Employees


(
Emp_id number(10) PRIMARY KEY ,
Emp_name varchar2(20),
Dept_id number(10)
);

Q2. Insert the following data in the Employees table

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

Query:-

INSERT INTO Employees (Emp_id, Emp_name, Dept_id)VALUES (1, 'Alice',10);


INSERT INTO Employees (Emp_id, Emp_name,Dept_id)VALUES (2, 'Bob',20);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id)VALUES(3,'Charlie', 30);
INSERT INTO Employees (Emp_id, Emp_name,Dept_id)VALUES (4, 'David',null );
INSERT INTO Employees (Emp_id, Emp_name, Dept_id)VALUES(5, 'Emma', 10);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id)VALUES (6, 'Frank', 20);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id)VALUES (7, 'Grace', 30);
INSERT INTO Employees (Emp_id, Emp_name, Dept_id)VALUES (8,'Hannah',30);

Q3. Create the following table Departments

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

Column Name Datatype Size


Dept_id (Primary Key) Number 10

Dept_name Varchar2 10

Query:-

CREATE TABLE Departments


(
Dept_id number PRIMARY KEY,
Dept_name varchar2(10)
);

Q4. Insert the following data in the Departments table

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

Query:-

INSERT INTO Departments (Dept_id, Dept_name) VALUES (10, 'HR');


INSERT INTO Departments (Dept_id, Dept_name) VALUES (20, 'IT');
INSERT INTO Departments (Dept_id,Dept_name) VALUES (40, 'MARKETING');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (30, 'FINANCE');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (50,' OPERATIONS');
INSERT INTO Departments (Dept_id,Dept_name) VALUES (60, 'RESEARCH');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (70, 'LEGAL');
INSERT INTO Departments (Dept_id, Dept_name) VALUES (80, 'SALES');

Q5. Based on above two tables answer the following


Questionaries: -

i)Use an INNER JOIN to find the names of employees who belong to a


department.

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

Query:-

Select Emp_name , Dept_name


From Employees inner join Departments on
Employees.Dept_id = Departments.Dept_id;

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

ii)
Use a LEFT OUTER JOIN to list all employees, showing their
department if they have one. If an employee does not belong to a
department, show NULL for the department name.

Query:-
Select Emp_name, Dept_name
From Employees left outer join Departments on Employees.Dept_id
= Departments.Dept_id;

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

iii)
Use a RIGHT OUTER JOIN to list all departments, showing the
names of employees in each department. If a department has no
employees, show NULL for the employee’s name.

Query:-

Select Dept_name, Emp_name


From Employees right outer join Departments on
Employees.Dept_id = Departments.Dept_id;

Use a FULL OUTER JOIN to list all employees and all departments,
showing NULL for departments with no employees and employees
with no department.

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

iv)

Query:-
SELECT Employees.Emp_name, Departments.Dept_name
FROM Employees
LEFT JOIN Departments ON Employees.Dept_id = Departments.Dept_id
UNION
SELECT Employees.Emp_name, Departments.Dept_name
FROM Departments
RIGHT JOIN Employees ON Employees.Dept_id = Departments.Dept_id;

Use an INNER JOIN to find the names of employees who are in both
IT and HR departments.

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

v)

Query:-

Select Emp_name From


Employees inner join Departments on
Employees.Dept_id=Departments.Dept_id
Where Dept_name ="IT" or Dept_name ="HR";

Write a query to find employees without a department using a LEFT


JOIN.

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

vi)
Query:-

Select Emp_name ,Emp_id ,Dept_name


From Employees left outer join Departments on Employees.Dept_id=Departments.Dept_id
Where Dept_name =NULL;

Find the total number of employees in each department using


INNER JOIN and GROUP BY.

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

vii)
Query:-

SELECT Departments.Dept_name,
COUNT(Employees.Emp_id) AS Total_Employees
FROM Employees INNER JOIN Departments ON
Employees.Dept_id = Departments.Dept_id
GROUP BY Departments.Dept_name;

Find all employees who do not belong to either the "HR" or "IT"
department using an OUTER JOIN.

Query:-

SELECT Employees.Emp_name, Departments.Dept_name


FROM Employees LEFT JOIN Departments ON
Employees.Dept_id = Departments.Dept_id

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

viii)
WHERE Departments.Dept_name NOT IN ('HR', 'IT')
OR Departments.Dept_name IS NULL;

List the names of employees and departments where employees


work, but exclude records where department names start with the
letter "M."

Query:-
SELECT e.Emp_name, d.Dept_name
FROM Employees e JOIN
Departments d ON
e.Dept_id = d.Dept_id
WHERE Dept_name NOT LIKE 'M%';

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

ix)

Use a LEFT JOIN to list all employees and count how many
employees are in each department, even if some departments have
no employees.

Query:-

SELECT d.Dept_name, COUNT(e.Emp_id) AS employee_count FROM


Departments d LEFT JOIN Employees e ON
d.Dept_id = e.Dept_id

GROUP BY d.Dept_name;

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

x)
Find the department name and employee name for employees who
have no department assigned by using an OUTER JOIN.

Query:-
SELECT e.Emp_name, d.Dept_name
FROM Employees e LEFT JOIN Departments d
ON e.Dept_id = d.Dept_id
WHERE d.Dept_id IS NULL;

Write a query to find employees who are in the same department


as another employee.

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :7

xi)

Query:-

SELECT e1.Emp_name AS employee1, e2.Emp_name AS employee2, e1.Dept_id


FROM Employees e1
JOIN Employees e2 ON e1.Dept_id = e2.Dept_id
WHERE e1.Emp_id <> e2.Emp_id;

Course: BCA with AI and DS


Name: Aditya Rai Section: D Roll no :71

Course: BCA with AI and DS

You might also like