Data Dictionary Queries for Oracle
For ORACLE
select * from sys.dba_objectswhere owner = 'scott'and object_type='TABLE'
SELECT owner, object_name, object_type FROM sys.dba_objectswhere object_type='SEQUENCE' and owner='scott';
Data Dictionary Queries for SQL Server
--Finding all details of Primary Key constraint
select * from sysobjectswhere xtype='PK'
--Finding all details of Foreign Key constraint
select * from sysobjectswhere xtype='F'
--Finding all User-Defined objects (tables, etc)
select * from sysobjectswhere xtype='U'
--Finding all System objects
select * from sysobjectswhere xtype='S'
--Finding all user names
select * from sysusers
--Finding Column Names of Particular Table
--Select Pubs Database
select c.name from sysobjects o, syscolumns cwhere o.id = c.id ando.name = 'publishers'
ADO.NET and Database Questions
- What is the wildcard character in SQL?
Let’s say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve ‘La%’. - What is the role of the DataReader class in ADO.NET connections?
It returns a read-only, forward-only rowset from the data source. A DataReader provides fast access when a forward-only sequential read is needed. - What are advantages and disadvantages of Microsoft-provided data provider classes in ADO.NET?
SQLServer.NET data provider is high-speed and robust, but requires SQL Server license purchased from Microsoft. OLE-DB.NET is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix. OLE-DB.NET is a .NET layer on top of the OLE layer, so it’s not as fastest and efficient as SqlServer.NET. - What connections does Microsoft SQL Server support?
Windows Authentication (via Active Directory) and SQL Server authentication (via Microsoft SQL Server username and password). - Between Windows Authentication and SQL Server Authentication, which one is trusted and which one is untrusted?
Windows Authentication is trusted because the username and password are checked with the Active Directory, the SQL Server authentication is untrusted, since SQL Server is the only verifier participating in the transaction. - What does the Dispose method do with the connection object?
Deletes it from the memory.
To Do: answer better. The current answer is not entirely correct. - What is a pre-requisite for connection pooling?
Multiple processes must agree that they will share the same connection, where every parameter is the same, including the security settings. The connection string must be identical. - What does the Initial Catalog parameter define in the connection string?
The database name to connect to. - Explain ACID rule of thumb for transactions.
A transaction must be:
1. Atomic - it is one unit of work and does not dependent on previous and following transactions.
2. Consistent - data is either committed or roll back, no “in-between” case where something has been updated and something hasn’t.
3. Isolated - no transaction sees the intermediate results of the current transaction).
4. Durable - the values persist if the data had been committed even if the system crashes right after.
General Database Interview Questions
In this segment, the interviewer may inquire about your general understanding about database management. It may include questions regarding definition of DDL and DML; sorting a table; different types of table join; definition of cursor; usages of cursor; the way of finding out the tables created by the user; implementation of locking; definition of lookup table in a database; about Datafile size; relation between free list and buffer busy wait; how to addressing modes; major differences between replication and logshipping; difference between database refresh and database cloning; etc.
Specific Questions
In this segment, the interviewer may ask you questions on the basis of your specialization, expertise and experience. The questions may vary depending on the database type. The interview questions may include questions on Btrieve, Database Admin, Informix, MYSQL, Progress, SQL, Stored Procedures, Teradata, Clipper, Firebird, Microsoft Access, PostgreSQL, SAP DB, SQL Server, Sybase, and Unify. Generally, in this segment, the applicants face questions from one or two categories depending on the job profile he has applied for.
Other Questions
In many cases, this type of job is mostly done in group setting. So during the interview process, the interviewer may ask you few relevant but other types of questions that may not be directly associated with your talent, skills or expertise. This type of questions may generally involve no correct or incorrect answers, but will reflect your idea about a specific ground. For example, the interviewer may ask you to assume a hypothetical situation when you find that you are working in such a group setting in which the relation between co-workers is not up to the mark, then what your state of action would be or he may ask you questions about your multi-tasking abilities, etc.
ASP.NET Interview Questions on Database Administrator
Why is "Connecting to SQL Server using Integrated Security" considered a best practice?
Connecting to SQL Server using integrated security instead of using an explicit user name and password, helps avoid the possibility of the connection string being compromised and your user ID and password being exposed.
What are the best practices to follow to secure connection strings in an ASP.NET web application?
1. Always store connection strings in the site's Web.config file. Web.config is very secure. Users will not be able to access web.config from the browser.
2. Do not store connection strings as plain text. To help keep the connection to your database server secure, it is recommended that you encrypt connection string information in the configuration file.
3. Never store connection strings in an aspx page.
4. Never set connection strings as declarative properties of the SqlDataSource control or other data source controls.
What is Script injection?
A script injection attack attempts to send executable script to your application with the intent of having other users run it. A typical script injection attack sends script to a page that stores the script in a database, so that another user who views the data inadvertently runs the code.
What is the advantage of storing an XML file in the applications App_Data folder? The contents of the App_Data folder will not be returned in response to direct HTTP requests.
What is SQL injection?
A SQL injection attack attempts to compromise your database by creating SQL commands that are executed instead of, or in addition to, the commands that you have built into your application.
What are the best practices to keep in mind when accepting user input on a web application?
1. Always use validation controls whenever possible to limit user input to acceptable values.
2. Always check the IsValid property of the aspx page. Run the server side code only if the IsValid property value is true. A value of false means that one or more validation controls have failed a validation check.
3. Always perform server side validation irrespective of client side validation being performed or not. This will protect your web application even if the client has by passed the client side validation by disabling javascript in the web browser.
4. Also make sure to re validate user input in the business logic layer of your application.
What are the steps to follow to avoid SQL Injection attacks?
Always use parameterized queries or stored procedures instead of creating SQL commands by concatenating strings together.
What are the steps to follow to avoid Script Injection attacks?
1. Encode user input with the HtmlEncode method. This method turns HTML into its text representation.
2. If you are using the GridView control with bound fields, set the BoundField object's HtmlEncode property to true. This causes the GridView control to encode user input when the row is in edit mode.
Can you encrypt view state data of an aspx page?
Yes, you encrypt view state data of an aspx page by setting the page's ViewStateEncryptionMode property to true.
