SQL – stands for Structured Query Language is used to communicate with database. It is very commonly used for relational database management systems. What is relational database? Data can be stored in different forms. Tabular form that means, data stored in columns and rows is referred to as relational database. SQL statements such as ‘create’, ‘select’, ‘insert’, ‘update’, ‘delete’ are known to achieve all the activities that we need to perform on a database. Relational Database Management Systems like Microsoft SQL server, Access, Oracle, Ingress, Sybase etc. uses SQL. They might have their own additional proprietary extensions but SQL commands are the same everywhere.
Here I am doing all the operations in an IMB DB2-warehouse on a cloud database. Cloud databases are user friendly for few reasons. It’s easy to use. You can access to your database from anywhere. It can make space for changing needs in runtime. Also, databases are completely safe even after a natural disaster and server and equipment damage.
As I mentioned earlier, SQL commands are same for any platform. Here I am using some basic and very useful SQL operations. First let’s create a table named ‘EMPLOYEES’ that we will use in this exercise.
CREATE TABLE EMPLOYEES (
EMP_ID CHAR(9) NOT NULL,
F_NAME VARCHAR(15) NOT NULL,
L_NAME VARCHAR(15) NOT NULL,
SSN CHAR(9),
B_DATE DATE,
SEX CHAR,
ADDRESS VARCHAR(30),
JOB_ID CHAR(9),
SALARY DECIMAL(10,2),
MANAGER_ID CHAR(9),
DEP_ID CHAR(9) NOT NULL,
PRIMARY KEY (EMP_ID));
First start with an insert statement. Insertion of a row can be done by following insert statement:
Insert into EMPLOYEES (EMP_ID, F_NAME, L_NAME, SSN, B_DATE, SEX, ADDRESS, JOB_ID, SALARY, MANAGER_ID, DEP_ID)
values
(‘E1011′,’Jonny’, ‘Smith’, ‘123452’, ‘2/12/1986’, ‘M’, ‘2011 NW 37 Ave Doral, Fl’, ‘325’, ‘65000’, ‘30001’, ‘2’)
It gives following output
After this insert statement now I remember that actually this person changed his address. I need to update his address. Let’s do this:
update empoyees set address=’4011 sw 8th st, Miami, fl’ where emp_id = ‘E1011’
I need some more data for this exercise. Instead of inserting data row by row, I uploaded a csv file into this table. The name of the csv file is employees.csv. You can find this file here.
I need some more data for this exercise. Instead of inserting data row by row, I uploaded a csv file into this table. The name of the csv file is employees.csv. You can find this file here.
This is how we can retrieve the whole table:
Select * from EMPLOYEES
Please notice, sometimes I am using uppercase and sometimes lowercase in sql statement. It works exactly the same. It’s a good idea to make a habit of your own. Use either uppercase or lowercase that is comfortable for you. But I am using both to show that both are the same.
One of our employees left the company last week. So, I need to remove him from the database.
delete from employees where emp_id = ‘E1011’
My boss asked me, how many employees do we have now. I kind of don’t remember. What can I do? Probably, I can simply check the number of rows in my employees table. Because I used one row for one employee. Cool!
SELECT count(*) FROM EMPLOYEES
Look at this picture. We have 10 employees. The 1 on top indicates that, this is the first column. We have only one column though. We don’t need more than 1 column to show how many employees we have.
Gender balance is very important in a company. We also decided to have gender balance in our office. First check how many female employees we have in our office.
Select count(sex) from employees where sex=’F’
Wow! We are so good. We already have 5 female employees out of 10 total employees.
Now see the name of all our female employees:
Select f_name, l_name from EMPLOYEES where sex = ‘F’
Which departments do our female employees work? Are they working in every department equally? We have id for each department in our table. Now find out how many unique ids do we have where female employees work? Select distinct dep_id from employees where sex = ‘F’
Retrieve just the first 4 rows in a table:
Select * from employees LIMIT 4
It gives just first 4 rows of the table.
Here I am showing some more operations that are little advanced and useful.
- Retrieve all employees whose address is in Elgin,IL
select * from EMPLOYEES where address like ‘%Elgin,IL%’;
2. Retrieve names of the employees who were born during the 1970’s.
select f_name, l_name from EMPLOYEES where b_date like ‘%197%’;
3. Retrieve all employees in department 5 whose salary is between 60000 and 70000
select f_name, l_name from EMPLOYEES where dep_id = 5 and salary between 60000 and 70000;
4. Retrieve a list of employees ordered by department ID.
select * from EMPLOYEES order by dep_id;
5. Retrieve a list of employees ordered in descending order by department ID and within each department ordered alphabetically in descending order by last name.
select * from EMPLOYEES order by dep_id desc, l_name desc;
6. For each department ID retrieve the number of employees in the department.
select dep_id, count(dep_id) from EMPLOYEES group by dep_id
7. For each department retrieve the number of employees in the department, and the average employees salary in the department.
select dep_id, count(dep_id), avg(salary) from EMPLOYEES group by dep_id
DEP_ID 2 3
2 3 86666.67
5 4 65000.00
7 3 66666.67
8. For each department retrieve the number of employees in the department, and the average employees salary in the department.
select dep_id, count(*) as “num_employees”, avg(salary) as “avg_salary” from EMPLOYEES group by dep_id;
DEP_ID num_employees avg_salary
5 4 65000.00
7 3 66666.67
2 3 86666.67
9 In Query 8 order the result set by Average Salary.
select dep_id, count(*) as “num_employees”, avg(salary) as “avg_salary” from EMPLOYEES group by dep_id order by avg(salary);
DEP_ID num_employees avg_salary
5 4 65000.00
7 3 66666.67
2 3 86666.67
10. In Query 9 limit the result to departments with fewer than 4 employees.
select dep_id, count(*) as “num_employees”, avg(salary) as “avg_salary” from EMPLOYEES group by dep_id having count(*) < 4 order by avg(salary);
DEP_ID num_employees avg_salary
7 3 66666.67
2 3 86666.67
If this was helpful to you please subscribe and share this to your friends. Stay tuned for more blog posts like this.
