sql exercises for the new dba





below are the example sql statements which touches on different clauses of a select statement. will later add more sql statements for update,delete and insert

you will also find sql exercises in the second part of this article so that as a new dba you should do so you can gain confidence in your interviews

Different Types of SQL Statements.

 

 

Create table table1(col1 number , col2 varchar2(10));

Create table table2(col3 number , col4 varchar2(10));

 

 

You can create the above table if you want to test below queries in your sqlplus session.

 

Now insert some rows into that table using below statements.

 

Insert into table1(1, ‘sql1’);

Insert into table1(2, ‘sql2’);

Insert into table1(3, ‘sql3’);

Insert into table1(4, ‘sql4’);

Insert into table1(5, ‘sql5’);

Insert into table1(6, ‘sql6’);

 

Insert into table2(1, ‘sql1’);

Insert into table2(2, ‘sql2’);

Insert into table2(3, ‘sql3’);

Insert into table2(4, ‘sql4’);

Insert into table2(5, ‘sql5’);

Insert into table2(6, ‘sql6’);

 

 

 

–dual is a dummy table in oracle

–you can use this table when you want to test some oracle functions

–and see how they work

 

Select * from dual

Select to_char(sysdate,’dd-mm-yyyy’) from dual;

Select to_date(’01-01-2010’,’dd-mm-yyyy’) from dual;

Select sysdate from dual;

 

 

–using group by function and having clause

–having clause is always used with group by

–to understand its behaviour it is best for you to execute this query in sqlplus or tool called toad or tool called oracle developer.

 

select col1 , max(col2) from table1 group by col1;

select col1,max(col2) from table1 group by col1 having length(col2) > 2;

 

 

–query having multiple tables and selecting all columns

 

select col1,col2,col3,col4 from table1,table2 where table1.col1 = table2.col3

 

 

–query having multiple tables and using aliases for the table names

 

select t1.col1,t1.col2,t2.col3,t2.col4 from table1 t1,table2 t2 where t1.col1 = te2.col3

 

–query using subqueries in the select clause

–be careful when using the subqueries in the select clause because

–the subquery must always return a single value.

 

select a.col1,a.col2,(select b.col4 from table2 b where b.col3 = a.col1)

from table1 a

 

 

–query using subquery in the where clause and using the IN operator

–the advantage of using a subquery with the IN operator is that the subquery can return more than 1 value

 

 

select a.col1,a.col2 from table1 a where a.col1 IN (select b.col3 from table2 b ) ;

 

–query using the not in operator with a subquery.

select a.col1,a.col2 from table1 a where a.col1 NOT IN (select b.col3 from table2 b ) ;

 

–query using a subquery in the where clause and using the = operator

–be careful when using the equal to operator with a subquery because –then the subquery must always return a single value

select ca.col2 from table1 a where a.col1 = (select b.col3 from table2 b where b.col4 = ‘SQL3’);

 

–select query using the not in operator

 

select col2 from table1 a where EXISTS (select 1 from table2 b where b.col3 = a.col1)

 

–select query using the not exists operator

 

select col2 from table1 a where NOT EXISTS (select 1 from table2 b where b.col3 = a.col1)

–select query using an inline view. An inline view is a subquery which you use like a table but this table is built in memory.

 

Select z.dummycolumn 1 , z.dummycolumn2 from

(select a.col1 dummycolumn1 , a.col2 dummycolumn 2

 from table1 a ) z

 

 

–select statement using bind variables and sum function.this is how it looks when you observe it thtough toad session browser while tracing it.inside the sum function I am also using a user defined function

 

SELECT SUM (  ggd.rateconstant

            * enshipmentmgr.getdiscountfactor (:b10, :b9, (:b8 – :b1))

           )

  FROM eninstrdate_v ggd

 WHERE ggd.idrument = :b7

   AND ggd.idelemtype IN (:b6, :b5, :b4, :b3, :b2)

   AND ggd.cashdate > :b1

 

If you want to execute it then you need to fill in the bind variables with approximate values.These values are filled by our application and then the sql statement is executed on the database level.

 

 

 

 

SQL exercises

1)

Create table called table1 with 2 columns called a and b. The column a should be of number data type and column b should be of varchar2 datatype and of length 10 characters.

** you need to have knowledge of below terms

Create table syntax , how to log into a database using username and password , what are datatype’s?, where can you find the syntax for creating a table and other syntaxes easily? You can for example find all SQL syntaxes in this free SQL document from oracle website.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm

2)a table contains rows and columns. Try to draw a picture of a table with 2 rows and 2 columns . One column is called car_name and the second column is called car_age. 1st row should contain following values. Accent 2009. Second row should contain values BMW 2001

Look at the picture and try to understand the relation between the rows and columns.

Think and draw pictures of 2 more tables with 3 columns and using above example formulate your own column names and row values.

3)write down in your own words – the use of a select statement , the different sections of a select statement and their use,

** you should have knowledge of – select clause , from clause, where clause ,( group by clause and having clause)

4)insert 5 rows into table1 that you created above . write down a select statement to retrieve all rows in table1.

**you should have knowledge of – insert statement syntax and knowledge of point 3 above

5)write down a select statement to retrieve all rows in tablea but only one column in the select list.

6) create a table table2 with 2 columns called c and d . Column c will have a number datatype and column d will have a varchar2 datatype with length 10 characters.

**you should have the same knowledge as in point 1 above.

7) select all the rows from table1 and table2 above.include all columns.join the two tables using column a from table1 and column c from table2

** you should have knowledge of – how to join two tables , what is a join condition, what happens when the join condition is not successful,

8)what are the different types of joins? Write down different sql statements using all the joins and draw pictures on paper of how the result set would look like?

9)write down a select statement to select all rows in table1 and group the results using a group by on column a and using count function on column b

** you should have knowledge of – group by clause , restrictions in the select clause when using group by, what is count function and how where can we use it?

Look at the link that was given above which gives information about the count function.

10)search in Internet and write down or copy and paste – 30 sql statements using group by clause , 30 sql statements using group by and having clauses, 30 select statements joining 2 or more tables, 30 sql statements using count function , using sum function , using min ,max functions , using rownum function,using to_char , to_date functions. The idea is to study these statements so you really understand their purpose and execute these statements on your database.

11)the most important sql terminologies and functions a dba should know

Select , from , where , group by , having clause , different joins like equi join , outer joins like left outer join , right outer join , subqueries , rowid, rownum,

Dual table , functions like count , max , min, avg,decode,case,sum,to_char,to_date,add_months,substr,instr, sql operaters like >,<,<>,!=,=,like,between,and,or etc,running SQL statements from SQL*plus tool and all commands specific to SQL*plus

Best is to pick up one term from the above list and search and investigate about it and practise on your database until you are comfortable with the term.

Author: admin