SQL Questions and Answers

Differences between oracle and mysql:

Etisalat

DEVELOPER

bangalore

india

BLOGGER

SQL

1.1.Some syntactic differences between Mysql and Oracle.
2.2.Oracle is not open source mysql is open source database.
3.3.Mysql does not have a language such as PL/SQL, but instead allows.
4.4.Mysql support auto increment function. Oracle does not support auto increment function.
5.5.Oracle support sub queries. Mysql does not support sub queries.
6.6.Mysql does not support views, trigger, indexing, sequence. Store procedure oracle support.
7.7.Oracle is generally use for much larger applications than mysql.
8.8.Oracle requires much more setup and tuning than mysql.

You can change and use it for getting Nth Highest Salary from Employee table as follows:

SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) aORDER BY salary

Using Self Joins: Example

The following query uses a self join to return the name of each employee along with the name of the employee's manager. (A WHERE clause is added to shorten the output.)

SELECT e1.last_name' works for 'e2.last_name "Employees and Their Managers" FROM employees e1, employees e2 WHERE e1.manager_id = e2.employee_id AND e1.last_name LIKE 'R%';

Employees and Their Managers

-------------------------------

Rajs works for Mourgos

Raphaely works for King

Rogers works for Kaufling

Russell works for King

The join condition for this query uses the aliases e1 and e2 for the sample table employees:e1.manager_id = e2.employee_id

Using Outer Joins: Examples

The following example uses a left outer join to return the names of all departments in the sample schema hr, even if no employees have been assigned to the departments:

SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id;

DEPT_ID LAST_NAME

------------ -----------------

10 Whalen

20 Hartstein

20 Fay

30 Raphaely

...

250

260

270

Users familiar with the traditional Oracle outer joins syntax will recognize the same query in this form:

SELECT d.department_id, e.last_name FROM departments d, employees e WHERE d.department_id = e.department_id(+) ORDER BY d.department_id;

Oracle Corporation strongly recommends that you use the more flexible Oracle9i FROM clause join syntax shown in the former example.
The left outer join returns all departments, including those without any employees. The same statement with a right outer join returns all employees, including those not yet assigned to a department:
Note: The employee Zeuss was added to the employees table for these examples, and is not part of the sample data.

SELECT d.department_id, e.last_name

FROM departments d

RIGHT OUTER JOIN employees e

ON d.department_id = e.department_id

ORDER BY d.department_id;

DEPT_ID LAST_NAME

------------- -------------------------

...

110 Higgins

110 Gietz

.....Grant

.....Zeuss

It is not clear from this result whether employees Grant and Zeuss have department_id NULL, or whether their department_id is not in the departments table.

To determine this requires a full outer join:

SELECT d.department_id as d_dept_id, e.department_id as e_dept_id, e.last_name

FROM departments d

FULL OUTER JOIN employees e

ON d.department_id = e.department_id

ORDER BY d.department_id;

D_DEPT_ID E_DEPT_ID LAST_NAME

---------- ---------- -------------------------

...

110 110 Gietz

110 110 Higgins

...

260 270 Zweues

Because the column names in this example are the same in both tables in the join, you can also use the common column feature (the USING clause) of the join syntax, which coalesces the two matching columns department_id.

The output is the same as for the preceding example:

SELECT department_id AS d_e_dept_id, e.last_name

FROM departments d FULL OUTER JOIN employees e

USING (department_id) ORDER BY department_id;

D_E_DEPT_ID LAST_NAME

----------- -------------------------

...

110 Higgins

110 Gietz ...

260

270

.... Grant

.... Zeuss

What is a self join? Explain it with an example?

CREATE TABLE candytest

(kidId char(2), candycolor varchar(10) )

GO

INSERT INTO candytest SELECT 'K1', 'Yellow'

INSERT INTO candytest SELECT 'K1', 'Red'

INSERT INTO candytest SELECT 'K2', 'Red'

INSERT INTO candytest SELECT 'K2', 'Blue'

INSERT INTO candytest SELECT 'K3', 'White'

INSERT INTO candytest SELECT 'K3', 'Red'

INSERT INTO candytest SELECT 'K3', 'Yellow'

Go--

Now to find those kidid's which has both yellow and redcolored candies, we can write query as:

SELECT c1.kididFROM candytest AS c1 JOIN candytest AS c2ON c1.candycolor = 'Red' AND c2.candycolor = 'Yellow'AND c1.kidid = c2.kidid

Re: Table Has C1 And C2 Column If Exits any record in c1 then Update c2 record Otherwise insert new record in the C1 And C2 (Using Procedure)

select * from x;

C1 C2

----- ----------
1
2
3
4
5
6
7
8
9
10

1 create or replace procedure updt_x is
2 cnt number(4);
3 begin
4 select count(1) into cnt from x where c1 is not null;
5 if cnt > 0 then
6 update x
7 set c2=10;
8 else
9 insert into x
10 values(1,2);
11 end if;
12 commit;
13* end;
SQL> execute updt_x;
PL/SQL procedure successfully completed.
SQL> select * from x;
C1 C2
---------- ----------
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
9 10
10 10
10 rows selected.
SQL> delete from x;
10 rows deleted.
SQL> commit;
PL/SQL procedure successfully completed.
SQL> select * from x;
C1 C2
---------- ----------
1 2