01. DDL, DML commands
DDL: Data
Definition Language (DDL) statements are used to define the database structure
or schema.
- CREATE - to create objects in the database
- ALTER - alters the structure of the database
- DROP - delete objects from the database
- TRUNCATE - remove all records from a table,
including all spaces allocated for the records are removed
- COMMENT - add comments to the data dictionary
- RENAME - rename an object
DML: Data Manipulation Language (DML) statements are used
for managing data within schema objects.
- SELECT - retrieve data from the a database
- INSERT - insert data into a table
- UPDATE - updates existing data within a table
- DELETE - deletes all records from a table, the
space for the records remain
- MERGE - UPSERT operation (insert or update)
- CALL - call a PL/SQL or Java subprogram
- EXPLAIN PLAN - explain access path to data
- LOCK TABLE - control concurrency
02. Group by , Having, Where and Order by clause
Where Clause:
WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.
eg: SELECT * FROM customers WHERE last_name = 'Anderson';
Order By Clause:
ORDER BY clause is used to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.
Syntax: SELECT expressions FROM tables WHERE conditions ORDER BY expression [ ASC | DESC ];
eg: SELECT supplier_city FROM suppliers WHERE supplier_name = 'Microsoft' ORDER BY supplier_city;
GROUP BY Clause:
GROUP BY Clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
aggregate_function can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.
eg: SELECT product, SUM(sale) AS "Total sales" FROM order_details GROUP BY product;
HAVING Clause:
HAVING Clause is used in combination with the GROUP BY Clause to restrict the groups of returned rows to only those whose the condition is TRUE.
eg1: SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 25000;
eg2: SELECT department, COUNT(*) AS "Number of employees" FROM employees
WHERE salary < 49500 GROUP BY department HAVING COUNT(*) > 10;
03. Types of Joins (Left Outer Join, Right Outer
Join, Full Outer Join, Self Join, Cross Join)
Joins can be classified into following types :
1.Inner Join (Equi Join & Natural Join)
2.Outer Join (Left Outer Join, Right Outer Join
& Full Join)
3.Self Join
4.Cross Join
Inner Join: To retrieve matched records we can
use comparison operators (i.e. =, < >).Whenever we need to get the
matched records from two tables,this type of Join is used.
Inner Join Also call as Natural Join or Equi
Join.
Syntax:
Select emp.emp_id, emp. sal, ph.phone_no from
employee emp Inner Join phone ph
on emp.emp_id = ph. emp_id where emp.sal >
2000
Outer Join : To retrieve matched and unmatched
records from two tables we can use Outer Join. Unmatched rows will be displayed
as NULL from both tables.
Outer Join can be classified as: Left Outer Join
, Right Outer Join and Full Outer Join.
Left Outer Join: All matched and unmatched
records from left table will be displayed and unmatched records will be
displayed as NULL from left table.But from right table only matched records
will be displayed as it is and unmatched records will not be displayed.
Syntax:
Select emp.emp_id, emp. sal, ph.phone_no from
employee emp Left Join phone ph
on emp.emp_id = ph.emp_id where emp.sal >
2000 ;
Right Outer Join: All Records from right table
will be displayed either matched or unmatched and unmatched records will be
displayed as NULL from right table.But from left table only matched records
will be displayed as it is and unmatched records will not be displayed.
Syntax:
Select emp.emp_id, emp. sal, ph.phone_no from
employee emp Right Join phone ph
on emp.emp_id = ph.emp_id where emp.sal >
2000 ;
Full Outer Join:
All matched and unmatched records from both left table and right table
will be displayed and unmatched records will be displayed as NULL from both the
tables.
Cross Join: This Join returns all rows from the
left table, each and every row from the left table is in combination with all
rows from the right table. Cross join also called as Cartesian Product Join.
Syntax:
SELECT au.au_fname, au.au_lname, p.pub_name FROM
authors AS au INNER JOIN publishers AS p ON au.city = p.city AND au.state =
p.state ORDER BY au.au_lname ASC, au.au_fname ASC ;
Self Join: A table can be joined to itself is a
self-join.
For example, we can use a self-join to find out
the authors in New Zealand who live in the same ZIP Code area.
SELECT au.au_fname, au.au_lname, a.au_fname,
au2.au_lname FROM authors au INNER Jauthors a ON au.zip = a.zip
WHERE au.city = ' New Zealand' ORDER BY
au.au_fname ASC, au.au_lname ASC ;
04. Difference between View and Materialized view
http://marukondav.blogspot.in/2014/07/what-is-difference-between-view-and.html
05. Difference between Join and Union
JOIN: A Join is used for displaying columns with the same or
different names from different tables. The output displayed will have all the
columns shown individually. i.e. The columns will be aligned next to each
other.
UNION: The UNION set operator is used for
combining data from two tables which have columns with the same datatype. When a UNION is
performed the data from both tables will be collected in a single column having
the same datatype.
06. Difference between Where clause and Having
clause
HAVING is used to
check conditions after the aggregation takes place. Such as Group by
HAVING clause introduces a condition on
aggregations
WHERE is used before the aggregation takes
place.
WHERE clause introduces a condition on
individual rows.
07. Difference between Union and Union All
Union: UNION performs a DISTINCT on the
result set, eliminating any duplicate rows.
Union All: UNION ALL does not remove duplicates
and it therefore faster than UNION.
08. Difference between Rank, Dense Rank and
RowNumber
ROW_NUMBER () OVER
([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row
within a partition of a result set, starting at 1 for the first row in each partition
RANK () OVER ([<partition_by_clause>]
<order_by_clause>)
Returns the rank of each row within the
partition of a result set.
DENSE_RANK () OVER
([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the
partition of a result set, without any gaps in the ranking.
09. Difference between Function and Stored Procedure
- Function must return a value but in Stored
Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters
for it whereas Procedures can have input/output parameters.
- Function takes one input parameter it is
mandatory but Stored Procedure may take o to n input parameters. Functions can be called from Procedure
whereas Procedures cannot be called from Function.
- Procedure allows SELECT as well as
DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT
statement in it.
- Procedures cannot be utilized in a SELECT
statement whereas Function can be embedded in a SELECT statement.
- Stored Procedures cannot be used in the SQL
statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
- Functions that return tables can be treated
as another rowset. This can be used in JOINs with other tables. Inline Function can be thought of as views
that take parameters and can be used in JOINs and other Rowset operations.
- Exception can be handled by try-catch block
in a Procedure whereas try-catch block cannot be used in a Function.
- We can go for Transaction Management in
Procedure whereas we can't go in Function.
10. Difference between Drop, Truncate and Delete
DELETE: The DELETE command is used to remove rows
from a table. A WHERE clause can be used to only remove some rows. If no
WHERE condition is specified, all rows
will be removed. After performing a DELETE operation you need to COMMIT or
ROLLBACK the transaction to make the
change permanent or to undo it. Note that this operation will cause all DELETE
triggers on the table to fire.
TRUNCATE:
TRUNCATE removes all rows from a table. The operation cannot be rolled
back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo
space as a DELETE.
DROP: The DROP command removes a table from the
database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation
cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas
DELETE is a DML command. Therefore DELETE operations can be rolled back (undone),
while DROP and TRUNCATE operations cannot be rolled back.
11.
Difference between Sub-query and Correlated Sub-query
Subquery : The inner query is executed
only once The inner query will get executed first and the output of the inner
query used by the outer query.The
inner query is not dependent on outer query.
Eg:- SELECT cust_name, dept_no FROM Customer
WHERE cust_name IN (SELECT cust_name FROM Customer);
Correlated subquery:The outer query will
get executed first and for every row of outer query, inner query will get executed. So the inner query will get
executed as many times as no.of rows in result of the outer query.The outer
query output can use the inner query
output for comparison. This means inner query and outer query dependent on each
other
Eg:- SELECT cust_name,dept_id FROM Cust
WHERE cust_name in (SELECT cust_name FROM dept
WHERE cust.dept_id=dept.dept_id);
12.
Update, Alter functions ex: Rename the column,
Update the column value
13.
Top 5 and bottom 5 sales
http://marukondav.blogspot.in/2014/06/based-on-parameter-to-find-top-5-and.html
14.
Indexes( Bitmap index B-tree Index)
Bitmap Indexes
Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.
Characteristic of Bitmap Indexes
@@For columns with very few unique values (low cardinality)
Columns that have low cardinality are good candidates (if the cardinality of a column is <= 0.1 % that the column is ideal candidate, consider also 0.2% – 1%)@@Tables that have no or little insert/update are good candidates (static data in warehouse)
@@Stream of bits: each bit relates to a column value in a single row of table
create bitmap index person_region on person (region);
Row Region North East West South
1 North 1 0 0 0
2 East 0 1 0 0
3 West 0 0 1 0
4 West 0 0 1 0
5 South 0 0 0 1
6 North 1 0 0 0
Advantage of Bitmap Indexes
The advantages of them are that they have a highly compressed structure, making them fast to read and their structure makes it possible for the system to combine multiple indexes together for fast access to the underlying table.
Compressed indexes, like bitmap indexes, represent a trade-off between CPU usage and disk space usage. A compressed structure is faster to read from disk but takes additional CPU cycles to decompress for access - an uncompressed structure imposes a lower CPU load but requires more bandwidth to read in a short time.
One belief concerning bitmap indexes is that they are only suitable for indexing low-cardinality data. This is not necessarily true, and bitmap indexes can be used very successfully for indexing columns with many thousands of different values.
Disadvantage of Bitmap Indexes
The reason for confining bitmap indexes to data warehouses is that the overhead on maintaining them is enormous. A modification to a bitmap index requires a great deal more work on behalf of the system than a modification to a b-tree index. In addition, the concurrency for modifications on bitmap indexes is dreadful.
15.
Integrity Constraints (Primary Key, Foreign Key,
Unique Key, Not Null Constraint, Check Constraint)
16.
Query Performance tuning
·
All columns involved in indexes should appear on
WHERE and JOIN clauses on the same sequence they appear on index.
·
Avoid too much JOINs on query.
·
Avoid cursors at all costs!
·
Always restrict the number of rows and columns
of your result. That way, you save disk, memory and network of the database
server. Always verify WHERE clause and use TOP if necessary.
·
Verify if your server isn’t suffering from
not-enough-disk-space illness.
·
SQL Server is case insensitive: he does not care
about ‘A’ or ‘a’. Save time and don’t use functions like LOWER and UPPER when
comparing VARCHARs.
·
The decreasing performance order of operators
is: = (faster)>, >=, <,
<=LIKE<> (slower)
·
Use EXISTS or NOT EXISTS instead of IN or NOT
IN. IN operators creates a overload on database.
·
Try to use BETWEEN instead of IN, too.
·
When using LIKE operator, try to leave the
wildcards on the right side of the VARCHAR.
·
Always avoid using functions on your queries.
SUBSTRING is your enemy. Try to use LIKE instead.
·
Sometimes is better to make various queries with
UNION ALL than a unique query with too much OR operations on WHERE clause. Test
it.
·
When there is a HAVING clause, it is better to
filter most results on the WHERE clause and use HAVING only for what it is necessary.
·
Use UNION ALL instead of UNION. Use less subqueries.
·
Avoid to do much operations on your WHERE
clause. If you are searching for a + 2 > 7, use a > 5 instead.
·
Use more variable tables and less temporary
tables.
·
To delete all rows on a table, use TRUNCATE
TABLE statement instead of DELETE.
17.Difference between in IN and EXISTS?
Performance wise both should be same with less no of records.If no of records will be more, EXISTS is faster than IN.
Mostly IN is used in case of subqueries and EXISTS is used in case of correlated subqueries.
18.How to find average sal without using avg function?
We can find the average salary by using "sum (salary)/count(salary)"
Join can be used in either Where or From clause. But Join specification in From clause is recommended. We can also use Having and Where clause for filtering the data.
19. Inline view?
An inline view is a statement in the FROM-clause of another SELECT statement. In-line views are commonly used simplify complex queries by removing join operations and condensing several separate queries into a single query. This feature was introduced in Oracle 7.2.An example of inline view is given blow
SELECT * FROM (select dept_no, count(*) emp_count
from employeesgroup by dept_no) employees,
department WHERE department.dept_no = employee.dept_no;
20. With Clause?
21. Difference between Package and Stored Procedure?
