The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise emp name

There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?

Use an ouer join to get your query.

Select E.ENAME, D.DNAME

from Employee E, Dept D

where D.DEPTNO (+)= E.DEPTNO

2.

RE: There are 2 tables, Employee and Department. There...

An outer join is correct, but try using the ANSII standard join syntax as it is more flexible with outer joins and filtering of the inner tables select e.name As Employee, d.name as Dept

3. select name, department from employee left join department on employee.department_id = department.id

4. Select DeptNo, DName, Count(1)FROM Emp a, Dept bWHERE a.deptno*=b.deptnoGroup By DeptNo, Dname

5. select e.empname,d.dptname

from employee e,department d

where e.eid=d.eid(+);

6. Select a.ename, b.dname

from Emp a, Dept b

where a.deptno (+)= b.deptno

Given a table which contains some rows with duplicate keys, how would you remove the duplicates?

select distinct * from table_1 into temp_table_1

truncate table_1

insert into table_1

select * from temp_table_1

This is one of the ways to eliminate duplicates .

How to find the 2 minimum salaries in a table?

select min(salary) from employee where salary >(select min(salary) from employee)

select salary, min(salary) from employee where salary >(select min(salary) from employee) group by salary.

DDL (Data Definition Language) statements are used to define the database structure or schema. Some examples:

1. CREATE - to create objects in the database
2. ALTER - alters the structure of the database
3. DROP - delete objects from the database
4. TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
5. COMMENT - add comments to the data dictionary
6. RENAME - rename an object

DML (Data Manipulation Language) statements are used for managing data within schema objects. Some examples:

1.SELECT - retrieve data from the a database
2. INSERT - insert data into a table
3. UPDATE - updates existing data within a table
4. DELETE - deletes all records from a table, the space for the records remain
5. MERGE - UPSERT operation (insert or update)
6. CALL - call a PL/SQL or Java subprogram
7. EXPLAIN PLAN - explain access path to data
8. LOCK TABLE - control concurrency

DCL is Data Control Language statements. Some examples:

1. GRANT - gives user’s access privileges to database
2. REVOKE - withdraw access privileges given with the GRANT command

Transaction Control

Manages the changes made by DML statements. These commands allow statements to be grouped together into logical transactions.

COMMIT - save work done
2. SAVEPOINT - identify a point in a transaction to which you can later roll back
3. ROLLBACK - restore database to original since the last COMMIT
4. SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

The Difference between ‘Count’ and ‘Count(*)’

‘Count’: Counts the number of non-null values.
‘Count(*)’: Counts the number of rows in the table, including null values and duplicates

The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible

Bore

Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes

Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete

Delete : (Data alone deleted), Doesn?t perform automatic commit

What’s the difference between a primary key and a unique key

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

Explanation : The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

CREATE TABLE .. AS SELECT command
Explanation : To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

What operator performs pattern matching

LIKE