Clouds
Home

SQL Interview Questions 1
SQL Interview Questions 2
RUP Interview Questions
UML Interview Questions

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.
ORDERID FIRSTNAME SURNAME QTY UNITPRICE
1001 Tapan Bhardwaj 25 10.5
1002 Kundan Upadhyaya 25 10.5
1003 Pawan Bhardwaj 25 10.5

SELECT SUM(QTY) AS Total FROM Orders;
The output is: Total = 75
SELECT AVG(UnitPrice * QTY) As AveragePrice FROM Orders;
The output is: AveragePrice = 262.50
If we inserted another row to the above table:
ORDERID FIRSTNAME SURNAME QTY UNITPRICE
1004 Tapan Bhardwaj 20 10.5

SELECT FIRSTNAME,SUM(QTY) FROM orders GROUP BY FIRSTNAME HAVING SUM(QTY)>25;
The output is: Tapan 45

Explain INSERT, UPDATE, and DELETE statements ?

INSERT statements can be carried out several ways:

INSERT INTO ORDERS values (1004, 'Tapan', 'Bhardwaj', 20, 10.50);
The above statement is fine but the one below is recommended since it is less ambiguous and less prone to errors.
INSERT INTO ORDERS (orderid, firstname, surname, qty, unitprice) values (1005, 'Tapan', 'Bhardwaj',20, 10.50);
We can also use INSERT with the SELECT statements as shown below
INSERT into NEW_ORDERS (orderid, firstname, surname, qty, unitprice) SELECT orderid, firstname, surname, qty, unitprice FROM orders WHERE orderid = 1004;

UPDATE statement allows you to update a single or multiple statements.

UPDATE ORDERS set firstname='Vishal', surname='Bhardwaj' WHERE orderid=1004;
Also can have more complex updates like
UPDATE supplier SET supplier_name = ( SELECT customer.name FROM customers WHERE customers.customer_id = supplier.supplier_id) WHERE EXISTS (SELECT customer.name FROM customers WHERE customers.customer_id = supplier.supplier_id);

DELETE statements allow you to remove records from the database.

DELETE FROM ORDERS WHERE orderid=1004;
We can clear the entire table using
TRUNCATE TABLE employees;
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:
  • * ( % in oracle) :: Match any number of characters.
  • ? ( _ in oracle) :: Match a single character.
  • To find all the employees who has "au":
    SELECT * FROM employees emp WHERE emp.firstname LIKE ‘%au%’;

    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.
    SELECT DISTINCT empname FROM emptable

    How can you find the total number of records in a table ?

    Use the COUNT key word:
    SELECT COUNT(*) FROM emp WHERE age>25

    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 ?

  • A best practice in database design is to use an internally generated primary key. The database management system can normally generate a unique identifier that has no meaning outside of the database system. For example "Sequences" in Oracle, "Identity" columns in Sybase etc.
  • It is bad practice to use timestamps as a primary key or using it as part of your composite primary key because you can get a primary key collision when two concurrent users access the table within milliseconds.
  • For better performance minimize use of composite keys or use fewer columns in your composite keys.
  • Where possible avoid using columns with business meaning as your primary key. For example Avoid using taxfilenumber, zipcode etc as your primary key because more than one town may have the same zipcode, taxfilenumber is private and should be encrypted and stored, some people may not have a taxfile number, you may want to reuse the same taxfilenumber after an individual’s death, an individual may have more than one taxfilenumber etc.
  • Remember to choose carefully, as it is difficult to change the primary key in a production table.
  • 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:
    ID table
    name value minValue maxValue
    AddressID 245 0 -1
    AccountID 123 0 -1

    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 ?

  • Always define referential constraints to improve referential integrity of your data. For example A "BankDetail" table can have BSB number and accountnumber as part of unique key constraint (to prevent duplicate account details), while having a generated unique identifier as the primary key.
  • Perform all your referential integrity checks and data validations using constraints (foreign key and constraints) instead of triggers, as constraints are faster. Limit the use of triggers only for auditing, custom tasks and validations that can not be performed using constraints. Constraints save you time as well, as you don't have to write code for these validations, allowing the RDBMS to do all the work for you.
  • 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 >>>




    Home Clouds