dbms theory

1->What is the difference between primary key and unique constraints?
Ans: Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains. 

2->What is the difference between having and where clause?
Ans: HAVING is used to specify a condition for a group or an aggregate function used in select statement. The WHERE clause selects before grouping. The HAVING clause selects rows after grouping. Unlike HAVING clause, the WHERE clause cannot contain aggregate functions. (See this for examples).
See Having vs Where Clause? for more details

3->

How to print duplicate rows in a table?

Let us consider below table.

NAME	SECTION
abc	CS1
bcd	CS2
abc	CS1

In the above table, we can find duplicate row using below query.

SELECT name, section FROM tbl
GROUP BY name, section
HAVING COUNT(*) > 1

4->

What are the uses of view?

1. Views can represent a subset of the data contained in a table; consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.
2. Views can join and simplify multiple tables into a single virtual table
3. Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
4. Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
5. Views take very little space to store; the database contains only the definition of a view, not a copy of all the data which it presentsv.
6. Depending on the SQL engine used, views can provide extra security

5->

What is a Trigger?
Ans: A Trigger is a code that associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table. Triggers can be useful to maintain integrity in database.

6->

What is a stored procedure?
Ans: A stored procedure is like a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks.

7->. Query to find 2nd highest salary of an employee?

SELECT max(salary) FROM EMPLOYEES WHERE salary IN
(SELECT salary FROM EMPLOYEEs MINUS SELECT max(salary)
FROM EMPLOYEES);

8->Q. Why we cannot use WHERE clause with aggregate functions like HAVING ?

The difference between the having and where clause in SQL is that the where clause canNOT be used with aggregates, but the having clause can. Please note : It is not a predefined rule but by and large you’ll see that in a good number of the SQL queries, we use WHERE prior to GROUP BY and HAVING after GROUP BY.

The Where clause acts as a pre filter where as Having as a post filter.

The where clause works on row’s data, not on aggregated data.

Let us consider below table ‘Marks’.

Student       Course      Score

a                c1             40
a                c2             50
b                c3             60
d                c1             70
e                c2             80

Consider the query

SELECT Student, sum(Score) AS total 
FROM Marks

This would select data row by row basis. The having clause works on aggregated data.

For example,  output of below query

SELECT Student, sum(score) AS total FROM Marks

Student     Total
a             90
b             60
d             70
e             80

When we apply having in above query, we get

SELECT Student, sum(score) AS total
FROM Marks having total > 70

Student     Total
a             90
e 80

9->  Q. Difference between primary key and unique key and why one should use unique key if it allows only one null ?

Primary key:

• Only one in a row(tuple).
• Never allows null value(only key field).
• Unique key identifier and can not be null and must be unique.

Unique Key:

• Can be more than one unique key in one row.
• Unique key can have null values(only single null is allowed).
• It can be a candidate key.

10-> What’s the difference between materialized and dynamic view?

Materialized views

• Disk based and are updated periodically based upon the query definition.
• A materialized table is created or updated infrequently and it must be synchronized with its associated base tables.

Dynamic views

• Virtual only and run the query definition each time they are accessed.
• A dynamic view may be created every time that a specific view is requested by the user.

11-> Q. What is embedded and dynamic SQL? 

Static or Embedded SQL

• SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application.

Dynamic SQL

• SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries.
• Dynamic SQL is a programming technique that enables you to buildSQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.

S.No.	Static (embedded) SQL	Dynamic (interactive) SQL
1.	In static SQL how database will be accessed is predetermined in the embedded SQL statement.	In dynamic SQL, how database will be accessed is determined at run time.
2.	It is more swift and efficient.	It is less swift and efficient.
3.	SQL statements are compiled at compile time.	SQL statements are compiled at run time.
4.	Parsing, validation, optimization, and generation of application plan are done at compile time.	Parsing, validation, optimization, and generation of application plan are done at run time.
5.	It is generally used for situations where data is distributed uniformly.	It is generally used for situations where data is distributed non-uniformly.
6.	EXECUTE IMMEDIATE, EXECUTE and PREPARE statements are not used.	EXECUTE IMMEDIATE, EXECUTE and PREPARE statements are used.
7.	It is less flexible.	It is more flexible.

12->

xxxxxxxxxxxxxxx

sql

 1->Query to find 2nd highest salary of an employee?

SELECT max(salary) FROM EMPLOYEES WHERE salary IN
(SELECT salary FROM EMPLOYEEs MINUS SELECT max(salary)
FROM EMPLOYEES);

2->Write a trigger to update Emp table such that, If an updation is done in Dep table then salary of all employees of that department should be incremented by some amount (updation)

Assuming Table name are Dept and Emp, trigger can be written as –

CREATE OR REPLACE TRIGGER update_trig
AFTER UPDATE ON Dept
FOR EACH ROW
DECLARE
CURSOR emp_cur IS SELECT * FROM Emp;
BEGIN
FOR i IN emp_cur LOOP
IF i.dept_no = :NEW.dept_no THEN
DBMS_OUTPUT.PUT_LINE(i.emp_no);  --  for printing those
UPDATE Emp                      -- emp number which are
SET sal = i.sal + 100           -- updated
WHERE emp_no = i.emp_no;
END IF;
END LOOP;
END;

xxxxxxxxxxxxxx