Relational DBMSes

Relational DBMSes


There is a table of two fields, primary key integer ID and
char(50) VALUE. Before adding the unique index to it, you need to
know, if there are duplicated VALUEs in the table. How you will do it?

Ans: should check the primary key integer is repeating in the
primary key field. If any one value from the field returning the
count greater than 1, we can not add unique index to the the field

What's the difference between INNER JOIN and OUTER JOIN? What
other types of JOINs do you know?

Ans : The INNER JOIN takes data from both tables returns the
specified data exists in both tables. But the OUTER JOIN check both
tables and returns values from the outer table when the criteria mets.

Other major join are LEFT OUTER JOIN, RIGHT OUTER JOIN.

What is a VIEW? What are the advantages and disadvantages of views?

Ans : View is a representation of a sql statement stored in
memory, which can be easily reusable.
Advantages of views:
we can view the data without storing the data into the object.
We can restict the view of a table i.e. can hide some of columns
in the tables.
We can Join two or more tables and show it as one object to user.
Disadvantages of views
we can not use DML operations on views.
When a table is dropped view will becomes inactive.. it depends on
the table objects.
It is an object so it occupies space

What's the main difference between WHERE and HAVING?

Ans: WHERE is a single row function, where as Having is based on groups.
When we use having Having we should use the Group by keyword.

What are subqueries? Does MySQL support them?

A query that is used within another query. For example a
select-statement within the WHERE or HAVING clause of another SQL
statement.
Mysql supporting the subquery system.

please complete the questions through the comments

thanks