When we store time stamp in database the column can be of type DATE TIME YEAR TO SECOND .
It Will contain day of month, month, year, time in seconds. But usually the scenarios that we Will be handling Will be pull all the data that happened this year or pull all the data that happened on particular date
on these scenarios we need to extract the columns from time stamp data.
here is the list of keywords
select DAY(activity_date) from table .
select MONTH(activity_date) from table.
select YEAR(activity_date) from table.
select DATE(activity_date) from table
say the timestamp is
queries will return
sometimes we spend very hard time by using some weird inner joins, group by clauses and nested queries for retrieving set of data from our database.
Thumb rule : when finding the rows that you want is moving very hard , then try eliminating rows that you don’t want to be there.
one simple delete statement can do an awesome job that a group by and complex sub queries does.
so , always keep in mind that they are two approaches for getting the data.
1. finding what you want
2. eliminating what you don’t want
Whenever we write an sql , it is very important that we check the cost of it. If there is any room for improvement in the query based on the time taken by the query to fetch the result.
One such occasion is using an OR clause. There are certain situations where we need “Either this or that” kind of results . We are forced to use OR conditions on that query . But, i personally feel OR condition are little slower than UNION when we hit on the bigger tables .
The UNION query looks bigger , but it actually runs faster than the OR condition. Just for the clarity , am just adding a simple example where we need to find out the employee names and their salary if they have an income greater than 2000 or their past increment is higher than 200
we have the table structures given below
table name : emp
table name : emp_sal
empid salary increment
1 1000 100
2 2000 200
3 4000 400
4 5000 500
by using or :
select e.empname, s.salary from emp e, emp_sal s where e.empid = s.emp_id and (s.salary > 2000 or s.increment > 200);
by using UNION :
select e.empname, s.salary from emp e, emp_sal s where e.empid = s.emp_id and s.salary > 2000
select e.empname, s.salary from emp e, emp_sal s where e.empid = s.emp_id and s.increment > 200;
The union query looks bigger. But , it runs pretty faster than OR query .
For extracting lines from file use sed commmand in linux
sed -n '10p,20p' input.txt > output.txt
in the above command -n represent lines and 10 and 20 represents the offset , input.txt is the file from which we are extracting the input and storing the output in output.txt
say if i just need the nth line , then use the command as
sed -n '10p' input.txt > output.txt
The above command will extract only the 10th line
if i need selected lines say 10,15,17
sed -n '10p;15p;17p' input.txt > output.txt
Note : Be careful when you are using the > operator . If it is misplaced entire file will get erased.
This error occurred in my code when i was trying to connect oracle 11g using java .
1. Jdbc connectivity code was perfect
2. Oracle service was running
3. Was able to login sqlplus
4. Database startup and shutdown commands were working .
5. All sql queries worked fine
Even thought i was not able to establish connection. After a long search in Google i found that we need to restart tns by this command .
Don’t forget to restart your database.
These are the few points that need to be taken care of when we write sql statements. I furnish details upto my knowledge , any suggestions and improvements are most welcome.
1. Try to write sql in aligned format. Type of alignment differ from people to people . The alignment that i follow is
keywords align in the left side whereas the content will be a one tab space from the left margin. Alignment improves readability. Editing and making comparison can be done easily.
2. The next part is the order in which we use table. Try to keep the order descending. if table1 is bigger than table2 , then the order should be table1,table2.
3. As we follow the order for table names, we need to follow the order for conditions too. For example if table a is bigger than b then finish off all the conditions related to a and then start b and so on.
4. Be careful when you use alias. setting keywords as alias will end you in trouble.
i had a file with 58000 rows in it.. was searching for a command to insert all the rows in one shot..
LOAD DATA INFILE ‘/path/to/sample.txt’ INTO TABLE `database_name`.`table_name`
worked like a charm..