SQL Interview Questions 2
Explain aggregate SQL functions ?SQL provides aggregate functions to assist with the summarization of large volumes of data. We’ll look at functions that allow us to add and average data, count records meeting specific criteria and find the largest and smallest values in a table.
The output is: Total = 75
The output is: AveragePrice = 262.50
If we inserted another row to the above table:
The output is: Tapan 45
Explain INSERT, UPDATE, and DELETE statements ?
INSERT statements can be carried out several ways:
The above statement is fine but the one below is recommended since it is less ambiguous and less prone to errors.
We can also use INSERT with the SELECT statements as shown below
UPDATE statement allows you to update a single or multiple statements.
Also can have more complex updates like
DELETE statements allow you to remove records from the database.
We can clear the entire table using
When running UPDATE/DELETE care should be taken to include WHERE clause otherwise you can inadvertently modify or delete records which you do not intend to UPDATE/DELETE.
How can you compare a part of the name rather than the entire name ?You can use wild card characters like:
How do you get distinct entries from a table ?The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table in a database.
How can you find the total number of records in a table ?Use the COUNT key word:
What's the difference between a primary key and a unique key ?Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, whereas unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
What is the best practice relating to primary key generation ?
What is the best practice to generate more portable primary keys ?The approach of using database specific unique id generator like a sequence in ORACLE, identity in Sybase etc is not portable because it is database dependent. You can use an "ID table" strategy to make your unique id generation more portable. This strategy uses a separate ID table to generate unique numbers for all your tables in the database. For example, ID table may look like:
maxValue of -1 means no max limit. You could write a EJB stateless session bean, which returns a unique id for the "name" passed in as an argument. You could use an entity bean or a stored proc to access the ID table. The ID table should be adequately isolated to prevent any dirty reads and non-repeatable reads occurring due to concurrent access to the ID table.
What are constraints ? Explain different types of constraints.Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
What are the best practices relating to constraints ?
What is an index ? What are the types of indexes ? How many clustered indexes can be created on a table? What are the advantages and disadvantages of creating separate index on each column of a table ?The books you read have indexes, which help you to go to a specific key word faster. The database indexes are similar. Enterprise Indexes are of two types. Clustered indexes and non-clustered indexes. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. The row located could be the RowID or the clustered index key, depending up on the absence or presence of clustered index on the table. If you create an index on each column of a table, it improves the query (i.e. SELECT) performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, and DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
RUP Interview Questions >>>