Code and Debug > Blog > Practice Problems > SQL Practice > SQL Queries for Practice

SQL Queries for Practice

November 11, 2022
Posted by: Code and Debug
Category: Practice Problems SQL Practice

In this blog, we will practicing SQL queries with solutions. This will help you improving your SQL skills. We will practice both Basic Queries as well as Nested Queries.

Create the following tables with dummy data

Table Name - Employee

Employeeid	EmployeeName	Department	ContactNumber	Email	EmployeeHeadID
351	Anirudh	S-203	123456788	anirudh@xyz.com	355
352	Sanjay	S-204	123456789	sanjay@xyz.com	353
353	Nihar	S-203	987654322	nihar@xyz.com	351
354	Manish	S-203	987654321	manish@xyz.com	355
355	Muskan	S-201	987654332	muskan@xyz.com	352

				
					CREATE TABLE `employee` (
  `employeeid` int(11) NOT NULL,
  `employeename` varchar(100) DEFAULT NULL,
  `department` varchar(50) DEFAULT NULL,
  `contactnumber` bigint(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `employeeheadid` int(11) DEFAULT NULL
);

ALTER TABLE `employee`
  ADD PRIMARY KEY (`employeeid`);

				
					INSERT INTO `employee` (`employeeid`, `employeename`, `department`, `contactnumber`, `email`, `employeeheadid`) VALUES
(351, 'Anirudh', 'S-203', 123456788, 'anirudh@xyz.com', 355),
(352, 'Sanjay', 'S-204', 123456789, 'sanjay@xyz.com', 353),
(353, 'Nihar', 'S-203', 987654322, 'nihar@xyz.com', 351),
(354, 'Manish', 'S-203', 987654321, 'manish@xyz.com', 355),
(355, 'Muskan', 'S-201', 987654332, 'muskan@xyz.com', 352);

Table Name - EmployeeDepartment

DepartmentId	DepartmentName	Dept_off	DepartmentHead
S-201	Coding	Tuesday	355
S-202	Sales	Monday	351
S-203	HR	Saturday	353
S-204	Hiring	Friday	354
S-205	House Keeping	Wednesday	354

				
					CREATE TABLE `employeedepartment` (
  `departmentid` varchar(50) NOT NULL,
  `departmentname` varchar(100) DEFAULT NULL,
  `dept_off` varchar(100) DEFAULT NULL,
  `departmenthead` int(11) DEFAULT NULL
);

ALTER TABLE `employeedepartment`
  ADD PRIMARY KEY (`departmentid`),
  ADD KEY `departmenthead` (`departmenthead`);
  
ALTER TABLE `employeedepartment`
  ADD CONSTRAINT `employeedepartment_ibfk_1` FOREIGN KEY (`departmenthead`) REFERENCES `employee` (`employeeid`);

				
					INSERT INTO `employeedepartment` (`departmentid`, `departmentname`, `dept_off`, `departmenthead`) VALUES
('S-201', 'Coding', 'Tuesday', 355),
('S-202', 'Sales', 'Monday', 351),
('S-203', 'HR', 'Saturday', 353),
('S-204', 'Hiring', 'Friday', 354),
('S-205', 'House Keeping', 'Wednesday', 354);

Table Name - EmployeeSalary

Employeeid	Salary	IsPermanent
351	50000	Yes
352	45000	No
353	80000	No
354	38000	No
355	27000	Yes

				
					CREATE TABLE `employeesalary` (
  `employeeid` int(11) DEFAULT NULL,
  `salary` bigint(20) DEFAULT NULL,
  `ispermanent` varchar(3) DEFAULT NULL
);

ALTER TABLE `employeesalary`
  ADD KEY `employeeid` (`employeeid`);
  
ALTER TABLE `employeesalary`
  ADD CONSTRAINT `employeesalary_ibfk_1` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`);

				
					INSERT INTO `employeesalary` (`employeeid`, `salary`, `ispermanent`) VALUES
(351, 50000, 'Yes'),
(352, 45000, 'No'),
(353, 80000, 'No'),
(354, 38000, 'No'),
(355, 27000, 'Yes');

Table Name - Project

ProjectId	Duration
p-1	23
p-2	15
p-3	45
p-4	2
p-5	30

				
					CREATE TABLE `project` (
  `projectid` varchar(50) NOT NULL,
  `duration` int(11) DEFAULT NULL
);

ALTER TABLE `project`
  ADD PRIMARY KEY (`projectid`);

				
					INSERT INTO `project` (`projectid`, `duration`) VALUES
('p-1', 23),
('p-2', 15),
('p-3', 45),
('p-4', 2),
('p-5', 30);

Table Name - Country

Countryid	Countryname
c1	Bangladesh
c2	Russia
c3	India
c4	England
c5	South Africa

				
					CREATE TABLE `country` (
  `countryid` varchar(50) NOT NULL,
  `countryname` varchar(100) DEFAULT NULL
);

ALTER TABLE `country`
  ADD PRIMARY KEY (`countryid`);

				
					INSERT INTO `country` (`countryid`, `countryname`) VALUES
('c1', 'Bangladesh'),
('c2', 'Russia'),
('c3', 'India'),
('c4', 'England'),
('c5', 'South Africa');

Table Name - Clients

ClientId	ClientName	CountryId
cl1	XYZ Group	c2
cl2	Amdani Group	c3
cl3	Tech Giant	c4
cl4	MLP	c2
cl5	Pannel	c5

				
					CREATE TABLE `clients` (
  `clientid` varchar(50) NOT NULL,
  `clientname` varchar(100) DEFAULT NULL,
  `countryid` varchar(50) DEFAULT NULL
);

ALTER TABLE `clients`
  ADD PRIMARY KEY (`clientid`),
  ADD KEY `countryid` (`countryid`);
  
ALTER TABLE `clients`
  ADD CONSTRAINT `clients_ibfk_1` FOREIGN KEY (`countryid`) REFERENCES `country` (`countryid`);

				
					INSERT INTO `clients` (`clientid`, `clientname`, `countryid`) VALUES
('cl1', 'XYZ Group', 'c2'),
('cl2', 'Amdani Group', 'c3'),
('cl3', 'Tech Giant', 'c4'),
('cl4', 'MLP', 'c2'),
('cl5', 'Pannel', 'c5');

Table Name - EmployeeProject

Employeeid	ProjectId	ClientId	StartYear	EndYear
351	p-1	cl3	2018	2020
352	p-4	cl4	2017	2019
353	p-5	cl5	2015	NULL
354	p-1	cl1	2012	2016
355	p-3	cl2	2018	NULL

				
					CREATE TABLE `employeeproject` (
  `employeeid` int(11) DEFAULT NULL,
  `projectid` varchar(50) DEFAULT NULL,
  `clientid` varchar(50) DEFAULT NULL,
  `startyear` int(11) DEFAULT NULL,
  `endyear` int(11) DEFAULT NULL
)

ALTER TABLE `employeeproject`
  ADD KEY `employeeid` (`employeeid`),
  ADD KEY `projectid` (`projectid`),
  ADD KEY `clientid` (`clientid`);
  
ALTER TABLE `employeeproject`
  ADD CONSTRAINT `employeeproject_ibfk_1` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`),
  ADD CONSTRAINT `employeeproject_ibfk_2` FOREIGN KEY (`projectid`) REFERENCES `project` (`projectid`),
  ADD CONSTRAINT `employeeproject_ibfk_3` FOREIGN KEY (`clientid`) REFERENCES `clients` (`clientid`);

				
					INSERT INTO `employeeproject` (`employeeid`, `projectid`, `clientid`, `startyear`, `endyear`) VALUES
(351, 'p-1', 'cl3', 2018, 2020),
(352, 'p-4', 'cl4', 2017, 2019),
(353, 'p-5', 'cl5', 2015, NULL),
(354, 'p-1', 'cl1', 2012, 2016),
(355, 'p-3', 'cl2', 2018, NULL);

Basic Queries

1) Select details of all employees whose name start with 'M'.

				
					SELECT * FROM employee WHERE employeename LIKE 'm%';

2) How many employee have salary more than 47000.

				
					SELECT * FROM employeesalary WHERE salary > 47000;

3) Select the details of employee who works for department S-201 or S-204.

				
					SELECT * FROM employee WHERE department='S-201' or department='S-204';

4) What is the name of employee of id 354.

				
					SELECT employeename FROM employee WHERE employeeid=354;

5) What is total salary paid to permanent employees?

				
					SELECT SUM(salary) FROM employeesalary WHERE ispermanent='Yes';

6) Name of all the employees whose name ends with 'h'.

				
					SELECT employeename FROM employee WHERE employeename LIKE '%h';

7) Count the number of projects whose duration is greater than 20.

				
					SELECT COUNT(projectid) FROM project WHERE duration>20;

8) Select details of all the employees who have 'n' in 3rd character of their name.

				
					SELECT * FROM employee WHERE employeename LIKE '__n%';

9) List the number of department of employees in each project.

				
					SELECT count(employeeid) AS employee, projectid FROM employeeproject GROUP BY projectid;

Nested Queries

1) Select the department name of the company whose employee id is greater than 353.

				
					SELECT departmentname FROM employeedepartment WHERE departmentid IN (SELECT department FROM employee WHERE employeeid > 353);

2) Select the name of the employee head who is permanent.

				
					SELECT employeename FROM employee WHERE employeeid IN (SELECT employeeheadid FROM employee) AND employeeid IN (SELECT employeeid FROM employeesalary WHERE ispermanent='Yes');

3) Show the employee names whose department is off on Saturday.

				
					SELECT employeename FROM employee WHERE department IN (SELECT departmentid FROM employeedepartment WHERE dept_off = 'Saturday');

4) Select all the details of England Clients.

				
					SELECT * FROM clients WHERE countryid IN (SELECT countryid FROM country WHERE countryname = 'England');

5) Select the details of all employee working in 'HR' department.

				
					SELECT * FROM employee WHERE department IN (SELECT departmentid FROM employeedepartment WHERE departmentname = 'HR');

6) Select the name of the employee who is department head of HR.

				
					SELECT employeename FROM employee WHERE employeeid = (SELECT departmenthead FROM employeedepartment WHERE departmentname='HR');

1 Comment

Tapas

November 16, 2022 at 8:38 AM Reply

Thank you so much. Can you put more examples query on Oracle SQL.