Interview Questions
Question: What is Database?
Answer: Database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.
(OR)
It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.Question: What is a Database system?
Answer:
The database and DBMS software together is called as Database system.
Question: Advantages of DBMS?
Answer:
Redundancy is controlled.
Unauthorized access is restricted.
Providing multiple user interfaces.
Enforcing integrity constraints.
Providing backup and recovery.
Question: How Many Databases Available Default in Sqlserver?
Answer: Default “6” Databases Available.
- Master
- Model
- TempDb
- MSDB
- Pubs
- North Wind.
Question: How many Tables available Default in Creation Of New Database?
Answer:
Default “19” Tables Available. That All Tables Belongs to System (Ex.Sysusers, sysobjects…)
Question: Defaults TempDb Size?
Answer:
2MB Upto 25Mb we will change.
Question: Where the Login Information’s Stored?
Answer:
Login Information’s Stored in Master Database in “SysxLogins”
Question: DTS Packages Information Stored In which Location?
Answer:
That store In “MSDB” – In “Sysdtspackages”
Question: What is difference Between UserId and LoginId?
Answer:
User ID Property:
The User ID property specifies the user ID to use when connects to the data source.This property specifies a valid user name that will be recognized by the user's computer, such as a valid domain logon or local logon.
Log Id:
A login ID is the account identifier that controls access to any SQL Server 2000 system
Question: What is the row size in SQL Server 2000?
Answer:
8060 bytes
Question: Give the connection strings from front-end for both type logins (windows, sqlserver)
Answer:
This are specifically for sqlserver not for any other RDBMS
Data Source=MySQLServer;Initial Catalog=NORTHWIND;Integrated Security=SSPI (windows)
Data Source=MySQLServer;Initial Catalog=NORTHWIND;Uid=” ”;Pwd=” ”(sqlserver)
Question: Type of backups?
Answer:
- Full database backup
- Differential Back up
- Transaction Log Backup
- File Group Backup
Question: What are the different authentication modes in Sqlserver? if a user is logged under windows authentication mode, how to find his userid?
Answer:
Three Different authentication modes in Sql Server.
- Windows Authentication Mode
- Sql Server Authentication Mode
- Mixed Authentication Mode
Question: What is Normalization?
Answer:
The process of table design is called normalization.
Question: What is denormalization?
Answer:
As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
Question: What is the default port for HTTP and HTTPS?
Answer:
80, 443
Question: Disadvantages in File Processing System?
Answer:
- Data redundancy & inconsistency.
- Difficult in accessing datAnswer:
- Data isolation.
- Data integrity.
- Concurrent access is not possible.
- Security Problems.
Question: How do we open Sql server in single user mode
Answer:
We can accomplish this in any of the three ways given below:-
a) From Command Prompt :-
sqlservr -m
b) From Startup Options :-
Go to SQL Server Properties by right-clicking on the Server name in the Enterprise manager. Under the 'General' tab, click on 'Startup Parameters'. Enter a value of -m in the Parameter.
c) From Registry :-
Go to HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters.
Add new string value.
Specify the 'Name' as SQLArg(n) & 'Data' as -m.
Question: What does SQL stand for?
Answer:
Structured Query Language
Question: What is a query?
Answer:
A query with respect to DBMS relates to user commands that are used to interact with a database. The query language can be classified into data definition language and data manipulation language.
Question: What is an Attribute?
Answer: It is a particular property, which describes the entity
Question: How do you communicate with an RDBMS?
Answer: You communicate with an RDBMS using Structured Query Language (SQL)
Question: What is Table?
Answer: Table is Logical Grouping of Related Information; Tables are made up of Rows & Columns.
Question: What is Relationship?
Answer: It is an association among two or more entities.
Question: What is a Primary Key?
Answer: The column (columns) that has completely unique data throughout the table is known as the primary key field.
Question: What is Foreign Key?
Answer: A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table.
You can create a foreign key by defining a FOREIGN KEY constraint when you create or alter a table.
Question: What is the main role of a primary key in a table?
Answer: The main role of a primary key in a data table is to maintain the internal integrity of a data table.
Question: What is the difference between Primary Key and Unique Key?
Answer: 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, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
Question: Define Candidate key, Alternate key, Composite key?
Answer: Candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.
Question: Can a table have more than one foreign key defined?
Answer: A table can have any number of foreign keys defined. It can have only one primary key defined.
Question: What is Unique Key?
Answer: Unique key distinctly identifies each row in a table, by default creates a non-clustered index on the Key specified and allows only one NULL value for the key specified.
Question: What is the Referential Integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.
Question: What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting datAnswer: IDENTITY columns and timestamp columns can't have defaults bound to them.
Question: Describe how NULL’s work in SQL?
Answer: The NULL is how SQL handles missing values.
Arithmetic operation with NULL in SQL will return a NULL.
Question: What is the Advantage of index?
Answer: Boost the performance of the query
Question: What is the Disadvantage of index?
Answer: Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the
number of indexes should not be very much.
Question: What is the Diff. B/W Clustered & Non Clustered?
Answer: No table scanning for non clustered index.
Question: Given a scenario that I have a 10 Clustered Index in a Table to all their 10 Columns.
what are the advantages and disadvantages?
A: Only 1 clustered index is possible.
Question: List all the possible values that can be stored in a BOOLEAN data field.
Answer: There are only two values that can be stored in a BOOLEAN data field: -1(true) and 0(false).
Question: How do you select all records from the table?
Answer: Select * from table_name
Question: How do you add record to a table?
Answer: INSERT into table_name VALUES (1,’reddaiah’,’hyd’)
Question: How do you add a column to a table?
Answer: ALTER TABLE Department ADD (AGE, NUMBER);
Question: How do you change value of the field?
Answer: UPDATE EMP set number = 200 where item_number = 1001
Question: What does COMMIT do?
Answer: Saving all changes made by DML statements
Question: Write a SQL SELECT query that only return each city only once from Students table?
Do you need to order this list with an ORDER BY clause?
Answer: SELECT DISTINCT City FROM Students
The Distinct keyword automatically sorts all data in ascending order. However, if you want the data sorted in descending order, you have to use an ORDER BY clause
Question: Write a SQL SELECT sample of the concatenation operator.
Answer: SELECT LastName ||',' || FirstName, City FROM Students
Question: How to rename column in the SQL SELECT query
Answer: SELECT LastName ||',' || FirstName AS "Student Name", City AS "Home City" "FROM StudentsORDER BY "Student Name"
Question: There are 50 columns in a table. Write a query to get first 25 columns
Answer: Need to mention each column names.
Question: Write SQL SELECT example how you limiting the rows returned with a WHERE clause.
Answer: SELECT InstructorID, Salary FROM Instructors WHERE Salary > 5400 AND Salary < 6600
Question: Which of the following functions can be used only with numeric values?
1. AVG 2. MIN 3. LENGTH 4.SUM 5. ROUND
Answer: 1 and 4 correct. The MIN function works with any character, numeric, or date datatype. The LENGTH function is a character function that returns the number of letters in a character value. The ROUND function works with both numeric and date values.
Question:Is the WHERE clause must appear always before the GROUP BY clause in SQL SELECT
Answer: Yes.
The proper order for SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.
Question: How you will create a column alias?
Answer: The AS keyword is optional when specifying a column alias. You must enclose the column alias in double quotes when the alias contains a space or lowercase letters. If you specify an alias in lowercase letters without double quotes, the alias will appear in uppercase.
Question: Explain the use of the WHERE clause?
Answer: WHERE is used with a relational statement to isolate the object element or row.
Question: What is the difference between group by and order by?
Answer: Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.
Question: Which of the following WHERE clauses will return only rows that have a NULL in the PerDiemExpenses column?
Answer: WHERE PerDiemExpenses <>
B. WHERE PerDiemExpenses IS NULL
C. WHERE PerDiemExpenses = NULL
D. WHERE PerDiemExpenses NOT IN (*)
Answer: Answer: B is correct, When searching for a NULL value in a column, you must use the keyword IS. No quotes are required around the keyword NULL.
Question: You issue the following query: SELECT FirstName FROM StaffListWHERE FirstName LIKE'_A%'Which names would be returned by this query? Choose all that apply.
1. Allen 2. CLARK 3. JACKSON 4. David
Answer: 1, 3 is correct Two wildcards are used with the LIKE operator.
Question: What is a synonym? How is it used?
Answer: A synonym is used to reference a table or view by another name. The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated. The synonym is linked to the AUTHID that created it.
Question: What is an alias and how does it differ from a synonym?
Answer: An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view. The alias is not dropped when the table is dropped.
Question: When can an insert of a new primary key value threaten referential integrity?
Answer: Never. New primary key values are not a problem. However, the values of foreign key inserts must have corresponding primary key values in their related tables. And updates of primary key values may require changes in foreign key values to maintain referential integrity.
Question: What is the difference between static and dynamic Sql?
Answer: Static sql is hard-coded in a program when the programmer knows the statements to be executed. For dynamic Sql the program must dynamically allocate memory to receive the query results.
Question: How many places to the right of the decimal can be stored in a CURRENCY data field?
Answer: The CURRENCY data type can store up to four places to the right of the decimal. Any data beyond the fourth place will be truncated by Visual Basic without reporting an error.
Question: What is a join?
Answer: Join is a process of retrieve pieces of data from different sets (tables)
Question: What is self-join?
Answer: A table can be joined to itself in a self-join.
Question: Explain an outer join.
Answer: An outer join includes rows from tables when there are no matching values in the tables.
Question: What are the differences between UNION and JOINS?
Answer: Join selects columns from 2 or more tables. A union selects rows.
Question: Does the View occupy memory space?
Answer: No
Question: Can u drop a table if it has a view?
Answer: Views or tables participating in a view created with the SCHEMABINDING clause cannot be d
dropped. If the view is not created using SCHEMABINDING, then we can drop the table
Question: How will you raise an error in Sql Server?
Answer: RAISERROR - Returns a user-defined error message and sets al system flag to record that an error has occurred. Using RAISERROR, the client can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. After the message is defined it is sent back to the client as a server error message.
Question: How to Handle Errors In Procedure Or In SQL-server?
Answer: The tables in the topics include the error message number, the severity level, and the description, which is the text of the error message from the master.sysmessages table.
@@ERROR system function returns 0 if the last Transact-SQL statement executed successfully; if the statement generated an error, @@ERROR returns the error number. The value of @@ERROR changes on the completion of each Transact-SQL statement
Question: How will you declare Local variables?
Answer: Local variables are user-defined identified by @. The value can change during batch or stored procedure in which it is used.
Question: How will you declare Global variables?
Answer: Global variables are system-supplied and predefined identified by @@.
Question: What is the use of these 3 Objects?
@@Rowcount is used to display the number of rows affected by last SQL statement.
@@Error displays the error number for the last SQL statement executed. The value is zero, if there is no error.
@@Identity returns the last inserted identity value.
Question: What is a Stored Procedure?
Answer: A procedure is a group of PL/SQL statements that can be called by a name. Procedures do not return values they perform tasks.
Question: What can cause a Stored procedure execution plan to become invalidated and/or fall out of cache?
1. Server Restart
2. Plan is aged out due to low use
3. DBCC FREEPROCCACHE (sometime desired to force it)
Question: What is Trigger?
A1. Trigger will execute a block of procedural code against the database when a table event occurs.
A2. A trigger defines a set of actions that are performed in response to an insert, update, or delete operation on a specified table. When such an SQL operation is executed, in this case the trigger has been activated.
Question: Can you call Triggers in Stored Procedure?
Answer: Yes Possible.
The Trigger object exposes the attributes of a single Microsoft® SQL Server™ trigger.
SQL Server supports using triggers as a kind of stored procedure. Triggers are executed when a specified data modification, such as an attempt to delete a row, is attempted on the table on which the trigger is defined.
Ex: USE pubs
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS
RAISERROR (50009, 16, 10) GO
Question: Difference between trigger and stored procedure?
Answer: Trigger will get execute automatically when an UPDATE, INSERT, or DELETE statement is
issued against a table or view.
We have to call stored procedure manually, or it can execute automatic when the SQL Server
starts (You can use the sp_procoption system stored procedure to mark the stored procedure
to automatic execution when the SQL Server will start.
Question: Drawback of trigger? Its alternative solution?
Answer: Triggers are generally used to implement business rules, auditing. Triggers can also be used to
extend the referential integrity checks, but wherever possible, use constraints for this purpose,
instead of triggers, as constraints are much faster.
Question: What is a DeadLock?
Answer: Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system. A thread in a multi-threaded system may acquire one or more resources (for example, locks). If the resource being acquired is currently owned by another thread, the first thread may have to wait for the owning thread to release the target resource. The waiting thread is said to have a dependency on the owning thread for that particular resource. A deadlock occurs when there is a cyclic dependency between two or more threads for some set of resources.
Question: What Is Rule?
Answer: The Rule object exposes the attributes of a single Microsoft® SQL Server™ data-integrity rule.
Bind an existing SQL Server rule to a column or user-defined data type.
Syntax: CREATE RULE range_rule AS @range >= $1000 AND @range < $20000
Question: What is time stamp?
Answer: Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
Question: What is Cast Operator?
Answer: Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.
Question: How to list all the tables in a particular database?
Answer: sp_help
Practical Questions:
1. Display the Those Details of those do not have any person working Under them?
Answer: Select * from emp e, emp m where m.empno <> e.empno.
2. Display the Details of those employes who are in sales department and Grade is 3?
Answer: Select e.ename,s.grade,e.sal from emp e, salgrade s where e.sal between s.lowsal and
s.highsal and e.job=’salesman’ and s.grade=3;
3. Dipsplay the employees whose name contains not less than 4 characters?
Answer: Select ename from emp where ltrim(rtrim(ename)) not like ‘----‘
4. Display those Departmentname start with ‘s’ while location ends with ‘k’?
Answer: Select * from dept where rtrim(ltrim(dname)) like ‘a%’ and Rtrim(Ltrim(loc))Like ‘%k’
5. Display those employees whose manager name in james?
Answer: Select * from emp where mgr = (select empno from emp where ename=’James’)
6. Dipslay those employees whose salary is more than 3000 after giving increments?
Answer: Select ename,sal,sal*0.2+sal from emp where sal*0.2+sal > 3000
7. Display all employes while their departments?
Answer: Select ename,dname from emp e, dept d where e.deptno = d.deptno
8. Display ename who are working sales department?
Answer: Select ename from emp where deptno = (Select deptno from dept where dname=’Sales’)
9. Display those Employees whose salary is greater than his manager salary?
Answer: Select e.ename,e.sal,m.ename,m.sal from emp e, emp m where e.mgr = m.emp and e.sal >
m.sal;
10. Display those employees who are not working under Manager?
Answer: Select * From emp where mgr is null;
11. Find Second Max Salary?
Answer: Select max (sal) from EMP group by sal having sal < (Select max (sal) from emp);
12. Select max(sal),job from emp where job in ( select job from emp) Group by job Order by max(sal) Desc;
13. Date Between Condition
Answer: Select ename,empno,job from emp where hiredate between ‘01-Jan-02’ and ‘30-jun-03’
OR
HireDate Between ‘01-Jan-03’ and ‘30-Jun-03’;
14. Write a query to fetch all the managers names with the employee names in the order of manager name.
Table: EMP & Columns: EmpId (Primary Key), EmpName (varchar), MgrId
A) create table emp( EmpId char(2) Primary Key, EmpName varchar(20), MgrId char(2) references emp(empid))
Answer: select empl.EmpName as employee,mgr.EmpName as manager
from emp empl,emp mgr where empl.mgrid=mgr.empid
15. Write a query to update all the names that has spaces in front of their names (above table).
Answer: Update emp set empname='updated' where empname in (select empname from emp where empname like '% %')
16. Write a query to delete all the names that starts with J.
Answer:Delete from emp where empname like 'j%'
17. Table A contains column1 which is primary key and has 2 values (1, 2) and Table B contains column1 which is primary key and has 2 values (2, 3). Write a query which returns the values that are not common for the tables and the query should return one column with 2 records
Answer: select Answer:col1 from a,b where Answer:col1<>(select b.col1 form a,b where Answer:col1=b.col1)
Union
select b.col1 from a,b where b.col1<>(select Answer:col1 from a,b where Answer:col1=b.col1)
18. There are 3 tables Titles, Authors and Title-Authors. Write the query to get the author name and the number of books written by that author, the result should start from the author who has written the maximum number of books and end with the author who has written the minimum number of books.
Answer: select authors.authorname,count(authtitles.authorid)
from authors,authtitles where author.authorid=authtitles.authorid
group by authors.authorname
order by count(authtitles.authorid) desc
19. For duplicate rows in a table( table should have an identity column(id) )
Answer: Delete from emp
where emp.id in
-- list 1 - all rows that have duplicates
(select f.id from emp as f
where exists (select field1, field2, count(id)
from emp where emp.field1 = f.field1
and emp.field2 = f.field2 group by emp.field1, emp.field2
having count(emp.id) > 1)) and emp.id not in
--list 2 - one row from each set of duplicate
(select min(id) from emp as f where exists (select field1, field2, count(id)
from emp where emp.field1 = f.field1
and emp.field2 = f.field2 group by emp.field1, emp.field2
having count(emp.id) > 1)
group by field1, field2);
20. There is a table named Temperature which contains three fields. The fields are DAYID, Day temperature and day. Get the result with day temp delta (diff of prev day and to day) with out using cursor?
Answer: Select Answer:day, Answer:degree-b.degree from temperature a, temperature b where
Answer:id=b.id+1
21. There are two employee tables named emp1 and emp2. Both contains same structure (salary details) . But Emp2 salary details are incorrect and emp1 salary details are correct. So, write a query which corrects salary details of the table emp2.
Answer: update a set Answer:sal=b.sal from emp1 a ,emp2 b where Answer:empid=b.empid
22. To find the employees who are having more then one phone number
Answer: select empid,count(phone_no) from emp group by empid having count(phone_no)>1
23. A table has a primary field which is an auto-identity field. It has 200 rows of datAnswer: Write a SQL query which returns all the rows except the first row?
Answer: select * from #test where roll <>(select min(roll) from #test)
23. I am setting up a database with clients. I know that you use the "insert" statement to insert information in the database, but how do I make sure that I do not enter the same client information again?
Answer: You can make sure that you do not insert duplicate information by using the EXISTS condition.
For example, if you had a table named clients with a primary key of client_id, you could use the following statement:
INSERT INTO clients (client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising' FROM suppliers WHERE not exists (select * from clients where clients.client_id = suppliers.supplier_id);
24. This statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following statement:
Answer: INSERT INTO clients (client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising' FROM dual WHERE not exists (select * from clients
where clients.client_id = 10345);
The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.
25. You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the FROM clause when you are performing a delete, you can use the EXISTS clause.
Answer: DELETE FROM supplier
WHERE EXISTS ( select customer.name from customer where customer.customer_id = supplier.supplier_id and customer.customer_name = 'IBM' );
This would delete all records in the supplier table where there is a record in the customer table whose name is IBM, and the customer_id is the same as the supplier_id.
No comments:
Post a Comment