SQL Exercises for Beginners / Simple SQL Exercises with Answers / SQL Exercises for simple Table creation and SELECT queries
Question:
[A]
Table Creation
Create a table according to the
schema given below. Choose the appropriate data types while creating the table.
Insert the records given in Table 1 into the Employee table. And, write SQL
queries to satisfy the questions given below.
Employee (Emp_ID, Emp_Name, DoB,
Department, Designation, DoJ, Salary)
Here, DoB means Date of Birth, DoJ
means Date of Joining.
Emp_ID
|
Emp_Name
|
DoB
|
Department
|
Designation
|
DoJ
|
Salary
|
F110
|
Sam
|
15-JUN-1970
|
Bio-Technology
|
Professor
|
12-APR-2001
|
45000
|
F111
|
Kumar
|
25-MAY-1980
|
Mechanical
|
Asst. Prof.
|
02-MAY-2006
|
30000
|
F115
|
Raguvaran
|
10-AUG-1982
|
CSE
|
Asst. Prof.
|
05-MAY-2007
|
27000
|
F114
|
Jennifer
|
10-SEP-1975
|
CSE
|
Asst. Prof.
|
03-JUN-2004
|
35000
|
F117
|
Ismail
|
15-MAY-1979
|
IT
|
Asst. Prof.
|
10-MAY-2005
|
33000
|
Table 1 – Employee
[B]
Queries
1. Display all the records from
table Employee.
2. Find all the employees who are
working for CSE department.
3. Get the details about the
employees who have joined after ’10-JUN-2005’.
4. Find all the employees who
earn more than 30000.
5. Get the details of employees
who are not ‘Professor’.
6. Find the name, date of birth,
and designation of all the employees who work for ‘IT’ department.
7. Find all the departments which
are offering salary above 25000.
8. Get the DoB of employee named ‘Kumar’.
9. Find the names and departments
of employees who earn the salary in the range 20000 to 40000.
10. Find the employee details of
any employee who work for ‘CSE’ and earn more than 30000.
********************
Answers:
[A]
Table Creation
The following CREATE TABLE
statement will create the table according to the specification given in
question.
CREATE TABLE Employee (
Emp_ID CHAR(3),
Emp_Name VARCHAR(30),
DOB DATE,
Department VARCHAR(20),
Designation VARCHAR(15),
DoJ DATE,
Salary Number(10,2));
Explanation:
Whatever mentioned in CAPITAL
LETTERS are keywords/reserved words.
I have used various datatypes for
various attribute declaration.
CHAR data type
used for Emp_ID attribute as I would know the exact size of any employee ids.
VARCHAR data
type used for Emp_Name, Department, and Designation attributes, because the
values stored in those columns may vary. That is, for example, a name would be
just 3 characters long or 30 characters long. Try
to learn the difference between CHAR and VARCHAR.
DATE data type
used for all the attributes where I need to store dates (DoB, and DoJ).
NUMBER is used
for salary as I would like to store salary values. In the above declaration
(10, 2) means that any values upto the size of total 10 digits out of which 2 digits after decimal
point.
The CREATE TABLE statement is
terminated with ‘;’ as any other statements in SQL.
Data
Insertion
All the records given in Table 1
can be inserted using the following INSERT INTO statement.
INSERT INTO Employee VALUES (‘F110’,
‘Sam’, ‘15-JUN-1970’, ‘Bio-Technology’, ‘Professor’, ‘12-APR-2001’, 45000);
As stated above, the words in
CAPITAL LETTERS are keywords/reserved words.
Observe that how the information
are specified for every column. You must mention the values in the order in
which you have declared the attributes in the table, with the following simple
rules;
CHAR,
VARCHAR, and DATE attribute values should be given inside a pair of single
quotes.
NUMBER
inputs can be specified without any quotes.
[B]
Queries
Before go into SELECT queries,
recall the structure of a SELECT query. Basically, we need at least two
clauses, SELECT and FROM to write a complete query. The questions given above
need the clauses, SELECT, FROM, and WHERE. See below for the clauses with the
parameters needed;
SELECT
*/list of attributes to be included in the result
FROM
list of one or more tables
WHERE
list of conditions ANDed, ORed, or Negated (NOT)
1.
Display all the records from table Employee.
For question 1, we need to write
a query that displays the entire table. For writing this query, we need only
two clauses, SELECT, and FROM as follows;
SELECT
*
FROM
Employee;
The parameter for SELECT clause, ‘*’
represents all the columns/attributes of the table parameter which is given in
FROM clause. You may write the query in single line also.
The result is Table 1 as it is.
2.
Find all the employees who are working for CSE department.
Question 2 includes a condition. The
condition is ‘the employees working for CSE department’. Hence, we have a
parameter for WHERE clause. The parameter given in the WHERE clause have a
form;
Attribute_name θ
value
Here, θ would mean any valid
comparison operators (=, <, >, <=, >=, <>). Then, the query
would be written as follows;
SELECT
*
FROM
Employee
WHERE
department = ‘CSE’;
Result:
Emp_ID
|
Emp_Name
|
DoB
|
Department
|
Designation
|
DoJ
|
Salary
|
F115
|
Raguvaran
|
10-AUG-1982
|
CSE
|
Asst. Prof.
|
05-MAY-2007
|
27000
|
F114
|
Jennifer
|
10-SEP-1975
|
CSE
|
Asst. Prof.
|
03-JUN-2004
|
35000
|
3.
Get the details about the employees who have joined after ’10-JUN-2005’.
The condition given in the
question is Date of Joining. The query is,
SELECT
*
FROM
Employee
WHERE
DoJ > ’10-JUN-2005’;
Result:
Emp_ID
|
Emp_Name
|
DoB
|
Department
|
Designation
|
DoJ
|
Salary
|
F111
|
Kumar
|
25-MAY-1980
|
Mechanical
|
Asst. Prof.
|
02-MAY-2006
|
30000
|
F115
|
Raguvaran
|
10-AUG-1982
|
CSE
|
Asst. Prof.
|
05-MAY-2007
|
27000
|
4.
Find all the employees who earn more than 30000.
The condition is about Salary
attribute.
SELECT
*
FROM
Employee
WHERE
Salary > 30000;
Result:
Emp_ID
|
Emp_Name
|
DoB
|
Department
|
Designation
|
DoJ
|
Salary
|
F110
|
Sam
|
15-JUN-1970
|
Bio-Technology
|
Professor
|
12-APR-2001
|
45000
|
F114
|
Jennifer
|
10-SEP-1975
|
CSE
|
Asst. Prof.
|
03-JUN-2004
|
35000
|
F117
|
Ismail
|
15-MAY-1979
|
IT
|
Asst. Prof.
|
10-MAY-2005
|
33000
|
5.
Get the details of employees who are not ‘Professor’.
The condition involves the
attribute Designation.
SELECT
*
FROM
Employee
WHERE
Designation <> ‘Professor’;
Result:
Emp_ID
|
Emp_Name
|
DoB
|
Department
|
Designation
|
DoJ
|
Salary
|
F111
|
Kumar
|
25-MAY-1980
|
Mechanical
|
Asst. Prof.
|
02-MAY-2006
|
30000
|
F115
|
Raguvaran
|
10-AUG-1982
|
CSE
|
Asst. Prof.
|
05-MAY-2007
|
27000
|
F114
|
Jennifer
|
10-SEP-1975
|
CSE
|
Asst. Prof.
|
03-JUN-2004
|
35000
|
F117
|
Ismail
|
15-MAY-1979
|
IT
|
Asst. Prof.
|
10-MAY-2005
|
33000
|
6.
Find the name, date of birth, and designation of all the employees who work for
‘IT’ department.
Question 6 is different from all
the above 5. It needs different parameters other than *. That is, we need to
display only what columns (attributes required). We need to display name, DoB,
and Designation of all employee records which satisfy the condition “Department
= ‘IT’”.
SELECT
Emp_Name, DoB, Designation
FROM
Employee
WHERE
Department = ‘IT’;
Here, the parameters for the
SELECT clause are separated using commas (‘,’).
Result:
Emp_Name
|
DoB
|
Designation
|
Ismail
|
15-MAY-1979
|
Asst. Prof.
|
7.
Find all the departments which are offering salary above 25000.
For this question we need to display
Departments. The condition to be satisfied is Salary > 30000.
SELECT
Department
FROM
Employee
WHERE
Salary > 25000;
Result:
Department
|
Bio-Technology
|
Mechanical
|
CSE
|
CSE
|
IT
|
8.
Get the DoB of employee named ‘Kumar’.
We need Date of Birth. The condition
involves Emp_Name attribute.
SELECT
DoB
FROM
Employee
WHERE
Emp_Name = ‘Kumar’;
Note: The parameters like the
list of attribute names, the list of table names, and even the keywords are CASE
INSENSITIVE. But, the literal values specified between the set of quotes are
CASE SENSITIVE. Hence, the above query would return a result only if you have a
record with the Emp_Name value as ‘Kumar’. If you have ‘kumar’, it cannot
include that record in the result.
Result:
DoB
|
25-MAY-1980
|
9.
Find the names and departments of employees who earn the salary in the range
20000 to 40000.
We need to display the columns
Emp_Names, and Departments. The condition is a range of values (salary). It can
be written in two ways at least;
(a) SELECT Emp_Name, Department
FROM
Employee
WHERE
Salary >= 20000 AND Salary <= 40000;
(b) SELECT Emp_Name, Department
FROM Employee
WHERE Salary BETWEEN 20000 AND 40000;
Here, you can find two new
things. The first one is how do we mention two or more conditions in the WHERE
clause using AND logical connective. The second is the keyword BETWEEN, which
is used to mention a range of values. The simple syntax for that would be as
follows;
…
WHERE
attribute BETWEEN value1 AND value 2.
Result:
Emp_Name
|
Department
|
Kumar
|
Mechanical
|
Raguvaran
|
CSE
|
Jennifer
|
CSE
|
Ismail
|
IT
|
10.
Find the employee details of any employee who work for ‘CSE’ and earn more than
30000.
This question requires all the
records from Employee table. The conditions are the Department and Salary.
SELECT
*
FROM
Employee
WHERE
Department = ‘CSE’ AND Salary > 30000;
Result:
Emp_ID
|
Emp_Name
|
DoB
|
Department
|
Designation
|
DoJ
|
Salary
|
F114
|
Jennifer
|
10-SEP-1975
|
CSE
|
Asst. Prof.
|
03-JUN-2004
|
35000
|