SQL Exercise 1 - SQL Exercises for Beginners

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