1. What is store procedure, what is the optimized way of calling a store procedure from .sql file or from the database itself?
Stored procedures allow a lot more flexibility offering capabilities such as conditional logic. Second, because stored procedures are stored within the DBMS, bandwidth and execution time are reduced. This is because a single stored procedure can execute a complex set of SQL statements.
Syntax:
create procedure SHOW_SUPPLIERS
as
select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
from SUPPLIERS, COFFEES
where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
order by SUP_NAME
2. 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.
The Normal Forms
The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you’ll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won’t be discussed in this article.
Before we begin our discussion of the normal forms, it’s important to point out that they are guidelines and guidelines only. Occasionally, it becomes necessary to stray from them to meet practical business requirements. However, when variations take place, it’s extremely important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies. That said, let’s explore the normal forms.
First Normal Form (1NF)
First normal form (1NF) sets the very basic rules for an organized database:
* Eliminate duplicative 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).
Second Normal Form (2NF)
Second normal form (2NF) further addresses the concept of removing duplicative 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.
* Create relationships between these new tables and their predecessors through the use of foreign keys.
Third Normal Form (3NF)
Third normal form (3NF) goes one large step further:
* Meet all the requirements of the second normal form.
* Remove columns that are not dependent upon the primary key.
Fourth Normal Form (4NF)
Finally, fourth normal form (4NF) has one additional requirement:
* Meet all the requirements of the third normal form.
* A relation is in 4NF if it has no multi-valued dependencies.
Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.
3. What is composite primary key?
Primary key is simply a primary key made up of more than one column. This is also known as a concatenated key.
4. Does a table contain two primary keys?
No
5. How to find the Minimum Salary?
Select Min (Salary) From Employee
6. How to find the Second Minimum Salary?
Select Salary from Employee where Salary > (select Min (Salary) from Employee);
7. How to find the Maximum Salary?
Select Max (Salary) From Employee
8. How to find the Second Maximum Salary?
Select Salary from Employee where Salary < (select Max (Salary) from Employee);
Or
Replace ‘n’ with the number of Max level you want ex: 2 (it displays the second highest).
Select a.Salary from emp a where &n= (Select count(Distinct b.salary)) from emp b where a.sal<=b.sal.
9. What does u test for in database testing other than data integrity (insert, delete, update?)Data Integrity Testing: Once a value undergoes any of the changes like (Update/Delete/Insert) the database should be verified for the changes performed on related entities i.e., Foreign Key/Primary Key and all dependent entities.
Store Procedure Testing: Every SP is to be tested separately for its functionality Based on Separate functions it performs. SP need to be broken up into Actions Items based on Functions and then each action item needs to be tested separately as the results of Complete SP. The following are to be considered.
a. The number of arguments being passed.
b. The data type of each of the arguments being passed.
c. The Order of the arguments being passed.
d. The return value.
e. The Data type of the return value.
Based on these you can write both positive and negative test cases.
Type Testing: Verifying the Data types. Often the data types chosen by developers are not the same as suggested by DBA. Ex: Phone (Num/Text), Description (Large Text).
Data Size Testing: Data size testing is often done only at the front end during the Unit Testing, but it is essential to perform it at back end separately.
Event Driven Item Testing: Event Driven Actions (Triggers or Scheduled Actions) needs to be tested based on two parameters.
a. Events that trigger these actions
b. Actions performed by the above stated events.
Input Item Verification: This is the process of verification of the input items, Often it is seen that the input items (Text Box/RTB/Combo Box/Active-X controls) are tested for validation only at front end (Screen testing) but these are again to be tested with junk character values to confirm that they do not push in such characters which the databases Often misrepresent or Replace with other characters.
10. What is a trigger? – What are different Types of Triggers?
Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.
triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur.
Syntax:
CREATE TRIGGER trig1AFTER INSERT ON T4
REFERENCING NEW AS newRow
FOR EACH ROW
WHEN (newRow.a <= 10)
BEGIN
INSERT INTO T5 VALUES(:newRow.b, :newRow.a);
END trig1;
Using the options listed previously, you can create four types of row and statement triggers:
* BEFORE statement trigger
Before executing the triggering statement, the trigger action is run.
* BEFORE row trigger
Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is run, if the trigger restriction was not violated.
* AFTER row trigger
After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is run for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.
* AFTER statement trigger
After executing the triggering statement and applying any deferred integrity constraints,
11. What is a view? – If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specific users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.
CREATE VIEW vpres AS select * from table
SELECT last_name, first_name, city, state FROM president;
12. What is an Index? – When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.
Syntax: If we want to create an index on both City and Country, we would type in,
CREATE INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)
13. What are the types of indexes available with SQL Server? – There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.
14. What is the basic difference between clustered and a non-clustered index? – The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
15. What are cursors? – Well cursors help us to do an operation on a set of data that we retrieve by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retrieval one by one and remove rows which have duplicate values.
16. When do we use the UPDATE_STATISTICS command? – This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
17. Which TCP/IP port does SQL Server run on? – SQL Server runs on port 1433 but we can also change it for better security.
18. From where can you change the default port? - From the Network Utility TCP/IP properties –> Port number. Both on client and the server.
19. Can you tell me the difference between DELETE, TRUNCATE and Drop commands? Delete command removes the rows from a table based on the condition that we provide with a WHERE clause.
Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.DROP TABLE removes a table. All table data and the table definition are removed.
20. Can we use Truncate command on a table which is referenced by FOREIGN KEY? No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.
21. What is the use of DBCC commands? – DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
22. Can you give me some DBCC command options?(Database consistency check) – DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked. and DBCC CHECKALLOC – To check that all pages in a db are correctly allocated. DBCC SQLPERF – It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP – Checks all tables file group for any damage.
23. What command do we use to rename a db? – sp_renamedb ‘oldname’ , ‘newname’
24. Well sometimes sp_reanmedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases? – In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode.
25. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? - HAVING CLAUSE is used for evaluating a condition with an aggregate function i.e, Group By.
WHERE CLAUSE is used for general conditions.
26. What do you mean by COLLATION? – Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary – case insensitive and Binary.
27. What is a Join in SQL Server? – Join actually puts data from two or more tables into a single result set.
28. Can you explain the types of Joins that we can have with Sql Server? -JOIN is used to combine records from two tables or more in relational database. There are 3 types of Joins in SQL: CROSS JOIN, INNER JOIN, and OUTER JOIN. OUTER JOIN itself can be further divided into left OUTER JOIN, right OUTER JOIN and full OUTER JOIN.
Cross Join The CROSS JOIN list the tables to be joined without putting any filtering criteria.
Example of CROSS JOIN is as follows:
Select * from Employee CROSS JOIN Department.
Inner Join It takes all records from table1 and find matching records from table 2. If no matching is found, record in table1 will not be returned. One row of record will be returned for each matching found in table2. If 1 record in table1 match with 3 rows in table2, then 3 records will be returned. The following example will return records in resultset2.
Select * from Employee
Inner Join Department
ON Employee.DepartmentID= Department.DepartmentID
Outer Join
Left Outer Join it will return all records from table1 no matter it match any records in table2 or not. If no matching is found in table2, then each column in table2 will be returned as NULL values.
FOR Example: SELECT * FROM Employee
LEFT OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID
Right Outer Join all the records in table2 or Department table will be returned. For example: SELECT * FROM Employee
RIGHT OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID
Full Outer Join FULL OUTER JOIN is the combination of LEFT OUTER JOIN AND RIGHT OUTER JOIN. FULL JOIN shows results from both tables and missing match will be filled with NULL.SELECT * FROM Employee
FULL OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID
29. Display the last 7 day records?
SELECT a.count,b.count as Rcount FROM $Table_links a, $Table_referer b WHERE
a.TO_DAYS(NOW()) – TO_DAYS(date) <= 7 && b.TO_DAYS(NOW()) – TO_DAYS(date) <= 7.
30. Display the records group by?The GROUP BY keyword is used when we are selecting multiple columns from a table (or tables) and at least one arithmetic operator appears in the SELECT statement. When that happens, we need to GROUP BY all the other selected columns, i.e., all columns except the one(s) operated on by the arithmetic operator.
Ex: SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name
Syntax for using Having clause.
Ex: SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
31. Display the records sort by?
Ex: SELECT Customer, Employee, Freight, ShipCountry ORDER BY ShipCountry
Ex: SELECT “column_name”
FROM “table_name”
[WHERE "condition"]
ORDER BY “column_name” [ASC, DESC]
32. What is a Foreign Key?A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.
SQL Server:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);
