## 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

Search results