Lab 2

SELECT                                     JOINS                                         INSERT                 DUAL

-------DISTINCT                           -----------EQUI JOIN

-------ORDER BY                        -----------SELF JOIN

                                                    -----------OUTER JOIN

How to grant pemission on tables in scott and then create the tables in your user space?

1. Log into scott and then type

>GRANT ALL ON tablename TO username,

where tablename is the name of your table and user name is you user name(csit95 etc).

So the grant command grants permissions on tables. You can grant permissions on only tables that you have created. ALL says that all commands or granted for the table. You can also specify GRANT only for SELECT, UPDATE, DELETE etc.

2. Then log out of scott and log into your acount and then type

>CREATE TABLE tablename AS SELECT * FROM SCOTT.tablename;

This will create a table in your user space just like the one in scott user space except for the constraints. You need to use ALTER TABLE to add the constraints and the use

>INSERT INTO tablename SELECT * FROM SCOTT.tablename;

This will insert existing values.

LAB2:

This will mainly concentrate on SELECT statement, DELETE, DROP and insert have already been covered in the last excercise.

Basic SQL SELECT Query Form:

    SELECT [DISTINCT] select-list

    FROM     from-list

    WHERE qualification;

Ex: Find names and ages of all students whose age is greater than 20.

SELECT Students.name,Students.age                     SELECT s.name,s.age

FROM Students                                         (or)               FROM Students s

WHERE Students.age>20                                           WHERE    s.age>20

You can think of every SQL query to have a basic hypothetical evaluation plan as follows.

	First  cross product of all the tables in the from-list is created
	Rows which do not satisfy conditions given in the WHERE qualification are then removed
	Finally only columns specified in the select-list are displayed.

Use the DISTINCT keyword to display only distinct column values or to remove duplicate values.

You can use expressions in the SELECT command

EX:

SELECT s.ratings+1

FROM Sailors s

WHERE s.age+10<60;

To compare strings you can use the LIKE operator. '_' and '%' are used as wild card characters.'_' stands for just one arbitrary character and '%' stands for zero or more arbitrary characters.

Note: Be careful when comparing strings, especiallky one with white spaces. Remember that Char data types pad a string with blank spaces where as Varchar2 dattypes do not do any padding.

Ex:

SELECT s.age SailorsAge

FROM Sailors s

WHERE s.name LIKE 'C_E%N';

Note: Here we are renaming the way S.age will be shown when displayed. Now you will get the output as SailorsAge rather than just S.age or age. Basically we are renaming columns only in the output. Remember the column is not renamed inside the table.

Questions:

1. I want to display  the column values in a sorted fashion, how do I do it?

A) You use the ORDER BY clause

Ex:

SELECT select-list                                                         SELECT s.rating

FROM from-list                                                                FROM Sailors s

WHERE qualification                                                      WHERE s.age>18

ORDER BY column1[column2,........];                            ORDER BY s.name,s.rating;

What the above query does is that it first select all sailors above 18 years the first orders them in ascending order by their names and if more than one sailor with the same name is there then their are ordered by their ratings.

In order to see the difference more clearly see below:

If you want to order in a descending order just include DESC at the end of the select Statement

EX:SQL>select ename,sal
from emp
order by ename,sal DESC;

JOINS (for some more in depth information on joins see JOINS

Consider the two tables EMP and DEPT below, we will work with these table instances.

Ex:

SELECT *

FROM EMP,DEPT;

This is an example of a simple join. In fact to exactly right we have to call this a cross product. The result is given below.

As you can see this information is not of much use as there is lot of wrong information. For Ex: that employee 1 is associated with department ECM and so on. So you need to remember that cross product will give you a lot of information most of which is not necessary. Also From the above you can notice that only the rows where DNO the common column of both tables has a common the informtion is relevant. So from the highlited rows you can make out that chaitanya works for CSIT department and that DEPT 1 has two employees etc. This is where we go to a new JOIN known as

EQUI-JOIN.

ex:

SELECT *

FROM EMP,DEPT

WHERE EMP.DNO=DEPT.DNO;

Basically you put an equality condition on the common column of the two tables.

Result is given below:

Now this is very useful for us as we are abl;e to combine information cross two or more tables and get valid information.

If you substitute equality sign with something else then it is known as non-equi join. this is not that important and we wont dwell on that for now.

Now sometimes we need to compare rows in the same table. For example consider the following table.

This table says that employees are there and they have managers and managers themselves are employees. Now I want to write a query which will give me the name of the manager of Chaitanya. As you can see, if I write SELECT Manager

FROM EMP

WHERE Ename='Chaitanya'; it will will return 2. But then I have to write another   query to get the name of the employee with ENO 2. So this is where self join comes in. Consider the query given below:

SELECT E1.Ename

FROM EMP E1, EMP E2

WHERE E1.ENO=E2.Manager AND E2.ENAME='Chaitanya';

what happens here is that we assume to instances of the same table EMP exist at the same time. First we select the rows having Chaitanya as the ENAME in one instance of EMP   and then join with the other instance of EMP to get the corresponding name of the manager of Chaitanya. That is first we get the number of the manager and then do self join to inspect every row of the same table to get the corresponding name for the number.

Outer Joins:

Normally we use joins to match tuples from two tables. In such cses it might so happen that some tuples do not have any matching tuples in the other table. This is where the outer joins come in. The outer joins return even the tuples which do not have any corresponding vlues in the other table.

Ex: SELECT EMPNO, ENAME

FROM EMP,DEPT

WHERE EMP.DNO (+)= DEPT.DNO;

this is an example of a LEFT OUTER JOIN(because the + comes to the left of the = sign) and here EMP rows without any matching DEPT rows are also returned with null values filled in the extra fields.

SELECT EMPNO, ENAME

FROM EMP,DEPT

WHERE EMP.DNO = DEPT.DNO (+);

this is an example of a RIGHT OUTER JOIN(because the + comes to the right of the = sign) and here DEPT rows without any matching EMP rows are also returned with null values filled in the extra fields.

You can try it out on different tables and observe the tables for better understanding.

DUAL:

As we all know we can only use the select command to display values to the user and the select command always requires a table as an argument. But sometimes we need to know the date and some resluts to some other calculations. For cases like these ORACLE provides us with a dummy table called as DUAL. this dummy table has just one row and one column. Run Select * on Dual and see what it returns. Anyway, you can use to dual as a dummy table to return values for example

>SELECT sysdate FROM DUAL;

will return the system date to you. As you learn aboput more functions try them out on dual and verify the results.

>SELECT 2*2 From DUAL;  output: 4.

INSERT:

The syntax for insert command is given below:

>INSERT INTO tablename(COLUMN1, COL2,.....) VALUES (VALUE1,VALUE2,...);

If you include the column names explicitly then the values should correspond. That is think of column1, col2,... as formal arguments and value1, value2,... as actual arguments. If you don't specify the columns explicitly then the order in which thy were created in CREATE TABLE is taken.

Q)How can I insert values from already existing tables?

A)>INSERT INTO tablename SELECT * FROM existing_table;

Note: keep in mind that the columns and the domains should match.

Q)What datatypes require single quotes around them when inserting values?

A) CHAR, VARCHAR2, DATE.