Database Developer

The SELECT INTO statement copies data from one table into a new table. The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.

SELECT * INTO newtable FROM oldtable WHERE condition;

A CHECK constraint is used to limit the value that is accepted by one or more columns.

E.g. ‘Age’ field should contain only the value greater than 18.

CREATE TABLE EMP_DETAILS(EmpID int NOT NULL, NAME VARCHAR (30) NOT NULL, Age INT CHECK (AGE > 18), PRIMARY KEY (EmpID));

The difference between the Delete, Truncate and Drop command is

☛ Delete command is a DML command, it is used to delete rows from a table. It can be rolled back.

☛ Truncate is a DDL command, it is used to delete all the rows from the table and free the space containing the table. It cant be rolled back.

☛ Drop is a DDL command, it removes the complete data along with the table structure(unlike truncate command that removes only the rows). All the tables’ rows, indexes, and privileges will also be removed.

SQL aggregate functions return a single value, calculated from values in a column. Some of the aggregate functions in SQL are as follows

☛ AVG() – This function returns the average value

☛ COUNT() – This function returns the number of rows

☛ MAX() – This function returns the largest value

☛ MIN() – This function returns the smallest value

☛ ROUND() – This function rounds a numeric field to the number of decimals specified

☛ SUM() – This function returns the sum

Data Integrity defines the accuracy as well as the consistency of the data stored in a database. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

In SQL Server, each column in a database table has a name and a data type. We need to decide what type of data to store inside each and every column of a table while creating a SQL table.

An index refers to a performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and hence it will be faster to retrieve data.

It is a tool used to duplicate enormous quantity of information from tables and views. It does not facsimile the structures same as foundation to target.

BULK INSERT command helps to bring in a data folder into a record, table or view in a user-specific arrangement.

A table is a database object used to store records in a field in the form of columns and rows that holds data.

☛ Writing a query with parameters.

☛ Using EXEC.

☛ Using sp_executesql.

A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY in a table is linked with the PRIMARY KEY of another table.

The largest number that can be represented in a single byte is 11111111 or 255. The number of possible values is 256 (i.e. 255 (the largest possible value) plus 1 (zero), or 28).

TCL commands are used to manage the changes made by DML statements.

☛ COMMIT: To write and store the changes to the database

☛ ROLLBACK: To restore the database since the last commit

Join is a query, which retrieves related columns or rows from multiple tables.

Normalization is the process of table design to minimize the data redundancy. There are different types of Noramalization forms in SQL.

☛ First Normal Form

☛ Second Normal Form

☛ Third Normal Form

☛ Boyce and Codd Normal Form

A unique constraint is used to ensure that there are no duplication values in the field/column.

Constraints are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement. The sample of constraints are:

☛ NOT NULL

☛ CHECK

☛ DEFAULT

☛ UNIQUE

☛ PRIMARY KEY

☛ FOREIGN KEY

A field in a Database table is a space allocated to store a particular record within a table.