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.
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
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.
Use the DISTINCT keyword to display only distinct column values or to remove duplicate values.
You can use expressions in the SELECT command
FROM Sailors s
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.
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.
1. I want to display the column values in a sorted fashion, how do I do it?
A) You use the ORDER BY clause
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
Consider the two tables EMP and DEPT below, we will work with these table instances.
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
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
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:
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.
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
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
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.
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.
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.