Database Developer

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

NOT NULL constraint is used to ensure that the value in the filed cannot be a NULL

TRUE and FALSE

A record (also called a row of data) is an ordered set of related data in a table.

SQL commands are segregated into following types:

☛ DDL – Data Definition Language

☛ DML – Data Manipulation Language

☛ DQL – Data Query Language

☛ DCL – Data Control Language

☛ TCL – Transaction Control Language

I like to phrase this interview question this way because I’m not saying the DBA is right – I’m just asking the developer to explain the DBA’s point of view. I don’t have a problem with the developer rolling their eyes as they explain the answer, but I have a problem with the developer being surprised by the question.

The candidate gets bonus points if they seem even vaguely aware of the terms “set-based processing” and “row-based processing”, but that’s purely a bonus. (I wish I could say that these concepts are requirements, but in today’s economic market, companies don’t always want to pay top dollar to get the best candidates.)

A PRIMARY KEY constraint uniquely identifies each record in a database table. All columns participating in a primary key constraint must not contain NULL values.

A Trigger is a SQL procedure that initiates an action in response to an event (Insert, Delete or Update) occurs. When a new Employee is added to an Employee_Details table, new records will be created in the relevant tables such as Employee_Payroll, Employee_Time_Sheet etc.,

An inner join returns rows when there is at least some matching data between two (or more) tables that are being compared.

An outer join returns rows from both tables that include the records that are unmatched from one or both the tables.

If they stumble on the question, circle back to the Orders and OrderDetails tables we used as examples earlier. What’s an orphan? How do we make sure that we don’t end up with OrderDetails for records with no matching Order record? Where are all the places we could enforce referential integrity? (Think foreign keys, triggers, the application, or not at all.) Have you worked in places where there was no referential integrity, and what problems did you run into?

SQL Overview: SQL stands for Structured Query Language. It is an American National Standard Institute (ANSI) standard. It is a standard language for accessing and manipulating databases. Using SQL, some of the action we could do are to create databases, tables, stored procedures (SP’s), execute queries, retrieve, insert, update, delete data against a database.

SQL Operator is a reserved word used primarily in an SQL statement’s WHERE clause to perform operations, such as arithmetic operations and comparisons. These are used to specify conditions in an SQL statement.

There are three types of Operators.

☛ Arithmetic Operators

☛ Comparison Operators

☛ Logical Operators

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:

SELECT * FROM sys.dm_os_wait_stats;

Another example is examining current sessions, much like the sp_who2 command:

SELECT * FROM sys.dm_exec_sessions;

Some of the constraints in SQL are – Primary Key, Foreign Key, Unique Key, SQL Not Null, Default, Check and Index constraint.

The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicated values.

A Stored Procedure is a collection of SQL statements that have been created and stored in the database to perform a particular task. The stored procedure accepts input parameters and processes them and returns a single value such as a number or text value or a result set (set of rows).