Data Base Definitions

Posted by on Jun 12, 2019 in Tutorials | 0 comments

Data Base Definitions

DDL Data Definition Language (DDL): statements are used to define the database structure or schema. Some examples:

CREATE: to create objects in the database

ALTER: alters the structure of the database

DROP: delete objects from the database

TRUNCATE: remove all records from a table, including all spaces allocated for the records are removed

COMMENT: add comments to the data dictionary

RENAME: rename an object

DML Data Manipulation Language (DML): statements are used for managing data within schema objects. Some examples:

SELECT: retrieve data from a database

INSERT: insert data into a table

UPDATE: updates existing data within a table

DELETE: deletes all records from a table, the space for the records remain

MERGE: UPSERT operation (insert or update)

CALL: call a PL/SQL or Java subprogram

EXPLAIN PLAN: explain access path to data

LOCK TABLE: control concurrency

DCL Data Control Language (DCL): statements. Some examples:

GRANT: gives user’s access privileges to database

REVOKE: withdraw access privileges given with the GRANT command

TCL Transaction Control (TCL): statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT: save work done.

SAVEPOINT: identify a point in a transaction to which you can later roll back.

ROLLBACK: restore database to original since the last COMMIT.

SET TRANSACTION: Change transaction options like isolation level and what rollback segment to use.

SQL Join: clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each.

CROSS JOIN: returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table.

Inner Join: is a commonly used join operation used in applications. It can only be safely used in a database that enforces referential integrity or where the join fields are guaranteed not to be NULL.

Equi-Join: is a specific type of comparator-based join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join.

Natural Join: is a type of equi-join where the join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally named columns.

Outer Join: does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table’s rows are retained (left, right, or both).

RIGHT and LEFT Join: let’s us see things in one table but not matching in the other.

How to find records duplicated?

SELECT email, COUNT(email) FROM user GROUP BY email HAVING COUNT(email) >= 1

Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433, it can be changed from Network Utility TCP/IP properties.

What is index? And the difference?

Index allow the database application to find the date fast, without reading the whole table.

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index, the leaf nodes of a clustered index contain the data pages.

A non clustered index is a special type of index in wich the logical order of the index does not match the physical stored order of the rows on disk, the leaf node of a non clustered index does not consist of the data pages, instead, the leaf nodes contain index rows.

What are the different index configurations a table can have?

A table can have one if the following index configuration:

No index.

A clustered index.

A clustered index and many not clustered index.

A not clustered index.

Many not clustered index.

What is the difference between DELETE and TRUNCATE 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 no data in the table after we run the truncate command.

Truncate is faster and uses fewer system and transaction log resources then delete.

Truncate removes the data by deallocating the data pages used to store the table´s data, and only the page deallocations are recorded in the transactions log.

Truncate cannot be rolled back.

Truncate is DDL command.

Truncate resets identity of the table.

Delete can be used with or without a where clause.

Delete activates the triggers.

Delete can be rolled back.

Delete is a DML command.

What is Primary Key?

A primary key constraints is a unique identifier for a row within a database table, every table should be have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table, the primary key constraints are used to enforce entity integrity.

What is Unique Key constraint?

A unique constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered; the unique key constraints are used to enforce entity integrity as the primary key constraints.

What is Foreign Key?

Prevents any actions that would destroy links between tables with the corresponding data values, a foreign key in one table points to a primary key in another table, and are used to enforce referential integrity.

What is Check Constraint?

Is used to limit the values that can be placed in a column, and are used to enforce domain integrity.

What is Not Null Constraint?

Enforces that the column will not accept null values, and are used to enforce domain integrity as the check constraints.

What’s the difference between a primary key and a unique key? Both primary key and unique key enforces 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 no clustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What is the difference between a Local and a Global temporary table? They are used most often to provide workspace for the intermediate results when processing data within a batch or procedure. They are also used to pass a table from a table-valued function, to pass table-based data between stored procedures or, more recently in the form of Table-valued parameters, to send whole read-only tables from applications to SQL Server routines, or pass read-only temporary tables as parameters. Once finished with their use, they are discarded automatically.

Temporary tables come in different flavours including, amongst others, local temporary tables (starting with #), global temporary tables (starting with ##), persistent temporary tables (prefixed by TempDB..), and table variables.(starting with (@)).

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What are the advantages of using Stored Procedures?

Stored procedure can be reduced network traffic and latency, boosting application performance.

Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.

Stored procedures help promote code reuse.

Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.

Stored procedures provide better security to your data.

What is a Scheduled Jobs or What is a Scheduled Tasks?

Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution. User can create a parallel and sequential job.

What are Cursors?

Cursors is a temporary work area created in the system when sql stamen is executed, a cursor contains information on a select stamen and the rows if date accessed by it.

Implicit cursors: are created by default when DML statements like insert are executed with select statement returns just one row.

Explicit cursors: must be created when you are executing a select statement that returns more than one row.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is View? It is used for data security reason to reduce the redundant data.

Leave a Reply