DBM QUIZ

Structured Query Language (SQL)

It is an industry-standard programming language used to insert, retrieve, modify, and delete data in a relational database. It also contains statements for defining and administering the objects in a database.

What SQL enables a programmer/DBA to do

Execute queries against a database; retrieve data from a database; insert records in a database; update records in a database; delete records from a database; create new databases; create new tables in a database; create stored procedures in a database; create views in a database; set permissions on tables, procedures, and views.

A Brief History of SQL

1970 – E. Codd develops relational database concept; 1974-1979 – System R with Sequel (later SQL) created at IBM Research Lab; 1979 – Oracle markets first relational DB with SQL; 1986 – ANSI SQL standard released; 1989, 1992, 1999, 2003 – Major ANSI standard updates; Current – SQL is supported by most major database vendors.

Relational Database

A database divided into logical units called tables, where tables are related to one another within the database. Relational database allows data to be broken down into logical, smaller, and manageable units for easier maintenance and better performance.

Tables

Collections of rows and columns. Tables are related to one another through common keys or fields in a relational database system, that's why even though the desired data may exist in more than one table, you can easily join multiple tables together to get combined data set using a single query.

Field or column

A single characteristic, attribute or property that provides information about an object.

Record or row

A complete set of all the fields/attributes combined together for a particular object.

Data Definition Language (DDL)

Commands used to define a database, including those for creating, altering, and dropping tables and establishing constraints. Sample commands are CREATE, ALTER and DROP.

Data Manipulation Language (DML)

Commands used for updating, inserting, modifying, and querying the data in the database. Sample commands are INSERT, UPDATE, DELETE and SELECT.

Data Control Language (DCL)

Commands help a DBA control the database; they include commands to grant or revoke privileges to access the database or particular objects within the database and to store or remove transactions that would affect the database.

Creating a Database

Before doing anything with the data we must need to create a database first. The basic syntax is CREATE DATABASE databasename.

Renaming a Database

To rename a database in SQL server, use the command sp_renamedb.

Deleting a Database

To delete an existing database in SQL server, use the command DROP DATABASE. Deleting a database will result in loss of complete information stored in the database.

Tables (as database objects)

Database objects that store data in a collection of rows and columns. Each row represents a unique record, and each column represents a field within the record.

Naming Tables and Columns

Table names can be up to 128 characters, must begin with an alphabetic character, and can also contain underscores (_), "at" symbols (@), pound signs (#), and numerals. Each table can have up to 1,024 columns. Spaces and other symbols may be used if delimited by double quotation marks or brackets [ ], though this is not recommended.

Data Types

An attribute that specifies the type of data that the column can store. The data type of an attribute decides the operations that can be performed on the data of that attribute (e.g., arithmetic operations can be performed on numeric data but not on character data).

Null

A value that may be assigned to an attribute when no other value applies or when the applicable value is unknown. A null value, or NULL, is not the same as zero (0), blank, or a zero-length or empty character string. NULL means that no entry has been made.

Primary Key

A field or a combination of fields that identify a record uniquely. The Primary key is a column or set of columns that are unique. Every table should have a primary key.

Rules for Primary Key

Each table can have only one Primary Key; all the values are unique and can uniquely identify each row; the system will not allow inserting a row with a primary key which already exists; Primary Key cannot be NULL.

Foreign Key

Provides a way of enforcing referential integrity within SQL Server. Foreign key ensures values in one table must be present in another table.

Rules for Foreign Key

NULL is allowed in Foreign key; the table being referenced is called the Parent Table; the table with the foreign key is called Child Table or dependent table; the foreign key in child table references the primary key in the parent table; this parent-child relationship enforces the rule known as "Referential Integrity."

Identity Column

Used to create a column that contains system-generated sequential values to identify each row inserted into a table. The (seed, increment) values will default to (1,1) if not specified.

Identity Seed

Provides the starting value for an Identity column.

Identity Increment

Defines how an Identity will increase or decrease with each new row added to a table.

Modifying Table

Syntax: ALTER TABLE table_name {[ALTER COLUMN column_name datatype] | ADD {[column definition]} | DROP {[CONSTRAINT] constraint_name | COLUMN column}}.

Alter a Table To Add A New Column

Syntax: ALTER TABLE table_name ADD column_1 datatype [NULL | NOT NULL].

Alter a Table To Drop a Column

Syntax: ALTER TABLE table_name DROP COLUMN column_name.

Alter a Table to Change The Data Type Of A Column

Syntax: ALTER TABLE table_name ALTER COLUMN column_name datatype.

Alter a Table Assign A Constraint In An Existing Column

Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type(column_name).

Renaming Table Name

Use the SQL command sp_rename followed by old table name, then the new table name. Syntax: sp_rename 'old_table_name', 'new_table_name'.

Renaming Column Name of a Table

Use the SQL command sp_rename followed by old column name, then the new column name, and the last parameter is the keyword "column". Syntax: sp_rename 'table_name.old_column_name', 'new_column_name', 'column'.

Dropping a Table

Use the DROP command to drop or delete a table. Syntax: DROP TABLE table_name.

Data Integrity

Refers to the overall accuracy, consistency, and completeness of data. Data integrity is usually imposed during the database design phase through the use of standard procedures and rules, and can be maintained through various error-checking methods and validation procedures.

Categories of Data Integrity

Domain Integrity; Entity Integrity; Referential Integrity.

Domain Integrity

Also known as column integrity, this integrity specifies a set of data values that are valid for a column. Defined by data type, format, data length, nullability, default value and range of allowable values.

Entity Integrity

Also known as row integrity, this integrity requires that all the rows in a table have a unique identifier, enforced by either a PRIMARY KEY or UNIQUE constraint. Every table must have its own primary key and each has to be unique and not null.

Referential Integrity

The concept of foreign keys. A state in which all foreign key values in database are valid. For a foreign key to be valid, it must contain either the value NULL, or an existing key value from the PRIMARY KEY value or UNIQUE KEY columns referenced by the foreign key. The referenced row cannot be deleted if a FOREIGN KEY refers to a row, nor can the key value be changed if a FOREIGN KEY refers to it.

Constraints

Business logic that your database server enforces for you. Used to specify rules for the data in a table and limit the type of data that can go into a table. This ensures the accuracy and reliability of the data. Constraints prevent the deletion of a table if there are dependencies; if there is any violation, the action is aborted.

Constraints Guidelines

The constraint can be created within the CREATE TABLE T-SQL command while creating the table, or added using ALTER TABLE T-SQL command after creating the table. Constraints can be defined at the column level (applied to that column only) or declared independently at the table level (applied to more than one column).

Types of Constraints

Primary Key Constraints; Foreign Key Constraints; Check Constraints; Default Constraints; Unique Constraints.

Primary Key Constraints

Ensures no duplicate values are entered in particular columns and that NULL values are not entered in those columns. If an attempt is made to insert a row of data with duplicate value or NULL value for the primary key, an error message will result and the insert is not allowed.

Foreign Key Constraints

Governs the link between the parent or referenced table and the child or referencing table. Enforces referential integrity between tables. The number and data type of the column in the parent table key must match with the number and data type of the column in the child table.

Unique Constraints

Ensures no duplicate values are entered into specified columns that are not a table's primary key. It enforces uniqueness in a column or combination of columns. You can attach multiple unique constraints to a table, but only one primary key constraint.

Default Constraints

Enables you to define the value that will be supplied for a column whenever a user fails to enter a value.

Check Constraints

Used to enforce domain integrity by restricting the values allowed in a column to specific values. Contains a Boolean expression that causes the database to evaluate whether the value of an inserted or updated record matches the criteria. If the value is not within the allowed set, SQL server will raise an error.

Viewing a Table Constraints

To list available constraints on a table, use the command sp_helpconstraint. Syntax: sp_helpconstraint 'table_name'. The table_name specified must be local to the current database.

Dropping a Constraint

To drop or delete a constraint, alter the table and use the command DROP CONSTRAINT. Syntax: ALTER TABLE table_name DROP CONSTRAINT constraint_name. Note: A constraint cannot be modified or altered — drop the constraint first, then recreate it.