Database Part - 1

What is DDL and DML ?

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

[ C A D T R ]

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
RENAME - rename an object
COMMENT - add comments to the data dictionary

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

[ S I  D U ]

SELECT - retrieve data from the a database
INSERT - insert data into a table
DELETE - deletes all records from a table, the space for the records remain
UPDATE - updates existing data within a table
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

To check the complete table information including storage engine.
show table status ;
show table status where Name = '';


Difference between INNODB and MYISAM ?

1. The main differences between InnoDB and MyISAM ("with respect to designing a table or database" you asked about) are support for "referential integrity" and "transactions".
If you need the database to enforce foreign key constraints, or you need the database to support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted) then you would choose the InnoDB engine, since these features are absent from the MyISAM engine.

2. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table.

MYISAM:

1. supports Table-level Locking
2. designed for need of speed
3. does not support foreign keys hence we call MySQL with MYISAM is DBMS
4. stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
5. does not support transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
6. supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

1. supports Row-level Locking
2. designed for maximum performance when processing high volume of data
3. support foreign keys hence we call MySQL with InnoDB is RDBMS
4. stores its tables and indexes in a tablespace
5. supports transaction. You can commit and rollback with InnoDB

Those two specific engines you asked about (InnoDB and MyISAM) have different design goals. MySQL also has other storage engines, with their own design goals. So, in choosing between InnoDB and MyISAM, the first step is in determining if you need the features provided by InnoDB. If not, then MyISAM is up for consideration. A more detailed discussion of differences is rather impractical (in this forum) absent a more detailed discussion of the problem space... how the application will use the database, how many tables, size of the tables, the transaction load, volumes of select, insert, updates, concurrency requirements, replication features, etc.

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What are the Properties of the Relational Tables?

Relational tables have the following six properties:

- Values are atomic.
- Column values are of the same kind.
- Each row is unique.
- The sequence of columns is insignificant.
- The sequence of rows is insignificant.
- Each column must have a unique name.

Difference between primary key and unique key in SQL

- Unique key in a table can be null, at-least one but primary key can not be null in any table in relation database like MySQL , Oracle etc.
- Primary key can be combination of more than one unique keys in same table.
- There can be only one primary key per table in relation database e.g. MySQL, Oracle or Sybase but there can be more than one unique key per table.
- Unique key is represented using unique constraint while primary key is created using primary key constraint in any table and it's automatically gets unique constraint.
- Many database engine automatically puts clustered index on primary key and since you can only have one clustered index per table, its not available to any other unique key at same time.

What is a candidate key ?

Candidate keys are those keys which is candidate for primary key of a table. In simple words we can understand that such type of keys which full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key. So thus type of key is known as candidate key. Every table must have at least one candidate key but at the same time can have several.

What is Alternate Key?

If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of  that table. Like here we can take a very simple example to understand the concept of alternate key. Suppose we have a table named Employee which has two columns EmpID and EmpMail, both have not null attributes and unique value. So both columns are treated as candidate key. Now we make EmpID as a primary key to that table then EmpMail is known as alternate key.

What is Normalization?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. There are several benefits for using Normalization in Database.

  Benefits: 

- Eliminate data redundancy

- Improve performance
- Query optimization
- Faster update due to less number of columns in one table
- Index improvement

What are the Different Normalization Forms?

1NF: Eliminate Repeating Groups -
- Eliminate duplicate columns from the same table.
- Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
  - Remove duplicate groups.
  - Create primary key.

Name	State	Country	Phone1	Phone2	Phone3
John	101	1	488-511-3258	781-896-9897	425-983-9812
Bob	102	1	861-856-6987
Rob	201	2	587-963-8425	425-698-9684
PK			[ Phone Nos ]
?				?
ID	Name	State	Country	Phone
1	John	101	1	488-511-3258
2	John	101	1	781-896-9897
3	John	101	1	425-983-9812
4	Bob	102	1	861-856-6987
5	Rob	201	2	587-963-8425
6	Rob	201	2	425-698-9684

2NF: Eliminate Redundant Data -
- Meet all the requirements of the first normal form.
- Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
- Remove columns which create duplicate data in a table and related a new table with Primary Key – Foreign Key relationship.

ID	Name	State	Country	Phone
1	John	101	1	488-511-3258
2	John	101	1	781-896-9897
3	John	101	1	425-983-9812
4	Bob	102	1	861-856-6987
5	Rob	201	2	587-963-8425
6	Rob	201	2	425-698-9684
ID	Name	State	Country		PhoneID	ID	Phone
1	John	101			1	1	488-511-3258
2	Bob	102			2	1	781-896-9897
3	Rob	201			3	1	425-983-9812
					4	2	587-963-8425
					5	3	587-963-8425
					6	3	425-698-9684

3NF: Eliminate Columns Not Dependent On Key -
- Meet all the requirements of the second normal form.
- Remove columns that are not dependent upon the primary key.

Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. For example, consider a table with following fields.

Student_Detail Table:

Student_id	Student_name	DOB	Street	city	State	Zip

In this table Student_id is Primary key, but street, city and state depends upon Zip. The dependency between zip and other fields is called transitive dependency. Hence to apply 3NF, we need to move the street, city and state to new table, with Zip as primary key.

New Student Table following 1NF will be:

Student_id	Student_name	DOB	Zip

Address Table:

Zip	Street	city	state

The advantage of removing transtive dependency is,
- Amount of data duplication is reduced.
- Data integrity achieved.

BCNF: Boyce-Codd Normal Form - A database table is said to be in BCNF if it is in 3NF and contains each and every determinant as a candidate key.The process of converting the table into BCNF is as follows:
- Remove the non trival functional dependency.
- Make separate table for the determinants.

- A relation is in Boyce-Codd Normal Form (BCNF) if every determinant is a candidate key. (See the links in the box at right for definitions of determinant and candidate key)

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

What is Truncate command in SQL ?

Use truncate table if you need to delete all rows, since truncate doesn't allow you to specify WHERE clause. truncate removes data by deallocating space used by table which removes lot of overhead in terms of logging and locking and that's why truncate is faster than delete.What you need to take care is rollback, data deleted by truncate can not be rolled back until data server specifically supports it e.g. MSSQL Server which allows to commit or rollback truncate table statement transactional. Another caveat with truncate table statement is that it doesn't fire a trigger and you can not truncate a table when a foreign key references any column to the table to be truncated. Only situation I see which is perfect for using truncate is purging tables with huge data, though there is another solution exists to drop table and recreated it if that make sense.

What is Delete command in SQL ?

Delete is another sql command available for removing records from table. Delete is even more flexible than truncate like it provides support to WHERE Clause which can be use to remove selective data. It logs each row which allows operation to be rolled back and it also fires triggers. One disadvantage of using delete is speed and locking. Delete acquires lock on table and its also very slow operation because of logging, which makes it unsuitable for removing records from large tables. One workaround for this is batch-delete in which you remove batch of records instead on one record at a time. Delete is most suitable fore removing selective data and use it where you want to rollback transaction in database. It’s not useful to purge large amount of data from tables and should not be used, otherwise it could lock the table for very long time, blew log segment and can take ages to complete.

Difference between truncate and delete command in SQL

This is an important point to understand before using truncate or delete on production environment, or writing any script which purges data from tables.

1. truncate is fast delete is slow.
2. truncate doesn't do logging delete logs on per row basis.
3. rollback is possible with delete not with truncate until specifically supported by vendor.
4. truncate doesn't fire trigger, delete does.
5. Don't delete, truncate it when it comes to purge tables.
6. truncate reset identity column in table if any, delete doesn't.
7. truncate is DDL while delete is DML (use this when you are writing exam)
8. truncate doesn't support where clause, delete does.

Database Part - 2

What are Different Types of Join?

Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Inner Join

A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.

Outer Join

A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:

Left Outer Join: In Left Outer Join, all the rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.

Right Outer Join: In Right Outer Join, all the rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

Full Outer Join: In Full Outer Join, all the rows in all joined tables are included, whether they are matched or not.

Self Join

This is a particular case when one table joins to itself with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchical reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

SQL Views

A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.

The Syntax to create a sql view is

CREATE VIEW view_name
AS
SELECT column_list
FROM table_name [WHERE condition];

Example of Displaying a View

Syntax of displaying a view is similar to fetching data from table using Select statement.

SELECT * from sale_view;

Force View Creation

force keyword is used while creating a view. This keyword force to create View even if the table does not exist. After creating a force View if we create the base table and enter values in it, the view will be automatically updated.

Syntax for forced View is,

CREATE or REPLACE force view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Update a View

Update command for view is same as for tables.

Syntax to Update a View is,

UPDATE view-name
set value
WHERE condition;
If we update a view it also updates base table data automatically.

Alter View

alter view emp_data as select Employee_id, First_name, last_name from Employee

Read-Only View

We can create a view with read-only option to restrict access to the view.

Syntax to create a view with Read-Only Access

CREATE or REPLACE force view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition with read-only
The above syntax will create view for read-only purpose, we cannot Update or Insert data into read-only view. It will throw an error.

Drop a view

Once a SQL VIEW has been created, you can drop it with the SQL DROP VIEW Statement.

The syntax for the SQL DROP VIEW Statement is:

DROP VIEW view_name;

Advanatges : 

1. Views can hide complexity
If you have a query that requires joining several tables, or has complex logic or calculations, you can code all that logic into a view, then select from the view just like you would a table.

2. Views can be used as a security mechanism
A view can select certain columns and/or rows from a table, and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see.

3. Views can simplify supporting legacy code
If you need to refactor a table that would break a lot of code, you can replace the table with a view of the same name. The view provides the exact same schema as the original table, while the actual schema has changed. This keeps the legacy code that references the table from breaking, allowing you to change the legacy code at your leisure.

Disadvantages : 

1. Performance
Views create the appearance of a table, but the DBMS must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex, multi-table query then simple queries on the views may take considerable time.

2. Update restrictions
When a user tries to update rows of a view, the DBMS must translate the request into an update on rows of the underlying source tables. This is possible for simple views, but more complex views are often restricted to read-only.

Question: Does the SQL View exist if the table is dropped from the database?

Answer: Yes, in Oracle, the SQL VIEW continues to exist even after one of the tables (that the SQL VIEW is based on) is dropped from the database. However, if you try to query the SQL VIEW after the table has been dropped, you will receive a message indicating that the SQL VIEW has errors.

Can you force the database to use an index on a query?

Generally, you will not tell the database when to actually use an index – that decision will be made by the database itself. Although it is worth noting that in most databases (like Oracle and MySQL), you can actually specify that you want the index to be used.

What is the cost of having a database index / Disadvantages of indexing?

So, what are some of the disadvantages of having a database index? Well, for one thing it takes up space – and the larger your table, the larger your index. Another performance hit with indexes is the fact that whenever you add, delete, or update rows in the corresponding table, the same operations will have to be done to your index. Remember that an index needs to contain the same up to the minute data as whatever is in the table column(s) that the index covers.

As a general rule, an index should only be created on a table if the data in the indexed column will be queried frequently.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What is the Difference between a Function and a Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

What is subquery? Explain the Properties of a Subquery?

Subqueries are often referred to as sub-selects as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A subquery is executed by enclosing it in a set of parentheses. Subqueries are generally used to return a single row as an atomic value although they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. This implies that a subquery SELECT statement can stand alone, and it does not depend on the statement in which it is nested. A subquery SELECT statement can return any number of values and can be found in the column list of a SELECT statement, and FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A subquery can also be used as a parameter to a function call. Basically, a subquery can be used anywhere an expression can be used.


What is User-defined Functions? What are the types of User-defined Functions that can be created?

User-defined Functions allow defining its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

Different Types of User-Defined Functions created are as follows:

Scalar User-defined Function

A scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages.

Inline Table-Value User-defined Function

An Inline table-value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-Statement Table-Value User-defined Function

A multi-statement table-value user-defined function returns a table, and it is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command, you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior encountered while using a stored procedure which can also return record sets.

In the table below, how would you retrieve the unique values for the employee_location without using the DISTINCT keyword?

We can actually accomplish this with the GROUP BY keyword.