Language we speak to communicate once idea with another. In order to communicate with the database world we have a SQL language with has its own defined set of rules. The intermediate language which give more sense to the human and full sense to the Database and act according to the human request is using the SQL
Structured Query Language. This language is used to query the database and can able to talk with the Database engine. This language is common across all the database like sql server, oracle, MySQL, Teradata.
- What is the difference between a where clause and a having clause?
Where is a restriction statement. It is used to restrict data from the database. Where clause is using before retrieving the result. But Having clause is using after retrieving the data. Having clause is a kind of filtering command.
- What is the basic form of a SQL statement to read data out of a table?
To read data out of table is “SELECT * FROM TBL”
- What are the tradeoffs with having indexes?
- Faster selects, slower updates.
- Extra storage space to store indexes. Updates are generally slower because in addition to updating the table you have to update the index as well.
- What is a join?
Join is used to connect two or more tables logically with or without common field.
- What is normalization and Denormalization? Why do you need denormalize?
Normalizing data means eliminating redundant information from a table and organizing the data. Denormalization means allowing redundancy in a table. The main benefit of denormalization is to improve performance with simplified data retrieval process. It reduces the number of joins needed for data retrieval process.
- What is a constraint?
Constraint is used to apply referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server:
PRIMARY/UNIQUE – enforces uniqueness of a particular table column.
DEFAULT – specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY – validates that every value in a column exists in a column of another table.
CHECK – checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint.
NOT NULL is one more constraint which does not allow values in the specific column to be null.
- What types of index data structures can you have? An index helps to faster search values in tables.
The three most commonly used index-types are: –
B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases.
string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast. But the limitation is data value cannot be large, e.g. Gender (M OR F ) OR degree(BSC,MCA,PHD)
A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
- What is a primary key?
Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must have NOT NULL property.
- What is a functional dependency? How does it relate to database table design?
Functional dependency relates to how one object depends upon the other in the database.
EX: procedure or function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2.
- What is a trigger?
Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurrence of one of the data-modification operations.
- Why can a group by or order by clause be expensive to process?
Processing of group by or order by clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive?
- What is index covering of a query?
Index covering means that Data can be found only using indexes, without touching the tables
- What is a SQL view?
An output of a query can be stored as a view.
View is a precompiled SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View can also be used to restrict users from accessing the tables directly.